Postgres Tuning

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

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

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

Async I/O Configuration and Storage Tuning

PostgreSQL 18 introduces native Asynchronous I/O (AIO), enabling pipelined I/O requests that deliver 2-3x throughput gains on NVMe storage.

AIO Configuration

io_method Selection

Choose the I/O method based on your operating system and deployment:

# postgresql.conf

# Option 1: Worker processes (all platforms, PG18 default)
io_method = worker
io_workers = 3

# Option 2: io_uring (Linux only, highest performance)
# Requires: --with-liburing build flag and Linux kernel 5.1+
io_method = io_uring

# Option 3: Synchronous I/O (PG17 compatibility)
# io_method = sync

Worker tuning: The default io_workers is 3. Consider setting it to roughly 1/4 of CPU cores. These workers are shared across all connections and databases.

AIO limitations: In PG18, AIO only covers read operations (sequential scans, bitmap heap scans, VACUUM). Write operations including WAL writes remain synchronous.

I/O Concurrency

# Max concurrent I/O operations per process (default: -1, auto-calculated)
io_max_concurrency = 64

Controls the maximum number of I/O operations one process can issue simultaneously. The default of -1 selects a value based on shared_buffers and max processes, capped at 64. Can only be set at server start.

Shared Buffer Tuning

shared_buffers is the in-memory cache for frequently accessed data pages.

Baseline Configuration

# Start here for most workloads
shared_buffers = '4GB'  # 25% of 16GB system RAM

Sizing Guidelines

AIO impact: With AIO enabled, read-heavy workloads can benefit from larger shared_buffers (up to 40-50% of RAM) because AIO reduces the CPU cost of cache misses. Benchmark before increasing beyond 40%.

Cache Hit Ratio Monitoring

SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(
    sum(heap_blks_hit)::numeric /
    nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
    2
  ) AS hit_ratio
FROM pg_statio_user_tables;

Target: > 99% hit ratio. If below 95%, increase shared_buffers or investigate which tables are causing cache misses.

pg_stat_io Monitoring

This view provides granular I/O analysis:

SELECT backend_type, object, context, reads, writes, extends
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY reads + writes DESC;

Key metrics:

Checkpoint Tuning

Checkpoints flush dirty pages to disk. Poorly tuned checkpoints cause latency spikes ("I/O storms").

# postgresql.conf
checkpoint_timeout = 30min
max_wal_size = 16GB
checkpoint_completion_target = 0.9

Goal: Spread checkpoint writes evenly over 90% of the timeout window, avoiding sudden I/O bursts.

Monitoring Checkpoints

-- PG17+: use pg_stat_checkpointer (columns renamed from pg_stat_bgwriter)
SELECT
  num_timed,
  num_requested,
  write_time,
  sync_time
FROM pg_stat_checkpointer;

If num_requested (forced checkpoints) is high relative to num_timed, increase max_wal_size.

WAL Configuration

# Write-Ahead Log
wal_level = replica
wal_compression = zstd
wal_buffers = '64MB'

wal_compression = zstd reduces WAL I/O by 50-70% for write-heavy workloads.

Full Configuration Template

# postgresql.conf - Performance Template (16GB RAM, NVMe, PG18)
shared_buffers = '4GB'
effective_cache_size = '12GB'
work_mem = '16MB'
maintenance_work_mem = '512MB'
io_method = worker
io_workers = 3
io_max_concurrency = -1
checkpoint_timeout = '30min'
max_wal_size = '16GB'
checkpoint_completion_target = 0.9
wal_compression = zstd
wal_buffers = '64MB'
random_page_cost = 1.1
effective_io_concurrency = 200

Adjust random_page_cost to 1.1 for SSDs (default 4.0 is for spinning disks). PG18 changed effective_io_concurrency default from 1 to 16; set higher (up to 200) for NVMe.

Indexing Strategies and Bloat Management

Effective indexing reduces query latency. Bloat management prevents performance degradation from dead tuples accumulating in tables and indexes.

Index Type Selection

B-tree Patterns

Standard Index

CREATE INDEX CONCURRENTLY idx_orders_status
ON orders (status);

Composite Index

Column order matters. Place the most selective column first:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status
ON orders (tenant_id, status);

This index serves queries filtering on tenant_id alone, or tenant_id AND status, but not status alone (pre-PG18).

Partial Index

Index only the rows you query:

CREATE INDEX CONCURRENTLY idx_orders_active
ON orders (created_at)
WHERE status = 'active';

Much smaller than a full index. Only covers queries with WHERE status = 'active'.

Covering Index (INCLUDE)

Eliminate table lookups by including extra columns in the index:

CREATE INDEX CONCURRENTLY idx_orders_status_covering
ON orders (status)
INCLUDE (customer_id, total);

Queries selecting only customer_id and total with a status filter can be served entirely from the index.

BRIN Index (Time-Series)

Block Range Index stores min/max values per block range. Ideal for append-only, physically ordered data:

CREATE INDEX idx_logs_timestamp
ON logs USING brin (created_at);

100x smaller than an equivalent B-tree. Only effective when physical row order correlates with the indexed column.

HNSW Vector Index (pgvector)

For semantic search and AI embeddings:

CREATE INDEX idx_embeddings_vector
ON embeddings USING hnsw (embedding_vector vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

At query time, control search breadth with ef_search (default 40, max 1000):

SET hnsw.ef_search = 100;

Increase m and ef_construction if recall is too low. Decrease if index build time is excessive. Indexes build faster when the graph fits into maintenance_work_mem.

UUIDv7 Migration

Random UUIDv4 primary keys cause B-tree page splits because new values are randomly distributed. UUIDv7 is time-ordered (RFC 9562), providing sequential inserts.

PostgreSQL 18 (Built-in)

CREATE TABLE transactions (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  amount numeric NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Extract timestamp from a UUIDv7 value
SELECT uuid_extract_timestamp(id) FROM transactions LIMIT 1;

-- uuidv4() is a PG18 alias for gen_random_uuid()

The built-in uuidv7() guarantees monotonic ordering within the same backend process, even for sub-millisecond generation.

Pre-PostgreSQL 18 (Extension)

-- Requires pg_uuidv7 extension
CREATE EXTENSION pg_uuidv7;

CREATE TABLE transactions (
  id uuid DEFAULT uuid_generate_v7() PRIMARY KEY,
  amount numeric NOT NULL,
  created_at timestamptz DEFAULT now()
);

Benefits: Better B-tree insert locality, reduced index fragmentation. The time component enables rough time-based ordering without a separate timestamp column.

Autovacuum Tuning

Autovacuum reclaims space from dead tuples (rows deleted or updated). Default settings trigger after 20% of the table changes.

Aggressive Settings for High-Churn Tables

ALTER TABLE high_churn_orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 1000,
  autovacuum_analyze_scale_factor = 0.005
);

Global Settings

# postgresql.conf
autovacuum_max_workers = 6
autovacuum_naptime = '30s'
autovacuum_vacuum_cost_delay = '2ms'

HOT (Heap Only Tuple) Updates

HOT updates avoid creating new index entries when an UPDATE does not modify any indexed column. They are a significant performance win.

Optimization rule: Only index columns you actually filter, sort, or join on. Every extra index:

• Slows INSERT and UPDATE operations
• Prevents HOT updates for modifications to indexed columns
• Consumes additional disk space and shared_buffers

Bloat Detection

Find tables with the most dead tuples:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

Action thresholds:

Reclaiming Space

-- Standard vacuum (reclaims space within table, non-blocking)
VACUUM VERBOSE orders;

-- Full vacuum (rewrites entire table, requires exclusive lock)
VACUUM FULL orders;

-- Reindex without locking (preferred)
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- pg_repack (no locking, requires extension)
-- pg_repack --table=orders --no-order

Prefer REINDEX CONCURRENTLY over VACUUM FULL in production because it does not require an exclusive lock.

GIN Index for JSONB

CREATE INDEX idx_metadata_gin
ON products USING gin (metadata);

SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

GIN indexes support containment (@>), existence (?), and key-path operators on JSONB columns.

Connection Pooling, Partitioning, and Query Patterns

PostgreSQL holds one OS process per connection. Without pooling, high connection counts waste memory and create scheduling overhead. Partitioning keeps large tables manageable. The query patterns in this file cover pagination, queue processing, and update performance.

Connection Pooling

Why Pooling Matters

Each PostgreSQL backend consumes ~5-10 MB of RAM and involves process forking overhead. Without pooling:

• 500 concurrent app connections = 500 backend processes
• Serverless functions spike connections on cold starts
• Connection storms during deployments exhaust max_connections

Rule of thumb: Set max_connections = 100 * CPU_cores in PostgreSQL. Route all application traffic through a pooler targeting 3-5 connections per CPU core to the database.

PgBouncer

PgBouncer is the standard lightweight connection pooler for PostgreSQL.

Transaction Mode (Recommended for Most Apps)

A client holds a server connection only for the duration of a transaction. The connection returns to the pool between transactions.

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

Session Mode

A client holds a server connection for its entire session lifetime. Use only when the application requires session-level features (SET, temp tables, advisory locks, LISTEN/NOTIFY).

pool_mode = session

Transaction mode does not support: SET outside a transaction, temporary tables, advisory locks held across statements, LISTEN/NOTIFY. Switch to session mode for these, or use SET LOCAL inside transactions.

Pool Size Formula

pool_size = (max_db_connections * 0.8) / num_pgbouncer_instances

Reserve 20% of max_connections for direct admin access, monitoring, and migrations.

Monitoring PgBouncer

-- Connect to the pgbouncer admin database
SHOW POOLS;
SHOW CLIENTS;
SHOW STATS;

Watch cl_waiting in SHOW POOLS — clients waiting for a connection. Non-zero values indicate the pool is undersized or the database is saturated.

Supavisor

Supavisor is a cloud-native, multi-tenant pooler built by Supabase. It runs in Elixir and supports the same transaction/session/statement modes as PgBouncer but adds:

• Per-tenant isolation without separate processes
• Integrated metrics and observability
• Horizontal scaling across multiple nodes

Use Supavisor when running a multi-tenant SaaS with many databases, or when deploying on Supabase. PgBouncer remains the standard for single-database deployments.

When to Use Pooling

---

Declarative Partitioning

Partitioning splits a large table into smaller physical tables (partitions) while presenting a single logical table to queries. The planner prunes irrelevant partitions from query plans.

Range Partitioning by Date

The most common partitioning strategy for time-series data such as events, logs, and orders.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  occurred_at timestamptz NOT NULL,
  event_type text NOT NULL,
  payload jsonb
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2024_q1 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE events_2024_q2 PARTITION OF events
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE events_2024_q3 PARTITION OF events
  FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE events_2024_q4 PARTITION OF events
  FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

CREATE TABLE events_default PARTITION OF events DEFAULT;

Always include a DEFAULT partition to catch rows that fall outside defined ranges.

Partition Pruning

The planner eliminates partitions that cannot contain matching rows based on the WHERE clause. Pruning only works when the partition key is present in the filter.

-- Pruning works: planner scans only events_2024_q1
EXPLAIN SELECT * FROM events
WHERE occurred_at BETWEEN '2024-01-01' AND '2024-03-31';

-- Pruning does NOT work: no filter on occurred_at
EXPLAIN SELECT * FROM events
WHERE event_type = 'signup';

Look for Partitions selected in the EXPLAIN output to confirm pruning is active.

-- Enable partition pruning (default on)
SET enable_partition_pruning = on;

List Partitioning

Partition by a discrete column such as region, tenant, or status.

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY,
  region text NOT NULL,
  amount numeric NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders
  FOR VALUES IN ('us-east', 'us-west');

CREATE TABLE orders_eu PARTITION OF orders
  FOR VALUES IN ('eu-west', 'eu-central');

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Indexes on Partitioned Tables

Indexes created on the parent table are automatically created on all existing and future partitions.

-- Index propagates to all partitions
CREATE INDEX CONCURRENTLY idx_events_occurred_at
ON events (occurred_at);

pg_partman for Automation

Manually creating partitions is error-prone. pg_partman automates partition creation and maintenance.

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
  p_parent_table   => 'public.events',
  p_control        => 'occurred_at',
  p_interval       => 'monthly',
  p_premake        => 3
);

p_premake = 3 creates 3 future partitions in advance, preventing gaps. Run the maintenance function on a cron schedule:

SELECT partman.run_maintenance_proc();

Schedule via pg_cron or an external scheduler. Without regular maintenance, new partitions are not created automatically.

Partition Anti-Patterns

---

Practical Query Patterns

Cursor-Based (Keyset) Pagination

OFFSET pagination degrades as page numbers grow because the database must scan and discard all preceding rows. Keyset pagination uses a stable cursor based on the last seen row.

OFFSET Pagination (Avoid for Deep Pages)

-- Scans and discards 10,000 rows on every call at page 200
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 10000;

Keyset Pagination

-- First page: no cursor
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Subsequent pages: use last row values as cursor
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2024-03-15 10:00:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 50;

The composite WHERE clause skips directly to the next page without scanning preceding rows.

Required index for efficient keyset pagination:

CREATE INDEX CONCURRENTLY idx_posts_cursor
ON posts (created_at DESC, id DESC);

TypeScript Application Pattern

async function getPostsPage(cursor?: { createdAt: Date; id: number }) {
  const rows = await db.query<Post>(
    cursor
      ? `SELECT id, created_at, title
         FROM posts
         WHERE (created_at, id) < ($1, $2)
         ORDER BY created_at DESC, id DESC
         LIMIT 50`
      : `SELECT id, created_at, title
         FROM posts
         ORDER BY created_at DESC, id DESC
         LIMIT 50`,
    cursor ? [cursor.createdAt, cursor.id] : [],
  );

  const nextCursor =
    rows.length === 50
      ? {
          createdAt: rows[rows.length - 1].created_at,
          id: rows[rows.length - 1].id,
        }
      : null;

  return { rows, nextCursor };
}

Trade-offs:

Queue Processing with FOR UPDATE SKIP LOCKED

FOR UPDATE SKIP LOCKED enables multiple workers to claim jobs from a shared queue without contention. Rows locked by one worker are silently skipped by others instead of blocking.

-- Worker claims the next available job
WITH claimed AS (
  SELECT id, payload, attempts
  FROM job_queue
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at ASC
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE job_queue
SET status = 'processing',
    started_at = now()
FROM claimed
WHERE job_queue.id = claimed.id
RETURNING job_queue.id, job_queue.payload;

Each worker locks exactly one row. Other workers skip that row and move to the next without waiting for a lock.

Required index to avoid a full table scan:

CREATE INDEX CONCURRENTLY idx_job_queue_status_run_at
ON job_queue (status, run_at)
WHERE status = 'pending';

TypeScript Worker Pattern

async function claimNextJob(db: DatabaseClient) {
  const result = await db.query<{ id: number; payload: unknown }>(`
    WITH claimed AS (
      SELECT id, payload
      FROM job_queue
      WHERE status = 'pending'
        AND run_at <= now()
      ORDER BY run_at ASC
      LIMIT 1
      FOR UPDATE SKIP LOCKED
    )
    UPDATE job_queue
    SET status = 'processing', started_at = now()
    FROM claimed
    WHERE job_queue.id = claimed.id
    RETURNING job_queue.id, job_queue.payload
  `);

  return result.rows[0] ?? null;
}

Requirements: Must run inside an explicit transaction. The lock is held until the transaction commits or rolls back.

fillfactor for HOT Updates

By default, PostgreSQL fills data pages to 100% (fillfactor = 100). When a row is updated, a new row version is written to the next available page, requiring an index entry update. Setting fillfactor below 100 leaves free space on each page, allowing HOT (Heap Only Tuple) updates to write the new row version on the same page without touching indexes.

-- Create table with fillfactor
CREATE TABLE sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id bigint NOT NULL,
  last_seen timestamptz NOT NULL,
  metadata jsonb
) WITH (fillfactor = 70);

-- Apply to an existing table
ALTER TABLE sessions SET (fillfactor = 70);

-- Reclaim space and apply the setting (requires brief lock)
VACUUM sessions;

Verify HOT updates are happening:

SELECT relname, n_tup_hot_upd, n_tup_upd,
  round(n_tup_hot_upd::numeric / nullif(n_tup_upd, 0) * 100, 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'sessions';

A hot_pct below 50% on a frequently updated table suggests removing unnecessary indexes or lowering fillfactor.

NULLS NOT DISTINCT (PostgreSQL 15+)

Standard SQL treats NULL as distinct from every other value, including other NULLs. A unique index on a nullable column allows multiple rows with NULL in that column. NULLS NOT DISTINCT changes this — NULL is treated as a regular value for uniqueness purposes.

-- PG15+: Only one row with NULL allowed in email
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email) NULLS NOT DISTINCT;

-- Table-level constraint
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE NULLS NOT DISTINCT (email);

Composite unique constraint example — useful for soft-delete patterns where deleted_at is nullable:

-- Allow multiple deleted rows with the same username, but only one active row
CREATE UNIQUE INDEX idx_users_username_active
ON users (username, deleted_at) NULLS NOT DISTINCT;

Without NULLS NOT DISTINCT, the index allows unlimited rows where deleted_at IS NULL and username = 'alice' — the opposite of the intended constraint.

Pre-PostgreSQL 15 workaround:

-- Partial unique index covers only the non-deleted (active) case
CREATE UNIQUE INDEX idx_users_username_active
ON users (username)
WHERE deleted_at IS NULL;

This is still the correct approach for multi-column uniqueness involving null semantics on older versions.

Query Plan Analysis and Operator Forensics

Optimizing queries without analyzing execution plans is guesswork. Use EXPLAIN ANALYZE with full context flags to identify bottlenecks.

The Standard EXPLAIN Call

-- PG18: BUFFERS is auto-included with ANALYZE
EXPLAIN (ANALYZE, VERBOSE, SETTINGS)
SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

-- Pre-PG18: explicitly include BUFFERS
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

Key Metrics to Audit

Identifying Slow Operators

Sequential Scan (Seq Scan)

Reads the entire table row by row. Acceptable for small tables (<1000 rows) or when reading most of the table.

Problem indicator: Seq Scan on a table with >10,000 rows in a WHERE clause.

Fix: Add an index on the filtered column:

CREATE INDEX CONCURRENTLY idx_orders_status
ON orders (status);

Nested Loop

Scans Table B for every row in Table A. Efficient when Table A returns few rows and Table B has an index on the join key.

Problem indicator: Nested Loop with high row counts on both sides.

Fix: Ensure Table B has an index on the join column:

CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders (customer_id);

Hash Join

Builds a hash table from one side and probes it with the other. Good for large joins but memory-intensive.

Problem indicator: Batches: 2 or higher in the plan means the hash table spilled to disk.

Fix: Increase work_mem for the session:

SET work_mem = '128MB';

Merge Join

Sorts both sides and merges. Efficient when both inputs are already sorted or have matching indexes.

B-tree Skip Scans (PostgreSQL 18)

PostgreSQL 18 can skip irrelevant parts of a composite index's leading column, using repeated targeted index searches for each distinct value in the prefix.

Scenario: Index on (tenant_id, status), query filters only on status:

SELECT * FROM orders WHERE status = 'pending';

When skip scan works best:

• Low cardinality (few distinct values) in the skipped prefix column
• Equality conditions on the trailing column (not ranges or inequalities)
• Narrow result sets and covering indexes (Index-Only Scans)

When skip scan does NOT help:

• High-cardinality prefix columns (millions of distinct values create too many probes)
• Range predicates (>, <, BETWEEN) on trailing columns -- equality only
• Large result sets where sequential or bitmap scans are more efficient

This can reduce the number of indexes needed, lowering storage costs and improving write performance. The planner enables skip scan automatically based on table statistics.

work_mem Tuning

work_mem controls memory available for sort and hash operations per query operator.

-- Check current setting
SHOW work_mem;

-- Increase for a specific session
SET work_mem = '64MB';

-- Increase for a specific query
SET LOCAL work_mem = '256MB';
SELECT ... ORDER BY complex_expression;
RESET work_mem;

Caution: work_mem is allocated per operator, not per query. A query with 5 sort/hash operations uses up to 5x the work_mem value. Set high values only for specific sessions, not globally.

Detecting work_mem Issues

Look for these in EXPLAIN output:

Sort Method: external merge  Disk: 45MB

This means the sort exceeded work_mem and spilled to disk. Increase work_mem to at least the reported disk size.

pg_stat_statements

The most important extension for identifying slow query patterns:

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 10 slowest queries by total time
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((shared_blks_hit::numeric /
    nullif(shared_blks_hit + shared_blks_read, 0)) * 100, 2) AS cache_hit_pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Review cadence: Check weekly for new slow queries. Focus on queries with high total_exec_time (most cumulative impact) rather than high mean_exec_time (single worst case).

Plan Analysis Workflow

1. Identify the slow query from pg_stat_statements 2. Run EXPLAIN (ANALYZE, BUFFERS, SETTINGS) on it 3. Find the most expensive node (highest actual time) 4. Check buffer hits vs reads (cache efficiency) 5. Look for Seq Scans on large tables, disk sorts, and high "Rows Removed" 6. Fix with targeted indexes, work_mem increases, or query rewrites 7. Verify by re-running EXPLAIN and comparing costs