PostgreSQL Native Table Partitioning: A Hands-On Tutorial

PostgreSQL's declarative partitioning lets you split a single logical table into smaller physical tables (partitions) based on column values. The database routes inserts automatically — queries against the parent table seamlessly fan out across all partitions.

This tutorial walks through range partitioning by date, using thermostat sensor data as our scenario. We'll deliberately leave a gap in our partition scheme, watch data fall into the default partition, then fix it — giving you a concrete feel for how partitioning behaves in practice.

Requirements: PostgreSQL 11 or later. Every SQL block is self-contained and copy-paste ready.

0. Setup

Create a fresh database for this tutorial and connect to it:

psql -c "CREATE DATABASE partitioning_demo"
psql -d partitioning_demo

All subsequent SQL blocks are run inside this psql session.

1. Create the Partitioned Table

We define a parent table partitioned by the recorded_at timestamp. No data is stored in the parent itself — it's a routing layer.

CREATE TABLE thermostat_readings (
    id            bigint GENERATED ALWAYS AS IDENTITY,
    device_id     text        NOT NULL,
    temperature_f numeric(5,1) NOT NULL,
    humidity_pct  numeric(4,1) NOT NULL,
    recorded_at   timestamptz NOT NULL
) PARTITION BY RANGE (recorded_at);

2. Create Partitions (with a Deliberate Gap)

We create partitions for January and March 2025, deliberately skipping February.

CREATE TABLE thermostat_readings_2025_01 PARTITION OF thermostat_readings
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- No February partition — this is intentional.

CREATE TABLE thermostat_readings_2025_03 PARTITION OF thermostat_readings
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

What happens without a default partition?

Before we add one, let's see what PostgreSQL does when a row doesn't match any existing range:

INSERT INTO thermostat_readings (device_id, temperature_f, humidity_pct, recorded_at)
VALUES ('thermo-living-room', 69.0, 34.2, '2025-02-02 08:00:00-05');
-- ERROR:  no partition of relation "thermostat_readings" found for row
-- DETAIL:  Partition key of the failing row contains (recorded_at) = (2025-02-02 13:00:00+00).

The insert fails outright — the data is rejected, not silently misplaced. In a production pipeline, this means data loss. A default partition prevents this by catching any row that doesn't match a defined range.

Add the default partition

CREATE TABLE thermostat_readings_default PARTITION OF thermostat_readings
    DEFAULT;

The default partition's anti-constraint

PostgreSQL internally maintains a partition constraint on the default that is the negation of all other partition bounds. This ensures the planner knows exactly which rows belong to the default. You can see it with the pg_get_partition_constraintdef system function:

SELECT pg_get_partition_constraintdef('thermostat_readings_default'::regclass);

Reading the output inside-out: recorded_at IS NOT NULL paired with the OR of every defined range means "this row belongs to a named partition." The outer NOT flips that — the default accepts everything that doesn't match a named partition. As you add or remove partitions, PostgreSQL updates this constraint automatically.

Verify the partition layout

This catalog query works in any SQL client (not just psql):

SELECT
    parent.relname  AS parent_table,
    child.relname   AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM
    pg_catalog.pg_inherits  inh
    JOIN pg_catalog.pg_class parent ON inh.inhparent = parent.oid
    JOIN pg_catalog.pg_class child  ON inh.inhrelid  = child.oid
WHERE
    parent.relname = 'thermostat_readings'
ORDER BY
    child.relname;

You should see three partitions: _2025_01, _2025_03, and _default.

3. Insert Data Across All Three Months

These readings span January through March 2025, simulating five thermostats in a house. Notice the seasonal temperature trend — colder in January, warming into March.

INSERT INTO thermostat_readings (device_id, temperature_f, humidity_pct, recorded_at) VALUES
    -- January 2025
    ('thermo-living-room', 68.2, 35.0, '2025-01-05 08:00:00-05'),
    ('thermo-bedroom',     65.5, 33.1, '2025-01-05 08:15:00-05'),
    ('thermo-kitchen',     70.1, 38.4, '2025-01-12 12:00:00-05'),
    ('thermo-basement',    60.8, 45.2, '2025-01-20 18:30:00-05'),
    ('thermo-garage',      42.3, 50.5, '2025-01-25 07:00:00-05'),

    -- February 2025 (no partition exists for this range!)
    ('thermo-living-room', 69.0, 34.2, '2025-02-02 08:00:00-05'),
    ('thermo-bedroom',     66.1, 32.8, '2025-02-02 08:15:00-05'),
    ('thermo-kitchen',     71.0, 37.0, '2025-02-10 12:00:00-05'),
    ('thermo-basement',    61.5, 44.0, '2025-02-18 18:30:00-05'),
    ('thermo-garage',      44.0, 48.9, '2025-02-24 07:00:00-05'),

    -- March 2025
    ('thermo-living-room', 70.5, 32.0, '2025-03-03 08:00:00-04'),
    ('thermo-bedroom',     67.8, 30.5, '2025-03-03 08:15:00-04'),
    ('thermo-kitchen',     72.4, 35.1, '2025-03-10 12:00:00-04'),
    ('thermo-basement',    63.0, 42.3, '2025-03-18 18:30:00-04'),
    ('thermo-garage',      48.6, 46.0, '2025-03-25 07:00:00-04');

4. Observe the Problem

Query each partition directly to see where the rows landed.

SELECT 'january' AS partition, count(*) FROM thermostat_readings_2025_01
UNION ALL
SELECT 'march',                 count(*) FROM thermostat_readings_2025_03
UNION ALL
SELECT 'default',               count(*) FROM thermostat_readings_default;

partition	count
january	5
march	5
default	5

January and March data routed correctly. But the five February readings landed in the default partition because no February range exists.

Let's confirm those are February rows:

SELECT device_id, temperature_f, recorded_at
FROM thermostat_readings_default
ORDER BY recorded_at;

Every row in the default partition has a recorded_at in February 2025. The default partition is doing its job as a catch-all, but this data is misrouted — it should live in a dedicated February partition.

5. Fix the Partitioning

Your first instinct might be to just create the missing partition directly:

CREATE TABLE thermostat_readings_2025_02 PARTITION OF thermostat_readings
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ERROR:  updated partition constraint for default partition
--         "thermostat_readings_default" would be violated by some row

PostgreSQL won't allow it — the default partition already contains rows that fall within the new range, and it refuses to create a partition that would leave the default in an inconsistent state.

You might think to detach the default partition, create the new one, move the rows, and reattach. But detaching the default is dangerous on a live system — any insert that doesn't match an existing partition will fail with no partition of relation found for row until the default is reattached. In a production pipeline, that means dropped writes.

Instead, we move the conflicting rows out of the default first, then create the new partition while the default stays attached the entire time.

Step 1: Move misrouted rows to a temporary table

Copy the February rows out of the default partition and delete them. This clears the conflict that would block creating the new partition.

CREATE TEMPORARY TABLE feb_holding_pen AS
SELECT device_id, temperature_f, humidity_pct, recorded_at
FROM thermostat_readings_default
WHERE recorded_at >= '2025-02-01' AND recorded_at < '2025-03-01';

DELETE FROM thermostat_readings_default
WHERE recorded_at >= '2025-02-01' AND recorded_at < '2025-03-01';

Step 2: Create the missing February partition

With the conflicting rows out of the default, PostgreSQL allows the new partition:

CREATE TABLE thermostat_readings_2025_02 PARTITION OF thermostat_readings
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

Step 3: Move the rows into the correct partition

Insert the held rows back into the parent table. PostgreSQL's partition routing sends them to the new February partition.

INSERT INTO thermostat_readings (device_id, temperature_f, humidity_pct, recorded_at)
SELECT device_id, temperature_f, humidity_pct, recorded_at
FROM feb_holding_pen;

DROP TABLE feb_holding_pen;

Verify the February partition received the rows and the default is empty:

SELECT 'february' AS partition, count(*) FROM thermostat_readings_2025_02
UNION ALL
SELECT 'default',               count(*) FROM thermostat_readings_default;

partition	count
february	5
default	0

6. Verify the Final State

All 15 rows should now be distributed across the three monthly partitions, with the default partition empty.

SELECT 'january'  AS partition, count(*) FROM thermostat_readings_2025_01
UNION ALL
SELECT 'february',              count(*) FROM thermostat_readings_2025_02
UNION ALL
SELECT 'march',                 count(*) FROM thermostat_readings_2025_03
UNION ALL
SELECT 'default',               count(*) FROM thermostat_readings_default;

partition	count
january	5
february	5
march	5
default	0

And the full partition layout:

SELECT
    parent.relname  AS parent_table,
    child.relname   AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM
    pg_catalog.pg_inherits  inh
    JOIN pg_catalog.pg_class parent ON inh.inhparent = parent.oid
    JOIN pg_catalog.pg_class child  ON inh.inhrelid  = child.oid
WHERE
    parent.relname = 'thermostat_readings'
ORDER BY
    child.relname;

Four partitions: January, February, March, and default — with all data in the right place.

Cleanup

When you're done exploring, drop the parent table. This cascades to all partitions.

DROP TABLE thermostat_readings;

jwieringa/pg-partitioning-demo.md

Select an option

No results found