Postgres tuning mistakes I'd check first, part 3: extensions from Go

┌─[fahim@BIDADARI:~]

└─$ less posts/2026-10-18-postgres-tuning-part-3-extensions-from-go.md

18 Oct 2026

This is the last part of the series. Part 1 and part 2 went through fourteen tuning mistakes. This one covers the extensions I leaned on along the way, each called from Go with pgx and measured on the same 2-CPU, 2 GiB Postgres 18 pod. The code for each one is in postgres-tuning under cmd/scenario.

pg_stat_statements

Covered in mistake 1. One more detail from PG 18: IN (...) lists of different lengths now collapse into one entry, so a query called with 3 ids and with 30 ids no longer shows up as two statements (release notes). The Go side is ordinary querying; printTopStatements in cmd/scenario/measure.go is the version used for the numbers in part 1.

auto_explain: the plan that actually ran

EXPLAIN shows the plan for the query you typed. When the slow path is a prepared statement with a generic plan (mistake 9), the plan you get from psql with a literal value is the good one. auto_explain logs the plan of each execution that crosses a time threshold, including its parameters.

In production, load it through shared_preload_libraries or session_preload_libraries and keep log_analyze off, or use auto_explain.sample_rate, because timing every node has overhead. For a debugging session you can load it in one connection (superuser only, docs) and have the plans sent to the client as notices:

var notices []string
cfg, _ := pgx.ParseConfig(dsn)
cfg.OnNotice = func(_ *pgconn.PgConn, n *pgconn.Notice) {
	notices = append(notices, n.Message)
}
conn, _ := pgx.ConnectConfig(ctx, cfg)

for _, stmt := range []string{
	`LOAD 'auto_explain'`,
	`SET auto_explain.log_min_duration = '50ms'`,
	`SET auto_explain.log_analyze = on`,
	`SET auto_explain.log_buffers = on`,
	`SET auto_explain.log_parameter_max_length = -1`, // log parameter values in full
	`SET auto_explain.log_level = notice`,             // send to the client too
} {
	if _, err := conn.Exec(ctx, stmt); err != nil {
		return err
	}
}

Running the generic-plan workload on that connection (make run S=autoexplain) produced exactly one notice, for the slow call:

duration: 2939.191 ms  plan:
Query Text: SELECT id, customer_id, total, created_at FROM orders
WHERE status = $1 ORDER BY created_at DESC LIMIT 50
Query Parameters: $1 = 'refunded'
Limit  (cost=0.43..10.38 rows=50 width=30) (actual rows=50.00 loops=1)
  Buffers: shared hit=47 read=7225
  I/O Timings: shared read=2712.179
  ->  Index Scan Backward using orders_created_at_idx on orders  (cost=0.43..397977.72 rows=2000003 width=30) (actual rows=50.00 loops=1)
        Filter: (status = $1)
        Rows Removed by Filter: 516889

Filter: (status = $1) and rows=2000003 give it away: this is the generic plan, estimated for an average status. (It took 2.9 s rather than the 135 ms from mistake 9 because this run started with a cold cache. I/O Timings shows 2.7 s of it was reading.)

pg_buffercache and pg_prewarm: after a restart

pg_buffercache shows what’s in shared_buffers. pg_prewarm loads a relation into it on demand, and its autoprewarm worker (on by default when the library is preloaded) saves the list of cached blocks at shutdown and reloads them at startup (docs).

var heapBlocks, idxBlocks int64
err := conn.QueryRow(ctx,
	`SELECT pg_prewarm('customers'), pg_prewarm('customers_pkey')`).Scan(&heapBlocks, &idxBlocks)

What the demo showed (make prewarm). 3,000 random primary-key lookups on customers after a restart, with the OS page cache dropped while Postgres was down:

After a restart shared_buffers holds 3,000 lookups p50 p99 Blocks read from outside shared_buffers
cold, autoprewarm off nothing 1.60 s 566 µs 1.10 ms 4,426
then pg_prewarm by hand (took 204 ms) all of customers and its pkey 0.27 s 88 µs 133 µs 0
cold, autoprewarm on (block list saved before the restart) 80 MB of 80 MB, 21 MB of 21 MB 0.28 s 92 µs 134 µs 24

Two restart flows: without autoprewarm nothing is saved, shared_buffers starts empty and 3,000 lookups take 1.60 s; with autoprewarm the block list is saved at shutdown, reloaded in about 2 s at startup, and lookups take 0.28 s

(The only difference is the block list written at shutdown and read back at startup.)

Loading 100 MB took 0.2 seconds, and lookups ran about six times faster afterwards. With autoprewarm on, the cache was back to almost the same state 2 seconds after startup, without anyone running anything. If your database gets restarted for minor version upgrades or pod rescheduling, this is the cheapest fix in the whole series: add pg_prewarm to shared_preload_libraries and autoprewarm runs by default.

The block list holds all of shared_buffers (autoprewarm_dump_now() wrote 65,536 blocks, the full 512 MB), so after a restart the cache looks the way it did at shutdown, not only for the tables you care about.

pgstattuple and pg_repack: measuring and removing bloat

n_dead_tup is an estimate. pgstattuple reads the table and reports exact live, dead and free space, and pgstatindex does the same for a B-tree. Both read the whole relation, so run them on a replica or at a quiet time on big tables. pgstattuple_approx is a cheaper variant.

err := pool.QueryRow(ctx, `
	SELECT pg_size_pretty(t.table_len), t.tuple_percent, t.dead_tuple_percent, t.free_percent,
	       i.avg_leaf_density
	FROM pgstattuple($1::regclass) t, pgstatindex($2::regclass) i`,
	table, table+"_pkey").Scan(&size, &tuplePct, &deadPct, &freePct, &leafDensity)

Plain VACUUM makes dead space reusable but rarely gives it back to the OS. VACUUM FULL rewrites the table and holds an ACCESS EXCLUSIVE lock on it “while it is being processed” (docs), blocking reads and writes. pg_repack builds the new copy online and only takes the exclusive lock briefly at the start and at the final swap (pg_repack docs). It needs a primary key or a unique index on a NOT NULL column, and the client binary has to match the extension version, which is why the scenario image ships both from one package.

What the demo showed (make run S=repack). Two copies of a 1.5M-row table, 70% of rows deleted, then plain VACUUM. A Go goroutine updates random surviving rows the whole time:

Heap before Heap after Duration Writer’s slowest update Updates over 100 ms
VACUUM FULL 355 MB, 69% free 106 MB, 1.4% free 10.7 s 10.7 s 1 (it waited the whole time)
pg_repack --no-order 355 MB, 69% free 106 MB, 1.4% free 9.2 s 0.93 s 20

Timeline from 0 to 11 seconds: VACUUM FULL holds ACCESS EXCLUSIVE for the whole 10.7 s and the writer is blocked throughout; pg_repack builds a copy online for 9.2 s with brief locks at the start and the swap while the writer keeps running

(Where each one holds the exclusive lock, and what the Go writer was doing meanwhile.)

Same result on disk, and the primary key’s leaf density went from 27% to 90% in both cases. With VACUUM FULL the writer was blocked for the entire rewrite. With pg_repack the writer kept going, though not untouched: the slowest update took 0.93 s, and 20 took over 100 ms. My guess is the brief exclusive lock at the swap plus the trigger pg_repack adds to capture changes during the copy, but I didn’t trace it. On an earlier run of the same scenario, VACUUM FULL took 2.5 s and pg_repack’s worst stall was 109 ms.

What matters is how each one scales. VACUUM FULL blocks writes for as long as the rewrite takes, which for a 50 GB table is minutes. pg_repack only needs its exclusive lock at the start and at the swap.

hypopg: try an index before building it

hypopg creates hypothetical indexes that exist only in your session’s memory. Plain EXPLAIN (not EXPLAIN ANALYZE) then shows whether the planner would use them (hypopg).

var name string
var estSize int64
err := conn.QueryRow(ctx,
	`SELECT indexname, hypopg_relation_size(indexrelid)
	 FROM hypopg_create_index('CREATE INDEX ON customers (email)')`).Scan(&name, &estSize)
// EXPLAIN now shows: Index Scan using "<13640>btree_customers_email" on customers

What the demo showed (make run S=hypopg). Two candidate indexes on the 1M-row customers table:

Query hypopg says Estimated size Real index Real size Planner uses real index
WHERE email = '...' Index Scan 47 MiB built in 6.5 s 38 MiB yes
count(*) WHERE tier = 'standard' (90% of rows) Seq Scan, not used 27 MiB built in 1.2 s 6 MiB yes, Index Only Scan

The first case is what hypopg is for: an answer in 3.6 ms instead of a 6.5 s build. The second shows where it stops being reliable. tier has three values, and B-tree deduplication (PG 13+) stores each value once per leaf page with a list of row pointers, so the real index is 6 MiB. hypopg’s estimate doesn’t seem to account for deduplication. Its source never mentions it, and the one pull request that discussed it (#64, “Postgres 13 deduplication is not yet considered”) was closed without merging. That’s my reading of the code, not something the project documents.

With the inflated size the hypothetical index looked too expensive, while the real one gave an index-only scan. hypopg’s answer depends on its size estimate, so treat “would not be used” as a hint, not a verdict, especially for low-cardinality columns.

pgvector: recall, ef_search and filtering

pgvector’s HNSW index trades recall for speed. Defaults are m = 16 and ef_construction = 64 at build time, and hnsw.ef_search = 40 at query time (pgvector). Vectors can go over the wire as text and be cast on the server, so the Go side needs nothing beyond pgx:

const knn = `SELECT id FROM items ORDER BY embedding <-> $1::text::vector LIMIT 10`

err := pgx.BeginFunc(ctx, conn, func(tx pgx.Tx) error {
	if _, err := tx.Exec(ctx, "SET LOCAL hnsw.ef_search = 100"); err != nil {
		return err
	}
	rows, err := tx.Query(ctx, knn, "[0.12,0.98,...]")
	if err != nil {
		return err
	}
	ids, err = pgx.CollectRows(rows, pgx.RowTo[int32])
	return err
})

What the demo showed (make run S=pgvector). 100k 128-dimensional vectors around 100 cluster centers, 50 categories, and 50 queries that are existing items plus noise. Recall@10 is measured against an exact sequential scan:

recall@10 p50
exact (no index) 1.000 66.8 ms
ef_search = 10 0.912 0.73 ms
ef_search = 40 (default) 0.930 1.01 ms
ef_search = 100 0.934 1.33 ms
ef_search = 200 0.934 1.62 ms

My first run used uniformly random vectors and got recall 0.26 at the default setting. Uniform random data in 128 dimensions has no neighborhood structure for the graph to follow, so it’s a poor stand-in for real embeddings. If you benchmark pgvector, use your own embeddings. Recall stopped at 0.934. Raising ef_search past 100 didn’t move it, and I didn’t dig into which queries were missing neighbors.

Filtering is where the default catches people out. The index returns ef_search candidates and the WHERE clause is applied afterwards. With a filter that keeps 2% of rows, 40 candidates leave fewer than one match on average. pgvector 0.8 added iterative index scans, which keep scanning until the LIMIT is satisfied:

Filtered top-10 (WHERE category = $2, 2% of rows) Rows returned per query recall@10 p50
hnsw.iterative_scan = off 0.7 0.062 0.99 ms
hnsw.iterative_scan = relaxed_order 10.0 0.926 2.55 ms
exact (no index) 10 1.000 16.2 ms

Without iterative scan, HNSW returns 40 candidates, the 2% filter leaves 0.7 rows on average, and LIMIT 10 gets 0.7 rows; with relaxed_order the index keeps scanning batches until it has 10 matches

(The filter runs after the index, so the index has to keep going for the LIMIT to fill.)

The query without iterative scan didn’t error. It returned 0.7 rows for a LIMIT 10, which nobody is likely to catch in code review.

One Kubernetes note: building that index with maintenance_work_mem = 512MB failed with could not resize shared memory segment ... No space left on device. A parallel HNSW build keeps the graph in dynamic shared memory, which in a container lives in /dev/shm, and this pod mounts a 256 MiB memory-backed emptyDir there. The pgvector README mentions this for Docker: “make sure --shm-size is at least that size to avoid an error with parallel HNSW index builds” (README). In Kubernetes the equivalent is the sizeLimit of that volume. Either size /dev/shm for your maintenance_work_mem or keep maintenance_work_mem under it.

Summary of part 3

Same 2-CPU, 2 GiB Postgres 18 pod as the rest of the series.

Extension What was measured Before After
pg_prewarm 3,000 lookups after a restart 1.60 s 0.28 s (autoprewarm)
pg_repack writer’s worst stall while removing bloat 10.7 s (VACUUM FULL) 0.93 s
pgvector filtered top-10: rows returned 0.7 10 (iterative_scan)

The repo has every scenario from all three parts, and the raw output of each run is in results/.

Sources

PostgreSQL documentation: pg_stat_statements, auto_explain, pg_prewarm, VACUUM, PG 18 release notes.

Extensions: pg_repack, HypoPG (and PR #64), pgvector.

← back to work