Database Partitioning

The Database-Specific Framing

 partitioning within databases comes in two flavors:

 1. INTRA-NODE (table partitioning):
    ONE server, table split into pieces by range/list/time:
    
    trips_2026_01, trips_2026_02 ... (declarative range partitions)
    
    wins: partition pruning (queries touch 1 month not 10 years),
          instant expiry (DROP partition vs DELETE millions),
          maintenance per-partition (index rebuild one month)

 2. INTER-NODE (sharding):
    data split ACROSS servers — the scaling topic proper.

 both share vocabulary; interviews mean #2 but testing #1
 knowledge marks depth.

Intra-Node Partitioning First

 before sharding, exhaust single-node partitioning:

 CREATE TABLE trips (...) PARTITION BY RANGE (created_at);

 query WHERE created_at BETWEEN ... 
   → planner prunes to relevant partitions only ✓
   
 retention: DROP TABLE trips_2024_01; → instant, no VACUUM debt ✓

 this solves GROWTH-OVER-TIME and EXPIRY problems
 WITHOUT distribution complexity. do it first;
 it also PREPARES clean shard boundaries later.

Inter-Node Partitioning: The Real Decision

 when writes/dataset exceed one node:

 ┌────────────┬──────────────────────────────────┐
 │ STRATEGY   │ MECHANISM                        │
 ├────────────┼──────────────────────────────────┤
 │ Range      │ user_id 1–1M → node A            │
 │ Hash       │ hash(user_id) % N → node         │
 │ Directory  │ lookup table key→node            │
 │ Entity/Geo │ EU users→EU node (residency!)    │
 └────────────┴──────────────────────────────────┘

 each has dedicated lessons ahead with sharding.
 the decision inputs here:

 - QUERY SHAPE:     point lookups → hash; time scans → range
 - SKEW RISK:       range = hotspots likely; hash = even
 - RESIZE PLANS:    directory = flexible; modulo = painful
 - COMPLIANCE:      entity/geo may be legally required

What Partitioning Buys vs Breaks

 BUYS:
 - write IOPS scale across nodes (the actual goal)
 - working sets fit per-node memory
 - blast radius shrinks (one node down ≠ all down)
 - parallel queries possible across partitions

 BREAKS:
 - cross-partition joins → app-level or denormalized
 - global uniqueness constraints → design ids carefully
 - multi-partition transactions → 2PC/sagas territory
 - secondary indexes become LOCAL (scatter-gather globally)
 - operational count multiplies (backups, upgrades ×N)

The Migration Ladder

 1. vertical + tuning           (free)
 2. intra-node partitioning     (cheap, big pruning/expiry wins)
 3. read replicas               (read offload)
 4. caching layer               (cut read volume entirely)
 5. inter-node sharding         (when writes demand it)

 skipping rungs costs months; each rung defers the next.

Interview Framing

Partitioning questions reward ladder-awareness: mention intra-node partitioning as a cheap first win (pruning + DROP-based retention), then inter-node strategies by decision inputs, then honest breakage list (local indexes, cross-shard transactions). Candidates who conflate table-partitioning with sharding get corrected gently — know which one the interviewer means and say so.