postgres-tuning

What this skill does

# 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