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 timepg_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 monitoringpg_statio_user_tables — buffer cache hit ratios per tablepg_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 removedeffective_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
- Identify slow queries from
pg_stat_statements (sort by total_exec_time)
- Analyze execution plans with
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
- Check buffer hit ratios via
pg_statio_user_tables (target > 99%)
- Monitor I/O patterns via
pg_stat_io (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 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
1---
2name: postgres-tuning
3description: 'PostgreSQL 17/18+ performance tuning and optimization. Covers async I/O configuration, query plan forensics, index strategies, autovacuum tuning, vector search optimization, connection pooling, declarative partitioning, and practical query patterns. Use when diagnosing slow queries, configuring async I/O, tuning autovacuum, optimizing vector indexes, analyzing execution plans with EXPLAIN BUFFERS, configuring PgBouncer or connection pooling, setting up table partitioning, implementing cursor pagination, or optimizing queue processing.'
4license: MIT
5metadata:
6 author: oakoss
7 version: '1.1'
8user-invocable: false
9---
10
11# PostgreSQL Tuning
12
13## Overview
14
15Optimizes 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.
16
17**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.
18
19**When NOT to use:** Schema design (use a data modeling tool), application-level caching strategy, database selection decisions, ORM query generation.
20
21**Key monitoring views:**
22
23- `pg_stat_statements` — identifies slow query patterns by cumulative execution time
24- `pg_stat_io` — granular I/O analysis by backend type, object, and context (PG16+)
25- `pg_stat_checkpointer` — checkpoint frequency and timing (PG17+; previously in `pg_stat_bgwriter`)
26- `pg_stat_user_tables` — dead tuple counts for bloat detection and autovacuum monitoring
27- `pg_statio_user_tables` — buffer cache hit ratios per table
28- `pg_aios` — in-progress AIO operations (PG18+)
29
30## Quick Reference
31
32| Pattern | Configuration / Query | Key Points |
33| --------------------- | --------------------------------------- | -------------------------------------------------------------------------------------------------- |
34| Async I/O | `io_method = worker` or `io_uring` | PG18 default is `worker`; `io_uring` Linux-only (kernel 5.1+, requires liburing build flag) |
35| I/O concurrency | `io_max_concurrency` and `io_workers` | `io_workers` defaults to 3; `io_max_concurrency` defaults to -1 (auto-calculated) |
36| Forensic EXPLAIN | `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)` | PG18 auto-includes BUFFERS with ANALYZE; target Shared Hit > 95% |
37| UUIDv7 primary keys | `DEFAULT uuidv7()` | PG18 built-in; time-ordered, monotonic within a session; RFC 9562 compliant |
38| B-tree skip scan | Composite index on `(a, b)` | PG18 skips leading column; works best with low-cardinality prefix and equality on trailing columns |
39| Aggressive autovacuum | `autovacuum_vacuum_scale_factor = 0.01` | Triggers at 1% row change instead of default 20% |
40| Shared buffers | Start at 25% of RAM | Do not exceed 40% without benchmarking |
41| work_mem tuning | `SET work_mem = '64MB'` per session | Prevents sort spills to disk; allocated per operator, not per query |
42| BRIN index | `CREATE INDEX USING brin(...)` | 100x smaller than B-tree for physically ordered time-series data |
43| HNSW vector index | `USING hnsw (col vector_cosine_ops)` | Tune `m` (default 16) and `ef_construction` (default 64) for recall vs speed |
44| GIN index | `CREATE INDEX USING gin(...)` | JSONB containment, full-text search, array operators; slower writes |
45| Checkpoint tuning | `checkpoint_timeout = 30min` | Spread writes over 90% of timeout window to avoid I/O storms |
46| WAL compression | `wal_compression = zstd` | Available since PG15; reduces WAL I/O 50-70% for write-heavy workloads |
47| Bloat detection | `pg_stat_user_tables.n_dead_tup` | Reindex concurrently if bloat > 30% |
48| I/O monitoring | `SELECT * FROM pg_stat_io` | Watch `evictions` (cache too small) and `extends` (fast growth) |
49| Checkpoint monitoring | `pg_stat_checkpointer` | PG17+ moved checkpoint stats out of `pg_stat_bgwriter` |
50
51## Key Version Changes
52
53**PostgreSQL 18:**
54
55- Native async I/O via `io_method` parameter (reads only; writes remain synchronous)
56- Built-in `uuidv7()` function with monotonic ordering within a session (RFC 9562)
57- `uuidv4()` alias for `gen_random_uuid()` and `uuid_extract_timestamp()` for UUIDv7
58- B-tree skip scan for composite indexes (equality on trailing columns, low-cardinality prefix)
59- EXPLAIN ANALYZE auto-includes buffer statistics without specifying BUFFERS
60- `pg_stat_io` gains byte-level columns (`read_bytes`, `write_bytes`, `extend_bytes`); `op_bytes` removed
61- `effective_io_concurrency` default changed from 1 to 16
62- AIO monitoring via `pg_aios` system view for in-progress I/O operations
63
64**PostgreSQL 17:**
65
66- Checkpoint statistics moved from `pg_stat_bgwriter` to `pg_stat_checkpointer`
67- Column renames: `checkpoints_timed` to `num_timed`, `checkpoints_req` to `num_requested`
68- `buffers_backend` and `buffers_backend_fsync` removed from `pg_stat_bgwriter` (now in `pg_stat_io`)
69
70**PostgreSQL 15:**
71
72- `wal_compression` expanded from boolean to support `pglz`, `lz4`, and `zstd` algorithms
73
74## Common Mistakes
75
76| Mistake | Correct Pattern |
77| ------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------- |
78| 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 |
79| Using `max_async_ios` as a configuration parameter | The correct PG18 parameter is `io_max_concurrency` (max concurrent I/O ops per process) |
80| Querying `pg_stat_bgwriter` for checkpoint statistics on PG17+ | Checkpoint stats moved to `pg_stat_checkpointer` in PG17; columns renamed (`num_timed`, `num_requested`) |
81| Using SELECT \* in high-frequency queries | Select only needed columns to reduce I/O and improve cache hit ratios |
82| Ignoring sequential scans on tables over 10k rows | Add targeted indexes on columns used in WHERE, ORDER BY, and JOIN clauses |
83| Setting shared_buffers above 40% of RAM without testing | Start at 25% and benchmark; excessive allocation causes OS page cache contention |
84| Leaving autovacuum at default settings for high-churn tables | Tune `autovacuum_vacuum_scale_factor` to 0.01 for tables with frequent UPDATE/DELETE |
85| Over-indexing columns rarely used in queries | Every extra index slows UPDATE/INSERT and prevents HOT (Heap Only Tuple) updates |
86| Expecting B-tree skip scan to work with range predicates | PG18 skip scan only works with equality operators on trailing columns |
87| Ignoring "External Merge Disk" in query plans | Increase work_mem for specific sessions; it indicates sort spills to disk |
88| Setting `io_method = io_uring` without verifying build flags | PostgreSQL must be built with `--with-liburing` and requires Linux kernel 5.1+ |
89| Assuming PG18 AIO accelerates writes | AIO in PG18 only covers reads (seq scans, bitmap heap scans, VACUUM); writes remain synchronous |
90
91## Tuning Workflow
92
931. **Identify** slow queries from `pg_stat_statements` (sort by `total_exec_time`)
942. **Analyze** execution plans with `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)`
953. **Check** buffer hit ratios via `pg_statio_user_tables` (target > 99%)
964. **Monitor** I/O patterns via `pg_stat_io` (watch evictions and disk reads)
975. **Optimize** with targeted indexes, work_mem adjustments, or query rewrites
986. **Verify** improvements by re-running EXPLAIN and comparing costs
997. **Maintain** with aggressive autovacuum settings for high-churn tables
100
101## Delegation
102
103- **Discover slow queries and I/O bottlenecks**: Use `Explore` agent to analyze pg_stat_statements, pg_stat_io, and slow query logs
104- **Execute query plan analysis and index optimization**: Use `Task` agent to run EXPLAIN ANALYZE, create indexes, and verify performance improvements
105- **Design database scaling and partitioning strategy**: Use `Plan` agent to architect sharding, partitioning, and replication topology
106
107## References
108
109- [Async I/O configuration and storage tuning](references/aio-tuning.md)
110- [Query plan analysis and operator forensics](references/query-plan-analysis.md)
111- [Indexing strategies and bloat management](references/indexing-and-bloat.md)
112- [Connection pooling, partitioning, and query patterns](references/pooling-and-patterns.md)
oakoss