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

num_timed

checkpoints_req

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

postgres-tuning

NPX Install

Tags

SKILL.md Content