PostgreSQL RLS環境で「読み込みに最適なIndex」と「削除に最適なIndex」は違うのか？実データで検証してみた

PostgreSQL RLS環境におけるIndex戦略の最適化：READ性能とCascade Delete性能のトレードオフ分析

簡単なまとめ
RLS環境下ではREADとCASCADE DELETEで最適なIndex戦略が異なる場合があります。
これはRLS環境のオプティマイザー挙動とCascade Delete処理の仕組みが関係しています。

概要

大規模なSaaSアプリケーションでPostgreSQLのRLSを活用している際、パフォーマンスチューニング過程で興味深い現象を発見しました。RLS用の複合Indexを導入したところ、READ性能は向上したものの、Delete性能(より正確にはCascade Delete性能)はあまり向上しませんでした。

この現象の背景には、PostgreSQLのRLSオプティマイザーが実行する以下の処理特性があります

1. READ処理でのRLSポリシー適用:
   クエリに暗黙的に企業IDフィルターが追加されるため、複合Indexが効果的
2. Cascade Delete処理の特殊性:
   外部キー制約がトリガーレベルで実行され、RLSポリシーを迂回する
3. LEAKPROOFとオプティマイザー:
   関数のLEAKPROOF属性がIndex選択に影響

この現象を体系的に分析するため、実際のプロダクション環境を模した条件下で性能検証を実施し、RLS環境におけるIndex戦略の最適化について知見を得ることができました。

以下、具体的な検証が続きますが まとめ のセクションに 実装における考慮点
を記載したので先にそちらに目を通されても良いかもしれません。

検証環境

システム構成

• PostgreSQL: 16.8
• データ規模: 企業100社、製品100,000件、製品ログ12,000,000件
• RLS構成: 企業IDベースの行レベルセキュリティ

実際のSaaSアプリケーションを想定し、性能差が顕著に現れる1,200万件のデータセットで検証を実施しました。

検証対象:

1. パターンA（基本Index）: 外部キー単体Index
2. パターンB（RLS複合Index）: (company_id, product_id)複合Index

スキーマ設計とRLS実装

データモデル定義

-- UUID拡張を有効化
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 企業テーブル
CREATE TABLE company (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 製品テーブル
CREATE TABLE product (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 製品ログテーブル
CREATE TABLE product_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    product_id UUID NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    company_id UUID NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    log_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Row Level Security実装

-- RLSの有効化
ALTER TABLE company ENABLE ROW LEVEL SECURITY;
ALTER TABLE product ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_log ENABLE ROW LEVEL SECURITY;

-- RLSポリシーの作成
CREATE POLICY company_policy ON company
    FOR ALL
    TO PUBLIC
    USING (id = current_setting('app.current_company_id')::uuid);

CREATE POLICY product_policy ON product
    FOR ALL
    TO PUBLIC
    USING (company_id = current_setting('app.current_company_id')::uuid);

CREATE POLICY product_log_policy ON product_log
    FOR ALL
    TO PUBLIC
    USING (company_id = current_setting('app.current_company_id')::uuid);

検証用ユーザー構成

RLSポリシーの適用を確実にするため、専用のデータベースユーザーを構成します：

-- RLS検証用ユーザーの作成
CREATE USER rls_user WITH PASSWORD 'testpass';

-- 必要な権限を付与
GRANT CONNECT ON DATABASE test TO rls_user;
GRANT USAGE ON SCHEMA public TO rls_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rls_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rls_user;

-- RLSポリシーを有効化（superuserは通常RLSの影響を受けないため）
ALTER TABLE company FORCE ROW LEVEL SECURITY;
ALTER TABLE product FORCE ROW LEVEL SECURITY;
ALTER TABLE product_log FORCE ROW LEVEL SECURITY;

データセット構築

検証に必要な大容量データセットを構築します。

-- 企業データ: 100社
INSERT INTO company (name)
SELECT 'Company ' || i FROM generate_series(1, 100) i;

-- 製品データ: 100,000件（各企業ごとに1,000件）
INSERT INTO product (company_id, name)
SELECT
    c.id,
    'Product ' || generate_series
FROM company c
CROSS JOIN generate_series(1, 1000);

-- 製品ログデータ: 1,200万件（各製品ごとに約120件）
-- 大量データをバッチ処理で効率的に投入
INSERT INTO product_log (product_id, company_id, log_data)
SELECT
    p.id,
    p.company_id,
    'Log data for product ' || p.name || ' entry ' || generate_series
FROM product p
CROSS JOIN generate_series(1, 120);

投入結果の確認：

SELECT 'company' as table_name, count(*) as count FROM company
UNION ALL
SELECT 'product' as table_name, count(*) as count FROM product
UNION ALL
SELECT 'product_log' as table_name, count(*) as count FROM product_log;

 table_name  |   count
-------------+-----------
 company     |       100
 product     |   100,000
 product_log | 12,000,000

データセットの構築が完了しました。

RLSポリシー動作検証

性能測定に先立ち、RLS専用ユーザーでのアクセステストにより、企業ID設定時は該当企業のデータのみアクセス可能で、他企業データは完全に隠蔽されることを確認しました。

RLS専用ユーザーでの接続とセッション設定

# RLS専用ユーザーでPostgreSQLに接続
psql -h localhost -U rls_user -d test

企業A（company_id設定）でのデータアクセス確認

-- 企業Aのセッション設定
SET app.current_company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392';

-- アクセス可能データの確認
SELECT COUNT(*) FROM company;        -- 結果: 1件（企業A分のみ）
SELECT COUNT(*) FROM product;       -- 結果: 1,000件（企業A分のみ）
SELECT COUNT(*) FROM product_log;   -- 結果: 120,000件（企業A分のみ）

他企業データへのアクセス禁止確認

-- 企業Bのセッション設定
SET app.current_company_id = 'aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb';

-- アクセス可能データの確認（該当企業のデータが存在しない場合）
SELECT COUNT(*) FROM company;        -- 結果: 0件
SELECT COUNT(*) FROM product;       -- 結果: 0件
SELECT COUNT(*) FROM product_log;   -- 結果: 0件

superuserでの全データアクセス確認

-- superuserで接続（比較用）
-- psql -h localhost -U test -d test

SELECT COUNT(*) FROM company;        -- 結果: 100件（全企業）
SELECT COUNT(*) FROM product;       -- 結果: 100,000件（全製品）
SELECT COUNT(*) FROM product_log;   -- 結果: 12,000,000件（全ログ）

Index戦略の性能比較

2つの異なるIndex戦略による性能差を定量的に分析します。

パターンA: 基本Index戦略（外部キー単体）

-- 外部キー単体のIndexのみ作成
CREATE INDEX idx_product_company_id_basic ON product(company_id);
CREATE INDEX idx_product_log_product_id_basic ON product_log(product_id);

パターンB: RLS複合Index戦略

-- RLS用複合Indexを作成
CREATE INDEX idx_product_rls_compound ON product(company_id, id);
CREATE INDEX idx_product_log_rls_compound ON product_log(company_id, product_id);

両戦略のREAD性能とCascade Delete性能を定量的に測定します。

READ性能検証

RLS適用環境下での実際の業務クエリ性能を測定します。

検証用データの特定

まず、RLS適用下で検証に使用する企業とその製品を特定します：

-- RLS適用ユーザー（rls_user）で接続してセッション設定
SET app.current_company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392';

-- 検証用製品IDを取得
SELECT id, name FROM product LIMIT 1;
-- 結果例: id = '90eef3a2-778d-4778-aaf9-ac261dfa7a6a', name = 'Product 1'

パターンA（基本Index戦略）でのREAD性能

RLS適用下で特定の製品のログを検索します：

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392';

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM product_log
WHERE product_id = '90eef3a2-778d-4778-aaf9-ac261dfa7a6a'::uuid;

実行結果：

Bitmap Heap Scan on product_log  (cost=7.23..1458.87 rows=4 width=0) (actual time=0.083..0.159 rows=120 loops=1)
Execution Time: 0.214 ms  ⚡ (120件のログを処理)

詳細なEXPLAIN結果（パターンA - product_idでの検索）

Aggregate  (cost=1458.88..1458.89 rows=1 width=8) (actual time=0.171..0.172 rows=1 loops=1)
  Buffers: shared read=5
  ->  Bitmap Heap Scan on product_log  (cost=7.23..1458.87 rows=4 width=0) (actual time=0.083..0.159 rows=120 loops=1)
        Recheck Cond: (product_id = '90eef3a2-778d-4778-aaf9-ac261dfa7a6a'::uuid)
        Filter: (company_id = (current_setting('app.current_company_id'::text))::uuid)
        Heap Blocks: exact=2
        Buffers: shared read=5
        ->  Bitmap Index Scan on idx_product_log_product_id_basic  (cost=0.00..7.23 rows=373 width=0) (actual time=0.059..0.059 rows=120 loops=1)
              Index Cond: (product_id = '90eef3a2-778d-4778-aaf9-ac261dfa7a6a'::uuid)
              Buffers: shared read=3
Planning Time: 0.485 ms
Execution Time: 0.214 ms

パターンB（RLS複合Index戦略）でのREAD性能

同じくRLS適用下で同じ製品のログを検索します：

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392';

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM product_log
WHERE product_id = '90eef3a2-778d-4778-aaf9-ac261dfa7a6a'::uuid;

実行結果：

Index Only Scan using idx_product_log_rls_compound on product_log
Execution Time: 0.124 ms  ⚡ (120件のログを処理)

詳細なEXPLAIN結果（パターンB - product_idでの検索）

Aggregate  (cost=4.53..4.54 rows=1 width=8) (actual time=0.080..0.081 rows=1 loops=1)
  Buffers: shared hit=1 read=3
  ->  Index Only Scan using idx_product_log_rls_compound on product_log  (cost=0.44..4.52 rows=4 width=0) (actual time=0.059..0.069 rows=120 loops=1)
        Index Cond: ((company_id = (current_setting('app.current_company_id'::text))::uuid) AND (product_id = '90eef3a2-778d-4778-aaf9-ac261dfa7a6a'::uuid))
        Heap Fetches: 0
        Buffers: shared hit=1 read=3
Planning Time: 0.520 ms
Execution Time: 0.124 ms

READ性能分析: RLS適用環境下では、product_id検索においてRLS複合Indexが約1.7倍の性能向上を実現。

Cascade Delete性能検証

RLS適用環境下でのCascade Delete処理における性能特性を分析します。

削除前の件数確認

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392';

-- 削除前の件数確認
SELECT COUNT(*) FROM product;       -- 結果: 1,000件
SELECT COUNT(*) FROM product_log;   -- 結果: 120,000件

パターンA（基本Index戦略）でのCascade Delete

RLS適用下で製品10件を削除し、Cascade Deleteでproduct_logも連動削除されます（約1,200件のログが削除される）。

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392';

EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM product
WHERE id IN (
    SELECT id
    FROM product
    WHERE company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392'::uuid
    LIMIT 10
);

実行結果：

Trigger for constraint product_log_product_id_fkey: time=1.726 calls=10
Execution Time: 2.241 ms  ⚡

詳細なEXPLAIN結果（パターンA）

Delete on product  (cost=22.63..106.92 rows=0 width=0) (actual time=0.373..0.375 rows=0 loops=1)
  ->  Nested Loop  (cost=22.63..106.92 rows=1 width=46) (actual time=0.145..0.275 rows=10 loops=1)
        ->  HashAggregate  (cost=22.22..22.32 rows=10 width=56) (actual time=0.119..0.124 rows=10 loops=1)
              ->  Subquery Scan on "ANY_subquery"  (cost=0.01..22.19 rows=10 width=56) (actual time=0.035..0.112 rows=10 loops=1)
                    ->  Limit  (cost=0.01..22.09 rows=10 width=16) (actual time=0.025..0.100 rows=10 loops=1)
                          ->  Result  (cost=0.01..2281.01 rows=1033 width=16) (actual time=0.024..0.098 rows=10 loops=1)
Trigger for constraint product_log_product_id_fkey: time=1.726 calls=10
Execution Time: 2.241 ms

パターンB（RLS複合Index戦略）でのCascade Delete

同じくRLS適用下で製品10件を削除します（約1,200件のログが削除される）。

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392';

EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM product
WHERE id IN (
    SELECT id
    FROM product
    WHERE company_id = 'ccc80d25-986b-4861-8e0a-c8fe54b95392'::uuid
    LIMIT 10
);

実行結果：

Trigger for constraint product_log_product_id_fkey: time=126.666 calls=10
Execution Time: 127.117 ms  🐌

詳細なEXPLAIN結果（パターンB）

Delete on product  (cost=1.72..86.00 rows=0 width=0) (actual time=0.353..0.355 rows=0 loops=1)
  ->  Nested Loop  (cost=1.72..86.00 rows=1 width=46) (actual time=0.114..0.243 rows=10 loops=1)
        ->  HashAggregate  (cost=1.30..1.40 rows=10 width=56) (actual time=0.084..0.088 rows=10 loops=1)
              ->  Subquery Scan on "ANY_subquery"  (cost=0.42..1.27 rows=10 width=56) (actual time=0.072..0.078 rows=10 loops=1)
                    ->  Limit  (cost=0.42..1.17 rows=10 width=16) (actual time=0.063..0.066 rows=10 loops=1)
                          ->  Result  (cost=0.42..78.49 rows=1033 width=16) (actual time=0.061..0.064 rows=10 loops=1)
                                ->  Index Only Scan using idx_product_rls_compound on product product_1  (cost=0.42..78.49 rows=1033 width=16) (actual time=0.058..0.059 rows=10 loops=1)
Trigger for constraint product_log_product_id_fkey: time=126.666 calls=10
Execution Time: 127.117 ms

性能測定結果

Index戦略	READ性能	Cascade Delete性能
パターンA（基本Index）	0.214ms	2.241ms
パターンB（RLS複合Index）	0.124ms	127.117ms

分析結果

RLS環境下において、Index戦略による性能特性に顕著な差異が確認されました：

• READ性能: RLS複合Indexが1.7倍の性能向上（0.124ms vs 0.214ms）
• Cascade Delete性能: 基本Indexが57倍の性能向上（2.241ms vs 127.117ms）
• トレードオフ: RLS複合IndexはREAD性能を最適化する一方、CASCADE DELETE性能が大幅に悪化

すべての測定は、RLSポリシーが適用された実運用環境と同等の条件下で実施されています。

技術的分析

RLSオプティマイザーとLEAKPROOFの影響

本検証結果の理解には、PostgreSQLのRLSメカニズムの詳細な理解が不可欠です：

READ処理におけるRLSポリシー適用

RLS環境下でのREAD処理では、以下のメカニズムが動作します：

1. 暗黙的フィルター追加: PostgreSQLは自動的にcompany_id = current_setting('app.current_company_id')条件を付与
2. 複合Index最適化: RLS複合Index (company_id, product_id)により、両条件が単一Index内で効率処理
3. Index Only Scanの実現: 必要データがIndex内に含まれるため、Heap Fetchesが0となり高速化
4. LEAKPROOFオペレーター: =演算子はLEAKPROOFであるため、セキュリティフィルターとの順序最適化が可能

Cascade Delete処理の特殊性

Cascade Delete処理では、RLSポリシーとは異なる処理経路を辿ります：

1. RLSポリシー迂回: Foreign Key制約トリガーは、RLSポリシーを迂回してシステムレベルで実行
2. 外部キー制約チェック: 大量のproduct_id単体検索が発生（company_id条件なし）
3. Index効率性の逆転:
   • RLS複合Index: 先頭カラム（company_id）なしの検索で非効率
   • 基本Index: product_id直接検索で最適
4. トリガー処理オーバーヘッド: 基本Index 1.726ms vs RLS複合Index 126.666ms

PostgreSQLオプティマイザーの動作特性

この性能差は、PostgreSQLの以下の設計思想に起因します：

• セキュリティ優先: RLSポリシーは常にユーザークエリレベルで適用
• 制約処理の分離: Foreign Key制約はデータベースシステムレベルで処理
• 最適化の競合: セキュリティポリシーと制約処理で異なる最適化戦略が必要

設計への示唆

RLS環境では、READ性能とCASCADE DELETE性能で最適なIndex戦略が対立する構造的な問題が存在します。これはPostgreSQLのアーキテクチャに起因する本質的な課題であり、従来のIndex設計手法では解決できません。

実装指針

アプリケーション特性別戦略

READ性能重視型アプリケーション

-- RLS複合Indexを優先
CREATE INDEX idx_product_rls_compound ON product(company_id, id);
CREATE INDEX idx_product_log_rls_compound ON product_log(company_id, product_id);

-- 必要に応じて基本Indexを併用
CREATE INDEX idx_product_log_product_id ON product_log(product_id);

データライフサイクル管理重視型アプリケーション

-- Cascade Delete性能を最優先
CREATE INDEX idx_product_log_product_id ON product_log(product_id);
CREATE INDEX idx_product_company_id ON product(company_id);

ハイブリッド型アプリケーション

-- 両戦略のIndexを併用（ストレージコストとのトレードオフ）
CREATE INDEX idx_product_company_id ON product(company_id);
CREATE INDEX idx_product_log_company_id ON product_log(company_id);
CREATE INDEX idx_product_log_product_id ON product_log(product_id);
CREATE INDEX idx_product_log_rls_compound ON product_log(company_id, product_id);

まとめ

主要な知見

本検証により、RLS環境における以下の重要な知見が得られました：

• READ性能: RLS複合Indexが1.7倍の性能向上を実現
  • RLSポリシーによる暗黙的フィルターとLEAKPROOFオペレーターの最適化効果
• Cascade Delete性能: 基本Indexが57倍の性能向上を実現
  • Foreign Key制約処理がRLSポリシーを迂回する設計に起因
• 設計原則: PostgreSQLのアーキテクチャレベルでの処理分離により、用途別Index戦略が必要

実装における考慮点

1. データライフサイクル: 大量削除処理が頻繁な場合、外部キー単体Indexは必須
2. クエリパターン: READ性能重視の場合、RLS複合Indexが効果的
3. リソース配分: 両方の性能が求められる場合、複数Index併用も選択肢

結論

本検証により、PostgreSQLのRLS環境では、セキュリティポリシー適用層と制約処理層の分離により、従来のIndex設計理論が通用しない構造的な課題が存在することが明らかになりました。

LEAKPROOFオペレーターの最適化効果とCascade Delete処理のRLSポリシー迂回という、PostgreSQLアーキテクチャの根本的な特性を理解した上で、アプリケーションの性能要件に応じた適切なIndex戦略を選択すること重要です。

免責事項

• 本検証は特定環境での結果であり、実際の適用時は各環境での統計的な検証を推奨します
• 本記事はLLMと手書きを併用して作成しました
  (今回のように大局的な情報共有が目的であれば、記事を書くのが楽になりましたね)

参考資料

• PostgreSQL Row Level Security - 公式ドキュメント: RLSの概要
• PostgreSQL Index Performance - 公式ドキュメント: Indexの作成と性能
• PostgreSQLの行レベルセキュリティ(RLS)が実行計画に与える影響に関する記事

Appendix: 複合プライマリーキーでの性能検証

この記事の本文を読んでいただいた同僚でもあるDBREチームのnnaka2992さんから、「ケースにもよるがもしかするとプライマリーキーを複合ユニークキーにすると自然と削除性能も向上するかもしれない」といったお話をいただき、実際に以下で簡単に検証してみました。
（nnaka2992さんコメントありがとうございました）

検証の目的：

1. 複合PKによるセキュリティ強化: RLS迂回時でもPK制約レベルでの保護効果
2. PKカラム順序の性能影響: (company_id, id) vs (id, company_id) の性能差
3. Index統合効果: 複合PKにより追加のRLS用Indexが不要になるかの検証

検証方針：
同一のデータ構造・データ量で、複合PKの2パターンを比較し、元のパターンA/Bとの性能差を定量的に分析します。

検証環境構築

複合プライマリーキーでは、カラムの順序によって異なる性能特性が期待されます。組織ID優先では企業単位での検索が最適化され、エンティティID優先では個別エンティティへの直接アクセスが最適化されます。

複合PK（組織ID優先）パターン

-- UUID拡張を有効化
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 企業テーブル（変更なし）
CREATE TABLE company_composite_c (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 製品テーブル（複合PK: company_id, id）
CREATE TABLE product_composite_c (
    company_id UUID NOT NULL REFERENCES company_composite_c(id) ON DELETE CASCADE,
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (company_id, id)
);
-- 製品ログテーブル（複合PK: company_id, id）
CREATE TABLE product_log_composite_c (
    company_id UUID NOT NULL REFERENCES company_composite_c(id) ON DELETE CASCADE,
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    product_id UUID NOT NULL,
    log_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (company_id, id),
    FOREIGN KEY (company_id, product_id) REFERENCES product_composite_c(company_id, id) ON DELETE CASCADE
);

複合PK（エンティティID優先）パターン

-- 企業テーブル（変更なし）
CREATE TABLE company_composite_d (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 製品テーブル（複合PK: id, company_id）
CREATE TABLE product_composite_d (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES company_composite_d(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, company_id)
);
-- 製品ログテーブル（複合PK: id, company_id）
CREATE TABLE product_log_composite_d (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    company_id UUID NOT NULL REFERENCES company_composite_d(id) ON DELETE CASCADE,
    product_id UUID NOT NULL,
    log_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, company_id),
    FOREIGN KEY (product_id, company_id) REFERENCES product_composite_d(id, company_id) ON DELETE CASCADE
);

RLS設定

複合PK（組織ID優先）パターン用RLS設定

-- RLSの有効化
ALTER TABLE company_composite_c ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_composite_c ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_log_composite_c ENABLE ROW LEVEL SECURITY;
-- RLS検証用ユーザーの作成（既存の場合はスキップ）
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'rls_user') THEN
        CREATE USER rls_user WITH PASSWORD 'testpass';
        GRANT CONNECT ON DATABASE test TO rls_user;
        GRANT USAGE ON SCHEMA public TO rls_user;
        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rls_user;
        GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rls_user;
    END IF;
END
$$;
-- RLSポリシーの作成
CREATE POLICY company_policy_composite_c ON company_composite_c
    FOR ALL
    TO PUBLIC
    USING (id = current_setting('app.current_company_id')::uuid);
CREATE POLICY product_policy_composite_c ON product_composite_c
    FOR ALL
    TO PUBLIC
    USING (company_id = current_setting('app.current_company_id')::uuid);
CREATE POLICY product_log_policy_composite_c ON product_log_composite_c
    FOR ALL
    TO PUBLIC
    USING (company_id = current_setting('app.current_company_id')::uuid);
-- RLSポリシーを強制適用
ALTER TABLE company_composite_c FORCE ROW LEVEL SECURITY;
ALTER TABLE product_composite_c FORCE ROW LEVEL SECURITY;
ALTER TABLE product_log_composite_c FORCE ROW LEVEL SECURITY;

複合PK（エンティティID優先）パターン用RLS設定

-- RLSの有効化
ALTER TABLE company_composite_d ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_composite_d ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_log_composite_d ENABLE ROW LEVEL SECURITY;
-- RLSポリシーの作成
CREATE POLICY company_policy_composite_d ON company_composite_d
    FOR ALL
    TO PUBLIC
    USING (id = current_setting('app.current_company_id')::uuid);
CREATE POLICY product_policy_composite_d ON product_composite_d
    FOR ALL
    TO PUBLIC
    USING (company_id = current_setting('app.current_company_id')::uuid);
CREATE POLICY product_log_policy_composite_d ON product_log_composite_d
    FOR ALL
    TO PUBLIC
    USING (company_id = current_setting('app.current_company_id')::uuid);
-- RLSポリシーを強制適用
ALTER TABLE company_composite_d FORCE ROW LEVEL SECURITY;
ALTER TABLE product_composite_d FORCE ROW LEVEL SECURITY;
ALTER TABLE product_log_composite_d FORCE ROW LEVEL SECURITY;

データセット構築

重要: 両パターンで同一のデータ規模・分布を使用し、元記事と比較可能な条件を維持します。

-- 各パターン共通のデータ投入手順
-- 1. 複合PK（組織ID優先）パターン
-- 企業データ: 100社
INSERT INTO company_composite_c (name)
SELECT 'Company ' || i FROM generate_series(1, 100) i;
-- 製品データ: 100,000件（各企業ごとに1,000件）
INSERT INTO product_composite_c (company_id, name)
SELECT
    c.id,
    'Product ' || generate_series
FROM company_composite_c c
CROSS JOIN generate_series(1, 1000);
-- 製品ログデータ: 1,200万件（各製品ごとに約120件）
INSERT INTO product_log_composite_c (company_id, product_id, log_data)
SELECT
    p.company_id,
    p.id,
    'Log data for product ' || p.name || ' entry ' || generate_series
FROM product_composite_c p
CROSS JOIN generate_series(1, 120);
-- 2. 複合PK（エンティティID優先）パターン
-- 同様の手順でcompany_composite_d、product_composite_d、product_log_composite_dにデータ投入

データ投入結果の確認

元記事と同様に、RLS適用下と非適用下でのデータアクセスを確認し、適切にデータが投入されていることを検証します。

投入データ量の確認

-- superuserでの全データ確認
SELECT 'company_composite_c' as table_name, count(*) as count FROM company_composite_c
UNION ALL
SELECT 'product_composite_c' as table_name, count(*) as count FROM product_composite_c
UNION ALL
SELECT 'product_log_composite_c' as table_name, count(*) as count FROM product_log_composite_c
UNION ALL
SELECT 'company_composite_d' as table_name, count(*) as count FROM company_composite_d
UNION ALL
SELECT 'product_composite_d' as table_name, count(*) as count FROM product_composite_d
UNION ALL
SELECT 'product_log_composite_d' as table_name, count(*) as count FROM product_log_composite_d;

結果：

     table_name      |   count
---------------------+-----------
 company_composite_c |       100
 product_composite_c |   100,000
 product_log_composite_c | 12,000,000
 company_composite_d |       100
 product_composite_d |   100,000
 product_log_composite_d | 12,000,000

RLS動作確認

-- RLS専用ユーザー（rls_user）で接続してセッション設定
-- psql -h localhost -U rls_user -d test
-- Password: testpass
-- 企業IDを設定（例：最初の企業）
SET app.current_company_id = (SELECT id FROM company_composite_c LIMIT 1);
-- RLS適用下でのアクセス確認
SELECT COUNT(*) FROM company_composite_c;        -- 結果: 1件（設定企業分のみ）
SELECT COUNT(*) FROM product_composite_c;       -- 結果: 1,000件（設定企業分のみ）
SELECT COUNT(*) FROM product_log_composite_c;   -- 結果: 120,000件（設定企業分のみ）
-- 他企業データへのアクセス禁止確認
SET app.current_company_id = 'aaaaaaaa-1111-2222-3333-bbbbbbbbbbbb'::uuid;
SELECT COUNT(*) FROM company_composite_c;        -- 結果: 0件
SELECT COUNT(*) FROM product_composite_c;       -- 結果: 0件
SELECT COUNT(*) FROM product_log_composite_c;   -- 結果: 0件

性能検証結果

READ性能比較

Index戦略	READ性能	実行計画
パターンA（基本Index）	0.214ms	Bitmap Heap Scan
パターンB（RLS複合Index）	0.124ms	Index Only Scan
複合PK（組織ID優先）パターン	0.876ms	Bitmap Heap Scan
複合PK（エンティティID優先）パターン	7.216ms	Sequential Scan

複合PK（組織ID優先）パターンでのREAD性能

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = 'e2d64b73-c9e7-4d66-a74e-9a637fa6b01f';
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM product_log_composite_c
WHERE product_id = '004e809f-ec14-4984-ac14-25f6e4ee05b2'::uuid;

実行結果：

Bitmap Heap Scan on product_log_composite_c
Execution Time: 0.876 ms  ⚡ (0件のログを処理)

詳細なEXPLAIN結果（複合PK: 組織ID優先パターン）

Aggregate  (cost=1495.57..1495.58 rows=1 width=8) (actual time=0.834..0.834 rows=1 loops=1)
  Buffers: shared hit=117
  ->  Bitmap Heap Scan on product_log_composite_c  (cost=39.90..1495.56 rows=1 width=0) (actual time=0.831..0.831 rows=0 loops=1)
        Recheck Cond: (company_id = (current_setting('app.current_company_id'::text))::uuid)
        Filter: (product_id = '004e809f-ec14-4984-ac14-25f6e4ee05b2'::uuid)
        Rows Removed by Filter: 1000
        Heap Blocks: exact=108
        Buffers: shared hit=117
        ->  Bitmap Index Scan on product_log_composite_c_pkey  (cost=0.00..39.90 rows=997 width=0) (actual time=0.114..0.114 rows=1000 loops=1)
              Index Cond: (company_id = (current_setting('app.current_company_id'::text))::uuid)
              Buffers: shared hit=9
Planning Time: 0.366 ms
Execution Time: 0.876 ms

複合PK（エンティティID優先）パターンでのREAD性能

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = '36d52f72-0ae2-4c17-ade6-11a5a4ebef9f';
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM product_log_composite_d
WHERE product_id = '1d310d91-da5f-4fdd-9e58-1f2f6ea90aeb'::uuid;

実行結果：

Sequential Scan on product_log_composite_d
Execution Time: 7.216 ms  🐌 (0件のログを処理)

詳細なEXPLAIN結果（複合PK: エンティティID優先パターン）

Aggregate  (cost=3790.00..3790.01 rows=1 width=8) (actual time=7.174..7.174 rows=1 loops=1)
  Buffers: shared hit=1540 dirtied=11
  ->  Seq Scan on product_log_composite_d  (cost=0.00..3790.00 rows=1 width=0) (actual time=7.171..7.171 rows=0 loops=1)
        Filter: ((product_id = '1d310d91-da5f-4fdd-9e58-1f2f6ea90aeb'::uuid) AND (company_id = (current_setting('app.current_company_id'::text))::uuid))
        Rows Removed by Filter: 99900
        Buffers: shared hit=1540 dirtied=11
Planning Time: 0.419 ms
Execution Time: 7.216 ms

READ性能分析: 複合PKでは、カラム順序がRLSポリシーと一致する組織ID優先パターンがBitmap Heap Scanで効率的に処理される一方、エンティティID優先パターンは複合PKの先頭カラムがRLSフィルターと不一致のため、Sequential Scanとなり性能が大幅に劣化。

Cascade Delete性能比較

Index戦略	Cascade Delete性能	トリガー時間
パターンA（基本Index）	2.241ms	1.726ms
パターンB（RLS複合Index）	127.117ms	126.666ms
複合PK（組織ID優先）パターン	2.520ms	2.064ms
複合PK（エンティティID優先）パターン	23.048ms	21.672ms

複合PK（組織ID優先）パターンでのCascade Delete性能

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = 'e2d64b73-c9e7-4d66-a74e-9a637fa6b01f';
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM product_composite_c
WHERE (company_id, id) IN (
    SELECT company_id, id
    FROM product_composite_c
    WHERE company_id = current_setting('app.current_company_id')::uuid
    LIMIT 5
);

実行結果：

Trigger for constraint product_log_composite_c_company_id_product_id_fkey: time=2.064 calls=5
Execution Time: 2.520 ms  ⚡

詳細なEXPLAIN結果（複合PK: 組織ID優先パターン）

Delete on product_composite_c  (cost=1.20..9.25 rows=0 width=0) (actual time=0.348..0.349 rows=0 loops=1)
  Buffers: shared hit=52 dirtied=5
  ->  Nested Loop  (cost=1.20..9.25 rows=1 width=62) (actual time=0.203..0.242 rows=5 loops=1)
        Buffers: shared hit=34
        ->  HashAggregate  (cost=0.78..0.79 rows=1 width=88) (actual time=0.185..0.188 rows=5 loops=1)
              Group Key: "ANY_subquery".company_id, "ANY_subquery".id
              Batches: 1  Memory Usage: 24kB
              Buffers: shared hit=14
              ->  Subquery Scan on "ANY_subquery"  (cost=0.42..0.77 rows=1 width=88) (actual time=0.172..0.178 rows=5 loops=1)
                    Filter: ("ANY_subquery".company_id = (current_setting('app.current_company_id'::text))::uuid)
                    Buffers: shared hit=14
                    ->  Limit  (cost=0.42..0.67 rows=5 width=32) (actual time=0.160..0.161 rows=5 loops=1)
                          Buffers: shared hit=14
                          ->  Index Only Scan using product_composite_c_pkey on product_composite_c product_composite_c_1  (cost=0.42..50.05 rows=1007 width=32) (actual time=0.158..0.159 rows=5 loops=1)
                                Index Cond: (company_id = (current_setting('app.current_company_id'::text))::uuid)
                                Heap Fetches: 10
                                Buffers: shared hit=14
        ->  Index Scan using product_composite_c_pkey on product_composite_c  (cost=0.42..8.45 rows=1 width=38) (actual time=0.009..0.009 rows=1 loops=5)
              Index Cond: ((company_id = (current_setting('app.current_company_id'::text))::uuid) AND (id = "ANY_subquery".id))
              Buffers: shared hit=20
Planning Time: 0.741 ms
Trigger for constraint product_log_composite_c_company_id_product_id_fkey: time=2.064 calls=5
Execution Time: 2.520 ms

複合PK（エンティティID優先）パターンでのCascade Delete性能

-- RLS適用セッションでの実行（rls_user）
SET app.current_company_id = '36d52f72-0ae2-4c17-ade6-11a5a4ebef9f';
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM product_composite_d
WHERE (id, company_id) IN (
    SELECT id, company_id
    FROM product_composite_d
    WHERE company_id = current_setting('app.current_company_id')::uuid
    LIMIT 5
);

実行結果：

Trigger for constraint product_log_composite_d_product_id_company_id_fkey: time=21.672 calls=5
Execution Time: 23.048 ms  🐌

詳細なEXPLAIN結果（複合PK: エンティティID優先パターン）

Delete on product_composite_d  (cost=15.99..24.04 rows=0 width=0) (actual time=1.235..1.237 rows=0 loops=1)
  Buffers: shared hit=53 dirtied=16
  ->  Nested Loop  (cost=15.99..24.04 rows=1 width=62) (actual time=1.060..1.112 rows=5 loops=1)
        Buffers: shared hit=35 dirtied=10
        ->  HashAggregate  (cost=15.57..15.58 rows=1 width=88) (actual time=1.026..1.030 rows=5 loops=1)
              Group Key: "ANY_subquery".id, "ANY_subquery".company_id
              Batches: 1  Memory Usage: 24kB
              Buffers: shared hit=15 dirtied=10
              ->  Subquery Scan on "ANY_subquery"  (cost=0.00..15.56 rows=1 width=88) (actual time=0.708..1.017 rows=5 loops=1)
                    Filter: ("ANY_subquery".company_id = (current_setting('app.current_company_id'::text))::uuid)
                    Buffers: shared hit=15 dirtied=10
                    ->  Limit  (cost=0.00..15.46 rows=5 width=32) (actual time=0.698..1.001 rows=5 loops=1)
                          Buffers: shared hit=15 dirtied=10
                          ->  Seq Scan on product_composite_d product_composite_d_1  (cost=0.00..3031.00 rows=980 width=32) (actual time=0.697..0.999 rows=5 loops=1)
                                Filter: (company_id = (current_setting('app.current_company_id'::text))::uuid)
                                Rows Removed by Filter: 1386
                                Buffers: shared hit=15 dirtied=10
        ->  Index Scan using product_composite_d_pkey on product_composite_d  (cost=0.42..8.45 rows=1 width=38) (actual time=0.014..0.015 rows=1 loops=5)
              Index Cond: ((id = "ANY_subquery".id) AND (company_id = (current_setting('app.current_company_id'::text))::uuid))
              Buffers: shared hit=20
Planning Time: 0.812 ms
Trigger for constraint product_log_composite_d_product_id_company_id_fkey: time=21.672 calls=5
Execution Time: 23.048 ms

CASCADE DELETE性能分析: 組織ID優先パターンは複合PKと削除対象の検索パターンが一致するためIndex Only Scanで高効率。エンティティID優先パターンはSequential Scanが発生し、外部キー制約のトリガー処理時間も大幅に増加。

検証結果の分析

複合プライマリーキーの検証により、以下の重要な知見が得られました：

主要な発見

1. PKカラム順序の重大な性能影響:
   • 組織ID優先: READ性能はパターンBに劣るが（0.876ms vs 0.124ms）、CASCADE DELETE性能は良好（2.520ms）
   • エンティティID優先: READ性能が大幅に悪化（7.216ms）、CASCADE DELETE性能も悪い（23.048ms）
2. RLSクエリ最適化の重要性:
   • 組織ID優先パターンは、RLSポリシーのcompany_id条件と複合PKの先頭カラムが一致するため、Bitmap Heap Scanで効率的
   • エンティティID優先パターンは、先頭カラムがidのため、RLSフィルターが効かずSequential Scanに
3. CASCADE DELETE処理の最適化:
   • 組織ID優先パターンは、外部キー制約の検索でも先頭カラムが活用され、トリガー時間が抑制される
   • エンティティID優先パターンは、複合FK制約の順序が悪影響

セキュリティ vs 性能のトレードオフ

複合PK採用のメリット:

• RLS迂回時でもPK制約レベルでのデータ整合性保証
• カラム順序を適切に設計すれば、実用的な性能を維持可能

設計指針:

• RLS環境では組織ID優先の複合PKが推奨: PRIMARY KEY (company_id, id)
• エンティティID優先は避けるべき: RLSポリシーとの不整合により性能劣化

パターン別推奨用途

以下はLLMがこの記事の内容を読んで提案してくれた用途別のまとめです。
例外もあるので掲載するか迷いましたが参考程度に掲載しておきます。
(個人的に性能要件が厳しくない場合は三つ目の「複合PK（組織ID優先）パターン」は悪くない選択肢かもしれないとも思います。実際の結果をみると以下表よりもそれぞれ1段階くらい上の性能が出ている印象です)

パターン	READ性能	DELETE性能	推奨用途
本文のパターンA（基本Index）	○	◎	データライフサイクル管理重視
本文のパターンB（RLS複合Index）	◎	△	READ性能重視
Appendix複合PK（組織ID優先）パターン	△	○	セキュリティ強化 + バランス型
Appendix複合PK（エンティティID優先）パターン	×	×	非推奨