Looking Forward to Postgres 19: Split Personality<br>Blog Home<br>Looking Forward to Postgres 19: Split Personality
Shaun Thomas|June 26, 2026
Postgres has had native support for declarative partitions since version 10, and every release since has filed off another rough edge. We got partition-wise joins, default partitions, hash partitioning, and the ability to attach and detach partitions concurrently. By any reasonable measure, declarative partitioning is one of the great success stories of modern Postgres.<br>Despite the power here, it's always been a kind of one-way ratchet. Creating or dropping partitions was easy. But reorganizing existing ones was a different beast entirely. There's no syntax or tool to assist in the event we want to convert a table to a partition set, or revise an existing design. Instead, it's just a long series of manual statements to carve the table up into partitions, or perhaps leveraging pg_partman to do it instead.<br>Well, Postgres 19 finally has an answer to that. Let's talk about the current state of the art, and how the new SPLIT PARTITION and MERGE PARTITIONS syntax for ALTER TABLE may change the story.<br>The Way Things Were<br>Suppose we have a busy partition and want to break it into smaller pieces. The current procedure works something like this:<br>Create new partitions as standalone tables.
Copy the relevant rows into each one.
Detach the old partition.
Attach the new ones.
Drop the empty table husk.
It's serviceable, but also tedious, error-prone, and subject to various locks that can delay the process. Postgres 19 collapses that whole routine into one statement.<br>Before diving into how, let's create something realistic to play with. How about an analytics pipeline containing a stream of events partitioned by time? The initial state will be one partition per quarter for 2026:<br>CREATE TABLE event_log (<br>id BIGINT GENERATED ALWAYS AS IDENTITY,<br>event_time TIMESTAMPTZ NOT NULL,<br>event_type TEXT NOT NULL,<br>payload JSONB,<br>PRIMARY KEY (id, event_time)<br>) PARTITION BY RANGE (event_time);
CREATE TABLE event_log_2026_q1 PARTITION OF event_log<br>FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');<br>CREATE TABLE event_log_2026_q2 PARTITION OF event_log<br>FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');<br>CREATE TABLE event_log_2026_q3 PARTITION OF event_log<br>FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');<br>CREATE TABLE event_log_2026_q4 PARTITION OF event_log<br>FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');Now let's inject a single event into every day of the year so each partition has something to hold:<br>INSERT INTO event_log (event_time, event_type, payload)<br>SELECT ts, 'click', '{"ok": true}'::jsonb<br>FROM generate_series('2026-01-15'::timestamptz,<br>'2026-12-15'::timestamptz, '1 day') ts;Let's assume that a quarter felt reasonable when we drew it up, but traffic in Q1 turned out to be heavier than expected. Now, querying three months of data to find a single morning's worth of clicks is wasteful. We'd much rather have monthly partitions there. In the past, that meant the migration shuffle.<br>Be Fruitful and Multiply<br>The new syntax reads almost exactly like what we might say out loud. "Take this partition, and split it into these smaller ones":<br>ALTER TABLE event_log SPLIT PARTITION event_log_2026_q1 INTO (<br>PARTITION event_log_2026_01 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'),<br>PARTITION event_log_2026_02 FOR VALUES FROM ('2026-02-01') TO ('2026-03-01'),<br>PARTITION event_log_2026_03 FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')<br>);That's it. The original events_2026_q1 partition is gone, replaced by three monthly children, and Postgres moved every row into the partition where it now belongs. Let's verify that:<br>SELECT tableoid::regclass AS partition, count(*)<br>FROM event_log<br>WHERE event_time >= '2026-01-01'<br>AND event_time No temporary table -> copy -> detach dance. Instead, the rows land exactly where the new bounds dictate. Seventeen days of January data, a full February, and a full March. The whole operation is transactional too, so it's perfectly valid to abort in the middle and leave the original structure unchanged.<br>One detail worth noting: the new partitions must cover the entire range of the partition being split, with no gaps or overlaps. Postgres will point out mistakes here:<br>ALTER TABLE event_log SPLIT PARTITION event_log_2026_q2 INTO (<br>PARTITION event_log_2026_p1 FOR VALUES FROM ('2026-04-01') TO ('2026-05-01'),<br>PARTITION event_log_2026_p2 FOR VALUES FROM ('2026-06-01') TO ('2026-07-01')<br>-- Note the gap between 2026-05-01 and 2026-06-01<br>);
ERROR: cannot split to partition "event_log_2026_p2"<br>together with partition "event_log_2026_p1"<br>DETAIL: The lower bound of partition "event_log_2026_p2" is not equal<br>to the upper bound of partition "event_log_2026_p1".<br>HINT: ALTER TABLE ... SPLIT PARTITION requires the partition bounds to<br>be adjacent.That's a good error; it tells us precisely what's wrong and exactly how to fix it.<br>E Pluribus Unum<br>Splitting is only half the story. The reciprocal scenario...