はじめに

PostgreSQLを使ったサービスを開発しています。そのサービスではマルチテナントで利用されることを想定しています。セキュリティ対応としてROW Level セキュリティを使用する方針で設計・開発を行っていましたが、パフォーマンス評価を行ったときに想定外のことが起きたので記事にしました。

2024年3月にPostgreSQL14で確認した内容です。
投稿時点ではPostgreSQL16でも同じ現象が発生するはずです。

何がおきたのか？

ROW Level セキュリティを有効にしたとき、numeric項目でインデックスが有効にならず、パフォーマンス劣化が発生しました。
インデックスを定義したい項目でnumericを使うなと思われる方もいるかもしれませんが、シーケンスから発番された値を主キーとしたい要件がありました。
また、ロジック側では整数しか扱わない項目でもnumericで定義することになっていました。
さらに言い訳をすると、そのサービスはOracleも扱えるようになっており、数値はOracleではnumber型、PostgreSQLではnumeric型を使用する方針にしていました。

検証データ

検証で使用したテーブル定義などを記載します。

テーブル定義

CREATE TABLE table1
(
key1 numeric (15,0) NOT NULL,
val1 varchar  NOT NULL,
tenant_id varchar NOT NULL
)
;

ALTER TABLE table1
ADD CONSTRAINT table1_pk PRIMARY KEY(key1,tenant_id)
;

Row Level セキュリティ設定

CREATE POLICY table1_policy ON table1 TO PUBLIC USING (tenant_id = 'test');

ALTER TABLE table1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE table1 FORCE ROW LEVEL SECURITY;

データ

insert into table1 values (1, '1', 'test') ;
insert into table1 values (2, '2', 'test') ;
insert into table1 values (3, '3', 'test') ;
insert into table1 values (4, '4', 'test') ;
insert into table1 values (5, '5', 'test') ;
insert into table1 values (6, '6', 'test') ;
insert into table1 values (7, '7', 'test') ;
insert into table1 values (8, '8', 'test') ;
insert into table1 values (9, '9', 'test') ;
insert into table1 values (10, '10', 'test') ;

上記のテーブルについてkey1,tenant_idに対してval1を更新する際にPRIMARY KEYでのインデックスが意図した動作にならない事象が発生しました。

検証SQL

EXPLAIN ANALYZE
update
/*+ IndexScan(table1 table1_pk) */
table1 set val1 = '1'
where 0=0
and key1 = 1
;

上記で実行計画を確認すると以下になりました。
IndexScanのヒント句を指定しないとシーケンシャルスキャンになりました。
そのため、「table1_pk」が使われるようにヒント句を指定しています。

実行結果

Update on table1  (cost=0.14..2.62 rows=0 width=0) (actual time=0.061..0.062 rows=0 loops=1)
   ->  Index Scan using table1_pk on table1  (cost=0.14..2.62 rows=1 width=38) (actual time=0.018..0.021 rows=1 loops=1)
         Index Cond: ((tenant_id)::text = 'test'::text)
         Filter: (key1 = '1'::numeric)
         Rows Removed by Filter: 9

ヒント句を指定してもkey1がFilterで評価されるため、大量データが格納されているときにパフォーマンス劣化が発生しました。
ROW Level セキュリティを無効にすると以下の実行結果になります。

実行結果（ROW Level セキュリティ：無効）

 Update on table1  (cost=0.14..1.75 rows=0 width=0) (actual time=0.064..0.064 rows=0 loops=1)
   ->  Index Scan using table1_pk on table1  (cost=0.14..1.75 rows=1 width=38) (actual time=0.013..0.013 rows=1 loops=1)
         Index Cond: (key1 = '1'::numeric)

インデックスが有効にならない原因

numeric型の等価評価を行う関数（numeric_eq）が、leakproofではないことが原因でした。
leakproofについては以下を参考にしてください。

LEAKPROOFは、関数が副作用を持たないことを示します。その引数に関する情報を戻り値以外で漏らしません。例えば、一部の引数値に対してのみエラーメッセージを返す関数や何らかのエラーメッセージの中に引数の値を含める関数は漏洩防止(leakproof)とはいえません。これはsecurity_barrierオプション付きで作成されたビュー、あるいは行単位セキュリティが有効にされたテーブルに対して、システムが問い合わせを実行する方法に影響します。データが偶然に露見することを防ぐため、システムは、漏洩防止でない関数を含む問い合わせのユーザが提供した条件より前に、セキュリティポリシーおよびセキュリティバリアビューの条件を強制します。漏洩防止であるとされた関数および演算子は信頼できると見なされ、セキュリティポリシーおよびセキュリティバリアビューによる条件より先に実行されることがあります。なお、引数を取らない、あるいはセキュリティバリアビューやテーブルから引数を渡されない関数は、セキュリティ条件より前に実行するために漏洩防止とする必要はありません。

上記により、ROW Level セキュリティが有効になっているとnumeric項目はインデックスが有効にならないようです。int型やbigint型では問題ありませんでした。
なお、numeric型の関数がleakproofではないことに関するコメントもされているようですが、現時点では変更されていないようです。

まとめ

PostgreSQLでROW Levelセキュリティ使用したときは、numeric型がleakproofではないのでインデックスが効きません。
なんでnumeric型だけleakproofではないのだ！と言っていてもしょうがないので、bigintで定義するようにアプリケーションを変更する予定です。PostgreSQLとして対応してほしいとも思っていますが、その予定もなさそう・・・
シーケンスをもとに主キーの値を設定するようなテーブル設計を行っている場合は、注意が必要です。

PostgreSQL：ROW Levelセキュリティ使用したときに numeric 型にはインデックスが効かない

はじめに

何がおきたのか？

検証データ

インデックスが有効にならない原因

まとめ