PostgreSQL Tuning

Overview

Optimizes PostgreSQL 17/18+ performance across I/O, query execution, indexing, and maintenance. Covers the native AIO subsystem introduced in PostgreSQL 18 for throughput gains on modern storage, forensic query plan analysis with EXPLAIN BUFFERS (auto-included in PG18), B-tree skip scans for composite indexes, native UUIDv7 generation, and autovacuum tuning for high-churn tables.

When to use: Diagnosing slow queries, configuring async I/O, tuning shared_buffers and work_mem, optimizing indexes for write-heavy workloads, managing table bloat, pgvector HNSW tuning.

When NOT to use: Schema design (use a data modeling tool), application-level caching strategy, database selection decisions, ORM query generation.

Key monitoring views:

• pg_stat_statements — identifies slow query patterns by cumulative execution time
• pg_stat_io — granular I/O analysis by backend type, object, and context (PG16+)
• pg_stat_checkpointer — checkpoint frequency and timing (PG17+; previously in pg_stat_bgwriter)
• pg_stat_user_tables — dead tuple counts for bloat detection and autovacuum monitoring
• pg_statio_user_tables — buffer cache hit ratios per table
• pg_aios — in-progress AIO operations (PG18+)

Quick Reference

Pattern	Configuration / Query	Key Points
Async I/O	io_method = worker or io_uring	PG18 default is worker; io_uring Linux-only (kernel 5.1+, requires liburing build flag)
I/O concurrency	io_max_concurrency and io_workers	io_workers defaults to 3; io_max_concurrency defaults to -1 (auto-calculated)
Forensic EXPLAIN	EXPLAIN (ANALYZE, BUFFERS, SETTINGS)	PG18 auto-includes BUFFERS with ANALYZE; target Shared Hit > 95%
UUIDv7 primary keys	DEFAULT uuidv7()	PG18 built-in; time-ordered, monotonic within a session; RFC 9562 compliant
B-tree skip scan	Composite index on (a, b)	PG18 skips leading column; works best with low-cardinality prefix and equality on trailing columns
Aggressive autovacuum	autovacuum_vacuum_scale_factor = 0.01	Triggers at 1% row change instead of default 20%
Shared buffers	Start at 25% of RAM	Do not exceed 40% without benchmarking
work_mem tuning	SET work_mem = '64MB' per session	Prevents sort spills to disk; allocated per operator, not per query
BRIN index	CREATE INDEX USING brin(...)	100x smaller than B-tree for physically ordered time-series data
HNSW vector index	USING hnsw (col vector_cosine_ops)	Tune m (default 16) and ef_construction (default 64) for recall vs speed
GIN index	CREATE INDEX USING gin(...)	JSONB containment, full-text search, array operators; slower writes
Checkpoint tuning	checkpoint_timeout = 30min	Spread writes over 90% of timeout window to avoid I/O storms
WAL compression	wal_compression = zstd	Available since PG15; reduces WAL I/O 50-70% for write-heavy workloads
Bloat detection	pg_stat_user_tables.n_dead_tup	Reindex concurrently if bloat > 30%
I/O monitoring	SELECT * FROM pg_stat_io	Watch evictions (cache too small) and extends (fast growth)
Checkpoint monitoring	pg_stat_checkpointer	PG17+ moved checkpoint stats out of pg_stat_bgwriter

Key Version Changes

PostgreSQL 18:

• Native async I/O via io_method parameter (reads only; writes remain synchronous)
• Built-in uuidv7() function with monotonic ordering within a session (RFC 9562)
• uuidv4() alias for gen_random_uuid() and uuid_extract_timestamp() for UUIDv7
• B-tree skip scan for composite indexes (equality on trailing columns, low-cardinality prefix)
• EXPLAIN ANALYZE auto-includes buffer statistics without specifying BUFFERS
• pg_stat_io gains byte-level columns (read_bytes, write_bytes, extend_bytes); op_bytes removed
• effective_io_concurrency default changed from 1 to 16
• AIO monitoring via pg_aios system view for in-progress I/O operations

PostgreSQL 17:

• Checkpoint statistics moved from pg_stat_bgwriter to pg_stat_checkpointer
• Column renames: checkpoints_timed to num_timed, checkpoints_req to num_requested
• buffers_backend and buffers_backend_fsync removed from pg_stat_bgwriter (now in pg_stat_io)

PostgreSQL 15:

• wal_compression expanded from boolean to support pglz, lz4, and zstd algorithms

Common Mistakes

Mistake	Correct Pattern
Using uuid_generate_v7() or gen_random_uuid() for ordered keys	PG18 provides built-in uuidv7() for time-ordered UUIDs; pre-PG18 use pg_uuidv7 extension
Using max_async_ios as a configuration parameter	The correct PG18 parameter is io_max_concurrency (max concurrent I/O ops per process)
Querying pg_stat_bgwriter for checkpoint statistics on PG17+	Checkpoint stats moved to pg_stat_checkpointer in PG17; columns renamed (num_timed, num_requested)
Using SELECT * in high-frequency queries	Select only needed columns to reduce I/O and improve cache hit ratios
Ignoring sequential scans on tables over 10k rows	Add targeted indexes on columns used in WHERE, ORDER BY, and JOIN clauses
Setting shared_buffers above 40% of RAM without testing	Start at 25% and benchmark; excessive allocation causes OS page cache contention
Leaving autovacuum at default settings for high-churn tables	Tune autovacuum_vacuum_scale_factor to 0.01 for tables with frequent UPDATE/DELETE
Over-indexing columns rarely used in queries	Every extra index slows UPDATE/INSERT and prevents HOT (Heap Only Tuple) updates
Expecting B-tree skip scan to work with range predicates	PG18 skip scan only works with equality operators on trailing columns
Ignoring "External Merge Disk" in query plans	Increase work_mem for specific sessions; it indicates sort spills to disk
Setting io_method = io_uring without verifying build flags	PostgreSQL must be built with --with-liburing and requires Linux kernel 5.1+
Assuming PG18 AIO accelerates writes	AIO in PG18 only covers reads (seq scans, bitmap heap scans, VACUUM); writes remain synchronous

Tuning Workflow

1. Identify slow queries from pg_stat_statements (sort by total_exec_time)
2. Analyze execution plans with EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
3. Check buffer hit ratios via pg_statio_user_tables (target > 99%)
4. Monitor I/O patterns via pg_stat_io (watch evictions and disk reads)
5. Optimize with targeted indexes, work_mem adjustments, or query rewrites
6. Verify improvements by re-running EXPLAIN and comparing costs
7. Maintain with aggressive autovacuum settings for high-churn tables

Delegation

• Discover slow queries and I/O bottlenecks: Use Explore agent to analyze pg_stat_statements, pg_stat_io, and slow query logs
• Execute query plan analysis and index optimization: Use Task agent to run EXPLAIN ANALYZE, create indexes, and verify performance improvements
• Design database scaling and partitioning strategy: Use Plan agent to architect sharding, partitioning, and replication topology

References

• Async I/O configuration and storage tuning
• Query plan analysis and operator forensics
• Indexing strategies and bloat management
• Connection pooling, partitioning, and query patterns