Partitioning and hot keys

A table partition divides one logical table into portions, for example by date range. PostgreSQL can prune irrelevant partitions when a predicate permits it, reducing work without implying that partitions live on separate servers. Distributed sharding assigns portions to different owners. Replication makes additional copies of a portion and has its own consistency protocol. More replicas can increase some read capacity, but they do not automatically parallelize a single ordered write stream.

Range partitioning supports ordered scans and dropping old ranges, but an append-only workload can concentrate writes in the newest range. Hash partitioning distributes many independent keys more evenly in expectation, while scattering range queries. Composite schemes can combine time buckets with a hash within each bucket. State the query fan-out and ownership boundary explicitly: transactions or aggregates spanning many partitions introduce coordination and merging work that a single-partition benchmark omits.

Measure traffic and service cost per key and partition, not just key counts. Equal numbers of keys can hide one tenant with large values or expensive updates. If all events for a key must preserve one total order, hashing that key always sends them to one logical partition. Adding empty partitions cannot split the key while preserving the exact same ownership rule. A noisy key can also delay unrelated keys sharing its partition or worker.

Salting a hot key creates several subkeys and can spread commutative updates such as partial counters. Reads then aggregate the shards, and correctness depends on the merge operation and visibility contract. Salting an account balance or sequence-sensitive workflow without redesigning its transaction semantics can break invariants. Caching, batching, dedicated placement, admission control, or separating tenant workloads may be safer than splitting the logical key. Choose the remedy from the operation's algebra and ordering needs.

Moving a partition means coordinating a source, destination, and routing view while writes continue. A design may copy a snapshot, replay subsequent changes, establish a cutover boundary, and fence the old owner. Without fencing, delayed requests can write to an obsolete owner after the new owner is active. Without a complete replay boundary, acknowledged updates can be omitted from the destination. Reads also need a declared policy while migration is in progress.

In an event log, ordering is usually a per-partition guarantee; a consumer group's parallelism is bounded by assignment and the processing model. Increasing partition count can alter key-to-partition mapping depending on the producer's partitioner, so do not assume old and new events automatically retain one combined key order. Validate migrations with stable event identities, versioned routing, and reconciliation counts. Capacity planning should include the copy traffic and temporary duplicate storage rather than only the final balanced state.