Category
PostgreSQL 本体・性能設計（パフォーマンスチューニング／インデックス／EXPLAIN／MVCC・VACUUM／JSONB／パーティショニング）の実装ガイド
PostgreSQLの性能は『推測』ではなく『計測 → 正しい順の最適化』で決まります。本クラスタは、pg_stat_statementsとEXPLAIN ANALYZEで重いクエリを特定する計測起点から、適切な型・列順のインデックス設計、実行計画の読解とスロークエリ改善、MVCCが生む肥大化とVACUUM/autovacuumの調整、半構造データを載せるJSONBの索引設計、超大規模表を物理分割するパーティショニングまでを、効く順に体系化します。PostgreSQL 16を中核にマルチテナント・多段商流のB2B SaaSを本番運用した知見を根拠に、専用DBを増やさず本番の要求に応える設計を、PostgreSQL 18公式ドキュメントに忠実な実コードで解説します。バックアップ・レプリケーション・接続プーリング・無停止アップグレード等の本番運用は『PostgreSQL 運用・信頼性』クラスタに、RLS（行レベルセキュリティ）やSupabase・Drizzleに特化した設計は『データベース・RLS』クラスタにまとめています。
6 articles in total
Foundational guide
Foundational guide (start here)
PostgreSQL production performance-tuning overview (v18 support): speed it up in the correct order of measure → index → execution plan → memory → VACUUM
A systematic guide to making PostgreSQL fast in production. Faithful to the official documentation (v18), with real code it explains measurement starting with pg_stat_statements, how to read the execution plan with EXPLAIN, the meaning of memory settings like shared_buffers/work_mem, MVCC/VACUUM and indexes, connection pooling, and PostgreSQL 18's asynchronous I/O and B-tree skip scan.
Related practical articles
- PostgreSQLパフォーマンスアーキテクチャ設計
Practical PostgreSQL index design (B-tree / GIN / GiST / BRIN, composite column order, covering, partial, expression indexes, CONCURRENTLY)
A practical guide so you don't get lost in 'which type, in which order, how far to index' for PostgreSQL indexes. Faithful to the official docs, with real code it explains the proper use of B-tree/Hash/GiST/SP-GiST/GIN/BRIN, the iron rule of composite-index column order, covering indexes via INCLUDE, partial and expression indexes, the non-stopping CREATE INDEX CONCURRENTLY, and PostgreSQL 18's B-tree skip scan.
12 min read - PostgreSQLパフォーマンスアーキテクチャ設計
How to read PostgreSQL EXPLAIN ANALYZE and improve slow queries (reading the plan, the meaning of each node, auto_explain, v18 support)
A practical guide to diagnosing PostgreSQL's slow queries with EXPLAIN ANALYZE and reliably making them fast. Faithful to the official docs, it covers: reading cost/rows/width, the gap between estimate and actual (stale statistics), the meaning of each node such as Seq Scan / Bitmap / Nested Loop, warning signs like external sort and Heap Fetches, auto_explain to log the plans of production slow queries, and PostgreSQL 18's BUFFERS-on-by-default.
10 min read - PostgreSQLアーキテクチャ設計パフォーマンス
Practical PostgreSQL MVCC, transaction isolation, and VACUUM/autovacuum guide (bloat, row locks, wraparound prevention, v18 support)
A practical explanation of MVCC, the foundation of PostgreSQL's correctness and performance. Faithful to the official docs, with real code it explains transaction isolation levels (Read Committed / Repeatable Read / Serializable) and retrying serialization failures, a job queue with FOR UPDATE SKIP LOCKED, dead tuples and bloat, the difference between VACUUM and VACUUM FULL, tuning autovacuum and PostgreSQL 18's autovacuum_vacuum_max_threshold, and preventing transaction-ID wraparound.
12 min read - PostgreSQLアーキテクチャ設計TypeScript
Practical PostgreSQL JSONB guide (difference from json, operators, GIN/expression index design, type-safe boundary, v18 support)
A practical guide to using PostgreSQL's JSONB correctly in production. Faithful to the official docs, with real code it explains the difference between json and jsonb, operators like ->/->>/@>/?, the pitfalls of containment/existence, updates like jsonb_set, SQL/JSON paths, judging index design between GIN (jsonb_ops and jsonb_path_ops) and expression B-tree, the choice vs. normalized columns, and a type-safe boundary with TypeScript/Zod.
11 min read - PostgreSQLアーキテクチャ設計パフォーマンス
PostgreSQL declarative partitioning in practice (RANGE/LIST/HASH, partition pruning, rolling-window operation, v18-ready)
A practical guide to correctly using PostgreSQL declarative partitioning in production. With real code faithful to the official documentation, it explains: when to partition (and when not to), the RANGE/LIST/HASH syntax, the boundary trap that the upper bound is exclusive, primary-key constraint rules, how to confirm partition pruning, ATTACH/DETACH and time-series rolling-window operation, partitionwise join, and PostgreSQL 18 improvements.
11 min read