List Partitioning by Category

Picture this: you're running an online store, and your orders table has grown to 50 million rows. Every query that filters by status or region seems to take forever, and VACUUM is constantly fighting with your nightly reports. You know indexes help, but they're not enough. What if you could tell PostgreSQL to physically split your table into smaller, more manageable pieces, each holding one category of data? That's exactly what list partitioning does — and it's one of the most powerful techniques you can add to your PostgreSQL toolkit. In this lesson, you'll learn how to use list partitioning for categories, from the core concept to hands-on implementation, so you can tame even the most bloated tables.

The problem this lesson solves

As your application grows, tables with hundreds of millions of rows become slow for several reasons:

• Full table scans: Even with indexes, PostgreSQL may choose to scan the whole table for highly selective queries, especially if the planner thinks the index won't help.
• Bloated indexes: Large indexes take up disk space and memory, and they degrade write performance.
• Long vacuum cycles: VACUUM has to process every page of a large table, making autovacuum less efficient.
• Maintenance nightmares: Dropping old data (e.g., old order categories) means running DELETE that can lock the table and generate huge WAL logs.

List partitioning solves these by dividing a table into smaller, category-specific partitions. Queries that filter on the partition key can skip entire partitions, making them faster. Maintenance becomes trivial: drop one partition instead of deleting millions of rows. Autovacuum can run per-partition, so it stays quicker.

But partitioning isn't just about performance — it's about data management. You can independently back up, restore, or archive partitions, and you can add new partitions as new categories appear without touching the whole table.

Core concept / mental model

Think of partitioning as compartmentalization. Imagine a giant warehouse with no shelves — finding a small box in that chaos is slow. Now imagine the same warehouse divided into labeled rooms: one for electronics, one for books, one for clothing. To find a book, you walk straight to the books room. That's list partitioning.

List partitioning assigns each row to a partition based on a column value that must match one of a fixed set of allowed values. For example, you can partition an orders table by status with partitions for 'pending', 'shipped', 'delivered', and 'cancelled'.

In PostgreSQL, a partitioned table is a parent table that doesn't store data itself. Instead, it defines the partitioning scheme and acts as a routing layer. Child tables (the partitions) store the actual rows. When you INSERT into the parent, PostgreSQL automatically routes the row to the correct partition based on the partition key.

Key terms:

• Partition key: The column(s) used to decide which partition a row goes to.
• Partition bounds: The allowed values for each partition (e.g., IN ('pending', 'shipped')).
• Parent/child tables: The parent (partitioned table) has no data of its own; children hold the actual rows.

This design keeps your application code unchanged — you still query and insert into the parent table as if it were a normal table. PostgreSQL handles the routing behind the scenes.

How it works step by step

Let's break down the process of creating and using a list-partitioned table:

1. Define the parent table using PARTITION BY LIST (column).
2. Create individual partitions with PARTITION OF and FOR VALUES IN (...), specifying the exact value(s) each partition holds.
3. Insert data into the parent — PostgreSQL automatically routes each row to the matching partition.
4. Query just the parent — the planner knows how to prune partitions when you filter on the partition key.
5. Maintain by adding new partitions as categories grow, or dropping/archiving old ones.

Partitioning is not the same as indexing

Indexes speed up lookups, but they don't reduce the size of the data structure the query must scan. Partitioning physically separates data, so a query that filters on the partition key can completely skip irrelevant partitions. This is called partition pruning — the planner eliminates partitions that can't contain the answer.

Declaration vs. creation

The PARTITION BY LIST clause is just a declaration. You must explicitly create each partition. If you try to insert a row that doesn't match any partition (and no default partition exists), PostgreSQL throws an error.

Hands-on walkthrough

Let's build a real example. Suppose you run a support ticket system, and you want to partition tickets by their category — 'bug', 'feature', 'question', and 'other'.

Step 1: Create the partitioned table

CREATE TABLE tickets (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY LIST (category);

Step 2: Create partitions

CREATE TABLE tickets_bug PARTITION OF tickets
    FOR VALUES IN ('bug');

CREATE TABLE tickets_feature PARTITION OF tickets
    FOR VALUES IN ('feature');

CREATE TABLE tickets_question PARTITION OF tickets
    FOR VALUES IN ('question');

CREATE TABLE tickets_other PARTITION OF tickets
    FOR VALUES IN ('other');

Step 3: Insert data

INSERT INTO tickets (title, category) VALUES
    ('Cannot login', 'bug'),
    ('Add export button', 'feature'),
    ('How to reset password?', 'question'),
    ('Misc feedback', 'other');

Expected output: none, but you can verify the routing:

SELECT tableoid::regclass AS partition, title, category FROM tickets;

Expected output:

     partition     |       title        | category
-------------------+--------------------+----------
 tickets_bug       | Cannot login       | bug
 tickets_feature   | Add export button  | feature
 tickets_question   | How to reset password? | question
 tickets_other     | Misc feedback      | other

Each row ended up in its respective partition.

Step 4: Query and observe partition pruning

Now query only bugs:

EXPLAIN SELECT * FROM tickets WHERE category = 'bug';

Expected output (simplified):

Seq Scan on tickets_bug  (cost=0.00..22.50 rows=1 width=48)
  Filter: (category = 'bug'::text)

Notice that the planner scanned only tickets_bug — not the whole parent table. That's partition pruning at work.

Step 5: What if you forget a partition?

Try inserting a category that doesn't exist:

INSERT INTO tickets (title, category) VALUES ('Spam', 'spam');

You'll get an error like:

ERROR: no partition of relation "tickets" found for row
DETAIL: Partition key of the failing row contains (category) = (spam).

To handle unexpected values gracefully, create a default partition:

CREATE TABLE tickets_default PARTITION OF tickets DEFAULT;

Now any unmapped category goes there.

Compare options / when to choose what

PostgreSQL offers multiple partitioning strategies. Here's a quick comparison:

When to choose list partitioning over others:

• Choose list partitioning when you have a fixed set of discrete values that you query frequently and that are evenly spread (e.g., status, region, category).
• Choose range partitioning for time-based data where you want to archive or drop old ranges.
• Choose hash partitioning when you need to distribute load but don't care about which partition holds which rows.

Variation: One common alternative is using CHECK constraints and table inheritance (pre-10 style). That's more manual and error-prone. Another approach is keeping a single table and relying on indexes — simpler but less scalable for massive tables.

Troubleshooting & edge cases

Error: "no partition of relation ... found for row"

This happens when you insert a value that doesn't match any partition and you haven't created a default partition. Fix: add a DEFAULT partition or create the specific partition needed.

Incorrect partition pruning

If your queries are not pruning partitions, check: - Are you filtering on the partition key? Pruning only works for queries that reference the partition column in a WHERE clause. If you filter on another column, PostgreSQL may scan all partitions. - Is the query using an immutable or stable function on the partition key? Functions can prevent pruning if the planner can't determine the value at planning time. Use constants or stable expressions.

Unique constraints across all partitions

PostgreSQL enforces uniqueness only within each partition, not across the whole partitioned table. If you have a global unique index (e.g., on email), you must create a unique index on the parent table, but note that it cannot be a plain PRIMARY KEY unless the partition key is part of the primary key. For example:

CREATE UNIQUE INDEX tickets_id_key ON tickets (id, category);

But this only works if id is unique per partition? Actually, for a partitioned table, a unique index must include the partition key columns. So if you want a unique id across all partitions, you need to include category in the unique index — but that allows duplicate id in different categories. To truly enforce global uniqueness, you need a separate constraint in application logic or a different design (like using a sequence with a composite key).

Performance pitfalls

• Too many partitions: PostgreSQL has a limit on partition count (default max 32768). Keep partitions reasonable — a few dozen is fine; thousands can degrade performance.
• Partition key type mismatch: Ensure the partition column type is consistent across partitions, otherwise inserts may fail or route incorrectly.
• Default partition as a bottleneck: If you rely on a default partition, queries that filter on unknown values will scan it, which may be large. Prefer creating dedicated partitions for known categories.

Edge case: Adding a new category after the fact

You can add a new partition at any time:

CREATE TABLE tickets_urgent PARTITION OF tickets FOR VALUES IN ('urgent');

Existing data won't move, but new inserts with 'urgent' will go there. To move old rows, you'd have to do an update or insert-select.

What you learned & what's next

You've learned how to use list partitioning for categories to improve query performance and manage large tables. Specifically, you:

• Understood the core concept and mental model of list partitioning.
• Created a partitioned table with PARTITION BY LIST and multiple partitions.
• Inserted data and saw how PostgreSQL routes rows to the correct partition.
• Used EXPLAIN to observe partition pruning.
• Handled unexpected categories with a default partition.
• Compared list partitioning with range and hash partitioning, and considered when to choose each.

You're now ready to apply this to your own projects. In the next lesson, we'll explore range partitioning for time-series data, where you'll learn how to manage logs and event data by month or day, and how to automatically create and drop partitions.

Remember the key takeaway: list partitioning is your friend for categorical data — it speeds up queries, simplifies maintenance, and keeps your database scalable.