postgres-engineering

name	postgres-engineering
description	PostgreSQL SQL engineering and performance: EXPLAIN plans, indexing, statistics, joins, partitioning, schema design, constraints, JSONB, full text, GIN/GiST/BRIN/B-tree indexes, pgvector, extensions, and query tuning.

PostgreSQL Engineering

Use plans and workload evidence before changing schema or indexes. Explain both read performance and write amplification.

How To Approach A Tuning Request

1. Get the real plan: EXPLAIN (ANALYZE, BUFFERS) with production-like data and parameters. Estimated plans alone mislead.
2. Compare estimated vs actual rows at each node. A large gap (10x+) is a statistics problem before it is an index problem.
3. Find where the time actually goes: the node with the largest exclusive actual time, loops included (actual time × loops).
4. Check whether the predicate is SARGable — a function or cast on the column side defeats plain indexes.
5. Propose the smallest change that fixes the plan: statistics refresh, extended statistics, an index, a query rewrite, then schema change last.
6. State the write cost of any new index and check for overlap with existing indexes first (scripts/04-duplicate-indexes.sql).

Plan Red Flags

Signal in plan	Likely cause	First response
Estimated rows off by 10x+	Stale stats, correlated columns, skew	ANALYZE; extended statistics for column combinations
Seq scan on large table with selective filter	Missing/unusable index, non-SARGable predicate	Check predicate form and available indexes
Nested Loop with huge inner loops count	Misestimate upstream chose the wrong join	Fix the estimate; the join strategy follows
Sort/Hash spills (Disk: in ANALYZE output)	work_mem too small for this node	Query rewrite, index providing order, or targeted work_mem
Rows Removed by Filter very high	Index doesn't cover the filter	Composite or partial index matching the predicate
Heap Fetches high on Index Only Scan	Visibility map not maintained	Vacuum more aggressively on that table
lossy bitmap heap blocks	work_mem too small for exact bitmap	Raise work_mem or make the predicate more selective

Index Type Selection

Index type	Use for	Avoid when
B-tree	Equality, range, ordering, uniqueness (default)	Predicate isn't leading-column-prefixed
GIN	JSONB containment, arrays, full text, trigram search	Heavy single-row update churn (write amplification)
GiST	Ranges, geometric, exclusion constraints, nearest-neighbor	Plain equality (B-tree is smaller/faster)
BRIN	Huge append-only tables with physical ordering (time series)	Data physically unordered on the indexed column
Hash	Equality only, large keys	Almost always — B-tree usually matches it
HNSW/IVFFlat (pgvector)	Vector similarity	Exact results required (they are approximate)

Composite index column order: equality columns first, then the range/sort column. A partial index (WHERE status = 'pending') beats a full index when queries always filter the same hot subset. Expression indexes (ON lower(email)) make non-SARGable predicates indexable — the query must use the identical expression.

Common Pitfalls

1. Adding an index per slow query without checking overlap; every index taxes writes, vacuum, and cache.
2. Tuning to an estimated plan when actual row counts diverge wildly.
3. Indexing a JSONB column with B-tree and expecting @> containment to use it (that needs GIN).
4. Ignoring Rows Removed by Join Filter and join order when the real fix is extended statistics.
5. Partitioning small tables for "performance"; partitioning pays off for pruning, retention (drop partition), and vacuum parallelism on genuinely large tables.
6. Forgetting that partitioned tables need the partition key in every unique constraint.
7. Using NOT IN (subquery) with nullable columns — both wrong and unoptimizable; use NOT EXISTS.
8. Marking functions used in indexes or generated columns as IMMUTABLE when they are not.

References

• references/query-indexing.md - plan reading, index recipes, statistics, partitioning, and JSONB indexing.
• ../postgres/references/diagnostics.md - imported domain-expert plan analysis, EXPLAIN ANALYZE, and diagnostics material.
• ../postgres/references/versions/ - version-specific SQL and optimizer features.

Scripts

• scripts/01-index-usage.sql - index usage and size.
• scripts/02-missing-index-signals.sql - sequential scan pressure signals.
• scripts/03-table-statistics.sql - table statistics freshness.
• scripts/04-duplicate-indexes.sql - structurally duplicate indexes.
• scripts/05-foreign-key-index-check.sql - foreign keys without matching leading-column indexes.
• scripts/06-partition-inventory.sql - partitioned tables, partition children, and bounds.
• scripts/07-extended-statistics.sql - extended statistics definitions and collected data.
• scripts/08-function-volatility.sql - function volatility, parallel safety, and security-definer posture.

Cross-Skill Routing

• Identifying which query is slow goes to postgres-monitoring; this skill fixes the query once known.
• work_mem, parallel workers, and JIT server settings go to postgres-infrastructure.
• Bloat and vacuum effects on plans go to postgres-operations.
• Extension availability on managed platforms goes to postgres-cloud.