Partitioning in Postgres

Partitioning in PostgreSQL allows large tables to be logically divided into smaller physical partitions, improving query performance and manageability. PostgreSQL natively supports declarative partitioning, eliminating the need for manual table inheritance and triggers.

Horizontal Partitioning

Horizontal partitioning, also called range or list partitioning, distributes rows across multiple partitions based on a key column. PostgreSQL supports three main partitioning strategies:

Range Partitioning

This method divides data into contiguous ranges. It is ideal for time-series data where queries typically focus on specific date intervals.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY RANGE (order_date);
 
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');
 
CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

When executing a query like:

SELECT * FROM sales WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';

PostgreSQL prunes irrelevant partitions, scanning only sales_2023.

List Partitioning

List partitioning categorizes data based on discrete values. This is useful for cases like region-based sharding.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    region TEXT NOT NULL
) PARTITION BY LIST (region);
 
CREATE TABLE users_us PARTITION OF users FOR VALUES IN ('US');
CREATE TABLE users_eu PARTITION OF users FOR VALUES IN ('EU');

Queries using region filters are automatically routed to the correct partition.

Hash Partitioning

Hash partitioning distributes rows based on the hashed value of the partition key. This provides even distribution but does not support range queries efficiently.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL
) PARTITION BY HASH (customer_id);
 
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Vertical Partitioning

Vertical partitioning splits a table by columns, keeping frequently accessed columns separate from rarely used ones.

CREATE TABLE users_core (
    user_id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);
 
CREATE TABLE users_profile (
    user_id INT PRIMARY KEY REFERENCES users_core(user_id),
    bio TEXT,
    profile_picture BYTEA
);

This improves I/O efficiency, since queries that don’t need profile_picture avoid loading unnecessary data.

Managing Partitions

Automatic Partition Creation

PostgreSQL does not automatically create partitions, but a default partition can catch unhandled inserts.

CREATE TABLE sales_default PARTITION OF sales DEFAULT;

Partition Maintenance

Over time, partitions must be dropped or detached to optimize performance. For example, dropping old partitions:

ALTER TABLE sales DETACH PARTITION sales_2023;
DROP TABLE sales_2023;

Query Performance: Partitioning vs. Full Table Scan

Without Partitioning (Full Table Scan)

EXPLAIN ANALYZE
SELECT SUM(amount) FROM sales_no_partition WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';

Seq Scan on sales_no_partition (cost=0.00..45000.00 rows=1000000 width=4) (actual time=200ms)

With Partitioning (Pruned Scan)

EXPLAIN ANALYZE
SELECT SUM(amount) FROM sales WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';

Index Scan using sales_2023_pkey on sales_2023 (cost=0.00..5000.00 rows=50000 width=4) (actual time=15ms)

Edmondo's Vault

Explorer