postgresql

PostgreSQL — 高性能リレーショナルデータベースシステム

ワークフロー — 使用手順

遇到 PostgreSQL 需求时，按以下顺序决策:

1. 明确需求类型
   ├── DDL (建表/改表)        → 见 SQL 语法速查
   ├── DML (查询/插入/更新)    → 见 SQL 语法速查
   ├── 函数/数据处理            → 见 函数速查
   ├── 查询性能优化             → 见 references/06-index-types.md + examples/03-performance-tuning.md
   └── 高可用/备份/复制         → 见 references/08-replication-backup.md + examples/04-streaming-replication.md

2. 确定模型: 关系型 → 标准表+B-Tree | JSON文档 → JSONB+GIN | 全文搜索 → tsvector+GIN | 地理 → PostGIS+GiST

3. 索引策略: 等值→B-Tree | 范围→B-Tree | 全文→GIN | JSON→GIN | 向量→IVFFlat/HNSW | 大表时序→BRIN

4. 数据量评估: <100GB→单实例 | 100GB-1TB→分区 | 1TB-10TB→分区+只读副本 | >10TB→Citus/逻辑复制

5. 运维策略: autovacuum + pg_stat_statements + WAL归档 + PgBouncer

使用する場合と使用しない場合

✅ 使用する場合	❌ 使用を避ける場合
完全な ACID トランザクションと複雑な SQL が必要な場合	純粋なキーバリューキャッシュ（Redis/Memcached を使用）
JSON ドキュメントと SQL クエリを組み合わせる場合	関連クエリのない純粋なドキュメントデータ（MongoDB を使用）
地理空間データの分析（PostGIS）	大規模な全文検索（Elasticsearch を使用）
強力なデータ整合性制約が必要な場合	大量のスキーマレスログ（Elasticsearch/S3 を使用）
OLTP と複雑な OLAP の混合ワークロード	超大規模な OLAP（ClickHouse/Snowflake を使用）
ストリーミングレプリケーション／論理レプリケーション／PITR が必要な場合	自動水平シャーディング（CockroachDB/YugabyteDB を使用）

基本原則：PostgreSQL は多機能なリレーショナルデータベースですが、あらゆる用途に最適な選択肢ではありません。

境界 — 対応範囲

✅ 完全に適している	⚠️ 条件付きで適している	❌ 適していない → 代替
標準的な OLTP 業務システム	超大規模な OLAP >20TB → ClickHouse/cstore_fdw	純粋なインメモリキャッシュ <1ms → Redis
JSONB とリレーショナルクエリの混在	高並行な単純 KV >50万 QPS → Redis	大量の時系列データ書き込み >100万ポイント／秒 → InfluxDB
全文検索（数億件のドキュメント）	リアルタイム検索 >10億件のドキュメント → Elasticsearch	複雑なグラフトラバーサル → Neo4j
ストリーミングレプリケーション HA（障害復旧 <30s）	複数地域でのマルチアクティブ構成 → CockroachDB	無停止での自動シャーディングとスケール変更

SQL 構文クイックリファレンス

SQL の詳細は references/ 内の各ファイルを参照してください。ここでは索引を示します。

• DDL: CREATE TABLE（パーティション、継承を含む）、ALTER TABLE、データ型（JSONB/TSVECTOR/CITEXT/配列など）、制約（CHECK/EXCLUDE/UNIQUE/FOREIGN KEY）
• DML: INSERT ... ON CONFLICT（UPSERT）、UPDATE ... FROM、DELETE ... USING、TRUNCATE、RETURNING 句
• CTE: 共通テーブル式（WITH）、再帰 CTE（WITH RECURSIVE）— examples/02-cte-recursive.md を参照
• 結合: INNER/LEFT/RIGHT/FULL/CROSS JOIN、LATERAL サブクエリ
• トランザクション: READ COMMITTED（デフォルト）、REPEATABLE READ、SERIALIZABLE、SAVEPOINT、FOR UPDATE/SHARE/NOWAIT/SKIP LOCKED、アドバイザリーロック

関数クイックリファレンス

詳細は references/ を参照してください。

カテゴリ	主な関数	参考ファイル
文字列／正規表現	FORMAT, SPLIT_PART, REGEXP_MATCH/REPLACE, STRING_AGG, CONCAT_WS, TRANSLATE, SUBSTRING	references/01-functions-string.md
日付／時刻	AGE, DATE_TRUNC, EXTRACT, TO_CHAR, MAKE_DATE, JUSTIFY_*、タイムゾーン変換	references/02-functions-datetime.md
集約／ウィンドウ	ARRAY_AGG, JSONB_AGG, STRING_AGG, PERCENTILE_CONT/DISC, MODE, GROUPING SETS/CUBE/ROLLUP, ROW_NUMBER, RANK, LAG/LEAD, NTILE、ウィンドウフレーム	references/03-functions-aggregate-window.md
JSONB	->/->>/#>, @>/?/`?	/?&, JSONB_SET, JSONB_BUILD_OBJECT, JSONB_EACH, JSONB_TYPEOF`、GIN インデックス

高度な機能の索引

機能	説明	参考
6種類のインデックス	B-Tree, Hash, GiST, GIN, BRIN, SP-GiST, Bloom + 部分インデックス／カバリングインデックス／CONCURRENTLY	references/06-index-types.md
ビューとマテリアライズドビュー	通常のビュー（仮想テーブル）とマテリアライズドビュー（物理スナップショット）、WITH CHECK OPTION、CONCURRENTLY による更新	references/06-index-types.md
PL/pgSQL	関数（FUNCTION）とプロシージャ（PROCEDURE）、制御構造、例外処理、関数のオーバーロード	references/01-functions-string.md
トリガー	BEFORE/AFTER/INSTEAD OF、行レベル／文レベル、イベントトリガー、制約トリガー	references/01-functions-string.md
全文検索	tsvector/tsquery、@@ 演算子、ts_rank、ts_headline、フレーズ検索、中国語検索（zhparser）	references/05-fulltext-search.md
パーティションテーブル	RANGE/LIST/HASH パーティション、サブパーティション、パーティションプルーニング、ATTACH/DETACH	references/07-partition-fdw.md
FDW 外部テーブル	postgres_fdw, file_fdw, IMPORT FOREIGN SCHEMA	references/07-partition-fdw.md
拡張機能	PostGIS, pgvector, pg_stat_statements, uuid-ossp, pgcrypto, citext, pg_trgm, unaccent	references/08-replication-backup.md
権限管理	ROLE, SCHEMA, GRANT、デフォルト権限、RLS 行レベルセキュリティ	references/08-replication-backup.md
ストリーミングレプリケーションと論理レプリケーション	同期／非同期、PUBLICATION/SUBSCRIPTION、Patroni/repmgr	references/08-replication-backup.md
バックアップと復旧	pg_dump/pg_restore, pg_basebackup, WAL アーカイブ + PITR	references/08-replication-backup.md
パフォーマンス最適化	EXPLAIN ANALYZE, VACUUM/autovacuum, pg_stat_statements、設定のチューニング	examples/03-performance-tuning.md

落とし穴 — よくある罠とアンチパターン

#	落とし穴	リスク	解決策
1	JSONB に GIN インデックスを作成していない	全表スキャン、パフォーマンス低下	CREATE INDEX ... USING GIN (config)
2	データベースへの直接接続が多すぎる	接続ごとに 5-10MB を消費し、メモリを圧迫	PgBouncer 接続プールを使用
3	インデックスの肥大化をメンテナンスしていない	インデックスのサイズがテーブルを大幅に上回る	定期的に REINDEX または pg_repack を実行
4	N+1 クエリと SELECT *	冗長なデータ転送、クエリの多発	必要な列のみ選択し、JOIN/LATERAL を使用
5	本番環境のピーク時に VACUUM FULL を実行	テーブルがロックされ、業務が中断	pg_repack を使用（テーブルをロックしない）
6	autovacuum の実行タイミングが遅い	デッドタプルの蓄積 → テーブルの肥大化 → パフォーマンスの低下	n_dead_tup を監視し、設定を調整
7	BIGSERIAL ではなく SERIAL を使用	21 億行を超えると ID がオーバーフロー	新しいテーブルでは BIGSERIAL または UUID を使用
8	マルチテナントで RLS を設定していない	データ漏えい	RLS と外部キー制約を有効化
9	トランザクション ID の周回を無視	データベースが強制的に読み取り専用になる	age(relfrozenxid) を監視
10	UUID を主キーに使用（ランダムな v4）	B-Tree のページ分割により、書き込みが 2-3 倍遅くなる	UUID v7 または BIGSERIAL を使用
11	大きなテーブルに対する COUNT(*)	1,000 万行以上では全表スキャンが非常に遅い	pg_class.reltuples の概算値を使用
12	外部キーにインデックスがない	親テーブルの削除／更新時に子テーブルを全表スキャン	外部キー列にインデックスを作成
13	再試行ロジックなしで SERIALIZABLE を使用	トランザクションの競合により失敗	アプリケーション層で再試行を実装

FAQ

Q1: PostgreSQL と MySQL の主な違いは？ PostgreSQL：完全な ACID、インデックスを利用できる JSONB、6 種類のインデックス、再帰 CTE、ストリーミングレプリケーション＋論理レプリケーション、豊富な EXTENSION。MySQL：Web アプリケーション向け、シンプルなクエリ、InnoDB トランザクション、ギャップロックによる同時実行制御。

Q2: JSONB と JSON のどちらを選ぶべきですか？ 常に JSONB を選んでください。バイナリ形式で、GIN インデックスをサポートし、クエリも高速です。空白とキーの順序を保持する必要がある場合に限り、JSON を使用してください。

Q3: UUID が主キーに適さないのはなぜですか？ UUID v4 のランダムな値により B-Tree のページ分割が頻発し、BIGSERIAL より 2-3 倍遅くなります。対策：UUID v7（時間順）、BIGSERIAL、または ULID/Snowflake を使用します。

Q4: PostgreSQL をオンラインで移行するには？ 論理レプリケーション（PG 10 以降、推奨）> pglogical 拡張機能 > pg_dump+pg_restore（停止が必要）。論理レプリケーションはメジャーバージョンをまたいだ選択的なレプリケーションをサポートします。

Q5: work_mem はどのように設定すればよいですか？ ソート操作ごとに割り当てられます。最大メモリ = work_mem ×（接続数 × 同時ソート数）。64GB のマシンでは 64-128MB を推奨します。temp_files の指標を監視し、ディスクソートが発生している場合は値を大きくしてください。

Q6: pg_repack と VACUUM FULL の違いは？ VACUUM FULL はテーブルをロックします（ACCESS EXCLUSIVE）。pg_repack は書き込みをロックしないため、オンライン環境に適しており、こちらを優先してください。

Q7: デッドロックにはどう対処すればよいですか？ PG は自動的に検出し、いずれかのトランザクションをロールバックします。予防策：ロックの取得順序を統一し、トランザクションを短く保ち、NOWAIT/SKIP LOCKED を使用し、pg_stat_database.deadlocks を監視します。

Q8: パーティションキーはどのように選べばよいですか？ 条件：クエリで頻繁に使用される（パーティションプルーニング）、データが均等に分散される、安定していて変更されない。一般的な選択肢：時間（RANGE）、地域（LIST）、ID のハッシュ（HASH）。パーティション数は 10-200 を推奨します。

Q9: 接続数はいくつに設定すればよいですか？ 接続ごとに 5-10MB を消費するため、通常は 200-500 で十分です。500 を超える場合は PgBouncer が必須です。（max_connections × work_mem × 0.5）+ shared_buffers + システムのオーバーヘッド < メモリの 80%。

Q10: SERIALIZABLE はいつ使用すればよいですか？ 金融取引、在庫の減算、強い整合性が必要なレポートに使用します。注意：競合が増えると失敗率も上がるため、アプリケーション層に再試行ロジックが必要です。

Q11: クエリがインデックスを使用しないのはなぜですか？ 統計情報が古い → ANALYZE | 型が一致しない → 暗黙の型変換 | 選択性が低い → プランナーが全表スキャンのほうが適切だと判断 | インデックス列が関数で包まれている → WHERE DATE(col) = の形式を避ける。

Q12: パーティションが必要かどうかはどう判断すればよいですか？ テーブルが 100GB を超える | 時間／地域による明確なクエリパターンがある | 古いデータを定期的にアーカイブする | VACUUM が更新に追いつかない。該当しない場合は、パーティションによる複雑さがメリットを上回ります。

Q13: メジャーバージョンをアップグレードするには？ pg_upgrade が最も推奨されます：pg_upgrade -b old_bin -B new_bin -d old_data -D new_data。--link モードが最速です。アップグレード後に ANALYZE を実行してください。

Q14: 論理レプリケーションとストリーミングレプリケーションの違いは？ ストリーミングレプリケーション：物理ブロック単位、データベース全体、メジャーバージョンを一致させる必要があり、HA に使用します。論理レプリケーション：SQL の変更、テーブル／行を選択可能、メジャーバージョンをまたいで使用でき、データ同期／移行に使用します。

Q15: VACUUM 後もテーブルサイズが小さくならないのはなぜですか？ VACUUM（FULL なし）は領域を再利用可能としてマークするだけで、OS には返しません。実際に縮小するには VACUUM FULL または pg_repack が必要です。

キーワード

postgresql, postgres, psql, SQL, DDL, DML, ACID, MVCC, トランザクション, インデックス, B-Tree, GIN, GiST, BRIN, JSONB, hstore, 配列, 全文検索, tsvector, tsquery, ウィンドウ関数, CTE, 再帰CTE, LATERAL, PL/pgSQL, ストアドプロシージャ, トリガー, マテリアライズドビュー, パーティションテーブル, PostGIS, pgvector, pg_stat_statements, pgcrypto, citext, pg_trgm, FDW, postgres_fdw, EXPLAIN, VACUUM, autovacuum, pg_dump, pg_restore, pg_basebackup, WAL アーカイブ, PITR, ストリーミングレプリケーション, 論理レプリケーション, PUBLICATION, SUBSCRIPTION, Patroni, repmgr, PgBouncer, RLS, 行レベルセキュリティ, パフォーマンス最適化, バックアップとリカバリー, 高可用性, デッドロック, 接続プール

参考資料

• PostgreSQL 公式ドキュメント
• PostgreSQL 中国語ドキュメント
• PostgreSQL パフォーマンスチューニング — pgtune
• Patroni ドキュメント
• PgBouncer 公式ドキュメント
• pgvector ドキュメント
• PostGIS ドキュメント

内部参考資料

• references/01-functions-string.md — 文字列／正規表現関数の詳細
• references/02-functions-datetime.md — 日付／時刻関数の詳細
• references/03-functions-aggregate-window.md — 集約／ウィンドウ関数の詳細
• references/04-functions-jsonb.md — JSONB 関数と演算子の詳細
• references/05-fulltext-search.md — 全文検索の詳細
• references/06-index-types.md — インデックスの種類とビューの詳細
• references/07-partition-fdw.md — パーティションテーブルと FDW の詳細
• references/08-replication-backup.md — レプリケーション／バックアップ／権限の詳細
• examples/01-jsonb-query.md — JSONB クエリの例
• examples/02-cte-recursive.md — 再帰 CTE の例
• examples/03-performance-tuning.md — パフォーマンスチューニングの例
• examples/04-streaming-replication.md — ストリーミングレプリケーションの構築例