Your Partitioned Table Will Fail at Midnight

By · · ·

Series: Partitioning in the Wild | Part 1 of 8

Platform: PostgreSQL 18.3 | Tested on Docker

Introduction:

  • Partitioning looks simple until a batch insert fails at 3 AM, the ingestion pipeline stalls,  every downstream job starts queueing behind it and dashboards start showing stale data.
  • In this part we set up three PostgreSQL partitioning approaches side by side using monthly partitions and simulate what happens when data arrives outside the existing partition range.
  • This is Part 1 of a 8 part series covering partitioning across PostgreSQL, MySQL, MariaDB, Oracle and MongoDB.
  • By the end, we will see why constraint-based partitioning becomes difficult to maintain, why native declarative partitioning still requires operational work even with a default partition and how pg_partman automates the lifecycle.

Note:

  • Tested on PostgreSQL 18.3 running in Docker on a Windows laptop.
  • Always test in a non-production environment before applying partitioning changes to a live system.

The problem we are solving:


  • When a table grows large, queries slow down, vacuums take longer and backups become painful. Partitioning splits data into smaller physical chunks while keeping a single logical table for the application.
  • PostgreSQL provides three main approaches:
  1. Constraint-based (legacy trigger routing, pre-PG10)
  2. Native declarative partitioning (PG10+)
  3. pg_partman (automated lifecycle on top of native)
  • We will build all three, intentionally break them with out-of-range inserts, fix the failures and compare the operational overhead each approach introduces.

Simulation setup:

Three tables, identical schema, monthly partitions.

Component Detail
PostgreSQL18.3
pg_partman5.4.3
DockerDesktop (Windows)
Partition intervalMonthly
SimulationDirect SQL INSERT commands
 

CREATE SCHEMA IF NOT EXISTS lab;

Step 1: Building the tables 

We start with the basic setup. Two monthly partitions created manually for each approach.

Approach A: Native declarative

CREATE TABLE lab.sensor_native (
    reading_id   BIGSERIAL,
    sensor_id    INT NOT NULL,
    location     TEXT NOT NULL,
    temperature  NUMERIC(5,2) NOT NULL,
    humidity     NUMERIC(5,2) NOT NULL,
    pressure     NUMERIC(7,2) NOT NULL,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (recorded_at);

CREATE TABLE lab.sensor_native_202605
    PARTITION OF lab.sensor_native
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE lab.sensor_native_202606
    PARTITION OF lab.sensor_native
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- Index on parent propagates to all existing and future partitions automatically
CREATE INDEX ON lab.sensor_native (sensor_id, recorded_at);

Approach B: Constraint-based

CREATE TABLE lab.sensor_constraint (
    reading_id   BIGSERIAL,
    sensor_id    INT NOT NULL,
    location     TEXT NOT NULL,
    temperature  NUMERIC(5,2) NOT NULL,
    humidity     NUMERIC(5,2) NOT NULL,
    pressure     NUMERIC(7,2) NOT NULL,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- CHECK constraints tell the planner which date range each child table holds.

CREATE TABLE lab.sensor_constraint_202605 (
    CHECK (recorded_at >= '2026-05-01' AND recorded_at < '2026-06-01')
) INHERITS (lab.sensor_constraint);

CREATE TABLE lab.sensor_constraint_202606 (
    CHECK (recorded_at >= '2026-06-01' AND recorded_at < '2026-07-01')
) INHERITS (lab.sensor_constraint);

CREATE INDEX ON lab.sensor_constraint_202605 (sensor_id, recorded_at);
CREATE INDEX ON lab.sensor_constraint_202606 (sensor_id, recorded_at);

-- Trigger routes inserts to the correct child table.
-- ELSE raises exception
CREATE OR REPLACE FUNCTION lab.sensor_constraint_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.recorded_at >= '2026-05-01' AND NEW.recorded_at < '2026-06-01' THEN
        INSERT INTO lab.sensor_constraint_202605 VALUES (NEW.*);
    ELSIF NEW.recorded_at >= '2026-06-01' AND NEW.recorded_at < '2026-07-01' THEN
        INSERT INTO lab.sensor_constraint_202606 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'No partition for recorded_at = %', NEW.recorded_at;
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER sensor_constraint_insert_trigger
BEFORE INSERT ON lab.sensor_constraint
FOR EACH ROW EXECUTE FUNCTION lab.sensor_constraint_insert();

Approach C: pg_partman

CREATE TABLE lab.sensor_partman (
    reading_id   BIGSERIAL,
    sensor_id    INT NOT NULL,
    location     TEXT NOT NULL,
    temperature  NUMERIC(5,2) NOT NULL,
    humidity     NUMERIC(5,2) NOT NULL,
    pressure     NUMERIC(7,2) NOT NULL,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (recorded_at);

-- Index on parent: partman creates matching indexes on all future partitions automatically
CREATE INDEX ON lab.sensor_partman (sensor_id, recorded_at);

SELECT partman.create_parent(
    p_parent_table => 'lab.sensor_partman',
    p_control      => 'recorded_at',
    p_interval     => '1 month',
    p_premake      => 3
);


pg_partman automatically created eight partitions, including future partitions defined by the premake setting, along with a default partition. No manual CREATE TABLE statements were required.

Step 2: Normal insert (all three work)

INSERT INTO lab.sensor_native
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (101, 'Server Room A', 25.5, 60.0, 1013.0, '2026-05-15 10:00:00+00');
INSERT 0 1

INSERT INTO lab.sensor_constraint
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (101, 'Server Room A', 25.5, 60.0, 1013.0, '2026-05-15 10:00:00+00');
INSERT 0 0  -- expected: trigger returns NULL, row landed in sensor_constraint_202605 directly

INSERT INTO lab.sensor_partman
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (101, 'Server Room A', 25.5, 60.0, 1013.0, '2026-05-15 10:00:00+00');
INSERT 0 1

Everything works. May 2026 data has a matching partition in all three approaches.

Step 3: Insert out-of-range data and watch them fail

Now insert an August 2026 row. No partition exists for August in native or constraint-based.

Native declarative: hard error, data rejected

INSERT INTO lab.sensor_native
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (102, 'Server Room B', 26.1, 58.0, 1012.0, '2026-08-10 10:00:00+00');

ERROR:  no partition of relation "sensor_native" found for row
DETAIL:  Partition key of the failing row contains
(recorded_at) = (2026-08-10 10:00:00+00).

Constraint-based: trigger raises exception, data rejected

INSERT INTO lab.sensor_constraint
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (102, 'Server Room B', 26.1, 58.0, 1012.0, '2026-08-10 10:00:00+00');

ERROR:  No partition for recorded_at = 2026-08-10 10:00:00+00
CONTEXT:  PL/pgSQL function lab.sensor_constraint_insert() line 8 at RAISE

pg_partman: succeeds, routed to August partition created in advance by premake

INSERT INTO lab.sensor_partman
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (102, 'Server Room B', 26.1, 58.0, 1012.0, '2026-08-10 10:00:00+00');
INSERT 0 1


Step 4: Apply the fix, add a default partition

The standard fix is to add a default partition that catches anything with no matching range.

Native declarative: add DEFAULT partition (PG11+)

CREATE TABLE lab.sensor_native_default
    PARTITION OF lab.sensor_native DEFAULT;

-- Retry the August insert
INSERT INTO lab.sensor_native
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (102, 'Server Room B', 26.1, 58.0, 1012.0, '2026-08-10 10:00:00+00');
INSERT 0 1  -- succeeds, lands in sensor_native_default

SELECT tableoid::regclass AS partition, recorded_at
FROM lab.sensor_native ORDER BY recorded_at DESC LIMIT 1;

partition                   | recorded_at
----------------------------+------------------------
lab.sensor_native_default   | 2026-08-10 10:00:00+00

Constraint-based: add catch-all child table, update trigger

-- No CHECK constraint on the default table: it must accept any row that does not match
CREATE TABLE lab.sensor_constraint_default ()
    INHERITS (lab.sensor_constraint);

-- Update trigger: replace RAISE EXCEPTION with INSERT into default
CREATE OR REPLACE FUNCTION lab.sensor_constraint_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.recorded_at >= '2026-05-01' AND NEW.recorded_at < '2026-06-01' THEN
        INSERT INTO lab.sensor_constraint_202605 VALUES (NEW.*);
    ELSIF NEW.recorded_at >= '2026-06-01' AND NEW.recorded_at < '2026-07-01' THEN
        INSERT INTO lab.sensor_constraint_202606 VALUES (NEW.*);
    ELSE
        INSERT INTO lab.sensor_constraint_default VALUES (NEW.*);
    END IF;
    RETURN NULL;
END;
$$;

-- Retry the August insert
INSERT INTO lab.sensor_constraint
    (sensor_id, location, temperature, humidity, pressure, recorded_at)
VALUES (102, 'Server Room B', 26.1, 58.0, 1012.0, '2026-08-10 10:00:00+00');
INSERT 0 0  -- succeeds, trigger routed row to sensor_constraint_default


All three approaches now accept the August row and handle out-of-range inserts. So the obvious question is:

Step 5: If both handle it now, why is pg_partman needed?

The default partition stops data loss. It does not stop the monthly manual work. Here is what a DBA must do every single month with native and constraint-based, even after adding the default partition:

-- Every month end without fail:

-- Step 1: Create the new partition (manual DDL)
CREATE TABLE lab.sensor_native_202608
    PARTITION OF lab.sensor_native
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- Step 2: Move rows out of default into the correct partition
WITH moved AS (
    DELETE FROM lab.sensor_native_default
    WHERE recorded_at >= '2026-08-01' AND recorded_at < '2026-09-01'
    RETURNING *
)
INSERT INTO lab.sensor_native_202608 SELECT * FROM moved;

-- For constraint-based: also update the trigger function every single month
CREATE OR REPLACE FUNCTION lab.sensor_constraint_insert() ...

With pg_partman, none of that is needed. One call handles everything and it runs automatically via the background worker:

SELECT partman.run_maintenance('lab.sensor_partman');

-- Creates August partition automatically
-- Moves the August row out of default into the correct partition
-- Premakes September and October partitions ready in advance
-- No DDL. No trigger edits. No manual work.

Monthly task Native + default Constraint + default pg_partman
Create new partition Manual DDL Manual DDL Automatic
Move rows from default Manual Manual Automatic
Update trigger code Not needed Required every month Not needed
Future partitions pre-created No No Yes (premake setting)
Survives missed maintenance Data piles in default Data piles in default BGW never misses
Drop old partitions Manual Manual Automatic via retention
Query pruning Full Partial (parent and default always scanned) Full
Trigger overhead per insert None One function call per row None

Step 6: Can a smarter trigger eliminate the monthly work?

A reasonable question: instead of updating the trigger every month, can we write a trigger that creates the partition automatically on first insert? Yes. Here is Approach D.

Approach D: dynamic trigger (auto-creates partition on first insert)

CREATE TABLE lab.sensor_dynamic (
    reading_id   BIGSERIAL,
    sensor_id    INT NOT NULL,
    location     TEXT NOT NULL,
    temperature  NUMERIC(5,2) NOT NULL,
    humidity     NUMERIC(5,2) NOT NULL,
    pressure     NUMERIC(7,2) NOT NULL,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ON lab.sensor_dynamic (sensor_id, recorded_at);

-- Trigger checks if the monthly partition exists, creates it if not, then inserts
CREATE OR REPLACE FUNCTION lab.sensor_dynamic_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    partition_name TEXT;
    start_date     TIMESTAMPTZ;
    end_date       TIMESTAMPTZ;
BEGIN
    start_date     := date_trunc('month', NEW.recorded_at);
    end_date       := start_date + interval '1 month';
    partition_name := 'lab.sensor_dynamic_'
                      || to_char(start_date, 'YYYYMM');

    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'lab'
          AND c.relname = 'sensor_dynamic_'
                          || to_char(start_date, 'YYYYMM')
    ) THEN
        EXECUTE format(
            'CREATE TABLE %s (
                CHECK (recorded_at >= %L AND recorded_at < %L)
            ) INHERITS (lab.sensor_dynamic)',
            partition_name, start_date, end_date
        );
        EXECUTE format(
            'CREATE INDEX ON %s (sensor_id, recorded_at)',
            partition_name
        );
    END IF;

    EXECUTE format(
        'INSERT INTO %s VALUES ($1.*)', partition_name
    ) USING NEW;

    RETURN NULL;
END;
$$;

CREATE TRIGGER sensor_dynamic_insert_trigger
BEFORE INSERT ON lab.sensor_dynamic
FOR EACH ROW EXECUTE FUNCTION lab.sensor_dynamic_insert();

This eliminates the monthly trigger update entirely. The first insert for any new month creates the partition on the fly. No manual DDL required. But there is a cost.

Step 7: Benchmark across all four approaches

1 million rows per test



Approach Write 1M single month Write 1M multi-month Factor vs native Auto partition creation Monthly manual work
native 5,221 ms 7,070 ms 1x No DDL every month
constraint (static trigger) 28,420 ms 27,436 ms 5x slower No DDL + trigger update
pg_partman 7,251 ms 9,685 ms 1.3x slower Yes, in advance None
constraint (dynamic trigger) 77,564 ms 75,869 ms 15x slower Yes, on first insert None

The dynamic trigger eliminates the monthly manual work but at 15x write overhead. Every insert performs a pg_catalog lookup to verify partition existence and executes dynamic SQL, which bypasses normal plan caching. The static trigger is 5x overhead. The dynamic trigger is 15x. Neither is acceptable for a continuous ingestion workload.

pg_partman solves the same automatic partition creation problem without touching the write path at all. The background worker creates partitions in advance during off-peak time. When the insert arrives the partition already exists and native routing handles it with zero trigger overhead. The 1.3x overhead compared to native partitioning comes mainly from pg_partman maintenance bookkeeping rather than trigger execution.

Wrapping Up:

  • Native declarative partitioning solves routing efficiently, but it still requires operational maintenance. Default partitions prevent immediate insert failures, yet they introduce cleanup work and monitoring requirements.
  • Constraint-based partitioning adds trigger overhead and ongoing maintenance complexity. Dynamic triggers reduce manual work but introduce significant write-path cost.
  • pg_partman automates partition creation, retention and default-partition cleanup while keeping insert performance close to native partitioning. It is the only approach here that eliminates recurring partition maintenance work entirely. Future partitions are created automatically, rows are moved out of the default partition by the background worker and old partitions can be dropped through retention policies. A team managing 10 partitioned tables ends up with just 10 entries in partman.part_config and zero monthly DDL tasks.
  • Lab scripts available here

Hope you liked the content 😄

Please share your feedback in the comments section.

Keep Learning ! Keep Sharing!

What is next

We solved the routing problem, but most production systems already have years of data sitting in a large unpartitioned table. Creating partitions is easy. Moving tens of millions of live rows into a partitioned structure without long locks, replication lag spikes or downtime is the hard part. 

In Part 2, we migrate 50 million rows into a partitioned table using controlled batch migration and a zero-downtime atomic rename cutover. The application continues writing throughout the migration, with no maintenance window required.

Series: Partitioning in the Wild

  • Part 1: Your partitioned table will fail at midnight .
  • Part 2: Moving 50 million rows into a partitioned table without downtime (coming soon)

No comments:

Post a Comment