postgresql-best-practices

PostgreSQL 高度なベストプラクティス（PostgreSQL 18 以降）

アーキテクチャの概要

                        ┌─── PostgreSQL Database ──────────────────────────────┐
                        │                                                      │
                        │  ┌──────────────────┐    ┌───────────────────────┐   │
                        │  │   api schema      │    │   private schema      │   │
  ┌─────────────┐       │  │──────────────────│    │───────────────────────│   │
  │ Application │─EXECUTE─▶│ get_customer()   │───▶│ set_updated_at()     │   │
  └─────────────┘       │  │ insert_order()   │    │ hash_password()      │   │
        │               │  └────────┬─────────┘    └──────────┬────────────┘   │
        │               │           │                         │                │
        │               │           │ SECURITY DEFINER        │ triggers       │
        │               │           ▼                         ▼                │
        │               │  ┌──────────────────────────────────────────────┐    │
        │               │  │              data schema                     │    │
     BLOCKED            │  │──────────────────────────────────────────────│    │
        │               │  │  customers    orders    ...                  │    │
        └ ─ ─ ─ ✕       │  └──────────────────────────────────────────────┘    │
                        │                                                      │
                        └──────────────────────────────────────────────────────┘

スキルの内容

🚀 はじめに（まずお読みください）

ドキュメント	目的
quick-reference.md	すぐに確認できるリファレンス - 1 ページのチートシート（印刷してお使いください！）
schema-architecture.md	まずはこちら - スキーマ分離パターン（data/private/api）
coding-standards-trivadis.md	コーディング規約と命名規則（l_、g_、co_）

📚 コアリファレンス（日常的に使用）

ドキュメント	目的
plpgsql-table-api.md	テーブル API 関数、プロシージャ、トリガー
schema-naming.md	すべてのオブジェクトの命名規則
data-types.md	データ型の選択（UUIDv7、text、timestamptz）
indexes-constraints.md	インデックスの種類、戦略、制約
migrations.md	ネイティブマイグレーションシステムのドキュメント
anti-patterns.md	避けるべきよくある間違い
checklists-troubleshooting.md	プロジェクトのチェックリストと問題の解決方法

🔧 高度なトピック（必要に応じて）

ドキュメント	目的
testing-patterns.md	pgTAP によるユニットテスト、テストファクトリー
performance-tuning.md	EXPLAIN ANALYZE、クエリ最適化、JIT
row-level-security.md	RLS パターン、マルチテナント分離
jsonb-patterns.md	JSONB のインデックス作成、クエリ、検証
audit-logging.md	汎用監査トリガー、変更の追跡
bulk-operations.md	COPY、バッチ挿入、アップサート
session-management.md	セッション変数、コネクションプーリング
transaction-patterns.md	分離レベル、ロック、デッドロックの防止
full-text-search.md	tsvector、tsquery、ランキング、多言語対応
partitioning.md	レンジ、リスト、ハッシュによるパーティショニング戦略
window-functions.md	フレーム、ランキング、累積計算
time-series.md	時系列データのパターン、BRIN インデックス
event-sourcing.md	イベントストア、プロジェクション、CQRS
queue-patterns.md	ジョブキュー、SKIP LOCKED、LISTEN/NOTIFY
encryption.md	pgcrypto、列の暗号化、TLS
vector-search.md	pgvector、埋め込み、類似検索
postgis-patterns.md	空間データ、地理クエリ

🚀 DevOps とマイグレーション

ドキュメント	目的
oracle-migration-guide.md	PL/SQL から PL/pgSQL への変換
cicd-integration.md	GitHub Actions、GitLab CI、Docker
monitoring-observability.md	pg_stat_statements、メトリクス、アラート
backup-recovery.md	pg_dump、pg_basebackup、PITR
replication-ha.md	ストリーミング／論理レプリケーション、フェイルオーバー

📊 データウェアハウジング

ドキュメント	目的
data-warehousing-medallion.md	メダリオンアーキテクチャ - Bronze／Silver／Gold、データリネージ、ETL
analytical-queries.md	分析クエリのパターン、OLAP の最適化、GROUPING SETS

実行可能なスクリプト

スクリプト	用途
001_install_migration_system.sql	マイグレーションシステムをインストールします（コア関数）
002_migration_runner_helpers.sql	ヘルパープロシージャ（run_versioned、run_repeatable）
003_example_migrations.sql	マイグレーションパターンの例
999_uninstall_migration_system.sql	マイグレーションシステムを完全に削除します

---

コアアーキテクチャ

スキーマ分離パターン

Application → api schema → data schema
                ↓
            private schema (triggers, helpers)

スキーマ	内容	アクセス	用途
data	テーブル、インデックス	なし	データストレージ
private	トリガー、ヘルパー	なし	内部ロジック
api	関数、プロシージャ	アプリケーション	外部インターフェース
app_audit	監査テーブル	管理者	変更追跡
app_migration	マイグレーション追跡	管理者	スキーマのバージョン管理

セキュリティモデル

すべてのapi関数には、次の設定が必須です。

SECURITY DEFINER
SET search_path = data, private, pg_temp

---

クイックリファレンス

テーブル作成パターン

CREATE TABLE data.{table_name} (
    id              uuid PRIMARY KEY DEFAULT uuidv7(),
    -- columns...
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER {table}_bu_updated_trg
    BEFORE UPDATE ON data.{table_name}
    FOR EACH ROW EXECUTE FUNCTION private.set_updated_at();

API関数パターン

CREATE FUNCTION api.{action}_{entity}(in_param type)
RETURNS TABLE (col1 type, col2 type)
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
    SELECT col1, col2 FROM data.{table} WHERE ...;
$$;

APIプロシージャパターン

CREATE PROCEDURE api.{action}_{entity}(
    in_param type,
    INOUT io_id uuid DEFAULT NULL
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
BEGIN
    INSERT INTO data.{table} (...) VALUES (...) RETURNING id INTO io_id;
END;
$$;

マイグレーションパターン

SELECT app_migration.acquire_lock();

CALL app_migration.run_versioned(
    in_version := '001',
    in_description := 'Description',
    in_sql := $mig$ ... $mig$,
    in_rollback_sql := '...'
);

SELECT app_migration.release_lock();

---

命名規則

Trivadisスタイルの変数プレフィックス

プレフィックス	型	例
l_	ローカル変数	l_customer_count
g_	セッション／グローバル変数	g_current_user_id
co_	定数	co_max_retries
in_	INパラメーター	in_customer_id
out_	OUTパラメーター（関数のみ）	out_total
io_	INOUTパラメーター（プロシージャ）	io_id
c_	カーソル	c_active_orders
r_	レコード	r_customer
t_	配列／テーブル	t_order_ids
e_	例外	e_not_found

Note：PostgreSQLのプロシージャがサポートするのはINOUTパラメーターのみで、OUTはサポートされません。プロシージャのすべての出力パラメーターには、io_プレフィックスを使用してください。

データベースオブジェクト

オブジェクト	パターン	例
テーブル	snake_case、複数形	orders、order_items
列	snake_case	customer_id、created_at
主キー	id	id
外部キー	{table_singular}_id	customer_id
インデックス	{table}_{cols}_idx	orders_customer_id_idx
一意制約	{table}_{cols}_key	users_email_key
関数	{action}_{entity}	get_customer、select_orders
プロシージャ	{action}_{entity}	insert_order、update_status
トリガー	{table}_{timing}{event}_trg	orders_bu_trg

---

データ型の推奨事項

使用する型	代わりに使用しない型
text	char(n)、varchar(n)
numeric(p,s)	money、float
timestamptz	timestamp
boolean	integerフラグ
uuidv7()	serial、uuid_generate_v4()
GENERATED ALWAYS AS IDENTITY	serial、bigserial
jsonb	json、EAVパターン

---

重大なアンチパターン

1. ❌ アプリケーションからテーブルに直接アクセスする
2. ❌ RETURNS SETOF table（すべての列を公開します）
3. ❌ SECURITY DEFINERでSET search_pathを設定しない
4. ❌ タイムゾーンなしのtimestamp
5. ❌ サブクエリでNOT INを使う（NOT EXISTSを使用してください）
6. ❌ タイムスタンプでBETWEENを使う（>= AND <を使用してください）
7. ❌ 外部キーにインデックスがない
8. ❌ serial／bigserialを使う（IDENTITYを使用してください）
9. ❌ 任意の上限を設けたvarchar(n)を使う（textを使用してください）
10. ❌ NOWAIT／SKIP LOCKEDなしでSELECT FOR UPDATEを使う

---

PostgreSQL 18以降の機能

機能	用途
uuidv7()	id uuid DEFAULT uuidv7() - 時刻順に並ぶUUID
仮想生成列	col type GENERATED ALWAYS AS (expr) - クエリ実行時に計算
RETURNING内のOLD／NEW	UPDATE ... RETURNING OLD.col, NEW.col
時間的制約	PRIMARY KEY (id) WITHOUT OVERLAPS
NOT VALID制約	テーブル全体をスキャンせずに制約を追加

---

ファイル構成

db/
├── migrations/
│   ├── V001__create_schemas.sql
│   ├── V002__create_tables.sql
│   └── repeatable/
│       ├── R__private_triggers.sql
│       └── R__api_functions.sql
├── schemas/
│   ├── data/           # Table definitions
│   ├── private/        # Internal functions
│   └── api/            # External interface
└── seeds/              # Reference data