Partition Tables by Range and List

Your PostgreSQL database is slowing down. Queries that used to return in milliseconds now take seconds, VACUUM can't keep up, and your largest table is 500 GB and growing. You've tried adding indexes, but they only help so much. The real problem is that you're storing all your data in one massive table, and every query has to scan through years of history to find the last week's rows. That's where partitioning tables by range and list changes everything. By splitting a huge table into smaller, manageable pieces, you can dramatically improve query performance, simplify data retention, and make maintenance a breeze.

The problem this lesson solves

PostgreSQL stores all rows in a single table unless you explicitly break them apart. When a table grows to millions—or billions—of rows, every sequential scan has to read the whole table, and even indexed lookups have to navigate a bloated B-tree. Maintenance operations like VACUUM, ANALYZE, and REINDEX become slower and more disruptive. Worse, deleting old data becomes a tedious DELETE that generates huge amounts of Write-Ahead Log (WAL) traffic and can lock the table for a long time.

Imagine you're running an e-commerce platform with an orders table that accumulates every order since day one. Your reporting queries filter by order_date, but the planner has to consider all historical rows. Even with an index on order_date, the index becomes enormous, and cache misses increase. The pain hits hardest when you want to archive last year's data: a DELETE FROM orders WHERE order_date < '2024-01-01' can take hours and grind your production database to a halt.

Partitioning solves this by physically dividing the table into smaller subtables, each holding a subset of rows based on a partitioning key. Queries can then skip irrelevant partitions entirely—a technique called partition pruning—making them radically faster. Data deletion becomes as simple as dropping a partition, which is instant and metadata-only. This lesson gives you the exact tools to fix these real-world pain points.

Core concept / mental model

Think of partitioning like a filing cabinet with labeled drawers. Instead of one giant drawer full of unordered papers, you have separate drawers for each year, each month, or each category. When you need last month's receipts, you go straight to that labeled drawer without rifling through everything. PostgreSQL does the same with partitions: each partition is a separate table under the hood, but your SQL queries treat them as one logical table.

There are two primary partitioning strategies you'll use in PostgreSQL:

• Range partitioning (BY RANGE): Rows are assigned to partitions based on a continuous range of values, like dates, timestamps, or numeric IDs. For example, sales_2024_q1, sales_2024_q2, and so on.
• List partitioning (BY LIST): Rows are assigned to partitions based on an explicit list of values, like region names, status codes, or category IDs. For example, orders_east, orders_west, orders_central.

Think of range as "time buckets" and list as "category buckets." Range is perfect for time-series data; list is perfect for categorical data with a finite set of values.

In PostgreSQL's declarative partitioning (introduced in version 10 and significantly improved since), you define a parent table with a PARTITION BY clause, and then create child tables that attach to it. The parent table itself contains no data—it's just a template. Queries automatically route to the correct child partition based on the partitioning key, so your application code doesn't change at all.

How it works step by step

Partitioning in PostgreSQL is declarative: you define the schema once, and the database manages the rest. Here's the logical flow:

1. Create the parent table with a PARTITION BY clause specifying the partitioning method (RANGE or LIST) and the key column(s). No data is stored in this table.
2. Create one or more child tables that inherit the structure of the parent. Each child defines a boundary (for range) or a set of values (for list) that it accepts. The child tables can have their own indexes, constraints, and storage parameters.
3. Attach the child tables to the parent using the PARTITION OF clause. PostgreSQL automatically ensures that each row falls into exactly one partition, and it enforces the partition boundaries.
4. Optionally create indexes on the parent table with CREATE INDEX ON parent (column). PostgreSQL automatically creates matching indexes on each child partition.
5. Insert data as you normally would. The INSERT statement is routed to the correct partition automatically based on the partition key value.
6. Query data as usual. The planner examines the WHERE clause and prunes partitions that cannot contain matching rows, reducing the amount of data scanned.

For range partitioning, you define the boundaries with FROM and TO clauses. The lower bound is inclusive, and the upper bound is exclusive. For example, a partition covering January 2024 would be FROM ('2024-01-01') TO ('2024-02-01'). A row with order_date = '2024-01-15' fits, but a row with order_date = '2024-02-01' does not.

For list partitioning, you specify the values that belong in each partition with IN ('value1', 'value2', ...). Every possible value of the partition key must match exactly one partition; otherwise, the insert fails with a check constraint violation.

A crucial detail: every row must map to exactly one partition. If you define partitions that don't cover all possible values, inserts outside those ranges will error. You can create a default partition (using PARTITION OF parent DEFAULT) to catch any rows that don't fit other partitions, but use it sparingly because it bypasses the pruning benefits.

Hands-on walkthrough

Let's put partitioning into practice. We'll build a time-series table for a logging system and a category-based table for an inventory system.

Range partitioning: daily logs

Step 1: Create the parent table

CREATE TABLE logs (
    id BIGSERIAL,
    log_date DATE NOT NULL,
    level TEXT NOT NULL,
    message TEXT
) PARTITION BY RANGE (log_date);

Step 2: Create monthly partitions

CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE logs_2024_03 PARTITION OF logs
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

Step 3: Create an index on the parent (auto-propagated)

CREATE INDEX idx_logs_date ON logs (log_date);

Step 4: Insert data — automatic routing

INSERT INTO logs (log_date, level, message) VALUES
('2024-01-15', 'INFO', 'Application started'),
('2024-02-20', 'ERROR', 'Database connection failed'),
('2024-03-05', 'WARN', 'Disk space low');

Step 5: Query with partition pruning

SELECT * FROM logs WHERE log_date >= '2024-02-01' AND log_date < '2024-03-01';

The planner will scan only logs_2024_02. You can verify with EXPLAIN:

EXPLAIN SELECT * FROM logs WHERE log_date >= '2024-02-01' AND log_date < '2024-03-01';

Expected output (simplified):

Seq Scan on logs_2024_02
  Filter: ((log_date >= '2024-02-01'::date) AND (log_date < '2024-03-01'::date))

List partitioning: inventory by region

Step 1: Create the parent table

CREATE TABLE inventory (
    sku TEXT NOT NULL,
    region TEXT NOT NULL,
    quantity INTEGER NOT NULL
) PARTITION BY LIST (region);

Step 2: Create partitions for each region

CREATE TABLE inventory_east PARTITION OF inventory
    FOR VALUES IN ('east');

CREATE TABLE inventory_west PARTITION OF inventory
    FOR VALUES IN ('west');

CREATE TABLE inventory_central PARTITION OF inventory
    FOR VALUES IN ('central');

Step 3: Insert and query

INSERT INTO inventory (sku, region, quantity) VALUES
('SKU-100', 'east', 50),
('SKU-200', 'west', 30),
('SKU-300', 'central', 100);

SELECT * FROM inventory WHERE region = 'west';

Again, only the inventory_west partition is scanned.

Dropping a partition for instant archival

One of the biggest wins is dropping an old partition instead of deleting rows:

DROP TABLE logs_2024_01;

This is a metadata-only operation and takes milliseconds, no matter how many rows it contains. For archiving, you can also DETACH the partition and keep it as a separate table:

ALTER TABLE logs DETACH PARTITION logs_2024_01;

Now logs_2024_01 remains as a standalone table while logs no longer includes it.

Compare options / when to choose what

In most production scenarios, you'll use range for time-series logs or events, and list for partitioning data by region, tenant, or status. If you have a high-volume table with no natural partitioning key, hash partitioning (available since PostgreSQL 11) can spread rows evenly across partitions, but beware that it doesn't help with range queries.

For some workloads, partitioning vs. plain indexes is a common comparison. Indexes help locate rows within a table but don't reduce the table size. Partitioning shrinks the effective index and data size per query, and it also enables fast partition-level operations like DROP or DETACH. If your table is less than a few hundred GB and queries are selective with indexes, you might not need partitioning. But when data grows large or you need rapid archival, partitioning is the right choice.

Troubleshooting & edge cases

"No partition found for row" error

The most common error when inserting into a partitioned table is:

ERROR: no partition of relation "logs" found for row
DETAIL: Partition key of the failing row contains (log_date) = (2024-04-15).

This happens when you try to insert a row whose partition key value doesn't fall into any existing partition. For example, you created partitions for January–March 2024 but try to insert an April row. Fix: create a partition for April, or add a default partition:

CREATE TABLE logs_default PARTITION OF logs DEFAULT;

But be cautious: default partitions break the partitioning pruner for some queries, so only use them for unexpected data.

Unique constraints and primary keys

Problem: You can't create a unique constraint on the parent table unless it includes the partition key.

-- This fails if id is not part of the partition key
ALTER TABLE logs ADD PRIMARY KEY (id);

PostgreSQL will error, telling you that the unique constraint must include all partitioning columns. Fix: Include the partition key in the constraint, e.g., PRIMARY KEY (id, log_date), or enforce uniqueness per partition (which doesn't guarantee global uniqueness). For automatically incremented IDs, you might not need a global unique constraint.

Inefficient queries when partition key is missing

Problem: Querying without a filter on the partition key forces the planner to scan all partitions.

SELECT * FROM logs WHERE level = 'ERROR';

This scans every partition because the planner can't prune any. Fix: Always include the partition key in your WHERE clause when possible. If you must query by non-key columns, consider creating a global index on that column (it will be a partitioned index) or rethink your partitioning strategy.

Default partition performance

Problem: Using a default partition causes the planner to scan it for every query that involves the partition key, because it can't assume which rows are in the default. Fix: Keep the default partition empty or avoid it entirely by ensuring all possible values are covered. Regularly move stray rows to the proper partition.

Not all partitions are automatically created

Problem: Declarative partitioning doesn't auto-create partitions when new data arrives (unlike some other databases). If you insert a date in the future and there's no partition, you get an error. Fix: Automate partition creation with a scheduled job (e.g., pg_cron) or a trigger—many production systems create partitions on a rolling basis.

What you learned & what's next

You've now grasped the core idea of partitioning tables by range and list in PostgreSQL. You understand how to create partitioned tables, define partitions with FOR VALUES FROM/TO and FOR VALUES IN, and how to leverage partition pruning to make queries faster. You also practiced dropping and detaching partitions for instant archival, and you know how to handle common pitfalls like missing partitions and unique constraints.

These skills directly tackle the performance and maintenance challenges that plague large PostgreSQL tables. Now that you have partitioned tables in your toolbox, you're ready to move to the next lesson in this track, where you'll apply these techniques to more advanced schema design and query optimization patterns. Keep practicing, and soon partitioning will become second nature.

Pro tip: To see how much partitioning helps, run EXPLAIN ANALYZE before and after partitioning—the difference in query times and rows scanned will be your best proof.