Table Partitioning

You're running an analytics platform. Your events table has 2 billion rows. Every query against it, even with indexes, takes 30 seconds. Adding a new index takes 6 hours. Running VACUUM takes 12 hours. Backing up the table takes all night.

The indexes don't fit in memory anymore. The database has to read index pages from disk, which is 1000x slower than memory. Your B-tree indexes are 7 levels deep instead of the usual 3-4, meaning every lookup requires 7 disk reads.

The table isn't broken. It's just too big for a single unit of storage to handle efficiently.

Table partitioning solves this by dividing one logical table into multiple physical pieces (partitions). Each partition stores a subset of the rows. The database treats them as one table, your queries don't change, your application doesn't change. But internally, each partition is a separate, smaller table with its own indexes, its own storage, and its own maintenance schedule.

Instead of one 2-billion-row table, you might have 24 monthly partitions of ~80 million rows each. Each partition is manageable. Queries that filter by date only touch the relevant partitions. Maintenance runs on one partition at a time. Dropping old data is instantaneous, just drop the partition.

Partitioning splits a table into pieces based on a partition key, a column (or combination of columns) that determines which partition a row belongs to.

Logical view (what your application sees):

SELECT * FROM events WHERE created_at >= '2025-01-01';

One table. One query. Normal SQL.

Physical reality (what the database manages):

events (parent table, no data stored here)
├── events_2024_01 (January 2024 rows)
├── events_2024_02 (February 2024 rows)
├── events_2024_03 (March 2024 rows)
├── ...
├── events_2025_01 (January 2025 rows)
├── events_2025_02 (February 2025 rows)
└── events_2025_03 (March 2025 rows)

When you query with a filter on the partition key (created_at), the database knows which partitions to check and skips the rest. This is called partition pruning, and it's the primary performance benefit.

Without partitioning: scan all 2 billion rows. With partitioning by month: scan only the ~80 million rows in the relevant month.