Database Partitioning Strategies

Improve query performance and manageability of large tables through partitioning. Covers range, list, and hash partitioning, partition pruning, maintenance operations, and the patterns that keep billion-row tables fast and manageable.

Database partitioning is the act of splitting a large database table across multiple physical storage units, enabling scalable performance, efficient maintenance, and improved query response. It matters when a single table grows beyond a single server’s capacity — when read and write throughput, storage size, or query latency begin to degrade. Use partitioning when you have a large table with a clear access pattern over time or by a key, such as time-series data, user-specific records, or regionally distributed data.

When to Partition by Time (Time-Based Partitioning)

Use time-based partitioning when data is primarily accessed or inserted by time ranges — common in logs, metrics, event streams, and transaction histories.

Create a partitioned table by time

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date TIMESTAMP WITH TIME ZONE NOT NULL,
    amount DECIMAL(10,2),
    status TEXT
)
PARTITION BY RANGE (order_date);

Define partitions for monthly intervals

CREATE TABLE orders_2023_01 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-02-01 00:00:00+00');

CREATE TABLE orders_2023_02 PARTITION OF orders
    FOR VALUES FROM ('2023-02-01 00:00:00+00') TO ('2023-03-01 00:00:00+00');

-- Auto-create partitions for the next 12 months
DO $$
DECLARE
    start_date DATE := '2023-03-01';
    end_date DATE;
BEGIN
    FOR i IN 1..12 LOOP
        end_date := start_date + INTERVAL '1 month';
        EXECUTE format(
            'CREATE TABLE orders_%s PARTITION OF orders
             FOR VALUES FROM (%L) TO (%L)',
            to_char(start_date, 'YYYY_MM'),
            start_date,
            end_date
        );
        start_date := end_date;
    END LOOP;
END $$;

Querying across partitions

Queries on order_date automatically push down predicates to individual partitions:

-- Efficiently scans only relevant partitions
EXPLAIN (ANALYZE, BUFFERS)
SELECT SUM(amount) FROM orders
WHERE order_date BETWEEN '2023-05-15' AND '2023-06-15';

Failure modes:

Maintenance: Archiving and dropping old partitions

-- Drop partitions older than 2 years
DO $$
DECLARE
    cutoff_date DATE := CURRENT_DATE - INTERVAL '2 years';
BEGIN
    EXECUTE format(
        'DROP TABLE IF EXISTS orders_%s',
        to_char(cutoff_date, 'YYYY_MM')
    );
    -- Move data to archive table
    EXECUTE format(
        'INSERT INTO orders_archive
         SELECT * FROM orders_%s
         WHERE order_date < %L',
        to_char(cutoff_date, 'YYYY_MM'),
        cutoff_date
    );
END $$;

When to Partition by Key (Key-Based Partitioning)

Use key-based partitioning when access is primarily by a specific column — user ID, tenant ID, region, or product category.

Create a partitioned table by user ID

CREATE TABLE user_events (
    event_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    metadata JSONB
)
PARTITION BY HASH (user_id);

Define partitions by user ID range

CREATE TABLE user_events_0_9999 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 10000, REMAINDER 0);

CREATE TABLE user_events_10000_19999 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 10000, REMAINDER 1);

-- For 10000 users per partition
CREATE TABLE user_events_20000_29999 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 10000, REMAINDER 2);

Querying by user ID

-- Efficiently targets one or more partitions
SELECT * FROM user_events
WHERE user_id = 12345;

Failure modes:

When to Partition by Range (Range-Based Partitioning)

Use range-based partitioning when data is naturally grouped into ranges — by geographic region, price tiers, or integer ID spans.

Create a partitioned table by price range

CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category TEXT,
    created_at TIMESTAMP WITH TIME ZONE
)
PARTITION BY RANGE (price);

Define partitions by price tiers

CREATE TABLE products_0_100 PARTITION OF products
    FOR VALUES FROM (0) TO (100);

CREATE TABLE products_100_500 PARTITION OF products
    FOR VALUES FROM (100) TO (500);

CREATE TABLE products_500_1000 PARTITION OF products
    FOR VALUES FROM (500) TO (1000);

CREATE TABLE products_1000_inf PARTITION OF products
    FOR VALUES FROM (1000) TO (infinity);

Querying across price ranges

-- Targets only relevant partitions
SELECT name, price FROM products
WHERE price BETWEEN 250 AND 750;

Failure modes:

When to Combine Partitioning Strategies (Composite Partitioning)

Use composite partitioning when data has multiple natural grouping dimensions — e.g., time and user ID.

Create a composite partitioned table

CREATE TABLE user_sessions (
    session_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    started_at TIMESTAMP WITH TIME ZONE NOT NULL,
    duration INTERVAL,
    metadata JSONB
)
PARTITION BY RANGE (started_at)
PARTITION BY HASH (user_id);

Define a two-level partitioning scheme

-- First-level: by time (monthly)
CREATE TABLE user_sessions_2023_01 PARTITION OF user_sessions
    FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-02-01 00:00:00+00');

-- Then, sub-partition by user ID within each monthly partition
CREATE TABLE user_sessions_2023_01_0_9999 PARTITION OF user_sessions_2023_01
    FOR VALUES WITH (MODULUS 10000, REMAINDER 0);

CREATE TABLE user_sessions_2023_01_10000_19999 PARTITION OF user_sessions_2023_01
    FOR VALUES WITH (MODULUS 10000, REMAINDER 1);

-- Add more sub-partitions dynamically
CREATE TABLE user_sessions_2023_01_20000_29999 PARTITION OF user_sessions_2023_01
    FOR VALUES WITH (MODULUS 10000, REMAINDER 2);

Querying composite partitions

-- Efficiently scans only relevant time and user partitions
SELECT COUNT(*) FROM user_sessions
WHERE started_at >= '2023-01-15'
  AND user_id BETWEEN 10000 AND 20000;

Failure modes:

Maintenance and Operations

Adding and dropping partitions dynamically

-- Add a new partition for 2024-05
CREATE TABLE user_sessions_2024_05 PARTITION OF user_sessions
    FOR VALUES FROM ('2024-05-01 00:00:00+00') TO ('2024-06-01 00:00:00+00');

-- Add sub-partitions for user IDs 30000 to 39999
CREATE TABLE user_sessions_2024_05_30000_39999 PARTITION OF user_sessions_2024_05
    FOR VALUES WITH (MODULUS 10000, REMAINDER 3);

-- Drop an old partition
DROP TABLE user_sessions_2023_01;

Monitoring partitioned tables

-- List all partitions and their row counts
SELECT
    parent.relname AS table_name,
    child.relname AS partition_name,
    pg_total_relation_size(child.oid) AS size_bytes,
    (SELECT count(*) FROM child) AS row_count
FROM pg_inherits i
JOIN pg_class parent ON i.inhparent = parent.oid
JOIN pg_class child ON i.inhrelid = child.oid
WHERE parent.relname = 'user_sessions'
ORDER BY parent.relname, child.relname;

Handling data skew and load imbalance

CREATE TABLE user_sessions_high_volume_0_999 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 1000, REMAINDER 0);

Failures in partitioning

Best Practices

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.