Table Partition Overview PARTITION BY · RANGE · LIST · HASH

Table Partitioning — Introduction

Split a giant logical table into smaller physical pieces. Queries on the partition key skip whole partitions; archival becomes a single DDL drop. Learn the four methods and when each fits.

What table partitioning is

Partitioning splits a logical table into many physical pieces. The application still queries one table — sales, say — but PostgreSQL stores the rows across several smaller child tables organised by a partitioning key. When a query has a WHERE clause on that key, the planner can ignore irrelevant partitions entirely.

The point is twofold: read performance on huge tables (scan only the partition you need) and maintenance (drop a whole partition to archive a year's data, instead of deleting millions of rows one at a time).

One logical table, many physical partitions

Partitioning methods

Method	Splits data by	Best for
RANGE	Contiguous ranges of a column	Time-series data, numeric tiers
LIST	Discrete values (region, status, country code)	Categorical data with finite buckets
HASH	Hash of a column, modulo N	Even distribution when no natural grouping exists
Multilevel	Combination of the above, level by level	Complex distributions (region × month, etc.)

Why bother — concrete benefits

Partition pruning. A query like WHERE sale_date = '2024-06-01' only touches sales_2024. PostgreSQL never even opens the other partitions.
Cheap archival. Drop an entire year by dropping its partition table — a single DDL statement instead of a multi-hour DELETE.
Smaller, hotter indexes. Each partition has its own indexes. The "current month" index stays small and cache-friendly.
Parallel-friendly. Bulk loads can target multiple partitions at once; vacuum, analyse, and backups run partition by partition.

Trade-offs

Complexity — every partition is a real table you have to maintain.
Some constraints (especially across partitions) take more work to enforce.
Indexes on the parent are propagated to each child — but the parent itself stores no rows.
For small tables (a few million rows), the overhead may outweigh the gains. Partition when the data is genuinely large or naturally segmented.

📌 Modern declarative partitioning. Everything on these pages uses PostgreSQL 10+ syntax — PARTITION BY on the parent, PARTITION OF on the children. Older Postgres relied on inheritance + check constraints + triggers, which is much messier. If you see that style in old code, you're looking at a pre-10 partitioning scheme.

Generic syntax

-- Step 1: parent table with partition strategy
CREATE TABLE parent_table (
    col1 datatype, col2 datatype, ...
) PARTITION BY {RANGE | LIST | HASH} (partition_column);

-- Step 2: one child table per partition
CREATE TABLE child_table
    PARTITION OF parent_table
    FOR VALUES ...;

A first taste — RANGE

SQL

CREATE TABLE sales_data (
    sales_id    INT,
    sale_date   DATE,
    amount      NUMERIC
) PARTITION BY RANGE (amount);

CREATE TABLE sales_amount_low
    PARTITION OF sales_data
    FOR VALUES FROM (0)    TO (1001);

CREATE TABLE sales_amount_medium
    PARTITION OF sales_data
    FOR VALUES FROM (1001) TO (2001);

CREATE TABLE sales_amount_high
    PARTITION OF sales_data
    FOR VALUES FROM (2001) TO (10001);

-- Insert into the parent — Postgres routes rows automatically
INSERT INTO sales_data VALUES (1, '2024-01-15', 500);   -- → low
INSERT INTO sales_data VALUES (2, '2024-02-08', 1500);  -- → medium
INSERT INTO sales_data VALUES (3, '2024-03-21', 5500);  -- → high

Inspecting partitions

SQL

-- List partitions of a table
SELECT inhrelid::regclass AS partition_name
FROM   pg_inherits
WHERE  inhparent = 'sales_data'::regclass;

-- Or in psql:
\d+ sales_data

Where to go next

Range partitioning — splitting by contiguous ranges (dates, amounts).
List partitioning — splitting by discrete values (regions, statuses).
Hash partitioning — even distribution via modulus.
Multilevel partitioning — partitions of partitions, mixing methods.