PostgreSQL is approaching its write capacity: what would you do before sharding?

• Sharding cost – cross-shard joins, transactions, and unique constraints become hard or impossible; every query must carry a shard key.
• Diagnose first – use pg_stat_activity and pg_stat_statements to find the saturated resource.
• Query/index fixes – the cheapest win; a missing index can make a write 100x more expensive.
• Transaction optimization – shorten transactions, reduce hot-row updates.
• Write reduction – eliminate redundant writes, batch inserts, coalesce updates.
• Connection tuning – PgBouncer to right-size; too many connections cause contention.
• Data lifecycle – partition old data, archive, keep working set small.
• Vertical headroom + workload separation – bigger instance, read replicas for heavy reads.

Why is sharding a last resort?

• Sharding changes application development permanently:
• Cross-shard joins become impossible.
• Cross-shard transactions need sagas.
• Unique constraints cannot span shards.
• Every query must carry or derive a shard key.
• Resharding later is painful.

Step 1: Diagnose the actual bottleneck

-- What are the slowest queries?
SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- What are sessions waiting on?
SELECT wait_event_type, wait_event, COUNT(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2
ORDER BY 3 DESC;

Step 2: Fix queries and indexes

-- A write that triggers a full-table scan for a constraint check
-- Adding the right index can cut write cost 100x
CREATE INDEX CONCURRENTLY idx_transfers_account_id
ON transfers (account_id);

Step 3: Shorten transactions

// WRONG: long transaction holds locks
@Transactional
public void process() {
    doDebit();         // holds lock
    callExternalApi(); // network call while lock held!
    doCredit();
}

// CORRECT: move non-essential work outside the transaction
public void process() {
    doTransfer(); // short @Transactional
    callExternalApi(); // after commit, no lock held
}

Step 4: Batch small inserts

// WRONG: 10,000 single-row inserts
for (LogEntry entry : entries) {
    jdbcTemplate.update("INSERT INTO audit_log ...", entry);
}
// 10,000 round trips + 10,000 WAL entries

// CORRECT: batch insert
jdbcTemplate.batchUpdate("INSERT INTO audit_log ...", entries);
// 1 round trip + much less WAL

Step 5: Right-size connections with PgBouncer

  Without PgBouncer: 50 pods × 20 connections = 1000
  PostgreSQL max_connections = 200 → OVERLOAD

  With PgBouncer: 50 pods → PgBouncer → 150 connections to DB
  DB sees 150 regardless of pod count

Step 6: Partition and archive old data

-- Native table partitioning by month
CREATE TABLE transactions (
    id BIGINT, account_id TEXT, amount NUMERIC, created_at TIMESTAMPTZ
) PARTITION BY RANGE (created_at);

CREATE TABLE transactions_2026_01 PARTITION OF transactions
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- Old partitions can be detached and archived

Step 7: Vertical headroom

A bigger instance with more CPU, memory, and faster disk is instant and reversible. It buys time.

Step 8: Workload separation

Move heavy reads to read replicas. Move unrelated high-write workloads to their own database.

The ordering matters

Every step before sharding is reversible. Sharding is a one-way door.

| Step | Reversible? | Typical Impact |
|---|---|---|
| Query/index fixes | Yes | 10-100x |
| Shorten transactions | Yes | 2-5x |
| Batch inserts | Yes | 5-50x |
| PgBouncer | Yes | Removes connection wall |
| Partitioning | Yes | Keeps working set small |
| Vertical scale | Yes | 2-4x |
| Workload separation | Yes | Removes contention |
| Sharding | NO | Changes everything permanently |

What should a senior engineer know?

• Sharding should be last because it changes application development permanently.
• Work through the ladder: diagnose, query/index, transaction, write reduction, batching, connections, lifecycle, vertical, workload separation.
• Only shard when the single primary is genuinely saturated after all cheaper fixes AND write rate is projected to keep growing.