Range partitioning turns a boundary definition into a write-routing rule. PostgreSQL places a row in the partition whose bounds accept its key, whether the row arrives on time or months late. Correct boundaries, compatible timestamp types, and a plan for missing ranges matter before any query can benefit from pruning.

Use adjacent half-open ranges

A range partition includes its lower bound and excludes its upper bound. September can cover the interval from September first through, but not including, October first. October then starts at that exact boundary without overlap. Do not model a month by guessing its final representable timestamp; adjacent half-open ranges work regardless of the timestamp’s fractional precision.

Queries should express the intended interval just as carefully. A lower-inclusive, upper-exclusive predicate matches a monthly slice. BETWEEN includes both endpoints, so using the next month’s first instant as its upper endpoint can include data from that next month. Partition routing does not correct a predicate that asks for the wrong interval.

The key’s type and time zone are part of the definition. A timestamptz value represents an instant, while a timestamp without time zone represents a local date and time without that instant’s zone interpretation. The example uses explicit UTC offsets for every boundary. If business months follow another time zone, derive boundaries using that zone’s rules instead of assuming every local day has the same elapsed duration.

Missing ranges are an operational state

An insert with no matching partition raises an error unless a DEFAULT partition accepts it. PostgreSQL does not automatically create the next monthly table when the calendar advances. Create needed ranges ahead of demand and account for late data and corrections to old dates. A key need not arrive monotonically for routing to work; its destination simply must still exist.

DEFAULT can absorb values outside the explicitly declared ranges. That can reduce immediate insertion failures, but it also makes missing partition management less visible unless the default table is inspected. A growing default partition can contain several unrelated time ranges and become harder to reorganize. Keep the difference between an intentional exception area and forgotten routine data visible.

Adding a new explicit partition changes which values the default partition may contain. If rows belonging to the new range remain in DEFAULT, PostgreSQL rejects the change rather than leaving overlapping ownership. Move those rows to an appropriate staging table or otherwise reconcile them before attaching the new partition. Bounds are enforced data rules, not labels that can simply be made inaccurate.

When it breaks

A deployment can fail exactly at a month boundary because the next partition was never created. Another can fail while adding that partition because DEFAULT already accumulated its rows. Both failures concern routing and data placement, not stale planner statistics. Inspect the rejected key and existing bounds before running ANALYZE or changing indexes.

ATTACH may need to scan the candidate table to validate its bounds and also check the default partition for conflicting rows. Suitable validated constraints can avoid some scans, but the operation still requires locks and compatible table structure. On active tables, coordinate writers and lock acquisition while relocating data; an isolated transactional demonstration is not a complete online migration procedure.

Nullability is separate from range selection. The example requires a nonnull timestamp so a missing event time cannot quietly become ordinary exception data. Also verify row counts and identifiers after relocation: a successful attach is not proof that an earlier copy step neither lost nor duplicated rows.

Check it quickly

Save this as partition-ranges.sql and run psql -X -v ON_ERROR_STOP=1 -f partition-ranges.sql in a disposable PostgreSQL 18 database. All tables are temporary and the whole experiment is rolled back.

\set ON_ERROR_STOP on
BEGIN;
SET LOCAL timezone='UTC';
CREATE TEMP TABLE samples (at timestamptz NOT NULL,id integer) PARTITION BY RANGE(at);
CREATE TEMP TABLE samples_sep PARTITION OF samples
FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00');
INSERT INTO samples VALUES ('2026-09-01 00:00:00+00',1),('2026-09-30 23:59:59.999999+00',2);
DO $$ BEGIN
  INSERT INTO samples VALUES ('2026-10-01 00:00:00+00',3);
  RAISE EXCEPTION 'upper boundary entered September';
EXCEPTION WHEN check_violation THEN RAISE NOTICE 'upper boundary needs another partition';
END $$;
CREATE TEMP TABLE samples_default PARTITION OF samples DEFAULT;
INSERT INTO samples VALUES ('2026-10-01 00:00:00+00',3);
DO $$ BEGIN
  CREATE TEMP TABLE samples_oct_bad PARTITION OF samples
  FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');
  RAISE EXCEPTION 'new partition overlapped populated default';
EXCEPTION WHEN check_violation THEN RAISE NOTICE 'matching default rows must be moved first';
END $$;
CREATE TEMP TABLE samples_oct (LIKE samples INCLUDING ALL);
WITH moved AS (
  DELETE FROM samples_default
  WHERE at >= TIMESTAMPTZ '2026-10-01 00:00:00+00' AND at < TIMESTAMPTZ '2026-11-01 00:00:00+00'
  RETURNING *
) INSERT INTO samples_oct SELECT * FROM moved;
ALTER TABLE samples ATTACH PARTITION samples_oct
FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');
SELECT id,at,tableoid::regclass AS physical_table FROM samples ORDER BY id;
DO $$ BEGIN
  IF (SELECT array_agg(id ORDER BY id) FROM samples_sep) IS DISTINCT FROM ARRAY[1,2] OR
     (SELECT array_agg(id ORDER BY id) FROM samples_oct) IS DISTINCT FROM ARRAY[3] OR
     (SELECT count(*) FROM samples_default) <> 0 THEN
    RAISE EXCEPTION 'range migration lost or duplicated data';
  END IF;
END $$;
ROLLBACK;

PostgreSQL 18.6 accepted the first instant of September and the final tested instant before October. October first was rejected before a destination existed, then entered DEFAULT after that partition was created. Attempting to add an overlapping October partition failed while the matching default row remained there.

The script moved that row using DELETE RETURNING, attached its staging table as October, and displayed each row’s actual tableoid. Ids one and two remained in September, id three appeared in October, and DEFAULT was empty. Assertions verify the final membership and count. This tests both boundary inclusion and a complete relocation path, without treating a quiet default partition as evidence that future range management can be omitted.

Sources