Partitioning and Sharding: Range, Hash, List, and Hot Partitions

Problem Context

Partitioning is how you keep a single logical table queryable when it has 10 billion rows. The trick: physically split the data into smaller pieces (partitions / shards) so each query touches only the slice it needs. Postgres declarative partitioning + pg_partman (2026 stable on PG 17/18), Cosmos DB partition keys, DynamoDB partition + sort keys, and Cassandra wide rows all rest on the same ideas with different vocabulary.

The 2026 reality: most teams should partition their largest 1-3 tables and leave the rest alone. Premature partitioning multiplies operational pain (planner overhead, broken constraints, refresh complexity) for tables that fit comfortably on one node. But once you cross ~50-100M rows or single-table indexes stop fitting in RAM, partitioning is the difference between sub-second queries and page-out hell.

Concept Explanation

Three orthogonal axes:

• Vertical partitioning — split a wide table into narrower ones (rare attribute groups, large blobs). Mostly a schema concern.
• Horizontal partitioning (single node) — split rows across partitions of one DB. Postgres declarative partitioning. Each partition is a child table.
• Sharding (multi-node) — split rows across separate database servers. Citus, Vitess, Cosmos, DynamoDB, Cassandra. Hardest because cross-shard joins/transactions are expensive or impossible.

Three strategies for choosing which row goes where:

• Range— by date or ID range. Best for time-series; supports easy "drop oldest partition".
• Hash— by hash of the key. Best for even distribution; no "drop a date range" trick.
• List — by explicit value (region, tenant tier). Use when you really need per-value isolation.

flowchart LR
    Q["Query<br/>WHERE event_at &gt;= '2026-04-01'"] --> P["Planner<br/>partition pruning"]
    P --> P1["events_2026_04<br/>(scanned)"]
    P -.skipped.-> P2["events_2026_03"]
    P -.skipped.-> P3["events_2026_02"]
    P -.skipped.-> P4["events_..."]

    style P fill:#0078D4,color:#fff,stroke:#005a9e
    style P1 fill:#16a34a,color:#fff,stroke:#15803d

Implementation

Step 1: Range partition by month (Postgres)

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id   uuid NOT NULL,
    event_at    timestamptz NOT NULL,
    payload     jsonb NOT NULL,
    PRIMARY KEY (id, event_at)          -- partition key MUST be in PK
) PARTITION BY RANGE (event_at);

CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

-- Index per partition (or once on parent → propagated)
CREATE INDEX ON events (tenant_id, event_at DESC);

Step 2: Automate partition lifecycle with pg_partman

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table    => 'public.events',
    p_control         => 'event_at',
    p_type            => 'range',
    p_interval        => '1 month',
    p_premake         => 4              -- pre-create 4 months ahead
);

-- Schedule retention via cron / pg_cron
UPDATE partman.part_config
SET retention            = '12 months',
    retention_keep_table = false        -- physically drop, not detach
WHERE parent_table = 'public.events';
SELECT partman.run_maintenance();

Step 3: Hash partition for even distribution

-- Use hash when you don't have a natural range and want flat load
CREATE TABLE messages (
    id         bigint GENERATED ALWAYS AS IDENTITY,
    user_id    bigint NOT NULL,
    body       text NOT NULL,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

-- 8 partitions
DO $$
BEGIN
    FOR i IN 0..7 LOOP
        EXECUTE format(
            'CREATE TABLE messages_p%s PARTITION OF messages FOR VALUES WITH (MODULUS 8, REMAINDER %s)',
            i, i);
    END LOOP;
END $$;

Step 4: Drop old partitions instead of DELETE

-- DELETE FROM events WHERE event_at < '2025-04-01';   -- slow + bloat
DROP TABLE events_2025_03;                              -- instant + reclaims disk

-- Or detach (keep for archive) then drop later
ALTER TABLE events DETACH PARTITION events_2025_03 CONCURRENTLY;

Step 5: Cosmos DB partition key from C#

// Partition key choice IS the partitioning strategy in Cosmos
var props = new ContainerProperties("events", partitionKeyPath: "/tenantId");
await db.CreateContainerIfNotExistsAsync(props, throughput: 4000);

// Hierarchical partition keys (GA 2024) for two-level locality
var hpkProps = new ContainerProperties("events", new[] { "/tenantId", "/yearMonth" });
// Queries scoped to one tenantId hit a single physical partition.
// Queries scoped to tenantId + yearMonth hit a sub-slice — even cheaper.

// Always pass the PartitionKey on point reads
var resp = await container.ReadItemAsync<Event>(
    id: eventId, partitionKey: new PartitionKey(tenantId));

Step 6: Verify partition pruning is happening

-- The EXPLAIN should mention only the partitions you expect
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE event_at >= '2026-04-15' AND event_at < '2026-04-16'
  AND tenant_id = '...'::uuid;
-- Look for "Append" → only events_2026_04 scanned, others pruned.
-- If all partitions appear, the WHERE doesn't include the partition key
-- in a form the planner can prune (e.g., function-wrapped column).

Common Pitfalls

1. Partition key not in the query.The whole point is partition pruning. Queries that don't filter on the partition key scan every partition — slower than the un-partitioned table.
2. Wrong key for the workload. Range by created_at for an append-only log is great. Range by created_atfor tenant-scoped reads is terrible — every query crosses every partition.
3. Hot partition.Skewed keys (one tenant 100x bigger, today's date for time-series) overload one partition. Add a second dimension (tenantId + month) or use hash.
4. Foreign keys to a partitioned table. Postgres has historically restricted FKs into partitioned tables. PG 18 closed most gaps, but verify on your version before designing.
5. Partitioning too early. Below ~50M rows or where the index fits in RAM, partitioning adds planner overhead with no benefit. Measure first.
6. Forgetting to maintain partitions.Without pg_partman or a cron job, you wake up to "no partition exists for this row" errors at midnight on the 1st.

Practical Takeaways

• Partition by what your queries filter on. Time-series → range by date. Multi-tenant → key by tenant.
• Use pg_partman (or equivalent) to automate creation + retention. Manual maintenance always fails on a holiday.
• Drop old partitions; never DELETE billions of rows.
• In Cosmos / DynamoDB, the partition key is forever — design as if you can't change it.
• Hierarchical partition keys (Cosmos) and composite shard keys (DynamoDB) avoid hot partitions while keeping locality.
• Always verify pruning with EXPLAIN — "partitioned" ≠ "fast".
• Don't shard until you've maxed out a single node. Operating one big DB is much easier than ten small ones.