PostgreSQL Partitioning Basics
Table of Contents
1. Overview
Partitioning divides a large table into smaller physical pieces while presenting them as a single logical table.
Oracle : One table with multiple partitions, It is 1 single table divided internally.
PostgreSQL : One parent table with multiple child tables (partitions), The main table is just a parent that routes data into separate individual tables (children).
RANGE partitioning – Best for time-series (date) data.
LIST partitioning – Best for categorical data (region, status, type).
HASH partitioning – Best for even distribution when no natural range/list exists.
Note: pg_partman is a PostgreSQL extension that automates the creation and maintenance of partitions, especially useful for time-based RANGE partitions. We will not discuss about this here.
| Benefit | Description |
|---|---|
| Query Performance | Partition pruning skips irrelevant partitions |
| Bulk Operations | Drop/detach a partition instead of mass DELETE |
| Maintenance | VACUUM, REINDEX on smaller tables |
| Storage Tiering | Move old partitions to cheaper tablespaces |
| Parallel Scans | Each partition can be scanned in parallel |
2. Range Partitioning
# Best for time-series data.
postgres=# \dt public.*
Did not find any relation named "public.*".
postgres=# CREATE TABLE orders (
id BIGSERIAL,
order_date DATE NOT NULL,
customer_id INT,
amount NUMERIC
) PARTITION BY RANGE (order_date);
CREATE TABLE
postgres=#
-- Create partitions
postgres=# CREATE TABLE orders_2023_q1 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE orders_2023_q2 PARTITION OF orders
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE orders_2023_q3 PARTITION OF orders
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE orders_2023_q4 PARTITION OF orders
FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=#
postgres=# \dt public.*
List of relations
Schema | Name | Type | Owner
--------+----------------+-------------------+----------
public | orders | partitioned table | postgres
public | orders_2023_q1 | table | postgres
public | orders_2023_q2 | table | postgres
public | orders_2023_q3 | table | postgres
public | orders_2023_q4 | table | postgres
(5 rows)
postgres=#
postgres=# \d+ orders
Partitioned table "public.orders"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+------------------------------------+---------+-------------+--------------+-------------
id | bigint | | not null | nextval('orders_id_seq'::regclass) | plain | | |
order_date | date | | not null | | plain | | |
customer_id | integer | | | | plain | | |
amount | numeric | | | | main | | |
Partition key: RANGE (order_date)
Partitions: orders_2023_q1 FOR VALUES FROM ('2023-01-01') TO ('2023-04-01'),
orders_2023_q2 FOR VALUES FROM ('2023-04-01') TO ('2023-07-01'),
orders_2023_q3 FOR VALUES FROM ('2023-07-01') TO ('2023-10-01'),
orders_2023_q4 FOR VALUES FROM ('2023-10-01') TO ('2024-01-01')
postgres=#
3. List Partitioning
# Best for categorical data (region, status, type).
postgres=# CREATE TABLE customers (
id SERIAL,
name TEXT,
region TEXT NOT NULL
) PARTITION BY LIST (region);
CREATE TABLE customers_us PARTITION OF customers
FOR VALUES IN ('US', 'CA');
CREATE TABLE customers_eu PARTITION OF customers
FOR VALUES IN ('UK', 'DE', 'FR');
CREATE TABLE customers_apac PARTITION OF customers
FOR VALUES IN ('JP', 'AU', 'IN');
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=#
postgres=# \dt public.*
List of relations
Schema | Name | Type | Owner
--------+----------------+-------------------+----------
public | customers | partitioned table | postgres
public | customers_apac | table | postgres
public | customers_eu | table | postgres
public | customers_us | table | postgres
public | orders | partitioned table | postgres
public | orders_2023_q1 | table | postgres
public | orders_2023_q2 | table | postgres
public | orders_2023_q3 | table | postgres
public | orders_2023_q4 | table | postgres
(9 rows)
postgres=#
postgres=# \d+ customers
Partitioned table "public.customers"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('customers_id_seq'::regclass) | plain | | |
name | text | | | | extended | | |
region | text | | not null | | extended | | |
Partition key: LIST (region)
Partitions: customers_apac FOR VALUES IN ('JP', 'AU', 'IN'),
customers_eu FOR VALUES IN ('UK', 'DE', 'FR'),
customers_us FOR VALUES IN ('US', 'CA')
postgres=#
4. Hash Partitioning
# Best for even distribution when no natural range/list exists
postgres=# CREATE TABLE sessions (
id UUID NOT NULL,
user_id INT,
data JSONB
) PARTITION BY HASH (id);
CREATE TABLE sessions_p0 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=#
postgres=# \dt public.*
List of relations
Schema | Name | Type | Owner
--------+----------------+-------------------+----------
public | customers | partitioned table | postgres
public | customers_apac | table | postgres
public | customers_eu | table | postgres
public | customers_us | table | postgres
public | orders | partitioned table | postgres
public | orders_2023_q1 | table | postgres
public | orders_2023_q2 | table | postgres
public | orders_2023_q3 | table | postgres
public | orders_2023_q4 | table | postgres
public | sessions | partitioned table | postgres
public | sessions_p0 | table | postgres
public | sessions_p1 | table | postgres
public | sessions_p2 | table | postgres
public | sessions_p3 | table | postgres
(14 rows)
postgres=#
postgres=# \d+ sessions
Partitioned table "public.sessions"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | uuid | | not null | | plain | | |
user_id | integer | | | | plain | | |
data | jsonb | | | | extended | | |
Partition key: HASH (id)
Partitions: sessions_p0 FOR VALUES WITH (modulus 4, remainder 0),
sessions_p1 FOR VALUES WITH (modulus 4, remainder 1),
sessions_p2 FOR VALUES WITH (modulus 4, remainder 2),
sessions_p3 FOR VALUES WITH (modulus 4, remainder 3)
postgres=#
5. Check the Partition
postgres=# SELECT
c.relname AS table_name,
CASE pt.partstrat
WHEN 'r' THEN 'RANGE'
WHEN 'l' THEN 'LIST'
WHEN 'h' THEN 'HASH'
END AS partition_strategy,
pg_get_partkeydef(pt.partrelid) AS partition_key
FROM pg_partitioned_table pt
JOIN pg_class c ON pt.partrelid = c.oid
WHERE c.relname in ('orders','customers','sessions');
table_name | partition_strategy | partition_key
------------+--------------------+--------------------
orders | RANGE | RANGE (order_date)
customers | LIST | LIST (region)
sessions | HASH | HASH (id)
(3 rows)
postgres=#
postgres=# \d+ orders
postgres=# \d+ customers
postgres=# \d+ sessions
6. Partition Pruning
Partition pruning allows PostgreSQL to identify the relevant partition and scan only that partition instead of scanning the entire partitioned table.
-- Create partitioned table
postgres=# CREATE TABLE sugi (
id SERIAL,
customer_id INT,
created_at DATE NOT NULL,
amount NUMERIC,
region TEXT
) PARTITION BY RANGE (created_at);
CREATE TABLE
postgres=#
-- Create partitions
postgres=# CREATE TABLE sugi_2023 PARTITION OF sugi
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sugi_2024 PARTITION OF sugi
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE sugi_2025 PARTITION OF sugi
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE sugi_2026 PARTITION OF sugi
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=#
-- Create indexes
postgres=# CREATE INDEX ON sugi (created_at);
CREATE INDEX
postgres=# CREATE INDEX ON sugi (customer_id);
CREATE INDEX
postgres=#
-- Insert sample data
postgres=# INSERT INTO sugi (customer_id, created_at, amount, region)
SELECT
(random() * 1000)::INT,
DATE '2023-01-01' + (random() * 365)::INT,
(random() * 5000)::NUMERIC(10,2),
(ARRAY['US','EU','APAC'])[ceil(random()*3)::INT]
FROM generate_series(1, 100000);
INSERT INTO sugi (customer_id, created_at, amount, region)
SELECT
(random() * 1000)::INT,
DATE '2024-01-01' + (random() * 365)::INT,
(random() * 5000)::NUMERIC(10,2),
(ARRAY['US','EU','APAC'])[ceil(random()*3)::INT]
FROM generate_series(1, 100000);
INSERT INTO sugi (customer_id, created_at, amount, region)
SELECT
(random() * 1000)::INT,
DATE '2025-01-01' + (random() * 365)::INT,
(random() * 5000)::NUMERIC(10,2),
(ARRAY['US','EU','APAC'])[ceil(random()*3)::INT]
FROM generate_series(1, 100000);
INSERT 0 100000
INSERT 0 100000
INSERT 0 100000
postgres=#
postgres=#
-- ANALYZE
postgres=# ANALYZE sugi;
ANALYZE
postgres=#
— WITH Partition Pruning
postgres=# SHOW enable_partition_pruning;
enable_partition_pruning
--------------------------
on <--------------
(1 row)
postgres=#
postgres=# SET enable_partition_pruning = on;
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM sugi WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on sugi_2025 sugi (cost=0.00..2133.78 rows=99852 width=21) (actual time=0.021..27.584 rows=99852 loops=1)
Filter: ((created_at >= '2025-01-01'::date) AND (created_at < '2026-01-01'::date))
Buffers: shared hit=636
Planning:
Buffers: shared hit=6
Planning Time: 0.443 ms
Execution Time: 37.519 ms
(7 rows)
postgres=#
— WITHOUT Partition Pruning: Now PostgreSQL may scan all partitions postgres
postgres=# SET enable_partition_pruning = off;
SET
postgres=#
postgres=# SHOW enable_partition_pruning;
enable_partition_pruning
--------------------------
off <------------------
(1 row)
postgres=#
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM sugi WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..2652.90 rows=99855 width=21) (actual time=0.032..44.366 rows=99852 loops=1)
Buffers: shared hit=641
-> Index Scan using sugi_2023_created_at_idx on sugi_2023 sugi_1 (cost=0.29..8.31 rows=1 width=21) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((created_at >= '2025-01-01'::date) AND (created_at < '2026-01-01'::date)) Buffers: shared hit=2
-> Index Scan using sugi_2024_created_at_idx on sugi_2024 sugi_2 (cost=0.29..8.31 rows=1 width=21) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((created_at >= '2025-01-01'::date) AND (created_at < '2026-01-01'::date)) Buffers: shared hit=2
-> Seq Scan on sugi_2025 sugi_3 (cost=0.00..2133.78 rows=99852 width=21) (actual time=0.016..26.868 rows=99852 loops=1)
Filter: ((created_at >= '2025-01-01'::date) AND (created_at < '2026-01-01'::date)) Buffers: shared hit=636
-> Seq Scan on sugi_2026 sugi_4 (cost=0.00..3.22 rows=1 width=21) (actual time=0.042..0.042 rows=0 loops=1)
Filter: ((created_at >= '2025-01-01'::date) AND (created_at < '2026-01-01'::date))
Rows Removed by Filter: 148
Buffers: shared hit=1
Planning:
Buffers: shared hit=59 dirtied=1
Planning Time: 1.217 ms
Execution Time: 54.167 ms
(19 rows)
postgres=#
7. Attaching & Detaching Partitions
postgres=# \d+ sugi
Partitioned table "public.sugi"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('sugi_id_seq'::regclass) | plain | | |
customer_id | integer | | | | plain | | |
created_at | date | | not null | | plain | | |
amount | numeric | | | | main | | |
region | text | | | | extended | | |
Partition key: RANGE (created_at)
Indexes:
"sugi_created_at_idx" btree (created_at)
"sugi_customer_id_idx" btree (customer_id)
Partitions: sugi_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
sugi_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'),
sugi_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'),
sugi_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
postgres=#
-- DETACH PARTITION sugi_2025, The data in sugi_2025 will not deleted.
postgres=# ALTER TABLE sugi DETACH PARTITION sugi_2025;
ALTER TABLE
postgres=# \d+ sugi
Partitioned table "public.sugi"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('sugi_id_seq'::regclass) | plain | | |
customer_id | integer | | | | plain | | |
created_at | date | | not null | | plain | | |
amount | numeric | | | | main | | |
region | text | | | | extended | | |
Partition key: RANGE (created_at)
Indexes:
"sugi_created_at_idx" btree (created_at)
"sugi_customer_id_idx" btree (customer_id)
Partitions: sugi_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
sugi_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'),
sugi_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
postgres=#
postgres=# \dt public.sugi_2025
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | sugi_2025 | table | postgres
(1 row)
postgres=#
postgres=# SELECT count(*) FROM sugi_2025;
count
-------
99852
(1 row)
postgres=#
The data in sugi_2025 will not deleted.
-- ATTACH PARTITION
postgres=# ALTER TABLE sugi ATTACH PARTITION sugi_2025;
ERROR: syntax error at or near ";"
LINE 1: ALTER TABLE sugi ATTACH PARTITION sugi_2025;
^
postgres=# ALTER TABLE sugi ATTACH PARTITION sugi_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
ALTER TABLE
postgres=#
postgres=# \d+ sugi
Partitioned table "public.sugi"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('sugi_id_seq'::regclass) | plain | | |
customer_id | integer | | | | plain | | |
created_at | date | | not null | | plain | | |
amount | numeric | | | | main | | |
region | text | | | | extended | | |
Partition key: RANGE (created_at)
Indexes:
"sugi_created_at_idx" btree (created_at)
"sugi_customer_id_idx" btree (customer_id)
Partitions: sugi_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
sugi_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'),
sugi_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'),
sugi_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
postgres=#
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala