Postgres partitioning doesn’t actually split your data into separate tables; it’s a single logical table with multiple physical storage areas, each managed independently.
Let’s see how this plays out with a common scenario: tracking user activity logs. We want to partition this table by event_date to make querying recent events fast and archiving old events manageable.
-- First, create the parent table with the partition constraint
CREATE TABLE user_activity (
user_id INT,
event_type VARCHAR(50),
event_timestamp TIMESTAMP WITH TIME ZONE,
event_data JSONB
) PARTITION BY RANGE (event_timestamp);
-- Now, create partitions for specific date ranges
CREATE TABLE user_activity_2023_01 PARTITION OF user_activity
FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-02-01 00:00:00+00');
CREATE TABLE user_activity_2023_02 PARTITION OF user_activity
FOR VALUES FROM ('2023-02-01 00:00:00+00') TO ('2023-03-01 00:00:00+00');
CREATE TABLE user_activity_2023_03 PARTITION OF user_activity
FOR VALUES FROM ('2023-03-01 00:00:00+00') TO ('2023-04-01 00:00:00+00');
-- You can still query the parent table, and Postgres will route it
SELECT COUNT(*) FROM user_activity WHERE event_timestamp >= '2023-02-15 00:00:00+00';
-- The query planner sees this and only scans the relevant partition(s)
EXPLAIN SELECT COUNT(*) FROM user_activity WHERE event_timestamp >= '2023-02-15 00:00:00+00';
The magic here is that Postgres’s query planner understands the partitioning scheme. When you query user_activity with a WHERE clause that matches the partitioning key (like event_timestamp), it can prune partitions that don’t contain matching data. This means it only scans the user_activity_2023_02 and user_activity_2023_03 partitions in the example above, drastically improving performance on large tables.
Range Partitioning
This is what we saw above. You define partitions based on a continuous range of values for a column. This is ideal for time-series data (dates, timestamps) or any numeric data where you want to group by intervals.
Use Cases:
- Time-series data (daily, weekly, monthly logs, sensor readings)
- Large ID sequences where you want to manage older data separately.
How it works: You specify PARTITION BY RANGE (column_name). Then, for each partition, you define FOR VALUES FROM (lower_bound) TO (upper_bound). The upper_bound of one partition becomes the lower_bound of the next, creating a contiguous sequence.
List Partitioning
List partitioning allows you to partition data based on discrete, independent values of a column. Think of it as categorizing data into specific buckets.
-- Example: Partitioning by region
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
region VARCHAR(50),
amount DECIMAL(10, 2),
sale_date DATE
) PARTITION BY LIST (region);
-- Create partitions for specific regions
CREATE TABLE sales_north PARTITION OF sales
FOR VALUES IN ('North');
CREATE TABLE sales_south PARTITION OF sales
FOR VALUES IN ('South');
CREATE TABLE sales_east PARTITION OF sales
FOR VALUES IN ('East');
-- A default partition can catch unassigned values
CREATE TABLE sales_other PARTITION OF sales
DEFAULT;
-- Querying a specific region
SELECT SUM(amount) FROM sales WHERE region = 'South';
-- Querying across multiple regions
SELECT SUM(amount) FROM sales WHERE region IN ('North', 'East');
Use Cases:
- Categorical data (e.g., product types, customer segments, geographical regions)
- Any column with a finite, distinct set of values.
How it works: You specify PARTITION BY LIST (column_name). For each partition, you use FOR VALUES IN (value1, value2, ...). If a row’s partitioning column value doesn’t match any FOR VALUES IN clause, it will either error (if no DEFAULT partition exists) or go into the DEFAULT partition.
Hash Partitioning
Hash partitioning distributes data evenly across a fixed number of partitions based on a hash function applied to the partitioning key. This is useful for distributing I/O load when you don’t have a natural range or list to partition by, or when you want to ensure even data distribution.
-- Example: Partitioning by a user ID hash
CREATE TABLE user_profiles (
profile_id SERIAL PRIMARY KEY,
user_id INT,
profile_data JSONB
) PARTITION BY HASH (user_id);
-- Create a fixed number of hash partitions
CREATE TABLE user_profiles_0 PARTITION OF user_profiles
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_profiles_1 PARTITION OF user_profiles
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_profiles_2 PARTITION OF user_profiles
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_profiles_3 PARTITION OF user_profiles
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
-- When inserting, Postgres calculates the hash and routes it
INSERT INTO user_profiles (user_id, profile_data) VALUES (123, '{"bio": "..."}'); -- Goes to partition based on 123 % 4
INSERT INTO user_profiles (user_id, profile_data) VALUES (456, '{"bio": "..."}'); -- Goes to partition based on 456 % 4
-- Queries on the partition key benefit from pruning
SELECT * FROM user_profiles WHERE user_id = 123;
Use Cases:
- Distributing I/O load across many disks when other partitioning strategies aren’t suitable.
- When you need a predictable number of partitions for management.
- Large tables where you want to spread data and index storage.
How it works: You specify PARTITION BY HASH (column_name). You then create partitions using FOR VALUES WITH (MODULUS N, REMAINDER R), where N is the total number of partitions and R is the remainder when the hash of the partitioning key is divided by N. Postgres calculates hash(value) % N to determine which partition a row belongs to.
One of the most counterintuitive aspects of partitioning is how DROP TABLE and DETACH PARTITION work. When you DROP TABLE a parent partitioned table, it doesn’t actually delete all the child partition tables immediately. Instead, it marks the partitions for deletion and cleans them up in the background. DETACH PARTITION, however, severs the link and turns the detached partition into a regular, independent table, which you can then manage, archive, or drop as a single unit. This distinction is crucial for understanding resource management and avoiding accidental data loss or performance degradation.
Once you’ve mastered these partitioning types, you’ll naturally start thinking about how to combine them for even more granular control, leading you to the concept of composite partitioning.