Right, let’s talk about the junk drawer of your partitioned table: the default partition. You’ve meticulously planned your ranges, your lists, your hashes… and then reality happens. A date from the future slips in, a region code you’ve never seen before shows up, or someone fat-fingers a NULL. Without a default partition, your entire, beautiful INSERT operation explodes in your face with an error. The default partition is your safety net. It’s the “miscellaneous” folder that prevents your entire system from grinding to a halt because of one weird row. It’s not elegant, but it’s absolutely necessary for any robust production system.

The Syntax of Last Resort

Creating one is dead simple. You just add the DEFAULT keyword instead of a specific range or list value.

-- For a RANGE partition on a date column
CREATE TABLE sensor_data (
    sensor_id integer,
    capture_time timestamptz,
    reading numeric
) PARTITION BY RANGE (capture_time);

CREATE TABLE sensor_data_2023 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sensor_data_2024 PARTITION OF sensor_data
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Here's our lifesaver
CREATE TABLE sensor_data_default PARTITION OF sensor_data
    DEFAULT;

-- For a LIST partition on a region text column
CREATE TABLE sales (
    sale_id integer,
    region text,
    amount numeric
) PARTITION BY LIST (region);

CREATE TABLE sales_na PARTITION OF sales
    FOR VALUES IN ('us', 'ca', 'mx');

CREATE TABLE sales_eu PARTITION OF sales
    FOR VALUES IN ('de', 'fr', 'uk');

-- Catch anything that isn't US, Canada, Mexico, Germany, France, or the UK
CREATE TABLE sales_other PARTITION OF sales
    DEFAULT;

See? It’s the partition that says, “I don’t care what the value is, if it doesn’t have a home, it lives with me.”

The Inevitable Performance Tax

Here’s the part where I have to be the brilliant friend who gives you the bad news: this safety net is made of lead. The default partition is a performance black hole. Why? Because the database has no idea what’s in there. When you query for data, the planner can perform “partition pruning.” It looks at your WHERE clause and intelligently skips scanning partitions that couldn’t possibly contain the data. For sensor_data_2023, it knows it only has to look there if your query is for 2023.

The default partition? It could contain anything. A query for data from 2030? Might be in there. A query for region ‘jp’? Might be in there. The planner has to scan the entire default partition for almost every query that filters on the partition key, just in case. It’s the opposite of pruning; it’s weeding the entire garden to find one dandelion.

The Critical Housekeeping Ritual

This performance tax means you cannot just “set it and forget it.” A default partition that grows unchecked will eventually become a table-sized monument to your neglect, dragging the performance of your entire partitioned system down with it. You must have a active process to monitor it.

The goal is to keep it empty. Its existence is a contingency, not a destination. You should be running checks frequently—daily, or even hourly for high-throughput systems—to see what’s accumulating in there.

-- See what weirdness has landed in the default partition lately
SELECT DISTINCT capture_time
FROM sensor_data_default
ORDER BY capture_time DESC
LIMIT 10;

SELECT DISTINCT region
FROM sales_other;

When you find legitimate new data that deserves its own home—say, sales start coming in from Japan (‘jp’)—you “promote” it out of the default partition. This is a two-step dance: create the new partition, then migrate the data.

-- 1. Create the new, proper partition for Japan
CREATE TABLE sales_jp PARTITION OF sales
    FOR VALUES IN ('jp');

-- 2. Move the data from the default partition to the new one
-- This is a non-blocking operation in PG11+
ALTER TABLE sales MOVE PARTITION sales_other TO sales_jp
    WHERE region = 'jp';

Crucial note: That MOVE command will only move rows where region = 'jp'. The rest stay safely in the default partition. This is how you surgically remove data from the default without dropping the whole table.

The One Trap Everyone Falls Into

There is a classic, face-palm moment waiting for you if you’re not careful. You cannot add a new partition if its values would overlap with rows already existing in the DEFAULT partition. The database protects you from having the same row in two places.

Imagine you try to add a partition for 2025 data, but some 2025 data has already been lazily tossed into the default partition.

-- This will FAIL if any row in sensor_data_default has capture_time >= '2025-01-01'
CREATE TABLE sensor_data_2025 PARTITION OF sensor_data
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

The error will be infuriatingly vague until you remember this rule. The fix is to migrate the data out first (as shown above) or, in a pinch, to detach the default partition, create the new one, and then re-attach the default. But honestly, just migrate the data. It’s cleaner.

The default partition is your system’s immune system. It contains the weird stuff so the rest of the system can stay healthy and fast. But like an immune system, you have to check on it regularly, or it can turn on you.