Forem: Philip McClarence

PostgreSQL Join Optimization: Nested Loop, Hash, and Merge

Philip McClarence — Tue, 28 Apr 2026 14:00:09 +0000

PostgreSQL has three join algorithms. The planner picks between them for every join in every query, driven by several things at once: the estimated sizes of the two inputs, whether they arrive already sorted on the join key, the type of join (inner vs left/semi/anti), which operators are mergejoinable or hashjoinable, whether a hash table will fit in work_mem, and the cost parameters that weigh I/O against CPU. Get the decision right and a three-way join across millions of rows runs in tens of milliseconds. Get it wrong — usually by encouraging a Nested Loop on two large unsorted inputs — and the same query takes minutes.

This article is the third in the Complete Guide to PostgreSQL SQL Query Analysis & Optimization series. We assume the reader can read EXPLAIN output and is familiar with the indexing vocabulary. The running dataset is the same Neon Postgres 17.8 database used throughout the series: 500,000-row sim_bp_orders, 1,000,000-row sim_bp_order_items, 200,000-row sim_bp_users.

We'll cover how each of the three join strategies works, when the planner picks each, what indexes each one wants, and how to read multi-way joins.

Nested Loop — small outer, indexed inner

Nested Loop is the simplest strategy: for each row on the outer side, scan the inner side for matches. Without any index on the inner side, this is a full scan per outer row — O(outer × inner) — and catastrophic for two large tables. With an index on the inner side's join key, each "scan" of the inner is a handful of page reads (a btree descent plus a heap fetch for any columns not in the index), so the total cost is outer-rows × random-I/O-per-probe rather than a polynomial blowup. When the outer side is small and the inner has an index, Nested Loop is nearly unbeatable.

Here's a three-way join that the planner executes as a tower of Nested Loops. The query is "twenty recent pending orders with the user's email and the items in each order":

SELECT u.email, o.order_id, oi.quantity, oi.unit_price_cents
FROM sim_bp_users u
JOIN sim_bp_orders o ON o.user_id = u.user_id
JOIN sim_bp_order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'pending' AND u.status = 'active'
ORDER BY o.created_at DESC
LIMIT 20;

Limit  (cost=1.28..18.06 rows=20 width=41) (actual time=5.098..43.038 rows=20 loops=1)
  Buffers: shared hit=96 read=45
  ->  Nested Loop  (cost=1.28..159741.54 rows=190376 width=41)
        (actual time=5.097..43.027 rows=20 loops=1)
        ->  Nested Loop  (cost=0.85..78058.04 rows=95188 width=33)
              (actual time=2.949..10.878 rows=9 loops=1)
              Inner Unique: true
              ->  Index Scan Backward using idx_sim_bp_orders_created_at on sim_bp_orders o
                    (cost=0.42..30949.29 rows=100300 width=16)
                    (actual time=1.566..2.610 rows=9 loops=1)
                    Filter: ((o.status)::text = 'pending'::text)
                    Rows Removed by Filter: 44
              ->  Memoize  (cost=0.43..0.55 rows=1 width=25)
                    (actual time=0.916..0.916 rows=1 loops=9)
                    Cache Key: o.user_id
                    Cache Mode: logical
                    Hits: 0  Misses: 9  Evictions: 0  Overflows: 0  Memory Usage: 2kB
                    ->  Index Scan using sim_bp_users_pkey on sim_bp_users u
                          (cost=0.42..0.54 rows=1 width=25)
                          (actual time=0.846..0.846 rows=1 loops=9)
                          Index Cond: (u.user_id = o.user_id)
                          Filter: ((u.status)::text = 'active'::text)
        ->  Index Scan using idx_sim_bp_order_items_order_id on sim_bp_order_items oi
              (cost=0.42..0.83 rows=3 width=12)
              (actual time=2.418..3.567 rows=2 loops=9)
              Index Cond: (oi.order_id = o.order_id)
 Execution Time: 43.129 ms

43 ms for a three-way join across 200k × 500k × 1M rows is good. The plan is a tower of two Nested Loops — the inner one joins orders and users, the outer one joins that intermediate result with order items. Read it top-down:

The Index Scan Backward on sim_bp_orders.created_at walks the index in reverse — newest first — looking for pending orders. rows=9 loops=1 means the outer driver produced nine orders before the whole pipeline had enough downstream rows to satisfy LIMIT 20. Forty-four rows were read and filtered as non-pending along the way.
For each of those nine orders, a Memoize → Index Scan on sim_bp_users_pkey looks up the user. Memoize is a PostgreSQL 14+ cache that short-circuits the inner scan when the same key appears repeatedly; here the nine orders happen to be from nine different users, so it's effectively nine primary-key lookups with no cache hits.
For each matching (order, user) pair, the outer Index Scan using idx_sim_bp_order_items_order_id returns an average of two to three line items per order (rows=2 loops=9). The LIMIT 20 applies to the final joined row count, so the executor stops as soon as 20 (order, user, item) tuples have been produced — which is roughly the point where 9 orders × ~2 items each = 20 rows.

This is the Nested Loop success case: the outer driver returns a tiny number of rows thanks to the LIMIT + ordered index, and every inner lookup is an indexed point query. Without the LIMIT, the planner would likely pick a very different strategy — possibly a Hash Join cascade — because it would have to produce tens of thousands of rows instead of twenty.

The Nested Loop failure mode

The same strategy is a disaster when the outer side is large. Consider "count the items across all pending orders," which must process 100,000 pending orders:

SELECT count(*)
FROM sim_bp_orders o
JOIN sim_bp_order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'pending';

If we force the planner to use a Nested Loop (by disabling hash and merge joins), the result is telling:

Aggregate (actual time=1621.494..1621.495 rows=1 loops=1)
  Buffers: shared hit=398994 read=2894
  ->  Nested Loop  (actual time=6.422..1606.338 rows=200535 loops=1)
        ->  Index Only Scan on sim_bp_orders o
              (actual time=4.859..123.354 rows=100252 loops=1)
        ->  Index Only Scan on sim_bp_order_items oi
              (actual time=0.013..0.014 rows=2 loops=100252)
              Index Cond: (oi.order_id = o.order_id)
 Execution Time: 1621.525 ms

1.6 seconds for the same result the planner produces in 1.2 seconds via a Parallel Hash Join (next section). More interestingly, the Buffers line shows 398,994 pages hit — that's from 100,252 inner-index probes, each one re-traversing the btree descent of idx_sim_bp_order_items_order_id. Many of those probes hit the same upper index pages over and over (that's why it's mostly hit, not read), but it's still enormous repeated page traffic that dominates CPU even when the data is fully cached. Under concurrency, other queries would find their own working set evicted from shared_buffers to make room.

The MyDBA analyzer rule nested_loop_large is specifically for this failure mode: it fires when a Nested Loop has Plan Rows > 1000 on the outer side and Plan Rows > 100 on the inner side. At those sizes the Nested Loop is almost always the wrong strategy.

Hash Join — larger sides, unsorted input

Hash Join works in two phases:

Build phase. Read the smaller side in full, building an in-memory hash table keyed by the join column(s). This happens inside the Hash node you see in the plan.
Probe phase. Stream the larger side through the hash table, emitting matched rows as they come.

Hash Join doesn't care whether the inputs are sorted, which makes it the fallback when Merge Join isn't available. It wants the build side to fit in work_mem; if it doesn't, the join spills: PostgreSQL partitions both sides by the join key and processes one pair of partitions at a time. Spilling is visible in the plan as Batches > 1 on the Hash or Hash Join node, and the MyDBA analyzer rule hash_batches_spill fires on it.

Here's the same count query the planner actually chose — a Parallel Hash Join:

Finalize Aggregate  (actual time=1196.234..1199.894 rows=1 loops=1)
  Buffers: shared hit=3827 read=6356
  ->  Gather (Workers Planned: 2, Workers Launched: 2)
        ->  Partial Aggregate  (actual time=1179.014..1179.016 rows=1 loops=3)
              ->  Parallel Hash Join
                    (actual time=170.554..1143.676 rows=333333 loops=3)
                    Hash Cond: (oi.order_id = o.order_id)
                    ->  Parallel Seq Scan on sim_bp_order_items oi
                          (actual time=1.589..703.241 rows=333333 loops=3)
                    ->  Parallel Hash
                          Buckets: 524288  Batches: 1  Memory Usage: 23712kB
                          ->  Parallel Seq Scan on sim_bp_orders o
                                (actual time=0.009..38.403 rows=166667 loops=3)
                                Filter: ((o.status)::text = 'pending'::text)
 Execution Time: 1199.945 ms

1.2 seconds, 10,183 buffer pages touched — about 40× fewer than the forced Nested Loop. The planner built the hash table from sim_bp_orders (the smaller filtered side, 100k pending rows) and probed it with sim_bp_order_items. Batches: 1 means the hash table fit in work_mem entirely, so there was no spill.

Note the Parallel Seq Scan on both sides. That is not a planner mistake — when you're going to read every pending row anyway, a sequential scan is cheaper than an indexed scan because it avoids random I/O and plays nicely with read-ahead. Hash Join is perfectly happy to consume an unsorted stream.

The Parallel Hash Join is a newer variant (PostgreSQL 11+) where workers collaborate to build one shared hash table and then probe it in parallel. Under the hood, Parallel Hash coordinates the build; each worker contributes to it and then proceeds to scan its share of the probe side. This is why you see Workers Planned: 2, Workers Launched: 2 at the top and three loops in each node (one leader + two workers).

When Hash Join is suboptimal

Three cases:

Build side too large. If the smaller table is still multiple-of-work_mem, hash-join spilling degrades performance sharply. The fix is either to raise work_mem (per-session, not cluster-wide), or to force a different strategy via index creation. hash_batches_spill flags this in the analyzer output.

Probe side is tiny. If one input is five rows and the other is fifty million, Nested Loop into an indexed inner is cheaper than building any hash table. PostgreSQL's cost model handles this case correctly most of the time.

Both inputs already sorted. If both sides come out of index scans that produce rows in join-key order, Merge Join is strictly cheaper because it skips the hash build. The planner usually figures this out on its own when it sees the access paths.

Merge Join — both sides sorted

Merge Join walks two pre-sorted inputs in parallel, pairing rows with matching keys in a single pass. It's optimal when both inputs are already sorted on the join key — typically because both are served from index scans on the join column, or because the query itself requires an ORDER BY that aligns with the join key.

The planner picks Merge Join less often than you might expect, because:

If one side has a smaller size and the other has an index, Nested Loop is usually cheaper per row.
If neither side is sorted and both are large, Hash Join wins — sorting both sides just to merge them is rarely cost-effective.
Merge Join's sweet spot is two large pre-sorted streams, which is often a signal that a materialised view or a pre-joined table would be cheaper still.

A canonical Merge Join shape:

SELECT o.order_id, oi.quantity
FROM sim_bp_orders o
JOIN sim_bp_order_items oi ON oi.order_id = o.order_id
ORDER BY o.order_id;

If both tables have indexes on order_id (they do — the primary key on orders and idx_sim_bp_order_items_order_id) and the ORDER BY forces ordered output, the planner may produce something like:

Merge Join
  Merge Cond: (o.order_id = oi.order_id)
  ->  Index Scan using sim_bp_orders_pkey on sim_bp_orders o
  ->  Index Scan using idx_sim_bp_order_items_order_id on sim_bp_order_items oi

Single pass through both indexes, no hash build, no random access. When the prerequisites are met — both sides produced in join-key order — Merge Join is the cheapest option by a wide margin.

In practice you'll see Merge Join most often on joins with explicit ordering, or in the middle of larger plans where the planner noticed that an upstream node was already producing sorted output.

How the planner chooses

PostgreSQL's planner is cost-based. For each join, it enumerates the plausible strategies (Nested Loop, Hash Join, Merge Join, and each direction for each — which side is inner, which is outer) and picks the lowest-cost option. The cost model incorporates:

Estimated row counts from both sides (crucially — if these are wrong, everything downstream is wrong).
Whether each side has a useful index on the join column.
Current work_mem — the planner knows whether a hash table will fit or whether it'll have to plan a spill.
Whether inputs are already sorted (from index scans or prior sort nodes).
The cost parameters: random_page_cost, seq_page_cost, cpu_tuple_cost, etc.

The single biggest cause of wrong-strategy joins is bad row estimates. If the planner thinks a side will produce 15 rows and it actually produces 150,000, it might pick a Nested Loop (optimal for 15) when a Hash Join (optimal for 150,000) would be 100× faster. The MyDBA analyzer rule row_estimate_inaccurate fires when the actual-to-estimated ratio exceeds 10× in either direction, and the fix is almost always ANALYZE on the affected table, or extended statistics if the bad estimate comes from a correlation the planner doesn't know about.

The second biggest cause is stale column statistics on correlated predicates. The planner assumes predicates are independent — if WHERE tenant_id = 7 AND region = 'eu' implies a much narrower row set than P(tenant_id=7) × P(region='eu'), the planner will underestimate and pick the wrong join strategy. Extended statistics (CREATE STATISTICS ... ON tenant_id, region FROM ...) are the specific fix.

Join order: how PostgreSQL decides what to join first

In a three-way join A ⨝ B ⨝ C, there are several possible orders: (A ⨝ B) ⨝ C, A ⨝ (B ⨝ C), and if the join conditions allow it, (A ⨝ C) ⨝ B. For a fourth table you get a lot more permutations. PostgreSQL's planner searches through them.

The heuristic is: do the most selective joins first, so the intermediate result is as small as possible. A join that filters rows_A × rows_B down to 100 rows should happen before a join that would blow the intermediate to millions.

For queries with fewer than 12 tables, PostgreSQL uses dynamic programming to enumerate orders exhaustively. For 12+ tables, the planner switches to the Genetic Query Optimizer (GEQO) which uses heuristic search — sometimes producing non-optimal plans on complex joins. If you have a very wide query (12+ tables, complex conditions), tune geqo_threshold and from_collapse_limit or consider rewriting with explicit CTEs to split the problem.

A few practical levers when the planner picks a wrong join order:

Add or fix indexes. A missing index on a join column often drives the planner to avoid that join until later, resulting in large intermediates. Indexing fixes it.
ANALYZE recently. Stale row counts → bad estimates → bad orders. Autovacuum handles this for active tables; it's often out of date after a bulk load.
Extended statistics. For correlated join keys, CREATE STATISTICS on the correlation.
Rewriting to constrain the planner. STRAIGHT_JOIN doesn't exist in PostgreSQL, but you can force the order by using explicit JOIN syntax and setting join_collapse_limit = 1. Use sparingly — the cost model is usually right.

When join strategy doesn't matter — and what does

Sometimes the join strategy is correct and the query is still slow. The real costs are upstream:

A slow sub-query or CTE feeding the join. The join isn't the problem; its input is. Diagnose by looking at the actual timing of each side.
An expensive filter that prevents index use. If one side of the join is doing a sequential scan because of a non-sargable WHERE clause, the join strategy can't save you. See WHERE Clause Optimisation.
Over-selective projections. SELECT * on a 400-column table passed through a join is expensive in row width; projecting only the columns you need tightens the whole pipeline.

When reading a multi-way join plan, resist the urge to focus on the outermost join. Instead, scan the leaves of the plan tree for the biggest actual rows × loops node — that's where the time is actually going.

Quick reference

Outer size	Inner size	Inner indexed?	Inputs sorted?	Strategy
Small (≤1K)	Any	Yes	—	Nested Loop
Medium	Large	Yes	—	Nested Loop or Hash
Large	Large	—	Both	Merge Join
Large	Large	—	No	Hash Join (may spill)
Large	Large	Build side > work_mem	No	Hash Join with spill — raise work_mem or add an index

A plan shape that should always prompt investigation:

Nested Loop with outer rows > 1,000 and no Memoize cache → fires nested_loop_large.
Hash or Hash Join with Batches > 1 → fires hash_batches_spill; either raise work_mem or index to eliminate the join.
Any join where row_estimate_inaccurate fires on either side — fix statistics first, then re-examine the join.

Next steps

Joins are the category most affected by the quality of your WHERE clauses. The next article in the series covers WHERE Clause Optimisation — sargability, composite-index column ordering, and the operators that silently disable indexes. If your joins look right but the inputs to them are slow, that's almost always where the fix lives.

For the subquery/CTE patterns that sometimes appear in place of explicit joins (EXISTS, correlated subqueries, LATERAL), see Subquery & CTE Optimisation.

postgres #performance #database #sql

Originally published at mydba.dev/blog/postgres-join-optimization.

PostgreSQL Index Usage and Optimization

Philip McClarence — Mon, 27 Apr 2026 14:00:03 +0000

PostgreSQL Index Usage and Optimization

Indexing is the single biggest lever in SQL performance, and it is also the category where most of the bad advice lives. "Add an index" solves a narrow class of problems. "Add the right index, in the right shape, for the right query, and drop the ones you don't need" is the actual job — and it's more design work than most teams expect.

This is article 2 in a series on PostgreSQL query analysis. The pillar is The Complete Guide to PostgreSQL SQL Query Analysis & Optimization; article 1 covers reading EXPLAIN output. The running dataset is 500k-row sim_bp_orders / 200k-row sim_bp_users / 50k-row sim_bp_products on Neon Postgres 17.8; every EXPLAIN block is from a real run.

We'll cover: when the planner actually uses an index, the four design choices that matter most (column selection, partial, covering, expression), the less-common index types and when they beat btrees, how to find unused indexes, and four cases where not adding an index is the correct call.

When the planner picks an index

An index is a data structure; "using an index" is a planner decision. PostgreSQL estimates the cost of each candidate plan — sequential scan, index scan, index-only scan, bitmap scan — and picks the cheapest. Three things drive that choice:

Selectivity. The estimated fraction of rows the query will return. If the filter returns 0.1% of rows, an index scan is almost always cheaper. If the filter returns 30%, it depends on the rest of the query shape. If the filter returns 70%, the planner will almost always choose a sequential scan because visiting most of the heap sequentially costs less than reading index pages plus random heap I/O.

Correlation. If the rows matching the filter are physically clustered on disk, the planner's random-access penalty shrinks and an index scan becomes more attractive. If they're scattered, random I/O dominates and seq scan wins. The pg_stats.correlation column (range -1 to 1) tells you how clustered each column's values are. Time-series tables (created_at) often have near-1 correlation because they're append-mostly; status columns usually hover near 0.

Cost parameters. random_page_cost (default 4.0) vs seq_page_cost (default 1.0). On SSD-backed storage those defaults are too conservative; lowering random_page_cost to 1.5 or 2.0 makes the planner reach for indexes more readily. Setting it below seq_page_cost is almost always wrong — it implies random I/O is faster than sequential, which isn't true on any real storage. If you're tempted to go there, you probably want to raise effective_cache_size instead.

If a plan has a Seq Scan, no index-type nodes, and more than two nodes total, you probably have a missing or ignored index. It's a signal, not a verdict — some queries genuinely don't want an index — but it's worth checking.

The boring case — primary key lookup

The cheapest index in any database is the primary-key btree:

SELECT * FROM sim_bp_users WHERE user_id = 12345;

Index Scan using sim_bp_users_pkey on sim_bp_users
  (cost=0.42..8.44 rows=1 width=51) (actual time=8.683..8.686 rows=1 loops=1)
  Index Cond: (sim_bp_users.user_id = 12345)
  Buffers: shared read=4
 Execution Time: 9.700 ms

Four shared-buffer reads for a 200,000-row table. The 9.7 ms execution time is dominated by cold-cache reads against Neon's networked storage; on a warm-cache benchmark this drops to sub-millisecond. This is the shape every OLTP single-row lookup should have.

The four design choices that matter

1. Column selection — matching the query shape

A composite index on (user_id, created_at) helps:

WHERE user_id = ? (uses the leading column alone).
WHERE user_id = ? AND created_at > ? (uses both).
WHERE user_id = ? ORDER BY created_at DESC LIMIT n (uses leading equality + sorted trailing column).

It does not help WHERE created_at > ? in isolation. This is the leftmost-prefix rule: a btree composite index can answer queries that use a contiguous prefix of its columns, starting with the leading one. Skip-scan isn't efficient on PostgreSQL btrees for reasonable-cardinality leading columns.

Rule of thumb: leading columns should be equality predicates, trailing columns range predicates or sort keys. (tenant_id, created_at), not (created_at, tenant_id).

2. Partial indexes — when 80% of the table is irrelevant

CREATE INDEX idx_bp_orders_pending_recent
    ON sim_bp_orders (created_at)
    WHERE status = 'pending';

The index only contains rows where status = 'pending', so it's roughly one-fifth the size of a full index on created_at. The planner will use it for any query whose WHERE clause implies status = 'pending' — it proves this by theorem-proving over the predicates. So WHERE status = 'pending' AND created_at > now() - interval '1 day' works, but WHERE status IN ('pending', 'shipped') AND ... doesn't (the IN predicate doesn't imply the partial predicate).

Two gotchas: they're fragile to query rewording (a function, a cast, a reworded predicate can break the implication proof), and they pay write cost whenever a row moves into or out of the partial predicate.

3. Covering indexes — eliminating heap fetches

INCLUDE tucks non-key columns into the leaf pages:

CREATE INDEX idx_bp_orders_pending_by_amount
    ON sim_bp_orders (total_amount_cents DESC)
    INCLUDE (order_id, user_id, created_at)
    WHERE status = 'pending';

A query that SELECTs any combination of order_id, user_id, total_amount_cents, created_at from this index can be served entirely from index pages — provided the visibility map marks the relevant heap pages as all-visible. On a write-heavy table where autovacuum can't keep up, you'll see non-zero Heap Fetches: in EXPLAIN, which defeats most of the benefit.

INCLUDE columns cannot be used for index conditions. Rule: put columns used for filtering/joining/ordering in the key; put columns you're only retrieving in INCLUDE.

4. Expression indexes — indexing computed values

This is where most "why isn't my index being used?" problems live. A btree on email can't serve WHERE lower(email) = ? or WHERE lower(email) LIKE 'prefix%'. Case-insensitive prefix search on a 200k-row table without an expression index:

Gather  (cost=1000.00..5841.09 rows=1000 width=25) (actual time=0.553..122.758 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on sim_bp_users
        Filter: (lower((email)::text) ~~ 'user12%'::text)
        Rows Removed by Filter: 94444
 Execution Time: 122.833 ms

Parallel seq scan, 94k rows filtered per worker, 122 ms. The fix:

CREATE INDEX idx_bp_users_email_lower
    ON sim_bp_users (lower(email) text_pattern_ops);

For equality on lowercased email, a plain CREATE INDEX ... (lower(email)) is enough. For prefix LIKE, text_pattern_ops is needed because PostgreSQL can only rewrite LIKE 'prefix%' into an index range scan when the index orders text by byte value rather than by locale collation.

With the existing idx_sim_bp_users_email_pattern index on email text_pattern_ops:

Index Only Scan using idx_sim_bp_users_email_pattern on sim_bp_users
  (cost=0.42..29.87 rows=20 width=8) (actual time=0.057..24.729 rows=20 loops=1)
  Index Cond: ((email ~>=~ 'user12'::text) AND (email ~<~ 'user13'::text))
  Filter: ((email)::text ~~ 'user12%'::text)
  Heap Fetches: 0
 Execution Time: 24.757 ms

The Index Cond uses ~>=~ and ~<~ — real PostgreSQL operators from text_pattern_ops that do byte-order comparisons. 24.7 ms vs 122.8 ms — five times faster, and the gap widens on larger tables.

Index types beyond btree

GIN — when equality becomes containment

For values with internal structure (arrays, JSONB, full-text search vectors, trigrams):

CREATE INDEX idx_events_data_gin
    ON events USING gin (event_data jsonb_path_ops);

-- Now this is sargable:
SELECT * FROM events WHERE event_data @> '{"type": "purchase"}';

jsonb_path_ops indexes only the @> operator but produces a significantly smaller and faster index than the default jsonb_ops. Use it unless you need the other JSONB operators.

GIN with pg_trgm turns substring LIKE queries (LIKE '%needle%') into index-backed scans.

BRIN — when the data is physically ordered

CREATE INDEX idx_bp_orders_created_at_brin
    ON sim_bp_orders USING brin (created_at);

For our 500,000-row orders table, a BRIN index is ~24 kB; a btree on the same column is ~5 MB. BRIN loses effectiveness immediately if the data isn't correlated — on a shuffled table, the min/max of every page range overlaps the whole value domain and the planner can't skip anything. BRIN is effectively useless on uncorrelated columns and brilliant on time-series data.

GiST / SP-GiST / hash

Geometric types, ranges, and fuzzy matching use GiST or SP-GiST. Hash indexes only support equality and are usually beaten by btrees even for point lookups — use them only when you've measured a specific case where they win.

When NOT to add an index

Write-heavy, read-light tables. Every index is write cost.
Low selectivity. A btree on a boolean is_active where 90% of rows are active will never be used. A partial index is better.
Queries that need most of the table. Reports over large windows are best served by parallel seq scan.
Redundant indexes. (a, b, c) subsumes (a, b) and (a). Drop the prefixes, keep the longest.

Finding unused indexes

SELECT
    s.indexrelname AS index_name,
    s.relname AS table_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
    s.idx_scan
FROM pg_stat_user_indexes s
WHERE s.schemaname = 'public'
  AND s.idx_scan = 0
  AND NOT EXISTS (
      SELECT 1 FROM pg_constraint c
      WHERE c.conindid = s.indexrelid AND c.contype IN ('p', 'u', 'x')
  )
ORDER BY pg_relation_size(s.indexrelid) DESC;

Real result from the running database:

index_name	size	idx_scan
idx_sim_bp_users_username_pattern	6184 kB	0
idx_sim_bp_users_email_pattern	7960 kB	1

One 6 MB index with zero scans is a straightforward drop. The NOT EXISTS clause skips PK/unique/exclusion constraint indexes — those enforce integrity and are used internally even if no user query hits them.

Two caveats: pg_stat_reset() zeros the counter (check the stats timestamp before acting), and a replica's stats only count scans on that replica (don't drop an index from the primary based on replica stats alone).

Adding the right index — a complete example

SELECT order_id, user_id, total_amount_cents, created_at
FROM sim_bp_orders
WHERE status = 'pending'
ORDER BY total_amount_cents DESC
LIMIT 50;

51 ms sequential scan over 500k rows with a top-n heapsort. Three plausible candidates:

(status) — cheapest, most general, but the planner still needs a sort step.
(status, total_amount_cents DESC) — solves filter and sort. The sort is free because the index is already ordered on the trailing column within each status group.
(total_amount_cents DESC) WHERE status = 'pending' — only pending rows indexed. Smaller, faster to maintain, but only helps pending queries.

Option 3 plus INCLUDE (order_id, user_id, created_at) gives Index Only Scan and is the right call for this specific query. If the dashboard later adds status IN ('pending', 'processing'), you'd want option 2 instead. Design indexes for the query you have, and re-read the plans every six months.

postgres #performance #database #sql

Originally published at mydba.dev/blog/postgres-index-usage-optimization.

Reading PostgreSQL EXPLAIN and EXPLAIN ANALYZE Output

Philip McClarence — Fri, 24 Apr 2026 14:00:07 +0000

Every PostgreSQL performance conversation eventually lands on a question that sounds trivial: what does this EXPLAIN mean? The output is almost readable. There are node names in English, numbers that look familiar, and enough structure that you can guess at the intent. But if you're guessing, you're going to miss the signal that actually matters — and the difference between a plan that returns in 0.3 ms and one that returns in 400 ms is often one line of EXPLAIN output that looks like boilerplate.

This article is a systematic walk through how to read an EXPLAIN plan on PostgreSQL 17, using real output captured from a live database. By the end you should be able to look at a plan, identify what each node is doing and why, spot the three places where things usually go wrong, and articulate in one sentence why the query is slow — or whether it's actually fine and something else is wrong.

This is part of the Complete Guide to PostgreSQL SQL Query Analysis & Optimization series.

EXPLAIN vs EXPLAIN ANALYZE vs EXPLAIN (ANALYZE, BUFFERS)

The three variants you'll use in practice:

EXPLAIN — asks the planner what it would do, without running the query. Fast (milliseconds), safe for expensive queries, but every number is an estimate. Useful for "how expensive does the planner think this is?" and "did my new index change the plan shape?"

EXPLAIN ANALYZE — actually runs the query and reports what happened. You get both the planner's estimates and the real measured results, side by side. Use this in development and staging; use it on production only after thinking about the cost. Three warnings: (1) EXPLAIN ANALYZE on an INSERT/UPDATE/DELETE will execute the DML — wrap in a BEGIN; ... ROLLBACK; if you don't want the side effects. (2) The query runs end-to-end, so a slow query is slow again, and any locks it takes are held for real. (3) ANALYZE pulls rows into the buffer cache and may evict other working-set pages; running it on a busy production system can perturb the performance of the exact thing you're measuring. On hot-path queries, prefer capturing a representative plan via auto_explain or an EXPLAIN visualiser in a monitoring tool rather than running EXPLAIN ANALYZE ad-hoc under load.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) — the version you should default to. BUFFERS adds per-node cache-hit/read/dirtied counts and must still be specified explicitly; EXPLAIN ANALYZE on its own does not include buffer statistics. VERBOSE adds the output column list at each node (useful for spotting why indexes aren't being chosen). SETTINGS reports any non-default planner knobs that might be influencing the plan.

You can also ask for structured output with FORMAT JSON, FORMAT YAML, or FORMAT XML. JSON preserves every field and is what you want for programmatic analysis; the text format is easier to read inline.

The plan tree

Every EXPLAIN output is a tree. The root is the outermost node, which is whatever produces the query's final rows; children feed their output up to their parent. PostgreSQL indents children under their parent with arrows:

Parent Node
  ->  Child A
  ->  Child B
        ->  Grandchild

The top-down narrative is: "to produce Parent Node's output, PostgreSQL runs Child A and Child B, feeding both into the parent. Child B itself is produced by running Grandchild." Execution order is bottom-up (leaves run first), but the way to read the plan is top-down — start with "what is this query ultimately asking for?" and then follow the tree down to understand how PostgreSQL intends to answer.

Here's a real example — "show twenty recent pending orders with the user's email." The plan is against a 500,000-row sim_bp_orders table and 200,000-row sim_bp_users table on PostgreSQL 17.8:

Limit  (cost=0.85..16.38 rows=20 width=37) (actual time=0.075..0.277 rows=20 loops=1)
  Buffers: shared hit=211
  ->  Nested Loop  (cost=0.85..77853.84 rows=100300 width=37) (actual time=0.074..0.275 rows=20 loops=1)
        Inner Unique: true
        Buffers: shared hit=211
        ->  Index Scan Backward using idx_sim_bp_orders_created_at on sim_bp_orders o
              (cost=0.42..30949.29 rows=100300 width=20)
              (actual time=0.012..0.151 rows=20 loops=1)
              Filter: ((status)::text = 'pending'::text)
              Rows Removed by Filter: 106
              Buffers: shared hit=131
        ->  Memoize  (cost=0.43..0.55 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=20)
              Cache Key: o.user_id
              Cache Mode: logical
              Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 3kB
              Buffers: shared hit=80
              ->  Index Scan using sim_bp_users_pkey on sim_bp_users u
                    (cost=0.42..0.54 rows=1 width=25)
                    (actual time=0.003..0.003 rows=1 loops=20)
                    Index Cond: (u.user_id = o.user_id)
                    Buffers: shared hit=80
 Planning Time: 1.183 ms
 Execution Time: 0.309 ms

Read top-down. The root is Limit, which caps the result at twenty rows. Below it is a Nested Loop that joins two sources: an Index Scan Backward over sim_bp_orders and a Memoize wrapping an Index Scan on sim_bp_users. The outer loop walks the orders index backwards (newest first) filtering for status = 'pending', and for each matching order, looks up the user via the primary-key index — but the Memoize caches results by user_id in case the same user appears multiple times (they don't in this particular run, so all 20 are cache misses).

This is a very good plan. 0.309 ms, 211 shared-buffer hits, no reads from disk. The LIMIT 20 short-circuits the nested loop early — only 106 rows are read and filtered before twenty matches are found. The same query with a much larger LIMIT would have very different numbers.

Now let's break down what each number means.

Per-node fields: cost, rows, width, time, loops

On every node, PostgreSQL prints something like:

Node Name  (cost=S..T rows=R width=W) (actual time=s..t rows=r loops=l)

The first parenthesis (cost=... rows=... width=...) is the planner's estimate. The second (actual time=... rows=... loops=...) is what actually happened when the query ran. EXPLAIN without ANALYZE only prints the first.

cost=startup..total. Dimensionless units, scaled relative to seq_page_cost (1.0 by default). The other cost GUCs — random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost — are all expressed in the same arbitrary unit, which lets the planner compare heterogeneous operations against each other. startup is the estimated cost to produce the first row from this node; total is the estimated cost to produce all rows. The difference matters: a Sort node has a high startup cost (it has to consume all input before it can produce the first row) but a low marginal cost per row after that. An Index Scan has a very low startup cost. When you see a node above a LIMIT, what matters is the startup cost of the child, because the limit stops asking for rows as soon as it has enough.

rows=N. The planner's estimate of how many rows this node will emit. Per loop — see below.

width=W. Estimated average row width in bytes. Mostly informational; you use it to sanity-check whether a Sort or Hash might spill to disk (row width × estimated rows ≈ memory requirement).

actual time=startup..total. Wall-clock milliseconds, measured per loop. startup is the time to produce the first row from this node; total is the time to produce the last row.

actual rows=r loops=l. rows is the number of rows produced per loop, averaged over all l loops. To get the total rows this node emitted, multiply: rows × loops.

Loops matter. In the nested loop example above, the Memoize node reports rows=1 loops=20 — meaning the node was executed 20 times (once per outer row), and each execution produced 1 row. Total output: 20 rows. But the actual time=0.006..0.006 is per loop, so the total time spent in Memoize was about 0.006 ms × 20 = 0.12 ms. Forgetting to multiply by loops is the single most common mistake in reading EXPLAIN output — a node that looks fast per loop can still dominate the query time if it runs 50,000 times.

The relationship between rows estimate and actual rows is arguably the most important signal in a plan. If the planner estimated 15 and the actual was 8,000, the plan was built on bad assumptions: every decision it made downstream (join strategy, memory allocation, whether to parallelise) was wrong. A ratio past 10× in either direction is worth treating as a warning; past 100× it's usually critical. The fix is almost always ANALYZE on the affected table, or extended statistics if the bad estimate comes from correlated columns that the planner assumes are independent.

Node types you'll see most often

Scan nodes — where rows enter the plan.

Seq Scan — read every row of a table. Reports Filter: when there's a WHERE clause applied, and Rows Removed by Filter: telling you how many rows were read and discarded. Cheap on small tables, catastrophic on large ones with selective filters.
Index Scan — use an index to find rows, then fetch each matching row from the heap for any columns the index doesn't contain. Reports Index Cond: for conditions satisfied by the index, and optionally Filter: for conditions that have to be rechecked after the heap fetch.
Index Only Scan — use an index to find rows and return all requested columns directly from the index, skipping the heap entirely. Requires either that the index includes every referenced column (see INCLUDE) or that all columns are part of the index keys. Reports Heap Fetches: — this number should be close to zero; a non-zero count means the visibility map didn't cover some pages and PostgreSQL had to check the heap anyway, defeating the point.
Bitmap Index Scan + Bitmap Heap Scan — two-step pattern for combining multiple index conditions or for queries that match many rows. First, the index scan builds a bitmap of heap pages that might have matches. Then the heap scan visits those pages once each, avoiding re-reading pages that contain multiple matches. Reports Exact Heap Blocks and Lossy Heap Blocks — a high lossy-block count means work_mem was too small to track individual tuples, so PostgreSQL fell back to page-level tracking and has to re-filter the matches.

Join nodes — combining two inputs.

Nested Loop — for each row on the outer side, scan the inner side. Optimal when the outer side is small and the inner side has an index on the join column. Pathological when both sides are large.
Hash Join — build a hash table from the smaller side (the Hash child), then probe it with each row from the other side. Optimal for equi-joins on unordered data when the smaller side fits in work_mem. Reports Hash Batches: — if this is greater than 1, the hash table didn't fit in memory and had to spill.
Merge Join — two pre-sorted inputs, walked in parallel. Optimal when both sides are already sorted (or can be sorted cheaply via an index). Reports Merge Cond:.

Sort and aggregation nodes.

Sort — ordering rows. Reports Sort Key: (the columns being sorted), Sort Method: (algorithm), Sort Space Type: (Memory or Disk), and Sort Space Used: (in KB).
- top-N heapsort — used under a LIMIT N. Keeps only N rows in a heap regardless of input size. Efficient in memory and time.
- quicksort — everything fits in work_mem.
- external merge — didn't fit; spilled to disk.
Aggregate / HashAggregate / GroupAggregate — SUM/AVG/COUNT/GROUP BY. HashAggregate builds a hash table keyed by the group-by columns; GroupAggregate requires presorted input. HashAggregate can spill to disk with Planned Partitions: N Batches: M.
Limit — cap the number of rows. Often the shortcut that makes a plan fast.
WindowAgg — window functions like ROW_NUMBER() and SUM() OVER.

Parallelism.

Gather / Gather Merge — the leader process collecting results from parallel workers. Workers Planned: and Workers Launched: tell you how many workers the planner asked for vs actually got. When Launched < Planned, the system is short on parallel worker slots.
Parallel Seq Scan / Parallel Index Scan / Parallel Hash Join — parallel-aware variants of the base node types.

Utility nodes.

Materialize — cache an intermediate result so the parent can rescan it without redoing the work. Common above the inner side of a Nested Loop.
Memoize (new in PostgreSQL 14) — LRU cache above an inner loop. Reports Cache Key:, Hits:, Misses:, Evictions:, and Memory Usage:. A high hit ratio is good; a high miss ratio just means the cache didn't help this particular query but didn't hurt either.
CTE Scan — reading from a materialised CTE. In PostgreSQL 12+ most CTEs are inlined and this node disappears; you see it when a CTE is referenced multiple times or marked MATERIALIZED.
SubPlan — a correlated subquery, executed once per outer row. Almost always worth rewriting as a JOIN.

The Buffers line

With BUFFERS enabled, every node reports how many 8 KB pages it touched:

Buffers: shared hit=3689

The four counters to know:

shared hit=N — pages found in shared_buffers (PostgreSQL's cache). No I/O system calls.
shared read=N — pages the backend had to read into shared_buffers via a read() system call. Whether the OS page cache satisfied the read without touching disk is invisible to EXPLAIN — these show up as reads regardless.
shared dirtied=N — pages the query modified in cache. Common with DML; in a read-only SELECT, usually comes from hint-bit updates or cleanup.
shared written=N — pages written back out during this node's execution. Usually this is the backend itself being forced to evict dirty pages to make room for new ones, not the background writer — so a high written count means your query is doing someone else's work because the dirty-page pool was already full.

There's also local hit/read/dirtied/written for per-session temporary tables, and temp read=N written=N for work files produced by sorts and hash joins that spilled.

A query doing shared read=2016, temp written=2051 in a single node is telling you two things: the table isn't fitting in cache, and the query itself is generating its own on-disk temp files because some operation (hash, sort, bitmap) exceeded work_mem. Both are fixable; both hurt.

A harder plan: the HashAggregate spill

Here's a plan with more going on — "the twenty users with the most pending-or-shipped orders." Against the same 500,000-row orders table and 200,000-row users table:

Limit  (cost=42281.85..42282.10 rows=20 width=29) (actual time=408.141..408.145 rows=20 loops=1)
  Buffers: shared hit=3737 read=2016, temp read=1320 written=2051
  ->  Sort  (cost=42281.85..42722.81 rows=176383 width=29) (actual time=406.664..406.667 rows=20 loops=1)
        Sort Key: (count(*)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  HashAggregate  (cost=33757.54..37588.36 rows=176383 width=29)
              (actual time=347.354..392.138 rows=117060 loops=1)
              Group Key: u.email
              Planned Partitions: 4  Batches: 5  Memory Usage: 8241kB  Disk Usage: 6920kB
              ->  Hash Join  (cost=7932.00..21080.02 rows=176383 width=21)
                    (actual time=140.974..285.275 rows=175263 loops=1)
                    Hash Cond: (o.user_id = u.user_id)
                    ->  Seq Scan on sim_bp_orders o  (cost=0.00..9939.00 rows=176383 width=4)
                          (actual time=0.018..51.809 rows=175263 loops=1)
                          Filter: ((status)::text = ANY ('{pending,shipped}'::text[]))
                          Rows Removed by Filter: 324737
                    ->  Hash  (cost=4064.00..4064.00 rows=200000 width=25)
                          (actual time=140.864..140.865 rows=200000 loops=1)
                          Buckets: 131072  Batches: 2  Memory Usage: 6822kB
                          ->  Seq Scan on sim_bp_users u  (cost=0.00..4064.00 rows=200000 width=25)
                                (actual time=1.735..87.218 rows=200000 loops=1)
 Planning Time: 1.107 ms
 Execution Time: 408.215 ms

408 ms. Let's read it top-down and find where the time actually goes.

Root: Limit + Sort. The Sort is top-N heapsort, Memory: 26 kB — fine. Under the LIMIT 20, a top-N sort is almost free regardless of input size.

HashAggregate — the first red flag. The Group Key is u.email; the aggregate is a count(*) across the 175k joined rows. Two numbers jump out: Planned Partitions: 4 Batches: 5 and Memory Usage: 8241 kB Disk Usage: 6920 kB. PostgreSQL 13+ can spill a HashAggregate to disk when the hash table exceeds work_mem: the executor detects that not all groups will fit in memory, writes unfinished groups out to per-partition spill files, and processes them in a second pass. The exact number of spill-and-resume cycles isn't something you should read literally from the Batches count, but the presence of Disk Usage at all is the signal — this query is paying for temp file I/O on every run. The temp written=2051 buffer count at the top is driven by exactly this, and this is the dominant cost of the query.

Hash Join + Hash child — the second red flag. Buckets: 131072 Batches: 2 Memory Usage: 6822 kB. The hash table built from sim_bp_users needed about 13 MB (the build side is 200k rows at ~64 bytes each) and didn't fit in 4 MB of work_mem. When a hash join spills, PostgreSQL partitions both sides by the join key and processes one matched pair of partitions at a time — each probe row is tested only against its matching partition, not against every batch. The cost is the extra I/O of writing the build and probe sides to per-partition temp files and reading them back.

Seq Scan on sim_bp_orders. 175k rows returned, 324k removed by filter (total = 500k, the whole table). The filter is status IN ('pending', 'shipped'). No index on status, so the whole table is scanned.

Seq Scan on sim_bp_users. 200k rows returned, no filter — we need all users. Reads 2016 pages from disk (shared read=2016); the users table is mostly cold in cache.

The bottleneck order, from biggest to smallest: HashAggregate spill, Hash Join build-side batches, Seq Scans. Three different fixes are plausible, and which one is appropriate depends on how often this query runs, how much work_mem the rest of the workload can tolerate, and whether the data is append-mostly:

Raise work_mem per-session to ~20 MB so both the HashAggregate and the Hash Join stay in memory. Caveat: work_mem is allocated per sort/hash node per connection, so raising it globally multiplies by the number of concurrent queries doing sorts. Set it per-role (ALTER ROLE dashboard SET work_mem = '32MB') or per-session in the dashboard's connection pool, not cluster-wide.
Index sim_bp_orders.status so the scan becomes a Bitmap or Index Scan instead of reading all 500k rows. At ~35% selectivity a plain btree might not beat a seq scan by much, but a partial index or a multi-column (status, user_id) would.
Materialise the aggregate into a small summary table refreshed on a schedule or via triggers, if the query is a dashboard that runs every 10 seconds and the underlying data is append-mostly.

A fair DBA answer is "measure each fix in isolation and pick based on the workload" — not any specific prescribed order. If the query runs once a day in a reporting job, the work_mem bump is cheapest; if it runs constantly and powers a UI, the materialised result wins.

The five most common mistakes in reading plans

Comparing rows without multiplying by loops. A node reporting rows=1 loops=50000 produced 50,000 rows. A node reporting rows=50000 loops=1 produced the same 50,000 rows in a very different shape. Always look at loops.
Looking at top-line cost/time and calling it a day. The top-line number tells you the query is slow; it doesn't tell you which node is slow. Scan the tree for the node with the highest actual time × loops — that's where the time is spent, and usually where the fix is.
Trusting the planner's estimates when actual rows disagrees. If rows=15 on the estimate and actual rows=8000, every downstream decision was built on the wrong premise. Don't try to understand why the plan is shaped the way it is until you've fixed the estimate (usually with ANALYZE or extended statistics).
Missing the Rows Removed by Filter line. A Seq Scan returning a reasonable number of rows looks fine — until you notice the filter line says ten million rows were read and discarded to produce those few. The scan was fine; the cost is in the discard.
Ignoring the Buffers line. Two plans can have identical shapes and wildly different performance if one hits cache and the other doesn't. shared hit=5 means "hot"; shared read=50000 means "the storage layer did all the work, and next time it might be even worse." The Buffers line is the only way to see this without looking at the timing.

Next steps

If the first plan in this article (the nested loop) looked straightforward and the second (the HashAggregate spill) made sense, you've mostly got it. The rest of the series digs into specific bottleneck categories — missing indexes, join-strategy mistakes, aggregate spills, non-sargable WHERE clauses — and what to do about each. The next piece is PostgreSQL Index Usage and Optimization.

postgres #performance #database #sql

Originally published at https://mydba.dev/blog/postgres-explain-analyze-reading

The Complete Guide to PostgreSQL SQL Query Analysis & Optimization

Philip McClarence — Thu, 23 Apr 2026 14:00:06 +0000

Most PostgreSQL performance work is wasted because it starts from the wrong end. Someone notices a slow query, skim-reads EXPLAIN, pattern-matches to "missing index," adds one, and moves on. Sometimes that works. Often it doesn't — and when it doesn't, the next attempt is usually an even blunter instrument: "just add more RAM," "just use a read replica," "just cache it."

This guide is a systematic alternative. The argument is that a large fraction of single-query latency problems in OLTP workloads fall into one of a small number of bottleneck categories, each with a characteristic EXPLAIN signature and a well-understood fix. (Lock contention, vacuum bloat, replication lag, and the generic-plan vs custom-plan behaviour of prepared statements are real and common, but they are cluster-level or protocol-level problems rather than single-plan problems; this guide is strictly about the latter.) If you can name the category in sixty seconds of reading the plan, the fix usually follows in minutes.

We'll work through the full workflow end-to-end on a real query against a real PostgreSQL 17 database, then map the eight bottleneck categories to the eight deep-dive articles that make up this series. Every EXPLAIN snippet below is captured from an actual run against a 500,000-row sim_bp_orders table on a Neon Postgres 17.8 database — not a synthetic example.

The workflow

Read the EXPLAIN plan — specifically the three signals that matter most: estimated-vs-actual row counts, access path at each scan node, and where time is actually spent.
Categorise the bottleneck — translate the plan signals into one of eight categories.
Apply the matching fix — index, rewrite, tune memory, or restructure the query.
Verify with a second EXPLAIN — before/after is how you know you actually fixed something.

That's it. The rest of this article walks through each step on a concrete example.

A typical slow query

Our running example is a dashboard query: "show me the fifty highest-value pending orders."

SELECT order_id,
       user_id,
       total_amount_cents,
       created_at
FROM sim_bp_orders
WHERE status = 'pending'
ORDER BY total_amount_cents DESC
LIMIT 50;

The table is 500,000 rows, with roughly 20% in status = 'pending'. There's a primary key on order_id, indexes on user_id and created_at, but no index on status or total_amount_cents. We've disabled parallel execution (SET max_parallel_workers_per_gather = 0) for this example so the plan reads cleanly. Here's the plan:

Limit  (cost=13270.89..13271.02 rows=50 width=20) (actual time=50.873..50.883 rows=50 loops=1)
  Buffers: shared hit=3689
  ->  Sort  (cost=13270.89..13521.64 rows=100300 width=20) (actual time=50.871..50.877 rows=50 loops=1)
        Sort Key: sim_bp_orders.total_amount_cents DESC
        Sort Method: top-N heapsort  Memory: 30kB
        Buffers: shared hit=3689
        ->  Seq Scan on sim_bp_orders  (cost=0.00..9939.00 rows=100300 width=20) (actual time=0.011..37.781 rows=100252 loops=1)
              Filter: ((status)::text = 'pending'::text)
              Rows Removed by Filter: 399748
              Buffers: shared hit=3689
 Planning Time: 0.073 ms
 Execution Time: 50.908 ms

Fifty-one milliseconds is not a disaster on its own. It's the kind of number that gets shrugged at until a hundred of these queries run concurrently on a busy application server, at which point CPU saturates and every request starts stacking.

Step 1 — Read the plan

Three signals tell you nearly everything about a plan node.

Signal 1 — how the table is accessed. At the leaf of this plan is Seq Scan on sim_bp_orders. A sequential scan means the planner's cost model decided reading every row was cheaper than any available index — sometimes because no useful index exists, sometimes because existing indexes don't match the query shape, occasionally because statistics are misleading the cost estimate. On small tables, or when the query needs a large fraction of the table anyway, a seq scan is often genuinely the cheapest plan. But on a 500k-row table with a selective filter and an ORDER BY ... LIMIT 50, it's the wrong shape.

Signal 2 — rows removed by filter. Rows Removed by Filter: 399,748, with Actual Rows: 100,252 matching. The scan touched every row in the table. The filter selectivity is ~20% — not pathological by itself — but 400,000 rows of pure waste every time the dashboard refreshes. An index on the filter column would let PostgreSQL skip them entirely.

Signal 3 — planner estimate vs reality. rows=100,300 estimated vs rows=100,252 actual. Essentially perfect. If the ratio had been ten-to-one or worse in either direction, the plan would be built on bad assumptions and ANALYZE would be the first move. Here, statistics are healthy.

There's a fourth node worth naming: the Sort above the scan is a top-N heapsort. Unlike a full sort, a top-N heapsort streams all input rows through a heap of size N (50 here) — it reads all 100,252 pending rows but only ever holds 50 in memory. That's why the Memory: 30kB is so small. Even so, it's 100,252 rows of unnecessary work: an index on (total_amount_cents DESC) WHERE status = 'pending' would let the planner walk the index from the largest value downward and stop after fifty entries.

Step 2 — Categorise the bottleneck

Once you've read the plan, map what you see to one of eight categories. Each category has a characteristic signature; each maps to a deep-dive article in this series.

Plan signal	Bottleneck category	Fix article
You can't even read the plan confidently	Plan literacy	Reading EXPLAIN / EXPLAIN ANALYZE Output
`Seq Scan` with large row counts, many `Rows Removed by Filter`	Missing or wrong index	Index Usage & Optimisation
Nested Loop joining large tables; Hash Join spilling to disk	Join strategy	Join Optimisation
`CTE Scan` feeding a filter; `SubPlan` running per outer row	Subquery / CTE structure	Subquery & CTE Optimisation
`HashAggregate` or `Sort` spilling, expensive window functions	Aggregate or window tuning	Aggregate & Window Function Tuning
Index exists but isn't being used; function on indexed column	WHERE clause shape	WHERE Clause Optimisation
Plan is "fine" but the query itself is the problem	Query rewriting	Query Rewriting Techniques
`SELECT *`, implicit casts, deep pagination, N+1 from ORM	Anti-pattern	Anti-Patterns & Common Mistakes

Our example plan maps cleanly. A Seq Scan with 400,000 rows removed by filter, sitting under an ORDER BY ... LIMIT that can't exploit any existing index, is the textbook signature for the Missing or wrong index category. The Sort above it is solvable in the same stroke — a single partial index can eliminate both the scan and the sort.

Step 3 — Apply the fix

The fix is a partial index, with the non-filter columns tucked into INCLUDE so the planner can serve the query from the index alone without touching the heap:

CREATE INDEX CONCURRENTLY idx_sim_bp_orders_pending_by_amount
    ON sim_bp_orders (total_amount_cents DESC)
    INCLUDE (order_id, user_id, created_at)
    WHERE status = 'pending';

Four non-obvious choices:

Partial index. Only pending orders are indexed, because that's the only status the query cares about. A full index on (status, total_amount_cents) would work too; it would contain roughly 5× more entries. Partial indexes only help queries whose WHERE clause implies the index's predicate — so if this dashboard later adds WHERE status IN ('pending', 'processing'), the planner will skip this index.
Sort direction in the index. Specifying total_amount_cents DESC means the planner can scan the btree in the direction that produces rows in the needed order without an explicit Sort node.
Tiebreaker. In a real dashboard you'd almost always want a tiebreaker column — ORDER BY total_amount_cents DESC isn't deterministic for ties, and two rows with equal totals would shuffle between pages; adding , order_id DESC to both the index and the query fixes that.
Covering (INCLUDE). The SELECT list is satisfied entirely from index tuples, which lets the planner serve the query as an Index Only Scan without heap fetches. Index Only Scan also requires the visibility map to mark the relevant heap pages all-visible, so on a write-heavy table where autovacuum can't keep up, you may still see heap fetches even with a covering index.

CREATE INDEX CONCURRENTLY avoids taking an ACCESS EXCLUSIVE lock on the table, so normal reads and writes continue while the index builds. It still takes weaker locks (SHARE UPDATE EXCLUSIVE) twice, waits for transactions that hold old snapshots on the target table to finish before advancing between phases, and runs two passes over the table — so it's slower than CREATE INDEX in wall-clock time, and a single long-running transaction that has touched this table can stall the build indefinitely. On a 500k-row table the build takes seconds; on a 500M-row table it can take hours. The application stays up the whole time. A partial index on status = 'pending' still pays write cost when rows are inserted into or updated out of that state — so if pending is a high-churn status, weigh the read win against the write overhead.

Step 4 — Verify

Same query, same data, index in place:

Limit  (cost=0.42..2.18 rows=50 width=20) (actual time=0.021..0.031 rows=50 loops=1)
  Buffers: shared hit=5
  ->  Index Only Scan using idx_sim_bp_orders_pending_by_amount on sim_bp_orders
        (cost=0.42..3544.68 rows=100300 width=20)
        (actual time=0.021..0.026 rows=50 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=5
 Planning Time: 0.186 ms
 Execution Time: 0.045 ms

0.045 ms, down from 50.9 ms — roughly 1100× faster. Buffers dropped from 3,689 hit to 5 hit. The Sort node is gone entirely: the index is already sorted in the right order. The Filter line is gone: the partial index guarantees every row it contains already satisfies status = 'pending'. Heap Fetches: 0 means the visibility map covered every leaf page we touched, so PostgreSQL served all 50 tuples from the index without reading a single heap page.

Two caveats on the headline number. First, EXPLAIN ANALYZE's Execution Time measures server-side SQL execution only — it excludes network round-trip, client-side tuple deserialisation, and connection pool overhead. Real application latency for this query is probably closer to 2–10 ms depending on your region. Second, the measurement is on a hot cache with an immediately-post-vacuum visibility map; a colder cache would show Buffers: shared read=N instead of all hit. The meaningful improvement is the ~700× drop in buffer reads — that's what translates into lower CPU under concurrency.

The eight categories

The workflow above treats "spot the category" as a two-sentence step. In practice, each category has its own rules, exceptions, and non-obvious variants. The rest of this series is eight standalone articles, each diving into one category.

1. Reading EXPLAIN / EXPLAIN ANALYZE output

Before you can optimise a plan, you have to be able to read one. EXPLAIN reports the planner's estimated plan; EXPLAIN ANALYZE executes the query and reports what actually happened. The deep dive covers every common node type, the meaning of loops, Buffers, Memory, Workers Planned vs Launched, and the five most common ways to misread a plan. → Reading EXPLAIN / EXPLAIN ANALYZE Output.

2. Index usage and optimisation

A large share of single-query OLTP latency problems come down to indexing — either missing, or present but not matching the query shape. But "add an index" understates what's actually required: choosing columns in the right order, deciding between full and partial indexes, using INCLUDE for covering indexes, expression indexes for computed predicates, GIN/GiST/BRIN for the data types where btrees are wrong, and knowing when not to add one. → Index Usage & Optimisation.

3. Join optimisation

The planner picks between Nested Loop, Hash Join, and Merge Join based on cost estimates. Each has a regime where it's best, and the worst joins are the ones using the wrong strategy — usually a Nested Loop on two large tables. → Join Optimisation.

4. Subquery and CTE optimisation

PostgreSQL 12 changed CTE semantics — what used to always be materialised is now inlined by default, except when you ask for materialisation explicitly. That change made many old "CTE as optimisation fence" tricks silently stop working. → Subquery & CTE Optimisation.

5. Aggregate and window function tuning

GROUP BY and window functions look declarative, but the planner has strong opinions about how to execute them: HashAggregate versus GroupAggregate, partial and parallel aggregation, window frame optimisation. Sorts and hashes that spill to disk are almost always the visible symptom, and work_mem is almost always the knob. → Aggregate & Window Function Tuning.

6. WHERE clause optimisation

An index is only useful if the WHERE clause is sargable. Wrapping an indexed column in a function (lower(email) = '...'), doing implicit casts (varchar_column = 123), or comparing on the wrong side of an operator all silently disable indexes that look like they should apply. → WHERE Clause Optimisation.

7. Query rewriting techniques

Sometimes the plan is "fine" but the query itself is asking the wrong question. Correlated subqueries can usually become lateral joins; NOT IN with NULLs should be NOT EXISTS; offset pagination past a few hundred pages should be keyset pagination; DISTINCT over a large set is often GROUP BY in disguise. → Query Rewriting Techniques.

8. Anti-patterns and common mistakes

The final category is queries that are wrong by construction: SELECT * in hot paths, implicit type casts that silently disable indexes, missing LIMIT on exploratory joins, N+1 patterns coming out of ORMs, inserting one row at a time instead of batching. → Anti-Patterns & Common Mistakes.

Where to start

If you can already read EXPLAIN confidently, the highest-value articles are probably Index Usage and Query Rewriting, because those are where the largest wins hide. If reading the plans in this article felt like work, start with Reading EXPLAIN and come back here.

Slow queries are not mysterious. They fall into a small number of categories, each with a characteristic plan signature and a well-understood fix. Learn to recognise the signatures and most of the rest follows.

postgres #performance #database #sql

Canonical version with the full series linked: https://mydba.dev/blog/postgres-query-analysis-complete-guide

PostgreSQL Parallel Query: Configuration & Performance Tuning

Philip McClarence — Wed, 22 Apr 2026 10:00:02 +0000

PostgreSQL Parallel Query: Configuration & Performance Tuning

Your analytical query scans a 50 GB table, aggregates 200 million rows, and takes 25 seconds. Your server has 16 CPU cores. PostgreSQL uses... 2 of them. The other 14 sit idle. The max_parallel_workers_per_gather default of 2 is leaving 7x potential speedup on the table. Let's fix that -- and understand when you should not.

How Parallel Query Works

PostgreSQL divides large operations across multiple CPU cores. Worker processes each scan a portion of the data, feed results through a Gather node to the leader process, which combines them. Sequential scans, hash joins, aggregates, and B-tree index scans all support parallel execution.

The key defaults:

max_parallel_workers_per_gather = 2 -- max workers per parallel operation
max_parallel_workers = 8 -- total parallel workers across all sessions
max_worker_processes = 8 -- total background workers (shared with other subsystems)
min_parallel_table_scan_size = 8MB -- minimum table size for parallel scan
parallel_setup_cost = 1000 -- planner's estimate for starting a worker
parallel_tuple_cost = 0.1 -- per-tuple transfer cost estimate

These are tuned for general-purpose workloads. For analytical queries on large tables, they're far too conservative.

Detecting the Problem

Check your current settings:

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE '%parallel%'
   OR name = 'max_worker_processes'
ORDER BY name;

Check whether queries actually use parallel workers:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT customer_region, count(*), sum(order_total)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_region;

Look for:

Gather or Gather Merge -- parallel execution is happening
Workers Planned: 2 and Workers Launched: 2 -- how many workers
Workers Launched < Workers Planned -- system ran out of workers

Check if workers are being exhausted:

-- Currently active parallel workers
SELECT count(*) AS active_parallel_workers
FROM pg_stat_activity
WHERE backend_type = 'parallel worker';

-- The limit
SELECT setting AS max_parallel_workers
FROM pg_settings
WHERE name = 'max_parallel_workers';

If active workers frequently approach the limit, queries are competing for workers and some run with fewer than planned.

Tuning for Analytical Workloads

If your database runs analytical queries on large tables:

-- More workers per query
SET max_parallel_workers_per_gather = 4;

-- More total workers
ALTER SYSTEM SET max_parallel_workers = 16;

-- Enough background worker slots
ALTER SYSTEM SET max_worker_processes = 20;

-- Lower table size threshold
ALTER SYSTEM SET min_parallel_table_scan_size = '1MB';

-- Apply (max_worker_processes requires restart)
SELECT pg_reload_conf();

Rule of thumb: max_parallel_workers_per_gather = half your CPU cores, max_parallel_workers = total cores. On a 16-core server: 8 and 16 respectively.

Lower Cost Thresholds

If medium-sized tables aren't getting parallelized despite adequate configuration:

ALTER SYSTEM SET parallel_setup_cost = 100;    -- default: 1000
ALTER SYSTEM SET parallel_tuple_cost = 0.01;   -- default: 0.1
SELECT pg_reload_conf();

Lower parallel_setup_cost makes the planner consider parallelism for smaller operations. Lower parallel_tuple_cost makes parallel plans look cheaper.

Per-Session Overrides

For mixed workloads, set parallelism based on the connection type:

-- Reporting query: maximum parallelism
SET LOCAL max_parallel_workers_per_gather = 8;
SET LOCAL parallel_setup_cost = 0;
SET LOCAL parallel_tuple_cost = 0;

SELECT region, count(*), avg(order_total)
FROM orders
GROUP BY region;

-- OLTP session: disable parallelism
SET LOCAL max_parallel_workers_per_gather = 0;

Per-Table Settings

For critical large tables:

-- Guarantee up to 8 workers for scans on this table
ALTER TABLE orders SET (parallel_workers = 8);

This overrides the planner's automatic worker count calculation.

What Parallelizes (and What Doesn't)

Operation	Parallel?
Sequential Scan	Yes
B-tree Index Scan	Yes
Bitmap Heap Scan	Yes
Hash Join	Yes
Merge Join	Yes
Nested Loop	Yes (outer side)
Aggregate (count, sum, avg)	Yes
CREATE INDEX (B-tree)	Yes
Append (UNION ALL, partitions)	Yes
UPDATE, DELETE	No
CTEs (WITH queries)	No
Cursors	No
FOR UPDATE/SHARE	No

The Memory Multiplication Trap

work_mem applies per worker. A query with work_mem = 256MB and 4 parallel workers can consume 1.28 GB for sorting and hashing. Budget accordingly:

max_connections * max_parallel_workers_per_gather * work_mem < available RAM

This catches people who increase parallelism without accounting for memory.

Verify the Impact

Compare sequential vs parallel execution:

-- Disable parallelism
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT customer_region, count(*), sum(order_total)
FROM orders
GROUP BY customer_region;

-- Enable parallelism
SET LOCAL max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT customer_region, count(*), sum(order_total)
FROM orders
GROUP BY customer_region;

The parallel plan should show roughly sequential_time / (1 + num_workers) execution time, with 60-80% of theoretical speedup typical due to Gather overhead.

Prevention Strategy

OLAP databases: aggressive parallelism. max_parallel_workers_per_gather = CPU_cores / 2, max_parallel_workers = CPU_cores, lower cost thresholds.

OLTP databases: keep defaults or disable. Many short concurrent queries don't benefit -- worker overhead exceeds speedup on small queries.

Mixed workloads: per-connection settings. Reporting connections get high parallelism. App connections get zero.

Monitor Workers Launched vs Workers Planned. Consistent shortfall means you need more max_parallel_workers. If CPU hits 100% during parallel queries and other sessions slow down, reduce max_parallel_workers_per_gather.

Originally published at mydba.dev/blog/postgres-parallel-query

PostgreSQL Point-in-Time Recovery with pgBackRest

Philip McClarence — Tue, 21 Apr 2026 10:00:03 +0000

PostgreSQL Point-in-Time Recovery with pgBackRest

pg_dump gives you a snapshot at the moment you ran it. If your last dump was 6 hours ago and someone accidentally deletes a production table, those 6 hours are gone. Even with hourly dumps, you lose everything between the last dump and the incident. For a database processing thousands of transactions per minute, that gap is devastating. Point-in-time recovery (PITR) eliminates that gap -- restoring your database to any specific second by replaying the write-ahead log on top of a base backup.

How PITR Works

Two mechanisms combine:

Base backups -- periodic snapshots of all database files
WAL archiving -- continuous streaming of every WAL segment to a backup repository

The WAL records every change made to the database. By replaying WAL segments from a base backup forward to a target timestamp, you reconstruct the exact state at that moment. If the last archived WAL is 30 seconds old, your maximum data loss is 30 seconds -- not 6 hours.

pgBackRest is the standard tool for this. It handles base backups (full, incremental, differential), WAL archiving, retention, verification, and recovery -- with parallel compression, encryption, and remote repository support.

Detecting Whether You're Protected

Check if WAL archiving is even enabled:

SELECT name, setting
FROM pg_settings
WHERE name IN (
    'archive_mode',
    'archive_command',
    'archive_timeout',
    'wal_level'
);

You need archive_mode = on and wal_level = replica (or logical). If archive_mode is off, PITR is impossible.

Check for archiving failures:

SELECT
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time,
    last_failed_wal,
    last_failed_time,
    now() - last_archived_time AS archive_lag
FROM pg_stat_archiver;

A non-zero failed_count or archive_lag greater than a few minutes means the pipeline is broken. WAL segments are accumulating on the primary and will eventually fill the disk.

Verify backup freshness:

pgbackrest info --stanza=main
pgbackrest verify --stanza=main

Setting Up pgBackRest

Install and Configure

# Debian/Ubuntu
sudo apt-get install pgbackrest

# RHEL/Rocky
sudo dnf install pgbackrest

Create /etc/pgbackrest/pgbackrest.conf:

[main]
pg1-path=/var/lib/postgresql/18/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=your-secure-encryption-passphrase

process-max=4
compress-type=zst
compress-level=6

log-level-console=info
log-level-file=detail

Configure PostgreSQL

Add to postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60

The archive_timeout = 60 forces a WAL switch every 60 seconds even if the segment isn't full. This caps maximum data loss at 60 seconds.

Restart PostgreSQL, then initialize the stanza:

pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main check

Schedule Backups

# Full backup (weekly)
pgbackrest --stanza=main --type=full backup

# Differential (daily -- changes since last full)
pgbackrest --stanza=main --type=diff backup

# Incremental (every 6 hours -- changes since last any backup)
pgbackrest --stanza=main --type=incr backup

Cron schedule:

0 2 * * 0  pgbackrest --stanza=main --type=full backup
0 2 * * 1-6  pgbackrest --stanza=main --type=diff backup
0 */6 * * *  pgbackrest --stanza=main --type=incr backup

Performing Recovery

When disaster strikes, restore to a specific timestamp:

sudo systemctl stop postgresql

pgbackrest --stanza=main --type=time \
    --target="2026-02-28 14:30:00+00" \
    --target-action=promote \
    restore

sudo systemctl start postgresql

Set --target to just before the incident. --target-action=promote opens the database for read-write after recovery.

You can also restore to a named restore point:

-- Create before a risky operation
SELECT pg_create_restore_point('before_schema_migration');

pgbackrest --stanza=main --type=name \
    --target="before_schema_migration" \
    --target-action=promote \
    restore

Test Recovery Regularly

This is the most critical step. Schedule monthly recovery tests to a standby server:

pgbackrest --stanza=main --type=time \
    --target="2026-02-28 12:00:00+00" \
    --target-action=promote \
    --pg1-path=/var/lib/postgresql/18/test_recovery \
    restore

Verify the restored database contains expected data at the target timestamp. If recovery fails, fix the configuration before you need it in an emergency. Record the actual recovery time -- that's your real RTO.

Prevention

Build PITR into infrastructure from day one. Every production PostgreSQL database should have WAL archiving before its first production write.

Monitor three metrics continuously:

Archive lag -- alert if > 5 minutes
Failed archive count -- any non-zero value requires investigation
Backup age -- alert if exceeding your backup interval + buffer

An untested backup is not a backup. Test quarterly at minimum. Document the procedure, the expected recovery time, and who executes it. Run end-to-end: restore, replay WAL, verify data, record duration.

Store backups off-host. A backup on the same disk is destroyed by the same failure. Use S3, Azure Blob, GCS, or a separate server. Enable encryption.

Originally published at mydba.dev/blog/postgres-point-in-time-recovery

PostgreSQL BRIN Indexes: When & How to Use Block Range Indexes

Philip McClarence — Mon, 20 Apr 2026 10:00:03 +0000

PostgreSQL BRIN Indexes: When & How to Use Block Range Indexes

You have a 500-million-row events table. The B-tree index on created_at consumes 12 GB. Every insert must update that 12 GB index. Backups include 12 GB of index data. The buffer cache is full of index pages. And all you ever do is range queries: "give me events from last week." There's a better way. A BRIN index on the same column would be roughly 100 KB -- not 12 GB -- and for your query pattern, it works just as well.

How BRIN Works

Instead of indexing every individual row (like B-tree), BRIN stores the minimum and maximum values for ranges of consecutive physical blocks. The default is 128 pages (~1 MB of table data) per range entry.

To find rows where created_at = '2026-01-15', PostgreSQL reads the BRIN index, identifies which block ranges could contain that date (any range where min <= '2026-01-15' <= max), and scans only those ranges. Block ranges that can't contain the target value are skipped entirely.

The trade-off is precision. B-tree points to exact rows. BRIN points to block ranges that might contain matching rows, then scans those blocks sequentially. This is fine when matching rows are clustered together (time-series data), but terrible when values are scattered randomly across the table.

When BRIN Works (and When It Doesn't)

The key metric is physical correlation -- how closely the column values track with the physical row position on disk.

-- Check correlation for candidate columns
-- Values close to 1.0 or -1.0 = good for BRIN
SELECT
    attname AS column_name,
    correlation,
    n_distinct,
    null_frac
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'events'
  AND attname IN ('created_at', 'event_id', 'user_id')
ORDER BY abs(correlation) DESC;

Above 0.9: ideal for BRIN. Matching rows are tightly clustered in a few block ranges.
0.7 to 0.9: can still benefit, but more false-positive blocks scanned.
Below 0.7: BRIN will scan too many irrelevant blocks. Use B-tree.

The ideal BRIN candidate:

Characteristic	Why It Matters
Append-only or mostly-append	Physical order matches logical order
Time-series or log data	Timestamp correlates with insertion order
Table size > 1 GB	B-tree overhead becomes significant
Range queries are primary access	BRIN excels at range filtering
Low update/delete frequency	Updates break physical correlation

The failure mode: creating a BRIN index on user_id in a table where inserts come from many users in random order. Every block range contains every user_id, and PostgreSQL must scan the entire table anyway.

Finding BRIN Candidates

Identify large tables with oversized B-tree indexes:

SELECT
    t.schemaname,
    t.relname AS table_name,
    pg_size_pretty(pg_relation_size(t.relid)) AS table_size,
    i.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    round(100.0 * pg_relation_size(i.indexrelid) / NULLIF(pg_relation_size(t.relid), 0), 1)
        AS index_to_table_pct
FROM pg_stat_user_tables t
JOIN pg_stat_user_indexes i ON i.relid = t.relid
WHERE pg_relation_size(t.relid) > 1073741824  -- tables > 1 GB
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 20;

Tables over 1 GB with B-tree indexes consuming 10%+ of the table size are prime candidates -- if the indexed columns have high correlation.

Creating and Tuning BRIN Indexes

Basic BRIN index:

CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING brin (created_at);

Tune pages_per_range

The default of 128 pages summarizes ~1 MB of table data per entry. You can tune this:

-- More granular: larger index, fewer false positives
CREATE INDEX CONCURRENTLY idx_events_created_brin_fine
    ON events USING brin (created_at)
    WITH (pages_per_range = 32);

-- Less granular: tiny index, more false positives
CREATE INDEX CONCURRENTLY idx_events_created_brin_coarse
    ON events USING brin (created_at)
    WITH (pages_per_range = 256);

For most time-series tables, the default of 128 works well.

Enable Autosummarize

By default, new blocks are not reflected in the BRIN index until vacuum runs. This means recent data might trigger sequential scans:

CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING brin (created_at)
    WITH (autosummarize = on);

For append-heavy workloads, autosummarize = on is strongly recommended.

Multi-Column BRIN

BRIN indexes support multiple columns when both correlate with physical order:

CREATE INDEX CONCURRENTLY idx_events_multi_brin
    ON events USING brin (created_at, event_id);

Both created_at and an auto-incrementing event_id increase together, so both have high correlation.

Verify the Improvement

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

You should see Bitmap Heap Scan with Bitmap Index Scan on idx_events_created_brin. Buffer count should be much lower than a full sequential scan.

Compare index sizes:

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'events';

The size difference should be dramatic -- often 100-1000x smaller.

Prevention

Always check pg_stats.correlation before creating a BRIN index. A BRIN on a low-correlation column is worse than useless -- it costs maintenance time and fools you into thinking data is indexed.

Monitor BRIN effectiveness after creation. Compare buffer counts in EXPLAIN ANALYZE between BRIN-indexed queries and sequential scans. If BRIN isn't reducing buffer reads by at least 50%, the correlation is too low.

Watch for operations that break physical correlation: UPDATEs that change the indexed column, CLUSTER on a different column, or bulk DELETEs followed by new inserts. If correlation degrades, consider running CLUSTER to restore physical order.

For time-series tables growing by gigabytes per day, switching from B-tree to BRIN can reduce index storage by 99% -- and your insert performance will thank you.

Originally published at mydba.dev/blog/postgres-brin-index

PostgreSQL Covering Indexes: Eliminate Heap Fetches with INCLUDE

Philip McClarence — Sun, 19 Apr 2026 10:00:02 +0000

PostgreSQL Covering Indexes: Eliminate Heap Fetches with INCLUDE

You have an index on customer_id. Your query filters by customer_id and selects customer_name and customer_email. PostgreSQL finds the matching rows in the index (fast), then fetches each row from the heap table to get the name and email (slow). Those heap fetches are random I/O operations scattered across the table. On a 100M-row table returning 1,000 rows, that is 1,000 random reads -- and they dominate the query execution time. A covering index eliminates them entirely.

How Index Lookups Actually Work

Every standard B-tree index lookup is two steps:

Index scan: find matching row pointers (TIDs) in the index -- fast, sequential access
Heap fetch: retrieve actual row data from the heap table -- slow, random I/O

The heap fetch is the bottleneck. For single-row lookups it's barely noticeable. For queries returning hundreds or thousands of rows, it dominates execution time.

An index-only scan skips step 2 entirely. If all columns the query needs exist in the index, PostgreSQL reads everything from the index. No heap access, no random I/O.

The INCLUDE Clause (PostgreSQL 11+)

Before PostgreSQL 11, covering indexes required composite indexes on all columns: CREATE INDEX ON customers (customer_id, customer_name, customer_email). This works but has a cost -- the index maintains sort order on all three columns, wasting CPU on sorts for columns nobody searches or orders by.

INCLUDE adds columns to the index leaf pages without including them in the sort key:

CREATE INDEX CONCURRENTLY idx_customers_covering
    ON customers (customer_id)
    INCLUDE (customer_name, customer_email);

The key column (customer_id) is the search key for WHERE, ORDER BY, and joins. The included columns are stored alongside but not sorted -- they exist solely to enable index-only scans.

Detecting Covering Index Opportunities

Look for index scans with heap fetches in EXPLAIN output:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_name, customer_email
FROM customers
WHERE customer_id BETWEEN 1000 AND 2000;

Watch for:

Index Scan using idx_customers_id -- the heap was visited for each row
Heap Fetches: 1000 -- 1,000 trips to the heap table
High Buffers: shared hit=... from random heap access

The goal: Index Only Scan with Heap Fetches: 0.

Find candidates system-wide:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan > 100
ORDER BY idx_tup_fetch DESC
LIMIT 20;

Tables with high idx_tup_fetch are performing many heap fetches. Cross-reference with your most frequent queries.

Practical Examples

Dashboard Query

A reporting dashboard showing recent orders:

-- The query
SELECT order_id, customer_name, order_total, order_status
FROM orders
WHERE created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- The covering index
CREATE INDEX CONCURRENTLY idx_orders_recent_covering
    ON orders (created_at DESC)
    INCLUDE (order_id, customer_name, order_total, order_status);

This single index handles the WHERE filter, ORDER BY, LIMIT, and returns all selected columns -- entirely from the index. No heap access.

INCLUDE vs Composite

-- INCLUDE: customer_name is retrieved but never searched
CREATE INDEX ON orders (order_date)
    INCLUDE (customer_name, order_total);

-- Composite: both columns are used in WHERE or ORDER BY
CREATE INDEX ON orders (customer_id, order_date);

Use INCLUDE when extra columns are only in the SELECT list. Use composite when columns appear in WHERE or ORDER BY.

The Vacuum Dependency

Index-only scans require pages to be marked "all-visible" in the visibility map. PostgreSQL can skip the heap only for these pages. Vacuum maintains the visibility map.

If vacuum falls behind:

Pages are not marked all-visible
The planner falls back to regular index scans with heap fetches
Your covering index provides zero benefit

-- Check visibility map health
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'customers';

If last_autovacuum is stale and dead tuples are accumulating, tune autovacuum to run more frequently on that table. A covering index without healthy vacuum is a wasted investment.

Prevention

When writing a new query that selects specific columns from an indexed table, ask: "Can I add these columns to the index with INCLUDE to eliminate heap fetches?"

Keep covering indexes focused. Don't add every column -- include only the columns your most frequent queries select. Different queries needing different columns should get targeted covering indexes, not one massive index.

Monitor heap fetch counts over time. A query showing Heap Fetches: 0 today may regress if vacuum falls behind or if a new column is added to the SELECT list. Track idx_tup_fetch on important indexes -- a sudden increase signals regression.

Review covering indexes when query patterns change. Application refactors that add columns to SELECT clauses break index-only scans silently -- the query works, but performance drops.

Originally published at mydba.dev/blog/postgres-covering-index

PostgreSQL JSONB Indexing: GIN, Expression & Partial Index Strategies

Philip McClarence — Sat, 18 Apr 2026 10:00:04 +0000

PostgreSQL JSONB Indexing: GIN, Expression & Partial Index Strategies

JSONB is one of PostgreSQL's killer features. You get schema-less flexibility inside a relational database -- user preferences, API payloads, feature flags, event metadata, all stored without defining every column up front. The problem is that most developers treat JSONB as a black box: throw data in, query it with -> and ->>, maybe slap a GIN index on it, and assume PostgreSQL will figure out how to make it fast. It will not. Let's walk through the three indexing strategies and when to use each.

The Fundamental Confusion

The most common JSONB indexing mistake: creating a GIN index and expecting it to speed up ->> equality queries. It doesn't.

-- You create this index
CREATE INDEX idx_events_metadata_gin ON events USING gin (metadata);

-- And expect this query to use it
SELECT * FROM events WHERE metadata->>'status' = 'active';
-- NOPE. Sequential scan. The GIN index doesn't support ->>

The default GIN index (jsonb_ops) supports @>, ?, ?|, and ?& operators -- not ->>. To accelerate that query, you either need an expression index or must rewrite the query to use @> containment.

Detecting the Problem

Find JSONB columns that are triggering sequential scans:

SELECT
    t.schemaname,
    t.relname AS table_name,
    a.attname AS column_name,
    pg_size_pretty(pg_relation_size(t.relid)) AS table_size,
    t.seq_scan,
    t.seq_tup_read
FROM pg_stat_user_tables t
JOIN pg_attribute a ON a.attrelid = t.relid
JOIN pg_type ty ON ty.oid = a.atttypid
WHERE ty.typname = 'jsonb'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND t.seq_scan > 100
ORDER BY t.seq_tup_read DESC;

Confirm with EXPLAIN:

-- This does NOT use a GIN index
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE metadata->>'status' = 'active';

-- This DOES use a GIN index
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE metadata @> '{"status": "active"}';

If you see Seq Scan on the first query despite having a GIN index, the operator mismatch is your problem.

Strategy 1: GIN Index for Containment (`@>`)

When your queries use containment, GIN is the right choice:

-- Default operator class: supports @>, ?, ?|, ?&
CREATE INDEX CONCURRENTLY idx_events_metadata_gin
    ON events USING gin (metadata);

-- jsonb_path_ops: supports only @>, but 2-3x smaller and faster
CREATE INDEX CONCURRENTLY idx_events_metadata_pathops
    ON events USING gin (metadata jsonb_path_ops);

Use jsonb_path_ops when you only need @> containment. It hashes full paths rather than indexing every key/value, creating a significantly smaller index. On 10M rows with complex documents, the difference can be 3-4x.

Rewrite ->> equality to containment to leverage GIN:

-- Before (no GIN support)
SELECT * FROM events WHERE metadata->>'status' = 'active';

-- After (uses GIN index)
SELECT * FROM events WHERE metadata @> '{"status": "active"}';

Strategy 2: Expression Index for Specific Keys

When you repeatedly query one specific key, an expression index is more efficient:

-- B-tree on a specific extracted key
CREATE INDEX CONCURRENTLY idx_events_status
    ON events ((metadata->>'status'));

-- Now this works
SELECT * FROM events WHERE metadata->>'status' = 'active';

Expression indexes are smaller than GIN (one value per row vs. decomposing the entire document), support range queries (<, >, BETWEEN), and support ORDER BY. They're the right choice when you query known, specific keys.

For JSONB arrays:

CREATE INDEX CONCURRENTLY idx_events_tags_gin
    ON events USING gin ((metadata->'tags'));

-- Query: find events tagged "important"
SELECT * FROM events WHERE metadata->'tags' @> '"important"';

Strategy 3: Partial Index for Selective Conditions

When only a fraction of rows match your query, avoid indexing the entire table:

-- Index only active events (if 90% are archived)
CREATE INDEX CONCURRENTLY idx_events_active_metadata
    ON events USING gin (metadata jsonb_path_ops)
    WHERE metadata->>'status' = 'active';

Dramatically smaller, faster to maintain, and writes to archived rows skip the index entirely. Combine with expression indexes for laser-targeted optimization:

-- Expression index on user_id, only for purchase events
CREATE INDEX CONCURRENTLY idx_events_purchase_user
    ON events ((metadata->>'user_id'))
    WHERE metadata->>'type' = 'purchase';

The Decision Table

Query Pattern	Index Type	Example
`@>` containment	GIN (`jsonb_path_ops`)	`WHERE data @> '{"k": "v"}'`
`->>` equality on known key	Expression (B-tree)	`WHERE data->>'status' = 'x'`
Key existence (`?`)	GIN (default `jsonb_ops`)	`WHERE data ? 'email'`
Range on extracted value	Expression (B-tree)	`WHERE (data->>'score')::int > 90`
Array containment	GIN on sub-path	`WHERE data->'tags' @> '"x"'`

The Write Performance Trade-off

GIN index maintenance is expensive. Every INSERT or UPDATE touching the JSONB column must decompose the entire document to update the index. On write-heavy tables with large documents, this can cut insert throughput by 30-50%.

If you only query 2-3 keys, expression indexes on those keys are dramatically cheaper to maintain. Monitor insert latency after adding GIN indexes -- if INSERTs slow down significantly, switch to targeted expression or partial indexes.

Prevention

Document which JSONB keys will be queried when you add the column. This drives index selection from day one instead of retrofitting after performance degrades.

Monitor GIN index size relative to table size. If the GIN index approaches the table size, you're indexing too much.

Review JSONB query patterns quarterly. Application features evolve, and the keys you query change. Unused JSONB indexes waste space and slow writes for zero benefit.

Originally published at mydba.dev/blog/postgres-jsonb-indexing

PostgreSQL Transaction Isolation Levels Explained

Philip McClarence — Fri, 17 Apr 2026 10:00:04 +0000

PostgreSQL Transaction Isolation Levels Explained

If you've ever had a subtle data inconsistency bug in production and traced it back to "we're in a transaction, so the data should be consistent" -- you've run into a transaction isolation misunderstanding. PostgreSQL's Read Committed default is perfectly correct, but it doesn't do what most developers think it does. And switching to a higher level introduces a completely different class of problems. Let's unpack all three levels and when to actually use each one.

What PostgreSQL Actually Supports

The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. PostgreSQL accepts all four in syntax, but Read Uncommitted behaves identically to Read Committed -- PostgreSQL's MVCC architecture never exposes uncommitted data. So in practice, you have three distinct behaviors.

Read Committed (The Default)

Each statement within a transaction sees a snapshot as of the start of that statement, not the start of the transaction. If another transaction commits between your two SELECTs, the second one sees the committed changes.

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;  -- sees 1000

-- Another transaction commits: UPDATE accounts SET balance = 500 WHERE account_id = 1;

SELECT balance FROM accounts WHERE account_id = 1;  -- sees 500 (non-repeatable read)
COMMIT;

This is the right choice for most workloads. Non-repeatable reads and phantom reads are only a problem if your application logic depends on seeing the same data across multiple queries within a single transaction -- and most transactions execute a single query or a short sequence of independent queries.

Repeatable Read (Snapshot Isolation)

The transaction sees a snapshot as of its first non-transaction-control statement. All queries within the transaction see the same consistent view, regardless of concurrent commits.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 1;  -- sees 1000

-- Another transaction commits: UPDATE accounts SET balance = 500 WHERE account_id = 1;

SELECT balance FROM accounts WHERE account_id = 1;  -- still sees 1000 (snapshot isolation)

-- But trying to update the same row fails:
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;  -- must retry the entire transaction

The catch: if your transaction tries to UPDATE a row that another committed transaction modified after the snapshot was taken, PostgreSQL aborts with a serialization error. Your application must catch this and retry the entire transaction.

Serializable (SSI)

The strongest level. PostgreSQL uses Serializable Snapshot Isolation (SSI) to detect read/write dependency cycles and abort transactions that would produce non-serializable results.

-- Transaction A:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE branch = 'east';
INSERT INTO accounts (branch, balance) VALUES ('west', 100);
COMMIT;

-- Transaction B (concurrent):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE branch = 'west';
INSERT INTO accounts (branch, balance) VALUES ('east', 200);
COMMIT;
-- One of these will fail with a serialization error

This catches more anomalies than Repeatable Read but has a higher abort rate. PostgreSQL tracks predicate locks (SIRead locks) which consume memory and CPU.

Detecting Isolation Problems

Check the server-wide default:

SHOW default_transaction_isolation;

Monitor for sessions holding long-running snapshots (common with Repeatable Read/Serializable):

SELECT
    pid,
    usename,
    datname,
    backend_xid,
    backend_xmin,
    state,
    substring(query, 1, 80) AS query_preview
FROM pg_stat_activity
WHERE backend_type = 'client backend'
    AND backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Sessions with very old backend_xmin values are holding snapshots that prevent vacuum from cleaning dead tuples -- a major bloat risk.

Check for lock contention and deadlocks:

SELECT
    datname AS database_name,
    deadlocks,
    conflicts
FROM pg_stat_database
WHERE datname = current_database();

Implementing Retry Logic

This is non-negotiable for Repeatable Read and Serializable. The retry must re-execute the entire transaction, not just the failed statement:

import psycopg2
import time

def execute_with_retry(connection_pool, transaction_fn, max_retries=3):
    """Execute a transaction function with automatic retry on serialization failure."""
    for attempt in range(max_retries):
        conn = connection_pool.getconn()
        try:
            conn.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
            )
            result = transaction_fn(conn)
            conn.commit()
            return result
        except psycopg2.errors.SerializationFailure:
            conn.rollback()
            if attempt == max_retries - 1:
                raise
            # Exponential backoff with jitter
            time.sleep(0.01 * (2 ** attempt))
        finally:
            connection_pool.putconn(conn)

Which Level When?

Read Committed (default): most web applications, CRUD operations, independent queries within a transaction. The non-repeatable read phenomenon is only a problem if your logic depends on reading the same data twice.

Repeatable Read: read-heavy reporting that needs a consistent point-in-time snapshot. Financial reports, balance calculations, audit queries. Keep these transactions short to avoid holding snapshots that prevent vacuum.

Serializable: write-heavy transactions with complex invariants where application-level locking is impractical. Double-booking prevention, inventory allocation, accounting entries with zero-sum constraints.

Set isolation per-transaction, not globally:

-- Per-transaction (recommended)
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... queries ...
COMMIT;

-- Kill long-idle transactions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Prevention Strategy

Default to Read Committed. Upgrade per-transaction only when you have a documented reason.

Monitor idle in transaction sessions regardless of isolation level. At Read Committed it's wasteful; at Repeatable Read or Serializable it actively prevents vacuum from reclaiming dead tuples.

Design schemas to minimize serialization conflicts. Two transactions that always update the same counter row will always conflict under Serializable. Restructure to per-user or per-partition counters to reduce contention.

Under high concurrency at Serializable level, expect 5-20% serialization failures. If your retry rate is higher, the access patterns may need restructuring more than the isolation level needs raising.

Originally published at mydba.dev/blog/postgres-transaction-isolation-levels

PostgreSQL Slow Query Log: Finding & Fixing Your Slowest Queries

Philip McClarence — Thu, 16 Apr 2026 10:00:06 +0000

PostgreSQL Slow Query Log: Finding & Fixing Your Slowest Queries

Here's something that surprises a lot of developers: PostgreSQL doesn't log slow queries by default. The log_min_duration_statement parameter ships at -1 (disabled), which means your database could be running queries that take 5 seconds, 10 seconds, even 30 seconds -- and nobody knows unless someone complains. Performance regressions happen silently, accumulate over months, and by the time they cross the pain threshold, the root cause is buried under layers of schema changes and data growth.

Let's fix that.

The Problem

The most common scenario is a query that gradually degrades. It took 50ms six months ago, takes 500ms today, and nobody noticed the drift because slow query logging was never enabled. The table grew, statistics shifted, maybe an index got dropped during a migration. Without logging, there's no trail to follow.

Even teams that enable slow query logging often misconfigure it. Three common mistakes:

Too high a threshold. Setting log_min_duration_statement to 10 seconds catches only catastrophic queries. The steady stream of 1-2 second queries that collectively dominate database load goes completely unnoticed.

Too low a threshold. Setting it to 1ms floods the logs with noise. Finding meaningful signals in gigabytes of log output is impractical.

Optimizing outliers instead of total load. Slow query logs show individual executions. A query that runs once at 800ms is less important than a query running 10,000 times per hour at 50ms each (500 seconds of total load). Without aggregation via pg_stat_statements, you're chasing the wrong queries.

How to Detect It

First, check what's currently configured:

-- Check current slow query log configuration
SELECT
    name,
    setting,
    unit,
    short_desc
FROM pg_settings
WHERE name IN (
    'log_min_duration_statement',
    'log_statement',
    'log_duration',
    'log_line_prefix',
    'auto_explain.log_min_duration'
)
ORDER BY name;

If log_min_duration_statement shows -1, slow query logging is completely disabled. A value of 0 logs everything (useful for debugging, too noisy for production). A good production starting point is 250 to 1000 milliseconds.

Use pg_stat_statements to find the queries that matter most -- sorted by total execution time, not individual execution time:

-- Top queries by total execution time (cumulative load)
SELECT
    substring(query, 1, 100) AS query_preview,
    calls,
    round(total_exec_time::numeric, 1) AS total_time_ms,
    round(mean_exec_time::numeric, 1) AS avg_time_ms,
    round(max_exec_time::numeric, 1) AS max_time_ms,
    round(stddev_exec_time::numeric, 1) AS stddev_ms,
    rows
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 20;

The total_exec_time column is the most actionable metric. A query averaging 5ms called 1 million times (5,000 seconds total) deserves more attention than one averaging 2 seconds called 10 times (20 seconds total). The stddev_exec_time reveals plan instability -- queries where some executions are fast and others are slow.

Setting Up Slow Query Logging

Enable with an appropriate threshold:

-- 250ms is a good starting point for web/API workloads
ALTER SYSTEM SET log_min_duration_statement = '250ms';
SELECT pg_reload_conf();

-- Verify
SHOW log_min_duration_statement;

For analytical workloads where multi-second queries are normal, set it higher (2-5 seconds). You can always lower it later once you've processed the initial findings.

Add useful context to log lines:

ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';
-- %m = timestamp with milliseconds
-- %p = process ID
-- %u = user name
-- %d = database name

-- Log parameters for parameterized queries (PostgreSQL 14+)
ALTER SYSTEM SET log_parameter_max_length_on_error = 1024;

SELECT pg_reload_conf();

Enable auto_explain for automatic plan capture:

-- In postgresql.conf: shared_preload_libraries = 'pg_stat_statements, auto_explain'
ALTER SYSTEM SET auto_explain.log_min_duration = '500ms';
ALTER SYSTEM SET auto_explain.log_analyze = off;    -- on = actual times (adds overhead)
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_format = 'json';
ALTER SYSTEM SET auto_explain.log_nested_statements = on;

SELECT pg_reload_conf();

Setting auto_explain.log_analyze = off logs the estimated plan without running ANALYZE, avoiding execution overhead. Turn it on only during targeted debugging.

On AWS RDS/Aurora, configure via parameter groups:

log_min_duration_statement = 250
log_statement = none
shared_preload_libraries = pg_stat_statements,auto_explain
auto_explain.log_min_duration = 500

RDS logs go to CloudWatch. Enable "Publish to CloudWatch" in the RDS console to export PostgreSQL logs automatically.

Enable pg_stat_statements if not active:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
SELECT pg_reload_conf();

Analyzing Logs at Scale

For historical analysis, pgBadger generates HTML reports from PostgreSQL log files:

# Generate an HTML report
pgbadger /var/log/postgresql/postgresql-*.log -o slow_query_report.html

# For a specific date range
pgbadger --begin "2026-02-01 00:00:00" --end "2026-02-28 23:59:59" \
    /var/log/postgresql/postgresql-*.log -o february_report.html

pgBadger normalizes queries, groups them by pattern, and shows total execution time, call frequency, and hourly distribution. It's an excellent complement to real-time monitoring.

Prevention Strategy

Establish a slow query budget as part of your performance SLA. Define what "slow" means for your app -- for a web API, over 100ms might be unacceptable; for a batch pipeline, 5 seconds might be fine. Set log_min_duration_statement to match your SLA and treat every logged query as a performance bug.

Monitor trends, not just individual slow queries. A query degrading from 10ms to 50ms over three months won't trigger a 250ms threshold, but it's a 5x regression that will eventually become a problem.

Include EXPLAIN ANALYZE in code reviews. Before merging any PR that adds or modifies a database query, run it against a production-sized dataset and verify the plan uses indexes appropriately.

Reset pg_stat_statements after major releases (SELECT pg_stat_statements_reset()) to get a clean baseline. Cumulative stats make it hard to isolate the impact of recent changes.

Originally published at mydba.dev/blog/postgres-slow-query-log

PostgreSQL Monitoring Tools Compared (2026)

Philip McClarence — Wed, 15 Apr 2026 10:00:03 +0000

PostgreSQL Monitoring Tools Compared (2026)

PostgreSQL gives you everything you need to understand what's happening inside your database -- pg_stat_statements, pg_stat_activity, pg_locks, EXPLAIN ANALYZE. The data is all there. The problem is turning it into actionable insight without building a custom monitoring stack.

If you're running more than a couple of PostgreSQL instances, or if "SSH in and run a query" isn't a sustainable monitoring strategy, you need a tool. Here's every major option compared.

The Baseline: What PostgreSQL Provides Natively

Before evaluating tools, know what you get for free:

-- Current activity
SELECT state, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, wait_event_type, wait_event
ORDER BY count(*) DESC;

-- Top queries by total time (requires pg_stat_statements)
SELECT
    substring(query, 1, 80) AS query_preview,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 1) AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Database health snapshot
SELECT
    datname,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1) AS cache_hit_ratio,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    deadlocks
FROM pg_stat_database
WHERE datname = current_database();

If you find yourself running these queries manually more than once a week, you need a monitoring tool.

The Tool Categories

Postgres-Specialized SaaS

myDBA.dev

Purpose-built for PostgreSQL. A lightweight Go collector gathers metrics at 15-second intervals. No agent installation on the database server needed.

What it does well:

Automated health checks across 10 domains with scored assessments
EXPLAIN plan capture and regression detection
Index advisor with specific recommendations
Lock chain visualization
Extension-specific monitoring: TimescaleDB, pgvector, PostGIS
Replication topology mapping

Where it falls short:

Newer product, smaller community than established tools
No infrastructure-level metrics (CPU, memory, disk) -- monitors PostgreSQL, not the host

Pricing: Free tier (7-day retention, 1 instance). Pro tier for extended retention and multi-instance.

pganalyze

Mature PostgreSQL monitoring focused on query performance analysis. Ruby-based collector. Good documentation and established user base.

What it does well:

Deep query performance analysis
Automated index recommendations
Schema evolution tracking
Log-based EXPLAIN plan collection

Where it falls short:

Collector requires installation on the database host or sidecar container
Batch-interval processing (not real-time)
No health check scoring
No extension-specific monitoring (pgvector, PostGIS, TimescaleDB)

Pricing: Starts at $249/month per server. No free tier for production.

General-Purpose SaaS

Datadog

Monitors PostgreSQL as part of its broader platform alongside host metrics, APM, and logs.

What it does well:

Unified view across your entire stack
Correlate database slowness with application latency and infra issues
Excellent alerting and dashboard customization
APM integration shows which endpoints generate the most DB load

Where it falls short:

Shallow Postgres depth -- no EXPLAIN plans, no index advisor, no vacuum analysis
Query analysis relies on pg_stat_statements without deeper plan-level insights
Postgres is one of hundreds of integrations, not the focus

Pricing: Database monitoring starts at $70/host/month (on top of $15+/host for infra monitoring).

Self-Hosted Open Source

Percona Monitoring and Management (PMM)

Open-source monitoring for PostgreSQL, MySQL, and MongoDB. Grafana-based dashboards with VictoriaMetrics storage.

What it does well:

Free and open-source
Query analytics (QAN) for slow query identification
Familiar Grafana dashboards
Multi-database support

Where it falls short:

You must host, upgrade, backup, and scale the PMM server
PostgreSQL support less mature than MySQL (Percona's core focus)
No automated health checks or EXPLAIN plan regression detection
Dashboard complexity can overwhelm smaller teams

Pricing: Free. Commercial support available.

pgwatch2

Postgres-only monitoring. Collects metrics via SQL queries, stores in InfluxDB or TimescaleDB, visualizes with Grafana.

What it does well:

Free and Postgres-specific
Flexible custom SQL metric collection
Lightweight collector
Good time-series storage choices

Where it falls short:

Three components to host and maintain (collector, metrics store, Grafana)
No built-in alerting (Grafana alerting or external tools)
No EXPLAIN plan analysis or recommendations
Significantly more setup effort than hosted tools

Pricing: Free.

CLI / Log Analysis

pgBadger

Parses PostgreSQL log files and generates detailed HTML reports. Not real-time -- post-hoc analysis only.

What it does well:

Extremely detailed reports: query normalization, hourly patterns, error categorization
Zero database load (reads log files, not live connections)
Single binary, no dependencies
Free

Where it falls short:

Static reports, not continuous monitoring
Requires specific PostgreSQL logging configuration
No alerting, no dashboards, no ongoing tracking

DIY: pg_stat_statements + Grafana

Build your own by querying system views, storing results in Prometheus/InfluxDB/TimescaleDB, and building Grafana dashboards.

Strengths: Complete control, no vendor lock-in, integrates with existing Grafana.

Reality: Significant build and maintenance time. No automated analysis. Every PG upgrade may break queries. The builder must maintain it forever.

Decision Matrix

Criteria	myDBA.dev	pganalyze	Datadog	PMM	pgwatch2	pgBadger
Setup time	Minutes	Hours	Hours	Hours-Days	Hours-Days	Minutes
Self-hosting	No	No	No	Yes	Yes	N/A
Postgres depth	Deep	Deep	Shallow	Medium	Medium	Deep (logs)
EXPLAIN plans	Yes	Yes	No	No	No	No
Health check scoring	Yes	No	No	No	No	No
Index advisor	Yes	Yes	No	No	No	No
Extension monitoring	Yes	No	No	No	No	No
Real-time	Yes	Delayed	Yes	Yes	Yes	No
Alerting	Yes	Yes	Yes	Yes	Via Grafana	No
Free tier	Yes	No	No	Yes (self-host)	Yes (self-host)	Yes

How to Choose

Small team, few instances, need Postgres depth: myDBA.dev (free tier) or pganalyze (paid). Both provide the Postgres-specific insights that generic tools miss.

Platform team, many services, need unified observability: Datadog. Its Postgres monitoring is shallow but the correlation with APM and infrastructure metrics is valuable.

Budget-constrained, willing to self-host: PMM for the most features, pgwatch2 for a lighter footprint.

Just need periodic analysis: pgBadger. Parse your logs, get a report, fix the issues. No ongoing infrastructure.

The general rule: evaluate tools against your most common incidents. If your problems are missing indexes, vacuum backlogs, and replication lag, choose a tool that monitors all three with specific recommendations -- not one that shows you a CPU graph and leaves you to figure out the database-level cause.

Start Here

Regardless of which tool you choose, these are foundational:

Enable pg_stat_statements -- every tool relies on it
Set log_min_duration_statement -- capture slow queries in logs
Learn EXPLAIN ANALYZE -- no tool replaces understanding query plans
Monitor continuously -- trends reveal problems before users do

Then layer your monitoring tool on top for historical analysis, alerting, and automated recommendations.