Partitioning vs Sharding.

🧩Side by side

Partitioning          Sharding

Boundary inside 1 server across N servers

Who routes the query planner your app / proxy

Joins normal, full SQL cross-shard = you join in code

Transactions normal ACID distributed, or give up

Unique keys enforced (per part.) only per shard — use UUIDs

Solves table size, purging write throughput, CPU, disk

Key insight: partitioning does NOT increase write capacity. Same disk, same CPU, same WAL. If your bottleneck is 40k inserts/sec, partitioning buys you nothing.

🔧What partitioning actually buys you (Postgres)

CREATE TABLE events (
  id bigserial, created_at timestamptz NOT NULL, payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Two real wins:

1. Pruning — WHERE created_at >= '2026-09-01' scans 1 partition, not 12. Smaller index, better cache hit rate.
2. Retention — dropping old data goes from a 6-hour DELETE that bloats the table and starves autovacuum, to:

ALTER TABLE events DETACH PARTITION events_2025_09;
DROP TABLE events_2025_09;   -- instant, no bloat

⚠️The gotcha that trips everyone

Pruning only works if the partition key is in the WHERE clause. Partition by created_at, then run:

SELECT * FROM events WHERE user_id = 42;

…and Postgres scans every partition — 12 index lookups instead of 1. You made it slower. Always check with EXPLAIN that it says "Partitions removed" / only touches the ones you expect.

Second trap: partition by a hot key and you get a hot partition. Range-partitioning by created_at means today's partition takes 100% of writes. Fine for inserts, bad if you expected load spreading — that's what hash partitioning or sharding is for.

🎯How to choose

Table is huge but the server copes → partition.

You mostly query recent data / delete old data → partition (retention alone is worth it).

Writes are saturating the box, one machine can't hold the data → shard.

You're unsure → partition first. It's reversible and it's ordinary SQL. Sharding rewrites your data layer and takes cross-shard JOINs, unique constraints and transactions away from you permanently.

The honest order most teams should follow: index → partition → read replicas → shard. Sharding is the last resort, not the flex.

🧪Your exercise

Take a table you own with a created_at column. Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM your_table WHERE created_at > now() - interval '7 days';

Note the buffers read. Now list your top 3 query patterns — do all of them filter on created_at? If even one hot query filters only on user_id, range-partitioning by date would make it slower. Which key would you actually partition on, and what breaks?