Taming Time-Series Bloat with PostgreSQL Declarative Partitioning

The Wall of Vacuum Bloat

When you store millions of sensor readings or event logs in a single PostgreSQL table, you eventually hit a performance wall. As the table grows, indexes become massive, and the VACUUM process—the background mechanism that cleans up dead rows—takes longer to complete. In extreme cases, maintenance windows start overlapping with ingestion pipelines, causing locking issues and slowing down the very queries you need for real-time monitoring.

The solution is declarative partitioning. Instead of one monolithic table, you split your data into smaller, manageable chunks (partitions) based on a specific column, typically a timestamp. This allows the database to ignore irrelevant data entirely during a query, a process known as partition pruning.

How Declarative Partitioning Works

In PostgreSQL (version 10 and later), you define a "parent" table that acts as a template. This parent table does not store data itself; it defines the schema and the partitioning strategy. You then create "child" tables that hold the actual data for specific ranges.

For time-series data, PARTITION BY RANGE is the standard choice. By defining boundaries (e.g., one partition per month), you ensure that a query for "last week's data" only touches the partition containing that specific date range, rather than scanning a multi-terabyte index.

Worked Example: IoT Sensor Data

Imagine an IoT system tracking temperature readings. We want to partition the data by month to keep the active dataset small and make old data easy to archive.

1. Create the Parent Table

Run this in your psql terminal as a user with CREATE permissions on the schema.

CREATE TABLE sensor_readings (
    sensor_id INT NOT NULL,
    reading_time TIMESTAMPTZ NOT NULL,
    value NUMERIC
) PARTITION BY RANGE (reading_time);

2. Create Monthly Partitions

You must create the partitions before inserting data, or the insert will fail unless a default partition exists.

-- Partition for January 2026
CREATE TABLE readings_2026_01 PARTITION OF sensor_readings
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- Partition for February 2026
CREATE TABLE readings_2026_02 PARTITION OF sensor_readings
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Catch-all for data outside defined ranges
CREATE TABLE readings_default PARTITION OF sensor_readings DEFAULT;

3. Verifying Partition Pruning

To verify that PostgreSQL is ignoring irrelevant partitions, use EXPLAIN. Insert data into both months, then run a query for February:

EXPLAIN (COSTS OFF) 
SELECT * FROM sensor_readings 
WHERE reading_time >= '2026-02-10' AND reading_time < '2026-02-15';

Expected Result: The execution plan should show a Seq Scan or Index Scan on readings_2026_02 only. If you see readings_2026_01 in the plan, pruning is not working (check that enable_partition_pruning is on in your configuration).

Trade-offs and Engineering Constraints

Partitioning is not a "magic button" for performance; it introduces its own complexities:

• Planner Overhead: Creating too many partitions (e.g., daily partitions for five years) can slow down the query planner. The database must evaluate every partition to decide which ones to prune, which consumes memory and CPU.
• Primary Key Requirements: Any unique constraint or primary key on a partitioned table must include the partition key (in this case, reading_time). You cannot have a global unique ID across partitions without including the timestamp.
• Management Burden: Manually creating tables every month is error-prone. For production environments, use an extension like pg_partman to automate partition creation and retention.

Practical Verification and Maintenance

To ensure your partitioning strategy is healthy, monitor the row distribution and vacuum health using the pg_stat_user_tables view:

SELECT relname, n_live_tup, n_dead_tup 
FROM pg_stat_user_tables 
WHERE relname LIKE 'readings_%';

If you see a massive spike in n_dead_tup in a specific partition, you can run VACUUM on that specific child table without locking the entire dataset. When data becomes obsolete, instead of running a slow DELETE, you can simply DROP TABLE the old partition, which is an instantaneous metadata operation.