List Partitioning in PostgreSQL: What It Is and How It Works

In PostgreSQL, list partitioning is a way to divide a table into smaller pieces based on specific column values. Each partition holds rows matching a list of values, making data management and queries faster for those values.

How It Works

List partitioning in PostgreSQL splits a big table into smaller parts called partitions. Each partition contains rows where a chosen column matches one or more specific values. Think of it like sorting mail into different bins based on the city name written on the envelope.

When you query the main table, PostgreSQL knows which partitions to look at by checking the values you ask for. This means it can skip partitions that don't match, making data retrieval quicker and more efficient.

Example

This example creates a table partitioned by list on a column called region. Each partition holds rows for specific regions.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product TEXT,
  region TEXT
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales FOR VALUES IN ('North');
CREATE TABLE sales_south PARTITION OF sales FOR VALUES IN ('South');

INSERT INTO sales (product, region) VALUES
('Apples', 'North'),
('Oranges', 'South'),
('Bananas', 'North');

SELECT * FROM sales WHERE region = 'North';

Output

 id | product | region 
----+---------+--------
  1 | Apples  | North  
  3 | Bananas | North  

When to Use

Use list partitioning when your data naturally groups by specific, known values. For example, if you have sales data divided by regions, departments, or categories, list partitioning helps organize and speed up queries for those groups.

This is useful in real-world cases like reporting sales by region, managing user data by country, or separating logs by service type.

Key Points

• List partitioning divides a table by specific column values.
• Each partition holds rows matching a list of values.
• It improves query speed by scanning only relevant partitions.
• Ideal for data grouped by categories like regions or types.

Key Takeaways

List partitioning splits a table into parts based on specific column values.

It helps queries run faster by focusing only on relevant partitions.

Use it when data naturally groups by fixed categories like regions or types.

Each partition holds rows matching a list of values defined at creation.