Partitioning

By the end of this lesson you'll be able to split a billion-row table into manageable pieces, choose the right partitioning strategy for your data, and write queries the optimiser can prune down to a single partition — turning full-table scans into instant reads and slow archival into a one-line drop.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Partitioning splits one logically-single table into many smaller physical tables, called partitions , based on a partition key (one or more columns). To your application it's still one table — you SELECT and INSERT against the parent — but under the hood the database stores and scans each partition separately.

Think of a filing cabinet with one drawer per month. You don't dump every invoice in a single giant pile — you put each in its month's drawer. When someone asks for March invoices, you open only the March drawer and ignore the other eleven. That "open only the relevant drawer" move is exactly partition pruning , the optimisation that makes partitioning fast.

RANGE partitioning sends each row to a partition based on which range its key falls into. It's the most common strategy and the natural fit for anything ordered — dates above all, but also sequential IDs or numeric buckets. You define each partition with a lower and upper bound; the bounds are half-open [FROM, TO) , so the FROM value is included and the TO value is the first value that belongs to the next partition.

Partition pruning is the optimiser reading your WHERE clause, comparing it to each partition's bounds, and refusing to even open the partitions that can't contain a match. A query against five years of data that filters to one quarter touches one partition instead of twenty. Use EXPLAIN to prove it — the plan lists only the partitions actually scanned.

A table is queried by a small, fixed set of warehouse codes. Pick RANGE , LIST , or HASH and fill in the blank. The reasoning and answer are in the comments so you can check yourself.

LIST partitioning assigns rows by matching the key against an explicit set of values, like sorting mail into bins labelled by country. Reach for it when the key is categorical and you almost always filter by it: region, country, tenant ID, status. Add a DEFAULT partition as a catch-all — without one, inserting a value you didn't list raises an error.

A DEFAULT partition catches every value that doesn't match a listed partition. Without it, an unexpected region like 'za' makes the INSERT fail with "no partition of relation found for row" .

HASH partitioning has no natural ranges or categories — it runs the key through a hash function and uses hash(key) % N to pick a partition, spreading rows almost perfectly evenly. Use it to cap partition size when there's no meaningful range or list, for example splitting sessions by user_id . The trade-off: hashing destroys order, so it can only prune for exact equality, never for ranges.

Choosing hash when you actually run range queries. WHERE user_id = 42 prunes to one partition, but WHERE user_id > 100 must scan all of them — the hash scatters consecutive values everywhere. If you query by range, use RANGE.

Finish a RANGE partition that holds the first half of 2025. Mind the half-open bounds — the TO value is excluded.

Composite (or sub- ) partitioning makes a partition that is itself partitioned — split by RANGE on date, then by LIST on type within each year. Queries that filter on both dimensions get double pruning , narrowing straight to one sub-partition. It's a power tool for billion-row tables only: every layer multiplies your partition count, and too many partitions hurts.

The biggest day-to-day payoff of partitioning is cheap maintenance. Deleting a year of rows from a normal table is a slow, lock-heavy DELETE that leaves bloat behind; with partitions you just DROP or DETACH the whole partition — a near-instant metadata change. You typically add next month's partition on a schedule and drop the oldest at the same time. On PostgreSQL 11+, indexing the parent automatically creates a matching index on every partition.

Q: What's the difference between partitioning and sharding?

Partitioning splits a table across multiple tables on one server; sharding splits it across multiple servers . Partitioning helps a single machine cope with big tables; sharding scales beyond one machine. You'll cover sharding next.

Q: Can I change a non-partitioned table into a partitioned one?

Not in place — partitioning is set at CREATE TABLE . The usual path is: create a new partitioned table, copy rows in (or ATTACH the old table as a partition), then swap names. Plan it before the table gets huge.

Almost always yes. Pruning narrows the query to the right partition; an index then finds rows quickly inside it. On PostgreSQL 11+, indexing the parent creates the index on every partition automatically.

There's no hard limit, but query planning time and metadata overhead grow with partition count. Hundreds are usually fine; many thousands of tiny partitions often hurt more than they help. Size partitions so each one is worth skipping.

Put it all together — a brief, an empty canvas, and the expected shape in the comments. Decide the strategy, write the table and one partition, then the one-line archive. Copy it into a Postgres playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary