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 sharedbuffers and workmem, 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:

pgstatstatements — identifies slow query patterns by cumulative execution time
pgstatio — granular I/O analysis by backend type, object, and context (PG16+)
pgstatcheckpointer — checkpoint frequency and timing (PG17+; previously in pgstatbgwriter)
pgstatuser_tables — dead tuple counts for bloat detection and autovacuum monitoring
pgstatiouser_tables — buffer cache hit ratios per table
pg_aios — in-progress AIO operations (PG18+)

Quick Reference

Pattern	Configuration / Query	Key Points
Async I/O	`iomethod = worker` or `io``uring`	PG18 default is `worker`; `io_uring` Linux-only (kernel 5.1+, requires liburing build flag)
I/O concurrency	`iomaxconcurrency` and `io_workers`	`ioworkers` 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	`autovacuumvacuumscale_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 vectorcosineops)`	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	`pgstatusertables.ndead_tup`	Reindex concurrently if bloat > 30%
I/O monitoring	`SELECT * FROM pgstatio`	Watch `evictions` (cache too small) and `extends` (fast growth)
Checkpoint monitoring	`pgstatcheckpointer`	PG17+ moved checkpoint stats out of `pgstatbgwriter`

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 genrandomuuid() and uuidextracttimestamp() 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
pgstatio gains byte-level columns (readbytes, writebytes, extendbytes); opbytes removed
effectiveioconcurrency 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 pgstatbgwriter to pgstatcheckpointer
Column renames: checkpointstimed to numtimed, checkpointsreq to numrequested
buffersbackend and buffersbackendfsync removed from pgstatbgwriter (now in pgstat_io)

PostgreSQL 15:

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

Common Mistakes

Mistake	Correct Pattern
Using `uuidgeneratev7()` or `genrandomuuid()` for ordered keys	PG18 provides built-in `uuidv7()` for time-ordered UUIDs; pre-PG18 use `pg_uuidv7` extension
Using `maxasyncios` as a configuration parameter	The correct PG18 parameter is `iomaxconcurrency` (max concurrent I/O ops per process)
Querying `pgstatbgwriter` for checkpoint statistics on PG17+	Checkpoint stats moved to `pgstatcheckpointer` in PG17; columns renamed (`numtimed`, `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 `autovacuumvacuumscale_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 `iomethod = iouring` 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

Identify slow queries from pgstatstatements (sort by totalexectime)
Analyze execution plans with EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
Check buffer hit ratios via pgstatiouser_tables (target > 99%)
Monitor I/O patterns via pgstatio (watch evictions and disk reads)
Optimize with targeted indexes, work_mem adjustments, or query rewrites
Verify improvements by re-running EXPLAIN and comparing costs
Maintain with aggressive autovacuum settings for high-churn tables

Delegation

Discover slow queries and I/O bottlenecks: Use Explore agent to analyze pgstatstatements, pgstatio, 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](references/aio-tuning.md)
[Query plan analysis and operator forensics](references/query-plan-analysis.md)
[Indexing strategies and bloat management](references/indexing-and-bloat.md)
[Connection pooling, partitioning, and query patterns](references/pooling-and-patterns.md)

postgres-tuning

Installation

Summary

Similar popular skills

Also in this package

Agent compatibility

Repository health

Skill metadata

Package contents

History

SKILL.md