Partitioning & Scaling

Build it partition by partition. Create the index concurrently/online on each partition, create the parent index as an empty invalid placeholder, then attach each partition index to it; the parent becomes valid once all are attached. Work is resumable, bounded per partition, and can skip cold partitions.

Plan for: stale entries after writes (invalidate after commit, keep TTLs short), stampedes when a hot key expires or the cache restarts (single-flight plus jittered TTLs), the database having to survive a cold cache, and a hit-rate collapse from eviction. A cache is not a source of truth.

Updates to one row serialise on its exclusive row lock, so throughput is capped at roughly one update per lock-hold time — including commit and network round trips — while everyone else queues, and MVCC version chains and index churn make it worse. Fix by removing the single point: sharded counters, an append-only ledger with rollups, or an in-memory counter flushed periodically.

Application-level routing is explicit and safest: the code declares an operation read-only and gets a replica connection. Driver and proxy routing infer intent from statements, which breaks on transactions, session state, SELECT ... FOR UPDATE, and writes hidden inside functions. Whatever the layer, routing must be per transaction, not per statement.

Client-side routing embeds the mapping in the application: fastest, no extra hop, but every service must agree on the topology and redeploy when it changes. A proxy tier centralises routing, pooling and cross-shard merging at the cost of a hop and a component to run. A directory service stores explicit per-key placement, allowing arbitrary moves and pinning, at the cost of a lookup and a critical dependency.

Measure three things per shard: bytes/rows stored, requests per second, and the top keys by request count. Even rows with uneven traffic means request skew; uneven rows means data skew; one key dominating means a hot key. Fix by moving buckets, isolating the heavy tenant, or splitting the key.

Partition when the table is too big to maintain as one unit or when data expires in bulk by a key — not merely because queries are slow. Sixty million rows with a slow query usually needs a better index. Unwarranted partitioning adds planning overhead, more index objects, and constraint restrictions.

Each connection costs memory and a scheduling slot, and concurrency beyond the number of cores and disks only adds context switching, lock contention, and cache thrash. Throughput plateaus then falls. Fix it with a small pool per instance, or a shared external pooler that multiplexes many clients onto few server connections.

Measure where time goes, not what looks busy. Sustained high CPU with a hot working set in memory means CPU-bound; heavy read IO with a low cache hit ratio means memory-bound; high commit latency or IO wait means storage-bound. Lock waits and single-threaded hotspots are not fixed by bigger hardware.

Decide per access path by rate and latency target. Hot user-facing paths must stay single-shard, so duplicate the data and pay write amplification and a consistency window. Rare back-office paths can fan out. Anything analytical belongs in a derived store, not on the shards.

Make it declarative and data-driven: a config row per table (interval, premake count, retention, archive action). The job must be idempotent, singleton-locked, short-transactioned, and lock-timeout bounded with retries. Monitor runway, partition counts and the last successful run, and prefer a proven implementation such as pg_partman over bespoke scripts.

Measure what is actually saturated first — commit/fsync, log volume, index maintenance, lock contention, or connections. Then take the cheap levers in order: batch writes, drop redundant indexes, shorten transactions, move non-critical writes to a durable queue, add IOPS. Splitting across primaries is last because it costs cross-shard queries and transactions forever.

Scale out when the constraint is structural, not purchasable: the single primary's write ceiling, data volume that breaks maintenance and recovery windows, or a cost curve that has gone nonlinear — and only after tuning, caching, replicas, and partitioning have been exhausted. Bring measured headroom, growth rate, and the date the ceiling arrives.

Sub-partitioning partitions each partition again by a second key — typically RANGE by month at the top and HASH or LIST by tenant underneath. It pays off only when both keys appear in queries or maintenance. The cost is multiplicative: levels multiply into total partition count.

The optimizer can join matching partition pairs separately and append the results, so each join works on a small slice: hash tables fit in memory, each pair can run in parallel, and aggregation happens per partition. It requires identical bounds and a join on the partition key. It is off by default because planning cost and memory grow sharply with partition count.

Distribute the large, tenant-owned tables on the same key so matching rows land on the same shard; replicate small, shared lookup tables to every shard. Colocated tables can be joined and updated in one local transaction; non-colocated ones force cross-shard joins and distributed commits.