Data Partitioning Strategies

Production engineering guide for data partitioning strategies covering patterns, implementation strategies, and operational best practices.

Partitioning data across storage systems or compute nodes is essential for scaling read and write workloads. This strategy becomes critical when tables exceed 100 GB in size or when queries span multiple nodes. The goal is to ensure that related data stays together, minimizing cross-node communication during processing.

Horizontal Partitioning by Range

Range-based partitioning splits data based on a column's value range. For example, a table storing user activity logs can be partitioned by date ranges.

To implement range partitioning in PostgreSQL, use CREATE TABLE ... PARTITION BY RANGE (...) with child tables defined using CREATE TABLE ... INHERITS (...).

CREATE TABLE user_events (
    event_id BIGINT,
    user_id BIGINT,
    timestamp TIMESTAMP,
    details TEXT
) PARTITION BY RANGE (timestamp);

CREATE TABLE user_events_2023 PARTITION OF user_events
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

The system automatically routes inserts to the correct partition. When querying, PostgreSQL scans only relevant partitions.

If you add a partition for a date outside the defined range, PostgreSQL will silently fail to insert data and raise an error like:

ERROR:  no partition of relation "user_events" found for row
DETAIL:  Partition key value in segment 0 is too small

Use EXPLAIN ANALYZE to confirm that only expected partitions are scanned.

Hash-Based Partitioning

Hash partitioning distributes rows based on a hash function applied to a partition key. This strategy balances data distribution more evenly than range partitioning, especially when the key has many unique values.

In PostgreSQL, create hash partitions using:

CREATE TABLE sales (
    id BIGINT,
    product_id BIGINT,
    amount DECIMAL(10,2),
    sale_date DATE
) PARTITION BY HASH (product_id);

CREATE TABLE sales_p0 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sales_p1 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sales_p2 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sales_p3 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash partitioning requires careful planning of modulus and remainder values. If a new partition is added without updating all existing partitions, data can be silently routed to an incorrect partition.

Composite Partitioning

Many systems allow combining range and hash strategies. For example, a table might be partitioned by date (range) and then further split by hash on a user ID within each date range.

In PostgreSQL:

CREATE TABLE logs (
    log_id BIGINT,
    user_id BIGINT,
    timestamp TIMESTAMP,
    message TEXT
) PARTITION BY RANGE (timestamp);

CREATE TABLE logs_2023 PARTITION OF logs
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
PARTITION BY HASH (user_id);

Each sub-partition must be explicitly created with matching hash parameters. Misalignment in the number of partitions or remainder values can silently cause data loss or incorrect routing.

Partition Pruning

Partition pruning ensures that queries scan only relevant partitions, improving performance and reducing I/O.

In PostgreSQL, enable partition pruning by using EXPLAIN to inspect execution plans:

EXPLAIN SELECT * FROM user_events WHERE timestamp BETWEEN '2023-06-01' AND '2023-06-30';

If the query does not include a partition key in its WHERE clause, PostgreSQL will scan all partitions. This is silent and can cause performance degradation without warning.

Data Migration Between Partitions

Moving data between partitions often requires careful handling to avoid inconsistency. Use INSERT ... SELECT with appropriate filters.

For example, moving data from one partition to another:

INSERT INTO user_events_2024
SELECT * FROM user_events_2023
WHERE timestamp BETWEEN '2023-12-01' AND '2023-12-31';

After the migration, ensure that old data is removed from the source partition:

DELETE FROM user_events_2023
WHERE timestamp BETWEEN '2023-12-01' AND '2023-12-31';

Failure to delete old data results in duplicated records. The system does not enforce uniqueness across partitions, so this is a manual step.

Partition Maintenance

Regular maintenance of partitions is necessary for long-running systems. This includes adding new partitions, archiving old ones, and monitoring partition sizes.

To add a new partition:

CREATE TABLE user_events_2024 PARTITION OF user_events
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

To drop an old partition:

DROP TABLE user_events_2023;

Dropping a partition removes all its data. If you accidentally drop the wrong partition, the loss is permanent without backup or replication.

Partition Alignment

Misalignment in partition boundaries can lead to performance issues and incorrect results. For example, if one partition uses MODULUS 4 and another uses MODULUS 3, data may not be routed correctly.

Always ensure that all partitions in a composite structure use consistent modulus and remainder definitions. Mismatched definitions cause silent routing errors, making it hard to detect which rows are stored where.

Handling Partition Failures

If a partition becomes unavailable, queries can fail or return partial results depending on system configuration.

In PostgreSQL, if a partition is dropped or corrupted, a query will raise an error like:

ERROR:  could not open relation with OID XXXX

When using partitioned tables in a distributed system, ensure that metadata reflects the current partition state. A mismatch between physical data and metadata can cause silent failures or incorrect routing.

Querying Across Partitions

Queries that span multiple partitions should be designed to minimize cross-partition communication. Use UNION or JOIN carefully, as they may require scanning more partitions than necessary.

For example:

SELECT * FROM user_events WHERE timestamp BETWEEN '2023-01-01' AND '2023-12-31';

If the table is partitioned by date, this query should only scan relevant partitions. If not, it will scan all partitions and may include incorrect data.

Performance Monitoring

Monitor partition usage with queries that examine row counts and sizes:

SELECT
    partition_name,
    row_count,
    pg_size_pretty(pg_total_relation_size(partition_name)) AS size
FROM (
    SELECT
        relname AS partition_name,
        COUNT(*) AS row_count
    FROM user_events
    GROUP BY relname
) AS subquery
ORDER BY row_count DESC;

This helps identify uneven data distribution and can guide decisions about re-partitioning.

Partitioning for Write Scalability

When writes are frequent, consider using append-only strategies where new data is written to new partitions rather than updating existing ones. This avoids locking issues and simplifies concurrency control.

Example:

CREATE TABLE logs (
    id BIGINT,
    timestamp TIMESTAMP,
    message TEXT
) PARTITION BY RANGE (timestamp);

CREATE TABLE logs_2024q1 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

New data is written to the latest partition. This approach works best when queries are time-bound and do not need historical updates.

Partitioning for Read Efficiency

Partitioning can improve read performance by reducing I/O. Ensure that queries filter on partition keys to take advantage of partition pruning.

Example:

SELECT * FROM logs WHERE timestamp >= '2024-01-01' AND timestamp < '2024-02-01';

This query will only scan the relevant partition and ignore others. If the query does not include the timestamp filter, it may scan all partitions.

Data Consistency Across Partitions

Data consistency between partitions depends on how data is written and managed. If a transaction spans multiple partitions, ensure that all operations succeed or fail together.

Use explicit transactions:

BEGIN;
INSERT INTO user_events_2024 VALUES (1, 100, '2024-01-01', 'test');
INSERT INTO logs_2024 VALUES (1, '2024-01-01', 'test');
COMMIT;

If one part of the transaction fails, the entire operation rolls back. This prevents partial writes across partitions.

Partitioning Limits

Most systems have limits on the number of partitions. PostgreSQL supports up to 32,767 partitions per table, but performance degrades as the number increases.

Exceeding partition limits silently affects query performance and can cause errors during metadata operations.

Migration Considerations

When migrating data between partitioning strategies, ensure that all indexes are recreated correctly. Indexes on partitioned tables must be created at the parent level.

For example:

CREATE INDEX idx_user_events_timestamp ON user_events (timestamp);

This creates an index on the parent table and automatically applies to all partitions. If you recreate an index only on a child partition, queries may not use it effectively.

Partitioning with External Tools

External tools like pg_dump and pg_restore handle partitioned tables correctly when using the -F p (plain) format:

pg_dump -F p -t user_events database_name > dump.sql

However, if you restore to a system with different partitioning definitions, data may not be routed correctly.

Error Handling and Silent Failures

Many partitioning errors are silent. If a new partition is not defined for a value range, inserts silently fail. Queries that do not filter on partition keys scan all partitions without warning.

Always verify that queries use partition keys in WHERE clauses and validate partition definitions after changes.

This page was rewritten on 15 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.