Chapter 25: Table Partitioning — Scaling a Table Beyond a Single Heap

Where you are: Part IV, Chapter 25 of 40 (a ★ added chapter). Indexes (23) and query tuning (24) make most queries fast. But when a single table grows to hundreds of millions or billions of rows, even good indexes strain, and maintenance (VACUUM, bulk deletes) becomes painful. Partitioning splits one giant table into manageable pieces.

Learning paths: 🏗️ DBA and 💻 developers building at scale; 🔬 CS students (the pruning concept). 📊 analysts: partitioned fact tables are common in warehouses (Ch. 34).

When indexes aren't enough

An index makes finding rows fast. But a single enormous table has problems an index doesn't solve: VACUUM and ANALYZE take longer; the index itself becomes huge (and deep); bulk-deleting old data (e.g., "drop last year's logs") means a slow, lock-heavy DELETE of millions of rows; and a "last 30 days" query still has its index spanning all of history. Partitioning addresses these by splitting one logical table into many smaller physical partitions, each a real table under the hood, while you still query the whole thing as one.

The canonical case: a time-series-like table — orders, events, logs, metrics — that grows forever and is mostly queried/maintained by time range. Mercado's orders.order_date is the natural partition key.

Declarative partitioning

PostgreSQL (10+) supports declarative partitioning: you define a partitioned table with a partition key, then create partitions for ranges/values of that key. Queries and writes go to the parent; PostgreSQL routes each row to the right partition automatically.

-- The parent: partitioned by order_date
CREATE TABLE orders_part (
    order_id    bigint GENERATED ALWAYS AS IDENTITY,
    customer_id integer NOT NULL,
    order_date  date NOT NULL,
    status      text NOT NULL,
    PRIMARY KEY (order_id, order_date)        -- the partition key must be in the PK
) PARTITION BY RANGE (order_date);

Range partitioning (the common case)

Range partitioning assigns rows to partitions by ranges of the key — ideal for dates. Create one partition per month (or year):

CREATE TABLE orders_2024_01 PARTITION OF orders_part
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders_part
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ...one per month...

(Note the half-open ranges — FROM inclusive, TO exclusive — the same [start, end) pattern from Chapter 8 that avoids boundary gaps/overlaps.) An order inserted with order_date = '2024-02-14' lands automatically in orders_2024_02. You still write INSERT INTO orders_part ... and SELECT ... FROM orders_part — the partitioning is transparent.

List and hash partitioning

• List partitioning — assign rows by discrete values of the key (e.g., region, country): sql CREATE TABLE orders_us PARTITION OF orders_part FOR VALUES IN ('US'); CREATE TABLE orders_eu PARTITION OF orders_part FOR VALUES IN ('DE','FR','ES');
• Hash partitioning — distribute rows evenly across N partitions by a hash of the key, when there's no natural range/list but you want to spread load: sql CREATE TABLE orders_h0 PARTITION OF orders_part FOR VALUES WITH (MODULUS 4, REMAINDER 0); -- ...h1, h2, h3...

Range (by time) is by far the most common; list suits categorical keys; hash suits even distribution.

Partition pruning: the payoff

The key performance benefit is partition pruning: when a query filters on the partition key, PostgreSQL skips the partitions that can't match and scans only the relevant ones. "Orders in February 2024":

SELECT * FROM orders_part WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01';
-- EXPLAIN shows it scans ONLY orders_2024_02 — the other partitions are pruned away

Instead of touching all of history (even with an index spanning it), the query examines a single month's partition. On a table with years of data, that's a dramatic reduction in work — and it's automatic whenever the partition key is in the WHERE. EXPLAIN (Chapter 24) shows exactly which partitions were pruned.

Common mistake. Partitioning by a key your queries don't filter on. If you partition orders by order_date but most queries filter by customer_id (not date), pruning can't help — every query still scans all partitions. Partition by the key your hot queries actually filter on, or partitioning adds complexity for no benefit.

Why a single table eventually strains

Indexing (Chapter 23) makes finding rows fast even in large tables, so it's worth being precise about what problems indexes don't solve — the problems that motivate partitioning — because partitioning is only worth its complexity when you're actually facing them. A single enormous table has several pains that no index addresses, and they all stem from the table being one monolithic physical object.

The first is maintenance cost. VACUUM and ANALYZE (Chapter 28) must process the whole table, and on a billion-row table that takes a long time and a lot of resources, running repeatedly over data that mostly hasn't changed. The second is index size: even a well-chosen index over a billion rows is itself enormous — deep and large — so while it's still O(log n), the constants grow, the index strains memory, and it takes longer to maintain on every write. The third, and often most painful, is bulk data lifecycle: deleting old data ("drop everything before last year") from a monolithic table is a slow, lock-heavy DELETE of millions of rows that generates enormous WAL and can take hours, even though conceptually you just want to discard a chunk. The fourth is subtler: a query for recent data ("last 30 days") still uses an index that spans all of history, so the index is larger and the query touches a structure covering far more than the query needs.

These are the problems partitioning solves, and noticing that none of them is "finding rows is slow" (which indexes handle) is the key to knowing when partitioning is the right tool. Partitioning splits the one giant physical table into many smaller physical partitions — each a real table under the hood — while you continue to query and write to it as a single logical table. Suddenly VACUUM runs per-partition (and only on the partitions that changed); each partition's index is small and shallow; dropping old data is dropping a whole partition (instant); and a recent-data query touches only the recent partition. The monolith's pains dissolve because the table is no longer a monolith. This is why partitioning is a scale tool, not a general performance tool: it addresses the problems that arise specifically when a single table grows beyond what one physical heap handles gracefully, which is a different problem from "this query is slow" (that's indexing). Reaching for partitioning before you have these scale problems is premature complexity; reaching for it when you do is exactly right.

Partition pruning, in depth

Partition pruning is the performance heart of partitioning, so understanding it deeply — how it works, how to verify it, and the subtle cases — is what makes partitioning actually pay off rather than just adding complexity. Pruning is the optimizer's ability to skip partitions that cannot possibly contain rows matching a query's WHERE clause, scanning only the relevant ones. When you query orders for February 2024 and the table is partitioned by month, the planner knows that only the orders_2024_02 partition can hold February rows, so it scans that one partition and ignores all the others — turning a query that would scan years of data into one that scans a single month.

The crucial requirement, which determines whether pruning helps at all, is that the query must filter on the partition key. Pruning works by reasoning about the partition key's ranges, so it can only eliminate partitions when the query's WHERE constrains that key. A query filtering WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01' prunes beautifully (the planner maps the date range to the February partition). But a query filtering only WHERE customer_id = 4267, with no date constraint, cannot be pruned — customer 4267's orders could be in any month's partition, so the planner must scan them all. This is why partitioning by the wrong key is useless: if you partition by date but your hot queries filter by customer, pruning never engages, and you've added all of partitioning's complexity for none of its benefit. The partition key must match what your hot queries filter on.

You verify pruning with EXPLAIN (Chapter 24): the plan of a well-pruned query shows it scanning only the relevant partition(s), with the others absent from the plan entirely. If you see all partitions in the plan when you expected pruning, either the query isn't filtering on the partition key, or it's filtering in a way the planner can't use for pruning (a function on the key, or a value not known at plan time). PostgreSQL supports both plan-time pruning (when the filter value is a constant known when the query is planned) and run-time pruning (when the value comes from a parameter or a subquery, determined during execution) — the latter is important for parameterized application queries, where the specific date isn't known until the query runs. Modern PostgreSQL handles both well, but verifying via EXPLAIN (or EXPLAIN ANALYZE for run-time pruning, which shows partitions "removed" during execution) is how you confirm pruning is actually happening rather than assuming it.

The combination of pruning and per-partition indexes is what makes partitioning powerful: pruning narrows the query to the relevant partition(s), and then the partition's own (small, shallow) index finds the exact rows within them. These work together — partitioning and indexing are complementary, not alternatives. A query for "customer 4267's orders in February 2024" prunes to the February partition (via the date filter), then uses that partition's customer_id index to find the specific customer's rows — fast on both axes. Understanding that pruning depends entirely on filtering the partition key, verifying it with EXPLAIN, and pairing it with per-partition indexes is what turns partitioning from a complex feature into a genuine scale solution. Get the partition key right (matching your hot queries' filters), and pruning does the rest.

Maintenance: the other big win

Partitioning makes bulk lifecycle operations cheap:

• Dropping old data is instant. "Delete everything before 2023" is a slow, lock-heavy DELETE of millions of rows on a monolithic table. On a partitioned table, it's DROP TABLE orders_2022_* (or DETACH) — a near-instant metadata operation that frees the storage immediately. This alone justifies partitioning for log/event/retention data.
• Attaching/detaching partitions lets you load or archive whole chunks: build next month's partition in advance and ATTACH it; DETACH an old one to archive it without touching the rest.
• VACUUM/ANALYZE per partition — maintenance runs on smaller pieces, and only the partitions that changed need it.
• Smaller indexes per partition — each partition's index is smaller and shallower than one giant index over everything.

ATTACH and DETACH: whole-chunk operations

The ATTACH and DETACH operations deserve emphasis because they're partitioning's superpower for data lifecycle, turning operations that are agonizing on a monolithic table into near-instant metadata changes. The core idea: because each partition is a real, separate table under the hood, you can add or remove whole partitions — entire chunks of data — as fast metadata operations, without touching the rest of the data.

DETACH removes a partition from the partitioned table, leaving it as a standalone table. This is how cheap retention works: to archive last year's data, you DETACH the old partitions, and they become ordinary tables you can back up, move to cheaper storage, or eventually drop — all without a slow DELETE and without locking the live table. Compare the alternatives: on a monolithic table, removing old data is DELETE FROM orders WHERE order_date < '2023-01-01', scanning and removing millions of rows over hours, holding locks, generating enormous WAL. On a partitioned table, it's ALTER TABLE orders_part DETACH PARTITION orders_2022_* — a metadata change completing in moments, freeing the storage immediately. The difference is so dramatic that retention requirements alone often justify partitioning. ATTACH does the reverse: it adds an existing table as a new partition, which is how you load bulk data efficiently — build next month's data in a standalone table (loaded, indexed, validated at your leisure without affecting the live table), then ATTACH it as a partition in one fast operation, making it instantly part of the logical table.

This ATTACH/DETACH capability transforms how you handle the data lifecycle of large tables. Bulk loading becomes "prepare a table, attach it"; archiving becomes "detach the old partition"; even certain migrations become "build the new version as a partition, attach it, detach the old." These are the operations that are nightmares on monolithic tables — slow, lock-heavy, resource-intensive — and they become routine, fast, metadata-level operations on partitioned tables. For tables with significant data lifecycle needs (regular loading of new data, regular retirement of old data), this alone is often the decisive benefit, more than the query pruning. A logging or events or metrics table that ingests constantly and retains on a schedule is the perfect partitioning candidate not just because pruning speeds queries, but because ATTACH/DETACH make the constant load-and-retire cycle cheap. Understanding that partitions are real tables you can attach and detach as whole units is key to appreciating partitioning's full value — it's not only about query performance, but about making the entire lifecycle of large, time-bounded data manageable.

Partitioning in the scale toolkit

To close the conceptual picture, it helps to see partitioning's precise place in the toolkit of techniques for handling data at scale, because "my data is getting big" has a progression of answers and using the right one at the right time is the skill. Each technique addresses a limit the previous one doesn't, and reaching for a heavier one before you've exhausted the lighter is premature complexity.

The progression runs roughly: indexing first — for almost any "this query is slow" problem on a table of any size, the right index is the answer, and it's the lightest tool (Chapter 23). When a single table grows so large that even good indexes strain — the maintenance, index-size, and bulk-lifecycle problems this chapter opened with — partitioning is the next step, splitting that table physically while keeping it on one server. When a single server becomes the limit — its CPU, memory, or write throughput maxed even with partitioned, well-indexed tables — you move to distributing across servers: read replicas for read-scaling, or sharding for write-and-size-scaling (Chapter 35), which is a larger architectural leap. And for analytical workloads specifically, moving heavy queries to a separate data warehouse (Chapter 34) offloads them from the transactional system entirely. Each step up this ladder adds capability and complexity, so you climb only as high as your actual scale requires.

Partitioning sits in the middle of this ladder — more powerful than indexing, less complex than distribution — and it's the right tool for the specific, common problem of one table outgrowing a single heap on a server that's otherwise coping. Recognizing this placement keeps you from two errors: partitioning when an index would suffice (over-engineering — the table isn't actually that large, or the problem is a missing index, not table size), and not partitioning when you should, instead jumping to the much greater complexity of sharding when a partitioned single server would have handled it. The judgment, as throughout Part IV, is to match the tool to the actual, measured problem: index for slow queries, partition for tables too large for one heap, distribute for servers too small for the load. Most applications never need to climb past indexing and the occasional partitioned table — a well-indexed, selectively-partitioned single PostgreSQL server handles far more than people assume (theme #4 again). Partitioning is the tool that extends that single-server reach to genuinely large tables, postponing or eliminating the need for the distributed-systems complexity that lies further up the ladder. Knowing where it sits, and climbing the ladder only as far as your scale demands, is the scale judgment that this chapter, and Part IV's performance focus, is ultimately about. The next chapters turn from making the database fast to keeping it correct under the concurrent access that scale brings — transactions, isolation, and the MVCC internals that let many users hit the same data at once without corrupting it. Performance and correctness are the two halves of Part IV, and both, in the end, are about a database that holds up under real, demanding use.

Indexes on partitioned tables

Create an index on the partitioned parent and PostgreSQL creates a matching index on every partition (existing and future):

CREATE INDEX ON orders_part (customer_id);   -- propagates to all partitions

So you still index the columns your queries filter/join on (Chapter 23) — partitioning and indexing are complementary: pruning narrows to the right partition(s), then the per-partition index finds the rows within them.

A worked scenario: partitioning Mercado's orders

Let's make partitioning concrete by walking through partitioning Mercado's orders table, because seeing the full decision and design process is more instructive than the syntax alone. The scenario: Mercado has been running for years, orders has grown to hundreds of millions of rows, queries for recent orders have slowed despite good indexes, VACUUM takes hours, and the yearly "archive orders older than five years" job is a multi-hour, lock-heavy DELETE that strains the system. These are exactly the monolithic-table pains partitioning addresses — so it's a genuine candidate.

The first and most important decision is the partition key, and the analysis confirms order_date is right: the slow queries filter by date range (recent orders, monthly reports), and the retention policy is by date (archive old orders), so partitioning by order_date makes both pruning and retention work. (If the hot queries had instead filtered mostly by customer_id, date partitioning would be wrong — but they don't, so date it is.) The second decision is granularity: monthly partitions are a good balance — each month's partition is substantial (millions of rows, worth pruning to) but the count is manageable (a few dozen for several years), avoiding both the too-few-huge-partitions and too-many-tiny-partitions extremes. Daily partitions would give thousands of tiny partitions (excessive overhead); yearly would give partitions too large to prune usefully. Monthly fits.

The design then follows: a parent table PARTITION BY RANGE (order_date), with the primary key including order_date (the partition-key-in-PK rule), monthly partitions with half-open ranges, an index on the parent for the non-date filter columns (like customer_id, propagating to all partitions), and pg_partman configured to auto-create future months and auto-detach months older than the retention window. Migrating the existing monolithic table to this partitioned design is itself a careful operation (Chapter 22's migration discipline applies — typically creating the partitioned table alongside, copying data in batches, then switching over, all without downtime).

The payoff is dramatic and worth tallying: recent-order queries prune to one or a few monthly partitions instead of scanning years (fast); VACUUM runs per-month on only the months that changed (the old months are static and need no vacuuming); the yearly archive becomes DETACH of old partitions — a near-instant metadata operation freeing storage immediately, replacing the multi-hour DELETE; and each partition's index is small and shallow. Every monolithic pain from the scenario's opening is resolved. This is partitioning doing exactly what it's for: taking a table that had outgrown a single heap and restoring fast queries, cheap maintenance, and instant retention by splitting it along the key that matches how it's actually used. The scenario shows the full arc — recognize the monolithic pains, confirm the partition key matches the access pattern, choose a sensible granularity, design the parent and partitions with automation, and reap the pruning-and-maintenance benefits — which is the complete partitioning decision in practice.

Partitioning and the data warehouse

Partitioning has a special relationship with analytical workloads and data warehousing (Chapter 34), worth noting because it's where partitioning is most ubiquitous. Data warehouses store enormous fact tables — every sale, every event, every measurement, often billions of rows accumulated over years — and these tables are almost universally partitioned, typically by time. The reasons are exactly the ones this chapter covered, amplified by the warehouse's scale and access patterns: analytical queries are overwhelmingly time-bounded ("sales last quarter," "events this year"), so date partitioning prunes massively; warehouses load data in time-ordered batches (last night's data into a new partition), which partitioning makes clean (ATTACH the new batch); and warehouses retain data on a schedule (keep seven years, drop older), which partition DETACH makes instant.

In the warehouse context, partitioning combines with the columnar storage and star-schema denormalization of Chapter 34 to make queries over billions of rows feasible. A fact table partitioned by date, with each partition further optimized for analytical scanning, lets a query for "revenue by category last month" prune to one partition and scan it efficiently — turning a query that would be hopeless against a monolithic billion-row table into a fast one. This is why partitioning, while useful in transactional databases (OLTP) for specific large tables, is standard practice in analytical databases (OLAP): the warehouse's scale and time-bounded access patterns make partitioning's benefits nearly always applicable. When you reach Chapter 34's data warehousing, you'll see partitioned fact tables everywhere, and you'll understand why — it's this chapter's pruning and retention benefits, applied at the scale where they matter most.

The connection also reinforces the chapter's core judgment about when to partition. In OLTP (Mercado as a transactional store), you partition the specific large, time-bounded tables that have outgrown a heap — a deliberate exception for tables at scale, while most tables stay unpartitioned. In OLAP (a warehouse), you partition fact tables as a matter of course, because they're large and time-bounded by their nature. The same tool, applied selectively in one world and routinely in the other, because the workloads differ — exactly the OLTP/OLAP distinction from Chapter 20. Recognizing which world you're in tells you how readily to reach for partitioning, just as it told you how readily to denormalize. Partitioning is a scale tool; warehouses live at scale, so they use it pervasively; transactional systems use it for their few tables that reach that scale.

When to partition — and when not

Partitioning adds real complexity (more objects, the partition-key-in-PK constraint, partition management). It's worth it when:

• The table is very large (typically 100M+ rows, or growing without bound) — small/medium tables don't need it (a good index suffices).
• Queries and maintenance are naturally bounded by the partition key (especially time ranges) — so pruning and partition-drops pay off.
• You need cheap retention (drop old partitions) or bulk load/archive of whole chunks.

It's not worth it for modest tables, or when no single key matches your query/maintenance patterns. Don't partition speculatively — it's a tool for genuine scale, applied when an index alone stops being enough.

Common mistake. Creating too many tiny partitions (e.g., one per day for ten years = thousands). Excessive partitions add planning overhead and management burden. Choose a granularity (often monthly) that keeps each partition substantial and the count manageable.

Partition management and automation

A practical reality of range partitioning by time is that new partitions must keep being created as time advances — next month needs its partition before orders for next month arrive, or the inserts fail (or fall into a default partition). Managing this is part of running a partitioned table, and understanding the options keeps partitioning from becoming an operational burden.

The naive approach is to create partitions manually ahead of time — but relying on a human to remember to create next month's partition before the month begins is fragile (the day someone forgets, inserts break). The robust approaches automate it. You can write a scheduled job (a cron task or pg_cron) that creates upcoming partitions in advance — running monthly to ensure the next few months' partitions always exist. Better, the pg_partman extension automates partition management entirely: it creates future partitions on a schedule, optionally drops or detaches old ones per a retention policy, and handles the bookkeeping, turning "manage partitions forever" into "configure pg_partman once." For serious time-partitioned tables, pg_partman (or a similar tool) is the standard answer, because manual partition management doesn't scale and is error-prone.

A related safety mechanism is the default partition — a catch-all partition that receives any row not matching a defined partition's range. With a default partition, an insert for a date with no specific partition lands in the default rather than failing, which prevents the "forgot to create the partition, now inserts break" outage. But the default partition is a safety net, not a strategy: rows in it aren't pruned efficiently (the planner can't reason about its contents), so it should stay small — a buffer for the occasional out-of-range row, regularly drained into proper partitions, not a dumping ground. The combination of automated partition creation (so partitions exist before they're needed) plus a default partition (so an unexpected row doesn't break inserts) is the robust operational setup for time-partitioned tables.

The broader lesson is that partitioning isn't a one-time CREATE TABLE decision but an ongoing operational concern — partitions must be created, old ones retired, the scheme maintained. This operational dimension is part of partitioning's "complexity cost" that you weigh against its benefits: it's not just more objects to design, but an ongoing management responsibility (automated, ideally). For a genuinely large, time-bounded table, the benefits (pruning, cheap retention, per-partition maintenance) far outweigh this cost, especially with pg_partman handling the automation. But it is a cost, and accounting for it honestly — "if I partition this, I'm signing up to manage partitions, ideally via automation" — is part of the judgment about whether partitioning is worth it for a given table. The feature is powerful, but it's not free in operational terms, and knowing how to automate the management is what keeps it from becoming a maintenance headache.

Partitioning in context: indexes, constraints, and scaling

Partitioning interacts with the rest of the database in ways worth understanding, because a partitioned table isn't quite an ordinary table and the differences occasionally matter. The constraint that the partition key must be part of the primary key (and any unique constraint) is the most common surprise: because uniqueness is enforced per-partition, a unique constraint must include the partition key so the database can guarantee global uniqueness by guaranteeing it within each partition. This means a partitioned orders table's primary key becomes (order_id, order_date) rather than just order_id — a real design consequence to plan for, and occasionally a reason a table is awkward to partition (if its natural key doesn't include a good partition key).

Indexes on partitioned tables work cleanly in modern PostgreSQL: create an index on the partitioned parent, and PostgreSQL automatically creates a matching index on every partition, including future ones created later. So you index a partitioned table much as you would any table — index the columns your queries filter and join on — and the indexes propagate to all partitions. Foreign keys are supported both to and (in recent versions) from partitioned tables, though there are version-specific nuances worth checking. The general principle is that a partitioned table behaves like a normal table for querying, indexing, and most operations — the partitioning is largely transparent — with the partition-key-in-key constraint being the main structural difference to design around.

It's also worth situating partitioning among the other scaling approaches, because "my table is too big" has several possible answers and partitioning is one. Partitioning scales a table within a single database server — it splits the table physically but everything still lives on one machine. When a single server's capacity (CPU, memory, disk, or write throughput) is itself the limit, the next step is distributing across multiple machines — read replicas for read scaling, or sharding for write/size scaling (Chapter 35) — which is a bigger architectural leap. Partitioning and sharding are sometimes confused: partitioning is splitting a table across the storage of one server (still one database); sharding is splitting data across multiple servers (multiple databases). The progression of scale solutions runs roughly: index → partition → read replicas → shard, from least to most architectural complexity, each addressing a limit the previous one doesn't. Partitioning sits in the middle — more than an index, less than distribution — and it's the right tool for the specific problem of a single table too large for one heap, on a server that's otherwise coping. Knowing where it sits in that progression helps you reach for it at the right time: after indexing is exhausted, before the leap to distribution.

A field guide to partitioning mistakes

Partitioning, applied wrongly, adds complexity without benefit or even causes new problems, so a catalogue of its failure modes is worth keeping. The wrong partition key is the cardinal error: partitioning by a key your hot queries don't filter on, so pruning never engages and every query still scans all partitions. You've added all of partitioning's complexity (more objects, the partition-key-in-PK constraint, partition management) for none of its benefit. The partition key must match what your queries filter on — usually time, sometimes a categorical key, determined by your actual access patterns, not by what seems natural. Too many tiny partitions is the next: partitioning so finely (one per day for a decade = thousands of partitions) that the planning overhead of considering all those partitions, plus the management burden, outweighs the benefit. Choose a granularity (often monthly) that keeps each partition substantial and the count in the dozens-to-low-hundreds, not thousands.

Partitioning too small a table wastes effort: a table of a few million rows doesn't need partitioning — a good index handles it fine, and partitioning just adds complexity. Partitioning is a tool for genuinely large tables (typically 100M+ rows or growing unbounded); applying it to modest tables is premature optimization. Forgetting partition management causes outages: relying on manual partition creation and then forgetting to create next month's partition, so inserts fail. Automate partition creation (pg_partman or a scheduled job) and use a default partition as a safety net. Ignoring the partition-key-in-PK constraint until it bites: discovering mid-design that your natural primary key can't be the key because it doesn't include the partition key. Plan for this — the partitioned table's PK must include the partition key, which shapes the whole design.

The unifying cause of these mistakes is treating partitioning as a general performance tool ("big table → partition it") rather than the specific scale tool it is. Partitioning solves the specific problems of a single table too large for one heap, bounded by a key that matches your queries and retention. When those conditions hold (large, key-bounded, with pruning and retention benefits), partitioning is powerful. When they don't (small table, no matching key, no retention need), it's complexity for nothing. The mistakes all stem from partitioning when the conditions don't hold, or partitioning by the wrong key. The defense is the judgment the chapter keeps returning to: partition only genuinely large, key-bounded tables, by the key your hot queries actually filter on, at a sensible granularity, with automated management. Get those right and partitioning delivers; get any wrong and it's a burden.

Partitioning's place and PostgreSQL's evolution

A brief note on how partitioning arrived in PostgreSQL helps you read older material and understand the current state. Before PostgreSQL 10, partitioning was done through table inheritance plus triggers and check constraints — a manual, error-prone approach where you created child tables inheriting from a parent and wrote triggers to route inserts. It worked but was clumsy and easy to get wrong. PostgreSQL 10 introduced declarative partitioning (the PARTITION BY syntax this chapter teaches), which made partitioning a first-class, built-in feature, and subsequent versions (11, 12, 13, and beyond) steadily improved it — adding default partitions, better pruning (including run-time pruning), foreign-key support, automatic index propagation, and performance improvements for large partition counts. So if you encounter the old inheritance-based approach in legacy systems or older documentation, recognize it as the predecessor; declarative partitioning is the modern way, and it's what you should use.

This evolution matters practically because partitioning's capabilities and ergonomics depend on your PostgreSQL version. The declarative partitioning of recent versions (this book targets 15+) is genuinely pleasant — pruning is automatic and effective, indexes propagate, management tools like pg_partman integrate well. Older versions had more limitations and rougher edges. So "partitioning in PostgreSQL" means something better in 15+ than it did in 10, and far better than the pre-10 inheritance hack. When evaluating whether to partition, you're evaluating the modern declarative feature, which is mature and capable — one more reason to be on a current version (Chapter 2's advice to target 15+).

The steady improvement of partitioning also reflects a broader theme: PostgreSQL continuously absorbs capabilities that let it handle larger scale and more demanding workloads, reducing the cases where you'd need to reach for a different system. Partitioning is part of how a single PostgreSQL instance handles tables that would otherwise force a move to a specialized big-data system — theme #4 (PostgreSQL's power often eliminates other tools) applied to scale. A well-partitioned PostgreSQL table can handle billions of rows that a naive single-heap design couldn't, pushing the point at which you'd need distributed systems (Chapter 35) further out. Knowing partitioning well, then, is part of knowing how far a single PostgreSQL database can be taken — which is often much further than people assume, especially with the mature declarative partitioning of current versions.

Progressive project: partition a growth table

If your domain has a table that grows without bound and is queried/retained by a key (usually time) — orders, events, log entries, readings:

1. Identify the partition key — the key your hot queries filter on and/or you retain by (often a date).
2. Choose a strategy — range (time), list (category), or hash (even spread).
3. Sketch the partitioned design: the parent (PARTITION BY), a few partitions (half-open ranges), and an index on the parent for your filter columns.
4. Describe the maintenance win: how dropping old data becomes a DROP/DETACH instead of a giant DELETE.
5. If nothing in your domain is large enough to warrant it, note that — and that you'd revisit at scale.

Summary

Partitioning splits one logical table into many physical partitions while you query it as one — for tables too large for a single heap even with good indexes. PostgreSQL's declarative partitioning (PARTITION BY RANGE/LIST/HASH) routes rows automatically; range (by date) is the common case (with half-open ranges; the partition key must be in the PK). The headline benefit is partition pruning — queries filtering on the partition key scan only the relevant partition(s), automatically (so partition by the key your hot queries filter on). The other big win is maintenance: dropping old data becomes an instant DROP/DETACH instead of a slow DELETE, and VACUUM/indexes run per smaller partition. Index the parent (it propagates) — partitioning and indexing are complementary. Partition only genuinely large, key-bounded tables; avoid wrong-key partitioning and too-many-tiny-partitions.

You can now: - Explain when partitioning helps beyond what indexes provide. - Create range/list/hash partitioned tables with declarative partitioning. - Explain and rely on partition pruning (and why the partition key must match your queries). - Use partitions for cheap retention (drop/detach) and bulk load/archive. - Index partitioned tables, and judge when partitioning is (and isn't) warranted.

What's next. Chapter 26 — Transaction Management — ACID, BEGIN/COMMIT/ROLLBACK, isolation levels, and PostgreSQL's MVCC: how the database keeps data correct when many operations and users touch it at once. From performance to correctness under concurrency.

Practice in exercises.md, test yourself with the quiz, apply it in the case studies, review the key takeaways, and go deeper with further reading.