Partition Maintenance Gaps

When time-based partitioning suddenly starts dropping data on New Year's Eve.

The idea

Time-series data (like logs or metrics) grows infinitely. Instead of storing 10 years of data in one massive SQL table, we use Table Partitioning. We split the logical events table into physical sub-tables (e.g., events_jan, events_feb). This makes deleting old data instant (just DROP TABLE events_jan). But databases don't create these future sub-tables automatically! If your maintenance script fails to create the events_march partition before March 1st arrives, any new logs will be completely rejected by the database.

Step 1: The App is writing logs. The DB routes them seamlessly into the 'Oct' and 'Nov' partitions.

How it works (Pre-creation)

A Partition Maintenance Gap happens when time catches up to your database schema. The standard fix is to run a cron job that creates partitions several months in advance. If the cron job breaks, you have a multi-month buffer to notice and fix it before you actually hit the gap.

-- Manual partition creation in PostgreSQL
-- If this isn't run before March 1st, writes will FAIL!
CREATE TABLE events_march PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- The pg_partman extension automates this safely:
SELECT partman.create_parent(
    'public.events', 
    'created_at', 
    'native', 
    'monthly',
    p_premake := 3  -- ALWAYS premake 3 months into the future!
);

Cost

Having 1,000 partitions for a single table can slow down the query planner, as it has to evaluate which partitions to scan. Creating partitions too far into the future wastes catalog space. Usually, creating 3 to 6 months of future partitions strikes the right balance between safety buffer and planner overhead.

Watch out for