Performance Engineering: Writes, Reads and the Performance Lab

In one line

Improve performance in order of leverage: fix queries and indexes, fix schema and access patterns, tune pooling and memory, add caching and replicas, then partition or shard. For writes, batch and use COPY, size transactions, limit indexes, and move work async. For reads, use covering indexes, caches, read models and keyset pagination. Measure each change in a repeatable benchmark.

0/5 · 0%

Think of it like this

Speeding up a restaurant. First fix the menu and kitchen layout (queries, schema), then add prep stations (indexes), then hire runners (replicas) or open a second kitchen (sharding). Opening a second kitchen first is expensive and doesn't fix a bad recipe.

Key ideas

01
Batch writes: multi-row INSERT or JDBC batching (reWriteBatchedInserts=true for PgJDBC) reduce round trips; COPY loads 10–100× faster than row-by-row inserts. One commit per 1,000 rows instead of per row cuts WAL flushes.
02
Transaction sizing: too small means an fsync per row; too large means long locks, big WAL bursts and replication lag. Batches of 1K–10K rows are typical.
03
Write amplification: each index adds a write; non-HOT updates rewrite all indexes; full-page images after checkpoints. Fewer indexes and HOT-friendly updates help directly.
04
Bulk deletes: batch by key range, or partition and drop. Bulk updates: batch, and avoid updating rows that wouldn't change (WHERE col IS DISTINCT FROM new_value).
05
Performance lab loop: realistic dataset (millions of rows, real distribution) → baseline with EXPLAIN (ANALYZE, BUFFERS) and a load test (pgbench custom scripts or k6) → one change → re-measure latency, throughput, CPU, I/O and plan → keep or revert.

Code & diagrams

bulk-load.sqlsql

-- typical order of magnitude for 1M rows: autocommit row-by-row = minutes,
-- batched multi-row INSERTs = tens of seconds, COPY = seconds
\copy events (tenant_id, created_at, payload) FROM 'events.csv' WITH (FORMAT csv, HEADER)

-- generate a test dataset
INSERT INTO orders (customer_id, created_at, status, total)
SELECT (random() * 100000)::int,
       now() - random() * interval '365 days',
       (ARRAY['placed','paid','shipped','delivered'])[1 + floor(random() * 4)::int],
       round((random() * 5000)::numeric, 2)
FROM generate_series(1, 10000000);
ANALYZE orders;

-- avoid no-op updates (no new row versions, no WAL)
UPDATE product SET price = s.price FROM staging_price s
WHERE product.id = s.id AND product.price IS DISTINCT FROM s.price;

pgbench-custom.sqlsql

-- pgbench -n -c 32 -j 8 -T 120 -f pgbench-custom.sql shop
\set cid random(1, 100000)
SELECT id, created_at, total FROM orders
WHERE customer_id = :cid ORDER BY created_at DESC LIMIT 20;
-- tps = 18422 (without index: 211) | latency average = 1.7 ms

Interview problem

The problem

Ingest 50K events/sec into PostgreSQL

An API receives 50K events/sec, currently inserting each event in its own transaction; the database is at 100% CPU with high WAL fsync waits. Redesign ingestion.

When it breaks

Benchmarking on a tiny dataset

What you see

Everything fits in cache, sequential scans look fast, and the chosen design collapses in production when data exceeds memory.

Fix & prevent

Benchmark with production-sized data and realistic distributions (skewed tenants, hot keys), and cold as well as warm caches.

Explain it without notes

Why is COPY so much faster than individual INSERTs?

Practice

Run a pgbench custom script before and after adding an index and record tps and latency.

Trade-offs

↔
Batching and async ingestion raise throughput and add latency before data is visible; caches and replicas add staleness.

Done when you can

I can apply write and read optimizations and prove them in a repeatable performance lab.

Command Palette