📊Read vs write optimization
Every read shortcut taxes writes
Read-heavy surfaces love replicas, caches, and covering indexes. Write-heavy paths want narrow rows, batching, sequential or BRIN-friendly keys, and as few secondary indexes as you can justify, each index is another structure to update on every mutation.
🎯When signals disagree
Split paths before tuning blindly.
| Signal | Read path move | Write path move |
|---|---|---|
| Hot dashboard | Materialized view, cache, or read replica | Defer refresh, async jobs, bounded staleness SLAs |
| Ingest storm | Isolate analytics to replicas or warehouse | Bulk COPY, staging tables, temporarily fewer secondary indexes |
Read: covering index avoids heap hits
SELECT order_id, total_usd FROM orders WHERE customer_id = 441;| order_id | total_usd |
|---|---|
| 9001 | 44 |
| 9002 | 120.5 |
Write: defer secondary indexes during bulk load (pattern)
-- 1) DROP INDEX CONCURRENTLY …
-- 2) bulk insert
-- 3) CREATE INDEX CONCURRENTLY …| note |
|---|
| Reduces random index maintenance during load |
Index everything on a churn table
- Covering every dashboard variant while inserts crawl.
- Massive transactions pinning WAL and delaying checkpoints.
- Heavy BI on the primary without concurrency caps.
Measure write amplification
- Pair each new index with expected selectivity and write regression notes.
- Tune checkpoint and autovacuum (or equivalent) on high-churn tables.
- Use poolers and role limits to cap concurrent writers vs readers.
Isolation
Read scaling via async replicas changes what users may see after a write, say it in the product contract, not only in infra docs.
TL;DR
Read optimization uses indexes, covering paths, denormalized read models, and replicas to cut latency and primary load. Write optimization batches inserts, avoids unnecessary secondary indexes, orders updates to reduce deadlock risk, and uses appropriate isolation. Async replicas improve read throughput but are eventually consistent: route only workloads that tolerate staleness off the primary.