Partitioning & Scaling

On a non-partitioned table you get one physical index over all rows. On a partitioned table, most engines build one physical index per partition, each covering only that partition's rows; the index you declared on the parent is just metadata tying them together.

Pruning is the database eliminating partitions that cannot contain matching rows, using each partition's declared bounds. It needs a predicate on the partition key. An index narrows the search inside a table; pruning removes whole tables from the plan before any scan happens.

Replicas add read capacity: full copies of the data that can serve queries. They do not add write capacity — every replica must apply the same write stream as the primary — and with asynchronous replication their data is slightly behind, so reads can be stale.

Partitioning splits one logical table into physical pieces inside the same database, chosen by a partition key. RANGE assigns contiguous intervals (usually dates), LIST assigns explicit value sets (region, status), HASH spreads rows evenly by a hash of the key.

Vertical scaling means one bigger machine: more CPU, RAM, faster disks. Horizontal scaling means more machines: read replicas to spread reads, or shards to split data across independent primaries. Vertical is simple but capped; horizontal is unbounded but changes the programming model.

Changing N changes the modulus, so nearly every key maps somewhere new and almost the whole dataset has to move. Consistent hashing (a hash ring) moves only about 1/N of keys; the common relational approach is fixed virtual buckets — hash into e.g. 4096 slots and move whole slots between servers.

Options: co-locate both tables on the same shard key so the join is local; replicate small reference tables to every shard; denormalize the needed columns into the row; or join in the application after two lookups. For OLTP, co-locate or denormalize — never shuffle data between shards per request.

The router cannot pick one shard, so it broadcasts to all of them and merges the results — a scatter-gather. Latency becomes the slowest shard's, work multiplies by shard count, and ORDER BY, LIMIT, and aggregates must be recombined by the router.

Shared tables are cheapest and densest but rely on code for isolation and make per-tenant restore hard. Schema-per-tenant separates data structurally but fans out migrations and bloats the catalog. Database-per-tenant gives the strongest isolation and easy per-tenant backup, at the highest fixed cost per tenant.

A local index is one index per partition: cheap partition lifecycle, per-partition rebuilds, but N probes when the query cannot be pruned, and uniqueness only within a partition. A global index is one index over all partitions: single-probe lookups and true global uniqueness, but dropping or exchanging a partition invalidates it and forces maintenance.

Uniqueness is enforced by a per-partition index that only sees its own rows, so duplicates in different partitions would go undetected. Including the partition key guarantees a given key value can only land in one partition, making local enforcement equal global enforcement. Otherwise: repartition, use a global index engine, or enforce elsewhere.

DELETE touches every row: a log record per row, dead row versions, index entries left behind, bloat, and vacuum work afterwards, and the space is not returned. Dropping a partition is a catalog change plus file unlink: constant time, space freed at commit. It requires partitioning by the retention key.

Wrapping the partition key in a function or cast, comparing it to a mismatched type, filtering on a derived column instead of the key, using an operator the partitioning strategy cannot use (a range test on hash partitions), or having no direct predicate at all because the restriction is on a joined table. Rewrite as a plain half-open range on the raw key.

The write committed on the primary but the replica hadn't replayed it yet — replica lag breaks read-your-own-writes. Fixes: route a user's reads to the primary for a short window after they write, or capture the write's log position and make the replica wait until it has applied at least that position.

Hashing spreads keys uniformly, so load balances well but ordered and range scans must hit every shard. Range placement keeps neighbouring keys together, so range scans are local and splits are cheap, but sequential keys create a hot shard on the newest range.

Sharding splits the rows of one logical table across several independent database servers, choosing the server by a shard key. A good shard key appears in nearly every query, has high cardinality, spreads load evenly, and keeps rows that are read together on the same shard.

Pick the column that appears in most query predicates and governs how data expires — usually a timestamp. Size partitions so each holds a manageable slice, typically tens to a few hundred per table. A bad key is one your queries do not filter on, or one that creates thousands of tiny partitions.

In a single-primary cluster every write is executed once on the primary and then replayed on the others, so the extra machines are copies, not additional writers. The ceiling is one machine's commit path. You raise it by making that path cheaper, or by having more primaries — i.e. sharding.

Two-phase commit holds locks while it waits for a coordinator, blocks in-doubt if the coordinator dies, adds round trips and fsyncs, and makes availability the product of all participants. Teams instead redesign so the write is single-shard, or use a saga with idempotent compensating steps plus a transactional outbox.

Copy the slice's existing rows to the target, keep applying ongoing changes until lag is near zero, briefly fence writes for that slice, verify with checksums, flip the routing directory to the new shard, then keep the source read-only as a rollback path before deleting it.

Layer defenses: inject the tenant predicate at one application choke point, and enforce it again in the database with row-level security policies keyed off a per-transaction session setting. Force policies so table owners are not exempt, reset the setting on pooled connections, and test cross-tenant access.

Attach validates that every row in the incoming table satisfies the new bound, scanning it under an exclusive lock on that table unless a matching validated CHECK constraint lets the scan be skipped. Detach needs an exclusive lock on the parent; a concurrent detach takes a weaker lock in two steps instead of blocking all queries.

Plan-time pruning uses values known while planning, so unneeded partitions never enter the plan. Execution-time pruning handles values known only at run time: bind parameters of a reusable plan, subquery results, and per-row values in a nested loop. In EXPLAIN ANALYZE it shows as "Subplans Removed" or child nodes marked "never executed".

With a database per tenant, restore that database to a point in time and drop it on exit. With schema per tenant, restore a side copy and dump or swap the one schema. With shared tables, restore the whole database to a side copy, extract that tenant's rows in dependency order and merge them back, and delete by cascading batched DELETEs.

The insert fails and aborts the transaction, since there is nowhere to route the row. Teams pre-create future partitions on a schedule with a runway buffer and alert when the runway shrinks. A catch-all partition prevents the error but silently collects rows and must be scanned and emptied before overlapping partitions can be added.

The old style built partitions manually: child tables with CHECK constraints, a trigger or rule to route inserts, and the planner excluding children by constraint. Declarative partitioning makes the engine own routing, bounds and pruning — faster, correct by construction, and far less code to maintain.

Treat it as an orchestrated job, not one DDL: backward-compatible expand/contract steps, per-tenant version tracking, idempotent and resumable execution, batched throttled backfills, canary tenants first, and application code that tolerates tenants at both old and new versions mid-rollout.

Tenants share the buffer cache, CPU, I/O, connections and locks, so one heavy tenant evicts others' cached pages and starves workers. Fix with statement timeouts and per-tenant connection quotas, offload reporting to a replica, tune plans for skewed tenants, and move whales to their own database.

Outgoing FKs from a partitioned child are usually fine — they're enforced per partition. Incoming FKs to a partitioned parent need a unique key on the parent, which must include the partition key, so the referencing table must carry the whole composite key. Some engines (MySQL/InnoDB partitioning) forbid foreign keys entirely.