PostgreSQL Partitioning: Strategies for Large-Scale Data (Time-Series, Sharding)

PostgreSQL offers powerful partitioning capabilities that can dramatically improve performance and manageability when dealing with large-scale data. This post explores key partitioning strategies with a focus on time-series data and sharding techniques.

Why Partition Your PostgreSQL Tables?

Partitioning large tables provides several benefits:

• Improved query performance by scanning only relevant partitions
• Efficient maintenance as you can work with smaller chunks of data
• Better backup and restore operations on individual partitions
• Optimized storage management with different tablespaces for partitions

PostgreSQL Partitioning Methods

PostgreSQL supports three primary partitioning methods:

1. Range Partitioning: Divides data based on a range of values (ideal for dates/timestamps)
2. List Partitioning: Assigns rows to partitions based on discrete values
3. Hash Partitioning: Distributes data evenly across partitions using a hash function

Time-Series Partitioning Strategy

Time-series data is especially well-suited for range partitioning:

CREATE TABLE measurements (
    id SERIAL,
    device_id INTEGER,
    reading_value DECIMAL,
    reading_time TIMESTAMP
) PARTITION BY RANGE (reading_time);

-- Create monthly partitions
CREATE TABLE measurements_y2025m05 PARTITION OF measurements
    FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');

CREATE TABLE measurements_y2025m06 PARTITION OF measurements
    FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

Automating Partition Creation

Use a function to automate the creation of future partitions:

CREATE OR REPLACE FUNCTION create_partition_and_indexes()
RETURNS VOID AS $$
DECLARE
    next_month DATE;
BEGIN
    next_month := date_trunc('month', CURRENT_DATE) + interval '1 month';
    
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS measurements_y%sm%s PARTITION OF measurements
         FOR VALUES FROM (%L) TO (%L)',
        to_char(next_month, 'YYYY'),
        to_char(next_month, 'MM'),
        next_month,
        next_month + interval '1 month'
    );
    
    -- Create indexes on the new partition
    EXECUTE format(
        'CREATE INDEX ON measurements_y%sm%s (device_id, reading_time)',
        to_char(next_month, 'YYYY'),
        to_char(next_month, 'MM')
    );
END;
$$ LANGUAGE plpgsql;

Sharding Strategies for Distributed Data

Sharding distributes data across multiple physical nodes. While PostgreSQL doesn’t have built-in sharding, you can implement it using:

Hash-Based Sharding

Distribute data evenly across partitions based on a hash of a key column:

CREATE TABLE users (
    id INTEGER,
    username VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP
) PARTITION BY HASH (id);

-- Create 4 partitions (shards)
CREATE TABLE users_0 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
    
CREATE TABLE users_1 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
    
CREATE TABLE users_2 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
    
CREATE TABLE users_3 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Combined Strategies: Time + Tenant Sharding

For multi-tenant applications with time-series data:

CREATE TABLE events (
    id SERIAL,
    tenant_id INTEGER,
    event_type VARCHAR(50),
    event_time TIMESTAMP,
    payload JSONB
) PARTITION BY LIST (tenant_id);

-- Create partitions for each tenant
CREATE TABLE events_tenant_1 PARTITION OF events
    FOR VALUES IN (1)
    PARTITION BY RANGE (event_time);
    
CREATE TABLE events_tenant_2 PARTITION OF events
    FOR VALUES IN (2)
    PARTITION BY RANGE (event_time);

-- Create time-based subpartitions for tenant 1
CREATE TABLE events_tenant_1_2025q1 PARTITION OF events_tenant_1
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
    
CREATE TABLE events_tenant_1_2025q2 PARTITION OF events_tenant_1
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

Performance Optimization Tips

1. Use declarative partitioning (available since PostgreSQL 10) instead of inheritance-based partitioning
2. Create appropriate indexes on each partition rather than on the parent table
3. Implement partition pruning by including the partition key in your WHERE clauses
4. Consider partition-wise joins for queries that join partitioned tables
5. Use constraint exclusion to skip irrelevant partitions during query execution

Maintenance Considerations

• Partition Retention: Automatically drop old partitions
• Partition Rotation: Move data between “hot” and “cold” storage
• Monitoring: Track partition sizes and query performance

-- Example retention policy implementation
CREATE OR REPLACE FUNCTION drop_old_partitions()
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    retention_limit DATE := CURRENT_DATE - INTERVAL '1 year';
BEGIN
    FOR partition_name IN 
        SELECT relname 
        FROM pg_class 
        WHERE relname LIKE 'measurements_y%'
        AND relkind = 'r'
    LOOP
        -- Extract date from partition name and compare with retention limit
        IF to_date(substring(partition_name from 14 for 4) || 
                  substring(partition_name from 18 for 2) || '01', 'YYYYMMDD') < retention_limit THEN
            EXECUTE 'DROP TABLE IF EXISTS ' || partition_name;
            RAISE NOTICE 'Dropped partition: %', partition_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Conclusion

PostgreSQL partitioning provides powerful tools for managing large-scale data effectively. By choosing the right partitioning strategy—whether time-based, hash-based, or a combination—you can significantly improve query performance, maintenance operations, and overall database scalability.

For time-series data, range partitioning by date is typically the most effective approach. For distributed systems requiring horizontal scaling, implementing sharding through hash partitioning can distribute the load efficiently across multiple database instances.

The key to success with PostgreSQL partitioning is understanding your data access patterns and designing your partitioning scheme accordingly.