PostgreSQL Internals and Tuning

Most PostgreSQL performance problems I have seen — and I have seen a lot of them — trace back to the same root cause: the operator does not understand what the database is actually doing. They cargo-cult a Stack Overflow answer, set shared_buffers to something plausible-sounding, and call it done. Then three months later they are debugging a table with 400 million dead tuples, a planner choosing sequential scans on indexed columns, and autovacuum perpetually behind. Understanding the internals is not optional if you want to run PostgreSQL reliably at scale. It is the entire job.

PostgreSQL is not a black box. It is a remarkably well-documented, decades-mature system with clean abstractions. The server is a multi-process architecture: a postmaster parent process listens for connections and forks a backend process for each new connection. There is no thread pool. Each client gets its own OS process, which is why max_connections has real teeth. Background workers handle work that does not belong to a specific session: the bgwriter writes dirty pages from the shared buffer pool to disk ahead of checkpoints, the walwriter flushes WAL buffers, the checkpointer manages checkpoint cycles, and autovacuum launchers and workers reclaim dead tuple space. All of these processes share a single large chunk of shared memory: the shared buffer pool. Understanding how data flows through this system — from a write landing in shared buffers, to WAL, to disk, to a read being served from cache — is the foundation for every tuning decision that follows.

This guide covers PostgreSQL 17/18 (the current major versions as of mid-2026) and aims to give you the mental model, not just the commands. We will go deep on MVCC, storage internals, WAL, VACUUM, query planning, indexes, monitoring, and configuration. By the end you should be able to read a production system and reason about what is happening and why.

MVCC: Multi-Version Concurrency Control

The central design decision in PostgreSQL’s concurrency model is this: readers must never block writers, and writers must never block readers. This sounds obvious but most databases do not achieve it cleanly. Traditional locking approaches — read locks, write locks, two-phase locking — force reads and writes to serialize against each other. PostgreSQL’s answer is MVCC, and it shapes every aspect of how the database stores and retrieves data.

The Hidden System Columns

Every row in every PostgreSQL table carries hidden system columns. You cannot see them with SELECT *, but they are there:

• xmin: the transaction ID (XID) of the transaction that inserted this row version
• xmax: the transaction ID of the transaction that deleted or updated this row (0 if the row is live and has not been deleted)
• ctid: the physical location of this row tuple, as (page_number, item_offset)
• cmin / cmax: command IDs for intra-transaction visibility (rarely relevant for tuning)

You can query them directly:

1
2
3

SELECT xmin, xmax, ctid, id, email
FROM users
LIMIT 5;

 xmin  | xmax  | ctid  | id |       email
-------+-------+-------+----+--------------------
 12041 |     0 | (0,1) |  1 | alice@example.com
 12041 |     0 | (0,2) |  2 | bob@example.com
 12089 | 12102 | (0,3) |  3 | carol@example.com
 12102 |     0 | (2,1) |  3 | carol@newdomain.com
 12041 |     0 | (0,4) |  4 | dave@example.com

Notice row 3 appears twice. Transaction 12089 inserted carol@example.com. Transaction 12102 updated it to carol@newdomain.com. The old version has xmax = 12102 (the XID that superseded it), and the new version lives at a different physical location (2,1) with xmin = 12102 and xmax = 0 (still live).

What Happens on UPDATE

When PostgreSQL executes an UPDATE, it does not modify the existing row in place. It writes a completely new row version (called a tuple) to the heap, marks the old tuple’s xmax with the current transaction ID, and updates the relevant indexes to point to the new tuple location. The old tuple remains on disk until VACUUM reclaims it.

BEFORE UPDATE of row id=3:

  Page 0                          Page 2
  +-----------------+             +-----------------+
  | (0,1) xmin=12041|             |                 |
  |   id=1          |             |                 |
  |   alice@...     |             |                 |
  +-----------------+             |                 |
  | (0,2) xmin=12041|             |                 |
  |   id=2          |             |                 |
  |   bob@...       |             |                 |
  +-----------------+             |                 |
  | (0,3) xmin=12089|             |                 |
  |   id=3          |             |                 |
  |   carol@old     |  <-- live   |                 |
  |   xmax=0        |             |                 |
  +-----------------+             +-----------------+

AFTER UPDATE (tx 12102):

  Page 0                          Page 2
  +-----------------+             +-----------------+
  | (0,1) xmin=12041|             | (2,1) xmin=12102|
  |   id=1          |             |   id=3          |
  |   alice@...     |             |   carol@new     |
  +-----------------+             |   xmax=0        |  <-- new live version
  | (0,2) xmin=12041|             +-----------------+
  |   id=2          |
  |   bob@...       |
  +-----------------+
  | (0,3) xmin=12089|  <-- dead tuple
  |   id=3          |      (xmax set, no tx can see it
  |   carol@old     |       once tx 12102 committed)
  |   xmax=12102    |
  +-----------------+

Transaction Snapshots and Visibility

When a transaction begins, PostgreSQL captures a snapshot of the current state of the transaction ID counter. The snapshot records which XIDs are in-flight (started but not committed). A tuple is visible to a transaction if:

1. xmin is committed and was committed before the snapshot was taken
2. xmax is either zero, or represents a transaction that was not yet committed when the snapshot was captured

This means a long-running transaction can see an old version of a row even after it has been updated and committed by another transaction. The old tuple must remain on disk for exactly this reason. Two transactions can read and write the same table concurrently without blocking each other because each sees its own consistent snapshot.

The practical consequence for the READ COMMITTED isolation level (the default) is that each statement within a transaction gets a fresh snapshot. Under REPEATABLE READ or SERIALIZABLE, the snapshot is taken once at the start of the transaction and held for the duration.

The Cost: Dead Tuples and Bloat

MVCC elegance comes with a concrete storage cost. Every UPDATE produces a dead tuple. Every DELETE leaves a dead tuple. These dead tuples accumulate on heap pages, consuming space and forcing sequential scans to read through more pages than necessary. This is table bloat, and it is one of the most common performance problems in production PostgreSQL systems.

The pg_stat_user_tables view tells you how bad things are:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Transaction ID Wraparound

XID is a 32-bit unsigned integer. After approximately 2.1 billion transactions, it wraps around. This is not a theoretical concern — PostgreSQL shuts down rather than risk data corruption from XID wraparound if datfrozenxid gets too old. The system starts emitting warnings at 40 million transactions before the limit.

Autovacuum’s VACUUM FREEZE operation addresses this. It scans tuples whose xmin is old enough and marks them as “frozen” — permanently visible to all future transactions, removing their dependence on XID comparison. Watch this:

1
2
3
4
5
6

SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    2100000000 - age(datfrozenxid) AS transactions_remaining
FROM pg_database
ORDER BY xid_age DESC;

If xid_age is climbing toward 200 million and autovacuum is not keeping up, you have a problem. Manual intervention with VACUUM FREEZE VERBOSE tablename may be necessary.

Heap Storage and TOAST

The Heap: Pages, Tuples, and ctid

PostgreSQL stores table data in heap files. A heap file is divided into 8KB pages (configurable at compile time, but 8KB is universal in practice). Each page has a header, an array of item pointers (line pointers), and the actual tuple data growing from the bottom of the page upward. The free space in the middle is managed by the Free Space Map (FSM).

  8KB Page Layout
  +---------------------------+
  | Page Header (24 bytes)    |
  |   lsn, checksum, flags,  |
  |   lower, upper, special  |
  +---------------------------+
  | Item Pointer 1 (4 bytes) |  --> points to Tuple 1
  | Item Pointer 2 (4 bytes) |  --> points to Tuple 2
  | Item Pointer 3 (4 bytes) |  --> (dead, points nowhere useful)
  | ...                       |
  +---------------------------+
  |       Free Space          |
  +---------------------------+
  | Tuple 3 data              |
  | Tuple 2 data              |
  | Tuple 1 data (xmin, xmax, |
  |   ctid, null bitmap, data)|
  +---------------------------+
  | Special Space (indexes)   |
  +---------------------------+

The ctid value (3, 2) means page 3, item pointer 2. ctid changes when a row is moved by VACUUM or VACUUM FULL. Do not use ctid as a stable row identifier — it is useful for debugging and for HOT chain following, but it is emphatically not a primary key.

Fill Factor and HOT Updates

By default, PostgreSQL packs heap pages to 100% capacity (fillfactor = 100). This is space-efficient but means an UPDATE must write the new tuple to a different page, which requires updating every index that contains that row.

Lowering the fill factor (e.g., fillfactor = 70) reserves 30% of each page for updates. When an UPDATE modifies a row without touching any indexed columns, and there is free space on the same page, PostgreSQL can use a Heap Only Tuple (HOT) update. The new tuple is written in the reserved space on the same page, and the old item pointer is rewritten to redirect to the new tuple location. No index entries need updating.

1
2
3
4
5
6
7
8

-- Create a table with 70% fill factor
CREATE TABLE events (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL,
    event_type  text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) WITH (fillfactor = 70);

HOT updates are dramatically faster for write-heavy tables where updates frequently touch non-indexed columns. The trade-off is 30% more storage for the table. For a table under heavy churn where you frequently update a status or updated_at column that is not indexed, the write performance gain is well worth it.

TOAST: The Oversized-Attribute Storage Technique

PostgreSQL’s page size is 8KB. A single tuple must fit on one page (with some exceptions). When column values exceed roughly 2KB, PostgreSQL automatically stores them in a separate TOAST table, with the main table row holding only a small pointer.

TOAST applies per-column and operates transparently. The storage strategy for each column determines behavior:

You can inspect and change a column’s TOAST strategy:

1
2
3
4
5
6
7

-- See current storage strategies
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'my_table'::regclass AND attnum > 0;

-- Change strategy for a large JSON column
ALTER TABLE my_table ALTER COLUMN payload SET STORAGE EXTERNAL;

TOAST is a major feature for handling documents, JSON payloads, and binary data without bloating main table pages. The caveat is that queries fetching large TOAST values incur additional I/O to read the TOAST table, which is a separate file. For queries that only need small columns from a wide table, PostgreSQL may avoid TOAST reads entirely if those columns are indexed with an Index Only Scan. For queries that must fetch large TOAST columns for every matching row, the TOAST I/O cost can dominate.

Each table with TOASTable columns has a corresponding pg_toast.pg_toast_<oid> table. You can inspect its size:

1
2
3
4
5
6
7
8

SELECT
    c.relname AS table_name,
    t.relname AS toast_table,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS table_total,
    pg_size_pretty(pg_relation_size(t.oid)) AS toast_size
FROM pg_class c
JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relname = 'my_table';

Write-Ahead Log (WAL)

The Fundamental Contract

The WAL is PostgreSQL’s durability mechanism. Before any change is written to a data file, a record of that change is written sequentially to the WAL. On crash, PostgreSQL replays WAL records from the last checkpoint to bring data files back to a consistent state. This is the standard write-ahead logging protocol — nothing is novel here — but understanding the details of PostgreSQL’s implementation is essential for tuning.

  Write Path (simplified):

  Client COMMIT
       |
       v
  Backend Process
       |
       +---> Write change to shared_buffers (in-memory)
       |
       +---> Write WAL record to WAL buffers (in-memory)
       |
       v
  WAL Writer (background)
       |
       v
  WAL Files in pg_wal/ (on disk, sequential write)
       |
       v  (async, by bgwriter and checkpointer)
  Data Files (heap, index files)
       ^
       |
  Checkpoint: flush all dirty shared_buffers pages
              to data files, write checkpoint record to WAL

WAL is written sequentially. Sequential writes are among the fastest operations any storage system can do — even spinning disk. This is why PostgreSQL can sustain high write throughput even on relatively slow storage. The penalty comes from checkpoints, which involve random writes to data files.

WAL Segments and pg_wal

WAL is organized into 16MB segment files in the pg_wal/ directory (formerly pg_xlog/, renamed in PostgreSQL 10). Files are named with a 24-character hex string encoding the timeline and segment number:

000000010000001F00000001
000000010000001F00000002
000000010000001F00000003

You can inspect WAL content with pg_waldump:

1
2
3

pg_waldump --path=/var/lib/postgresql/data/pg_wal \
           000000010000001F00000001 \
           --timeline=1

This is invaluable for debugging replication issues or understanding what a transaction actually wrote.

wal_level and synchronous_commit

wal_level controls how much information is written to WAL:

• minimal: Just enough for crash recovery. Cannot support replication or WAL archiving.
• replica: Supports streaming replication and WAL archiving. This is the right choice for any production deployment.
• logical: Adds information needed for logical decoding and logical replication. Required for tools like Debezium or pglogical.

synchronous_commit is a per-transaction parameter that controls durability guarantees:

For workloads that can tolerate losing a few hundred milliseconds of writes (session state, analytics events, ephemeral logs), synchronous_commit = off at the session or transaction level can be a significant throughput win with no application-level code change:

1
2
3
4
5

-- For a batch insert session that does not need strict durability
SET synchronous_commit = off;
BEGIN;
INSERT INTO event_log SELECT ... FROM staging;
COMMIT;

Checkpoints

A checkpoint writes all dirty pages from shared_buffers to their data files and writes a checkpoint record to WAL. After a checkpoint, PostgreSQL knows that all WAL before that point is no longer needed for crash recovery (though it may be needed for replication or archiving). Checkpoints are triggered by:

• max_wal_size worth of WAL accumulating since the last checkpoint (default 1GB)
• The checkpoint_timeout interval elapsing (default 5 minutes)
• A manual CHECKPOINT command

The critical tuning parameters here are checkpoint_completion_target (default 0.9) and max_wal_size. With checkpoint_completion_target = 0.9, PostgreSQL tries to spread the checkpoint I/O over 90% of the interval between checkpoints, smoothing out the I/O spikes that would otherwise hammer storage every 5 minutes. On write-heavy workloads, increase max_wal_size to allow longer checkpoint intervals, reducing the frequency of the full-table-scan-equivalent I/O storm that a checkpoint represents.

Watch for this warning in your logs:

LOG:  checkpoints are occurring too frequently (9 seconds apart)
HINT:  Consider increasing the configuration parameter "max_wal_size".

WAL Archiving for PITR

Point-In-Time Recovery (PITR) requires a base backup plus all WAL segments generated since that backup. Configure archiving:

1
2
3
4

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-bucket/wal-archive/%f'

%p is the full path to the WAL file; %f is just the filename. The archive command must return exit code 0 on success. PostgreSQL retries on failure. Combined with pg_basebackup for the initial backup, this gives you the ability to restore to any point in time after the last base backup.

VACUUM and Autovacuum

What VACUUM Actually Does

VACUUM serves several distinct functions, and conflating them leads to poor tuning decisions:

1. Reclaims space from dead tuples: Scans the heap, identifies tuples whose xmax transaction has committed and that no current transaction can see, and marks those page slots as reusable. It does NOT shrink the table file — the space is made available for future inserts, but the file size stays the same.

2. Updates the Free Space Map: Records which pages have free space so INSERT can use them without scanning the entire table.

3. Updates the Visibility Map: Marks pages where all tuples are known visible to all current and future transactions. Index Only Scans use this to avoid heap accesses entirely.

4. Prevents XID wraparound: Freezes old tuple XIDs, advancing pg_database.datfrozenxid.

What VACUUM does NOT do: it does not return space to the OS. Only VACUUM FULL rewrites the table file and shrinks it, but VACUUM FULL takes an ACCESS EXCLUSIVE lock, blocking all reads and writes. On a large table this can mean hours of downtime. The practical alternative for severely bloated tables is pg_repack, which reorganizes the table online using triggers and a shadow table, releasing the lock once done.

Detecting Bloat

Start with pg_stat_user_tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    schemaname || '.' || relname AS table,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_autovacuum::date,
    last_autoanalyze::date
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

For a deeper estimate, the pgstattuple extension gives accurate page-level statistics (at the cost of a full table scan):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    tuple_len,
    tuple_percent,
    dead_tuple_len,
    dead_tuple_percent,
    free_space,
    free_percent
FROM pgstattuple('orders');

A dead_tuple_percent above 10-15% on a table under active use suggests autovacuum is not keeping up. Above 30%, you likely have a configuration or a long-running transaction problem.

Autovacuum Configuration

Autovacuum fires on a table when dead tuples exceed:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

Default: 50 + 0.20 * n_live_tup. This means a 100-million-row table does not get vacuumed until it has accumulated 20 million dead tuples. That is not a reasonable threshold. For large tables, set per-table storage parameters:

1
2
3
4
5
6

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_analyze_threshold = 500
);

This triggers vacuum after 1% dead tuples on the orders table, regardless of global settings.

Autovacuum also has a built-in throttle to limit I/O impact. Each 8KB page of work costs “vacuum cost” points, and when the total hits autovacuum_vacuum_cost_limit (default 200), the worker sleeps for autovacuum_vacuum_cost_delay milliseconds (default 2ms). This makes autovacuum gentle but slow. On modern SSDs with high I/O bandwidth to spare:

1
2

autovacuum_vacuum_cost_delay = 2ms         # default, reduce to 0 for aggressive vacuum
autovacuum_vacuum_cost_limit = 800         # default 200, increase for faster vacuum

Or per-table, during a maintenance window when you need to catch up:

1

ALTER TABLE orders SET (autovacuum_vacuum_cost_delay = 0);

Key pg_stat_user_tables Columns

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

SELECT
    relname,
    seq_scan,              -- full sequential scans (symptom of missing indexes)
    seq_tup_read,          -- rows read by sequential scans
    idx_scan,              -- index scans
    n_tup_ins,             -- rows inserted
    n_tup_upd,             -- rows updated
    n_tup_del,             -- rows deleted
    n_tup_hot_upd,         -- HOT updates (good: no index maintenance)
    n_live_tup,
    n_dead_tup,
    n_mod_since_analyze,   -- rows modified since last ANALYZE (drives autoanalyze trigger)
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'orders';

The ratio n_tup_hot_upd / n_tup_upd tells you what fraction of updates are HOT updates. If this is low on a write-heavy table, investigate whether the fill factor should be reduced or whether you are over-indexing.

Manual VACUUM and FREEZE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- Combined vacuum + statistics update (do this after bulk loads)
VACUUM ANALYZE orders;

-- Verbose output to see what VACUUM is doing
VACUUM VERBOSE orders;

-- Freeze old tuples to push back XID wraparound deadline
VACUUM FREEZE orders;

-- Check per-table frozen XID age
SELECT
    c.relname,
    age(c.relfrozenxid) AS table_xid_age,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind = 'r'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

EXPLAIN ANALYZE: Reading the Output

EXPLAIN shows the query plan the planner chose. EXPLAIN ANALYZE executes the query and annotates the plan with actual runtime statistics. Always use:

1

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <your query>;

BUFFERS adds cache hit/miss statistics. FORMAT TEXT is the default and most readable. FORMAT JSON is useful for programmatic analysis.

Anatomy of an EXPLAIN ANALYZE Output

Consider this query:

1
2
3
4
5
6
7
8

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '7 days'
ORDER BY o.total DESC
LIMIT 100;

Output:

Limit  (cost=4823.12..4823.37 rows=100 width=44)
       (actual time=28.441..28.461 rows=100 loops=1)
  Buffers: shared hit=892 read=147
  ->  Sort  (cost=4823.12..4836.87 rows=5499 width=44)
            (actual time=28.438..28.447 rows=100 loops=1)
        Sort Key: o.total DESC
        Sort Method: top-N heapsort  Memory: 35kB
        Buffers: shared hit=892 read=147
        ->  Hash Join  (cost=2145.00..4686.90 rows=5499 width=44)
                       (actual time=12.331..27.801 rows=5421 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=892 read=147
              ->  Bitmap Heap Scan on orders o
                      (cost=312.45..2283.12 rows=5499 width=32)
                      (actual time=2.441..14.332 rows=5421 loops=1)
                    Recheck Cond: ((status = 'pending') AND
                                   (created_at > (now() - '7 days'::interval)))
                    Heap Blocks: exact=1021
                    Buffers: shared hit=412 read=147
                    ->  BitmapAnd  (cost=312.45..312.45 rows=5499 width=0)
                                   (actual time=2.109..2.109 rows=0 loops=1)
                          ->  Bitmap Index Scan on idx_orders_status
                                  (cost=0.00..89.12 rows=18203 width=0)
                                  (actual time=0.981..0.981 rows=18411 loops=1)
                                Index Cond: (status = 'pending')
                          ->  Bitmap Index Scan on idx_orders_created_at
                                  (cost=0.00..222.89 rows=21456 width=0)
                                  (actual time=1.021..1.021 rows=21892 loops=1)
                                Index Cond: (created_at > ...)
              ->  Hash  (cost=1489.00..1489.00 rows=89200 width=20)
                        (actual time=9.771..9.771 rows=89200 loops=1)
                    Buckets: 131072  Batches: 1  Memory Usage: 5632kB
                    Buffers: shared hit=480
                    ->  Seq Scan on customers c
                            (cost=0.00..1489.00 rows=89200 width=20)
                            (actual time=0.012..5.441 rows=89200 loops=1)
                          Buffers: shared hit=480

Planning Time: 0.841 ms
Execution Time: 28.531 ms

Field by field:

• cost=4823.12..4825.37: startup cost (cost before the first row is returned) and total cost in arbitrary planner units. The ratio of these numbers matters: a Sort has high startup cost because it must consume all input before returning anything. A Limit node above a Sort short-circuits to “top-N heapsort” as an optimization.

• rows=100: planner’s estimated output rows. actual rows=100 confirms it. When these diverge significantly (e.g., estimated 5 actual 50,000), the planner may have chosen a suboptimal plan. Stale statistics are the usual cause — run ANALYZE tablename.

• width=44: estimated average width of output rows in bytes.

• loops=1: how many times this node was executed. For inner nodes of a Nested Loop Join, this can be large. Critically, actual time is per loop, not total. Multiply actual time by loops to get the real cost contribution of an inner node.

• Buffers: shared hit=892 read=147: 892 pages served from shared_buffers (fast); 147 pages read from disk (slow). In a well-cached production system you want read to be low or zero for frequently-executed queries. A cache hit ratio below 99% for your hottest queries is worth investigating.

• Sort Method: top-N heapsort Memory: 35kB: PostgreSQL recognized the LIMIT and used a heap sort that only tracks the top 100 rows, consuming minimal memory. Without the LIMIT it would have sorted all 5421 rows.

• Bitmap Heap Scan with Recheck Cond: a two-phase scan. The Bitmap Index Scans build an in-memory bitmap of page numbers matching each condition. BitmapAnd intersects the bitmaps. Then the Bitmap Heap Scan fetches the actual pages and rechecks the conditions (necessary because the bitmap is at page granularity, not row granularity). This is the right plan for a range query returning hundreds to thousands of rows — more efficient than individual index scans.

• Seq Scan on customers: sequential scan of 89,200 rows. This is correct here — the Hash Join needs all customer rows to build the hash table. A sequential scan of a small-to-medium table that is fully cached is often faster than an index scan due to sequential I/O prefetching.

Diagnosing row estimate errors:

1
2
3
4
5
6
7
8

-- Statistics on the status column
SELECT
    n_distinct,
    correlation,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

If n_distinct is -1 but the actual number of distinct values is very different from what PostgreSQL believes, or if most_common_vals is missing a heavily-used value, run ANALYZE orders or increase default_statistics_target for that column:

1
2

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

For correlated multi-column predicates (WHERE a = x AND b = y where a and b correlate), PostgreSQL 10+ supports extended statistics:

1
2

CREATE STATISTICS orders_status_date ON status, created_at FROM orders;
ANALYZE orders;

Index Types

PostgreSQL’s extensible index framework supports multiple index types, each suited to different access patterns. Choosing the wrong index type is not just suboptimal — it can make things worse by adding index maintenance overhead without ever being used.

B-tree (Default)

B-tree indexes implement a balanced tree that supports equality, range, prefix matching, sorting, and NULL handling. This is the right choice for roughly 90% of indexes.

Operators supported: =, <, <=, >, >=, BETWEEN, IN, LIKE 'prefix%' (with text_pattern_ops or C locale), IS NULL, IS NOT NULL, ORDER BY.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Standard index
CREATE INDEX ON orders (customer_id);

-- Covering index: include non-key columns to enable Index Only Scans
CREATE INDEX ON orders (customer_id) INCLUDE (status, total);

-- Partial index: only index pending orders — smaller, faster, perfect for status-filtered queries
CREATE INDEX ON orders (customer_id) WHERE status = 'pending';

-- Expression index: case-insensitive email lookup
CREATE UNIQUE INDEX ON users (lower(email));

Partial indexes deserve more use than they get. A table with 100 million completed orders and 50,000 pending orders benefits enormously from a partial index on WHERE status = 'pending' — the index is tiny, fits in memory, and covers exactly the hot path.

GIN (Generalized Inverted Index)

GIN indexes are designed for composite types where a single indexed value contains multiple keys. The classic use cases:

• Arrays: @> (contains), <@ (contained by), && (overlap), = ANY
• JSONB: @> (contains), ? (key exists), ?| (any key exists), ?& (all keys exist), @@ (JSONPath match)
• Full-text search: @@ (text search query match)
• pg_trgm: trigram similarity for fuzzy string matching

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Index for JSONB containment queries
CREATE INDEX ON events USING GIN (payload);

-- Specific JSONB path (more selective, smaller index)
CREATE INDEX ON events USING GIN ((payload -> 'event_type'));

-- Full-text search
CREATE INDEX ON articles USING GIN (to_tsvector('english', title || ' ' || body));

-- Trigram index for LIKE '%substring%' and similarity queries
CREATE EXTENSION pg_trgm;
CREATE INDEX ON products USING GIN (name gin_trgm_ops);

GIN indexes are slow to build and slow to update because they must update posting lists for each key. The fastupdate storage parameter (default on) buffers pending inserts in a separate list and merges them lazily, improving write throughput at the cost of slower reads until the flush occurs.

GiST (Generalized Search Tree)

GiST is a framework for implementing custom index types using arbitrary key types and predicates. PostGIS uses GiST extensively. Built-in uses:

• Geometric types (point, box, polygon, circle): containment, overlap, nearest-neighbor
• Range types (tstzrange, daterange, int4range): overlap (&&), containment (@>, <@), adjacency
• Full-text search (slower than GIN for most cases, but supports nearest-neighbor ranking)
• cube extension for multidimensional data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Spatial index for PostGIS geometries
CREATE INDEX ON locations USING GIST (geom);

-- Nearest-neighbor query using GiST <-> operator
SELECT name, location <-> ST_MakePoint(-122.4, 37.7) AS distance
FROM places
ORDER BY distance
LIMIT 10;

-- Range overlap queries
CREATE INDEX ON reservations USING GIST (during);
SELECT * FROM reservations WHERE during && '[2026-06-01, 2026-06-07]'::tstzrange;

BRIN (Block Range Index)

BRIN is a radically different approach: instead of indexing individual values, it stores the minimum and maximum value for each range of consecutive heap blocks. The index is tiny (a few KB even for billion-row tables) but only useful when the column’s values are strongly correlated with physical storage order.

The textbook BRIN use case is an auto-incrementing timestamp or serial ID on an append-only table. New rows are always written at the end of the heap, so timestamps are naturally ordered by block range. A BRIN index on such a column can answer range queries with high selectivity while consuming almost no space.

1
2
3
4
5

-- BRIN for a time-series events table (timestamps are nearly monotonic)
CREATE INDEX ON sensor_readings USING BRIN (recorded_at) WITH (pages_per_range = 128);

-- BRIN is useless for randomly distributed values
-- e.g., a UUID primary key -- do NOT use BRIN here

If your data is randomly distributed — UUIDs, hashed values, or anything without physical-to-logical correlation — BRIN degenerates to a table scan because every block range’s min/max spans the entire value domain.

Hash Indexes

Hash indexes support only equality (=) queries. They are slightly smaller and marginally faster than B-tree for pure equality lookups. Since PostgreSQL 10 they are WAL-logged and safe for production use. In practice, the difference is rarely significant enough to justify using Hash over B-tree, but for very high-throughput key-value equality lookups they can reduce index size.

Index Type Comparison

Index Bloat and Maintenance

Indexes accumulate dead entries just like heaps do. VACUUM reclaims dead index entries as well as heap tuples, but in some workloads index bloat accumulates faster than autovacuum can handle. Rebuild an index without a lock:

1
2
3

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
-- Or the whole table's indexes:
REINDEX TABLE CONCURRENTLY orders;

Find unused indexes (candidates for removal):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND NOT indisprimary
  AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 after several weeks of production traffic is probably not being used. Confirm by checking pg_stat_reset_shared (when stats were last reset) and drop it. Unused indexes have real costs: every INSERT, UPDATE, and DELETE must maintain them.

pg_stat_statements

Setup

pg_stat_statements is a core extension that tracks planning and execution statistics for every normalized query type. It is indispensable for production query analysis. Enable it:

1
2
3
4

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Restart PostgreSQL, then:

1

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Key Columns (PostgreSQL 17+)

In PostgreSQL 16, pg_stat_io was added as a separate view providing granular I/O statistics broken down by backend type, object type (relation, WAL, temp file), and I/O context (normal, bulkread, vacuum). Use it alongside pg_stat_statements for a complete I/O picture.

The Essential Queries

Find queries consuming the most total time (usually the highest priority to optimize):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    substring(query, 1, 80) AS query,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 3) AS mean_ms,
    round(stddev_exec_time::numeric, 3) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Find queries with the highest mean execution time (point to specific slow queries worth examining with EXPLAIN):

1
2
3
4
5
6
7
8
9

SELECT
    substring(query, 1, 80) AS query,
    calls,
    round(mean_exec_time::numeric, 3) AS mean_ms,
    round(max_exec_time::numeric, 3) AS max_ms
FROM pg_stat_statements
WHERE calls > 100  -- filter noise
ORDER BY mean_exec_time DESC
LIMIT 20;

Find queries causing the most I/O (missing indexes, poor plans, or legitimately large scans):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    substring(query, 1, 80) AS query,
    calls,
    shared_blks_read,
    shared_blks_hit,
    round(100.0 * shared_blks_hit /
        nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
    temp_blks_written  -- non-zero = spilling to disk due to work_mem
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

Find queries spilling to disk (increase work_mem for these queries or sessions):

1
2
3
4
5
6
7
8
9

SELECT
    substring(query, 1, 100) AS query,
    calls,
    temp_blks_written,
    round(mean_exec_time::numeric, 1) AS mean_ms
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

Reset statistics to establish a clean baseline before a deployment or after making changes:

1

SELECT pg_stat_statements_reset();

pg_stat_activity and Lock Analysis

For in-flight sessions, pg_stat_activity shows what every backend is currently doing:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS query_age,
    left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_age DESC NULLS LAST;

wait_event_type and wait_event are crucial. A backend waiting on Lock / relation or Lock / tuple is blocked by another transaction. The classic lock-finder query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocked.query_start AS blocked_for
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Long-running transactions are particularly dangerous in a heavily-updated MVCC system because they prevent VACUUM from reclaiming dead tuples — any dead tuple whose xmax is newer than the oldest open transaction’s snapshot cannot be removed. Alert on transactions older than 1 hour:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    pid,
    usename,
    now() - xact_start AS age,
    state,
    left(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '1 hour'
ORDER BY age DESC;

postgresql.conf Tuning

Configuration tuning is where theory meets reality. The parameters below have the largest impact for most workloads. All values assume a dedicated PostgreSQL server — not shared with application servers or other databases.

Memory Parameters

shared_buffers: PostgreSQL’s buffer pool — the primary in-memory cache for heap and index pages.

Start at 25% of RAM. PostgreSQL also benefits from the OS page cache, so the effective cache is larger than shared_buffers alone. Diminishing returns set in above 40% because the OS page cache becomes less effective when it has less memory available. On a 32GB server, 8GB is a reasonable starting point. Going to 12GB sometimes helps on read-heavy workloads; going to 20GB rarely helps further and can hurt by starving the OS page cache.

Changing shared_buffers requires a restart.

effective_cache_size: A hint to the query planner about total cache available — shared_buffers plus estimated OS page cache. This parameter does not allocate anything; it only informs the planner’s cost model, specifically making index scans cheaper relative to sequential scans as effective_cache_size increases.

Set it to approximately 75% of total RAM. On a 32GB server, 24GB.

work_mem: Memory allocated per sort or hash operation within a single query. The dangerous multiplier: a complex query can use multiple sort and hash operations simultaneously, and each active connection can have its own. The worst case is approximately max_connections * operations_per_query * work_mem.

Do not set work_mem high globally. Start at 4-16MB. Raise it for specific sessions running heavy analytics:

1
2

SET work_mem = '256MB';
SELECT ... (complex GROUP BY or window function query)

A query with temp_blks_written > 0 in pg_stat_statements is spilling to disk and would benefit from more work_mem. Increase per-session before increasing globally.

maintenance_work_mem: Used by VACUUM, CREATE INDEX, REINDEX, and CLUSTER. These operations are infrequent and typically run serially, so it is safe to set this much higher than work_mem. On a 32GB server, 1GB to 2GB is reasonable. Faster VACUUMs and index builds are the result.

WAL and Checkpoint Parameters

wal_buffers: WAL buffer pool, default -1 (auto, 1/32 of shared_buffers). This almost never needs manual tuning. If you have a very write-heavy workload and see WAL buffer contention in pg_stat_bgwriter, try 64MB.

max_wal_size: Maximum WAL accumulation before forcing a checkpoint. Default 1GB. On write-heavy workloads, increase this to 4GB or more to reduce checkpoint frequency. More frequent checkpoints mean more random I/O; less frequent checkpoints mean longer recovery time after a crash. On fast SSDs with high IOPS, the recovery time tradeoff is minor.

checkpoint_completion_target: Set to 0.9 (spread checkpoint writes over 90% of the interval). The default is 0.9 since PostgreSQL 14; older installations may have 0.5. This single parameter dramatically reduces I/O spikes from checkpoints.

wal_compression: Compresses WAL records. Reduces WAL volume and I/O at the cost of CPU. Useful on I/O-constrained systems. Set to on or lz4 (PG 15+).

Planner Cost Parameters

random_page_cost: The planner’s assumed cost of fetching a random page from disk, relative to sequential page cost (always 1.0). Default 4.0, designed for spinning disks. On SSDs, random I/O is roughly equivalent to sequential I/O. Set to 1.1 for NVMe SSDs or 1.5 for SATA SSDs. This is one of the highest-impact single parameters for systems on SSDs — the planner becomes much more willing to use indexes.

effective_io_concurrency: Hint for how many concurrent disk I/O operations are possible during a Bitmap Heap Scan’s prefetching. Set to 200 for SSDs, 1-2 for HDDs.

default_statistics_target: The number of histogram buckets maintained per column for planner statistics. Default 100. For columns with skewed distributions (status fields, category columns, date ranges), increase to 500 or more:

1
2

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Connection Management

max_connections: PostgreSQL forks a new OS process per connection. Each process uses at minimum 5-10MB of RAM for the stack and per-process state, plus whatever it allocates for its query. At 200 connections, you are spending 1-2GB just on process overhead before any query runs.

Do not raise max_connections above 300-500 without careful consideration. Instead, use PgBouncer in transaction pooling mode to multiplex thousands of application connections onto a small pool of real PostgreSQL connections. This is one of the highest-leverage operational decisions you can make for a PostgreSQL deployment receiving high connection rates.

Reference Configuration: 32GB RAM Server with SSDs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

# Memory
shared_buffers = 8GB                 # 25% of RAM
effective_cache_size = 24GB          # 75% of RAM (hint only, no allocation)
work_mem = 16MB                      # per-operation; raise per-session for analytics
maintenance_work_mem = 1GB           # for VACUUM, CREATE INDEX, etc.

# Connections
max_connections = 150                # use PgBouncer for application pooling

# WAL and Checkpoints
wal_level = replica                  # required for streaming replication
synchronous_commit = on              # change to 'off' for async where safe
wal_buffers = 64MB                   # default -1 (auto) is usually fine
max_wal_size = 4GB                   # reduce checkpoint frequency
checkpoint_completion_target = 0.9  # spread checkpoint I/O
checkpoint_timeout = 10min          # don't checkpoint more often than this
wal_compression = lz4               # requires PG 15+; use 'on' for older

# Planner (SSD)
random_page_cost = 1.1              # SSD: near-sequential random access
effective_io_concurrency = 200      # SSD parallelism hint

# Statistics
default_statistics_target = 200     # better estimates, esp. for skewed columns

# Logging
log_min_duration_statement = 1000   # log queries slower than 1 second
log_checkpoints = on                # monitor checkpoint frequency
log_autovacuum_min_duration = 500   # log autovacuums taking > 500ms
log_lock_waits = on                 # log lock wait events
log_temp_files = 0                  # log any temp file creation (work_mem spills)

# Extensions
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Monitoring and Alerting

Tuning is not a one-time event. PostgreSQL needs continuous monitoring. These are the views and queries that matter.

Cache Hit Ratio

1
2
3
4
5
6
7

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

A cache hit ratio below 99% for a read-heavy OLTP workload means either shared_buffers is too small or the working set simply exceeds available memory. For OLAP queries that scan large ranges, lower ratios are expected and acceptable.

In PostgreSQL 16+, pg_stat_io gives you a more granular breakdown — cache hits and reads per backend type (client backend, autovacuum worker, WAL sender), per I/O object (relation, temp relation, WAL), and per context. This allows separating VACUUM I/O from query I/O, which was previously impossible:

1
2
3
4

SELECT backend_type, object, context, hits, reads, evictions, reuses
FROM pg_stat_io
WHERE backend_type = 'autovacuum worker'
ORDER BY reads DESC;

Checkpoint Pressure

1
2
3
4
5
6
7
8
9

SELECT
    checkpoints_timed,
    checkpoints_req,             -- triggered by max_wal_size, not timeout
    buffers_checkpoint,
    buffers_clean,               -- written by bgwriter between checkpoints
    maxwritten_clean,            -- bgwriter hit its round limit (bad)
    buffers_backend,             -- written by backends directly (very bad)
    buffers_alloc
FROM pg_stat_bgwriter;

checkpoints_req much larger than checkpoints_timed means max_wal_size is too small — WAL is filling up and forcing checkpoints. buffers_backend > 0 means backends are writing dirty pages themselves because bgwriter is not keeping up — a serious I/O bottleneck.

Replication Lag

On the primary:

1
2
3
4
5
6
7
8
9

SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sent_lsn - replay_lsn AS lag_bytes
FROM pg_stat_replication;

On the standby:

1
2
3
4
5
6

SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_lag,
    pg_is_in_recovery(),
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn()
;

Connection Saturation

1
2
3
4
5
6
7

SELECT
    count(*) AS active,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max,
    round(100.0 * count(*) /
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 1) AS pct
FROM pg_stat_activity
WHERE state != 'idle';

Alert when this exceeds 80%. Hitting max_connections causes new connections to fail with “FATAL: sorry, too many clients already”.

Key Prometheus Metrics (via postgres_exporter)

For teams running Prometheus-based monitoring with postgres_exporter:

Putting It Together

The mental model to carry forward: PostgreSQL is a system that trades storage space for concurrency. MVCC’s dead tuples are not a bug — they are the price of non-blocking reads, and VACUUM is the mechanism that settles the debt. WAL is a sequential buffer against the randomness of heap writes, and checkpoints are where that randomness eventually lands. Indexes are a bet that the I/O cost of maintaining a secondary data structure is smaller than the I/O cost saved at query time — a bet that needs to be evaluated with pg_stat_user_indexes over time.

The operators who treat PostgreSQL as a black box eventually hit a wall: a table that grows without bound because autovacuum cannot keep up, a planner that chooses a sequential scan because statistics are stale, a checkpoint storm that pegs I/O utilization every five minutes. The operators who understand the internals see these problems coming from the monitoring queries above and address them before they become incidents.

PostgreSQL 17 and 18 represent the system at its most mature: the new memory-efficient vacuum in PG 17, the asynchronous I/O subsystem in PG 18 that can deliver up to 3x sequential scan throughput, incremental backups with pg_basebackup. The fundamentals described in this post — MVCC, WAL, VACUUM, the cost model — remain stable. The implementation gets faster and more capable with each release. Understanding the fundamentals means you benefit from every improvement, because you understand what problem each improvement is solving.

Run EXPLAIN (ANALYZE, BUFFERS) on your slowest queries today. Query pg_stat_user_tables for dead tuple ratios. Check pg_stat_statements for your top-10 total-time consumers. Look at your pg_stat_bgwriter for checkpoint pressure. The data is there; the question is whether you are reading it.