Postgres Partitioning with a Default Partition
Partitioning is an important database maintenance strategy for a growing application backed by PostgreSQL. As one of the main authors of pg_partman and an engineer here at Crunchy Data, I spend a lot of my time helping folks implement partitioning. One of the nuances of PostgreSQL’s partitioning implementation is the default partition, which I’ll dig into in this post and discuss how to use it effectively.
Why default partitions are important
The default partition is pretty much what it sounds like; you can make a special partition designated as the DEFAULT, which will capture any and all data that does not have an existing partition with matching boundary constraints.
If you’re new to partitioning, you might be making partitions a week in advance. But after monitoring you realize you need to make them more like 2 weeks in advance. Default partitions can help you learn how to manage and when to create your child partitions.
Default partitions are also there to catch mistakes. Maybe there’s an issue in application code putting timestamps a hundred years into the future instead of one year. Maybe there’s just some bad data getting created. Your default partition can help you spot that.
While having a default partition is a good idea, you don’t actually want to leave data in there. I’ll show you some tips later on about how to monitor the default for the presence of any rows. When you find data in there, you’ll want to evaluate whether the data is valid, and if it is, create the relevant child partitions and move the data there.
Adding a default
PostgreSQL declarative partitioning does not create any child partitions automatically, including the default. pg_partman can help with that and we’ll discuss that later.
Here we have a daily partition set that has been created but does not yet have a default.
Partitioned table "partman_test.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (col3)
Partitions: partman_test.time_taptest_table_p20241118 FOR VALUES FROM ('2024-11-18 00:00:00-05') TO ('2024-11-19 00:00:00-05'),
partman_test.time_taptest_table_p20241119 FOR VALUES FROM ('2024-11-19 00:00:00-05') TO ('2024-11-20 00:00:00-05'),
partman_test.time_taptest_table_p20241120 FOR VALUES FROM ('2024-11-20 00:00:00-05') TO ('2024-11-21 00:00:00-05'),
partman_test.time_taptest_table_p20241121 FOR VALUES FROM ('2024-11-21 00:00:00-05') TO ('2024-11-22 00:00:00-05'),
partman_test.time_taptest_table_p20241122 FOR VALUES FROM ('2024-11-22 00:00:00-05') TO ('2024-11-23 00:00:00-05'),
partman_test.time_taptest_table_p20241123 FOR VALUES FROM ('2024-11-23 00:00:00-05') TO ('2024-11-24 00:00:00-05'),
partman_test.time_taptest_table_p20241124 FOR VALUES FROM ('2024-11-24 00:00:00-05') TO ('2024-11-25 00:00:00-05'),
partman_test.time_taptest_table_p20241125 FOR VALUES FROM ('2024-11-25 00:00:00-05') TO ('2024-11-26 00:00:00-05'),
partman_test.time_taptest_table_p20241126 FOR VALUES FROM ('2024-11-26 00:00:00-05') TO ('2024-11-27 00:00:00-05'),
If you try to insert data for 2024-12-25, that will be outside the existing child partition boundaries, you will get an error, and the data is lost.
INSERT INTO partman_test.time_taptest_table (col3) VALUES ('2024-12-25'::date);
ERROR: no partition of relation "time_taptest_table" found for row
DETAIL: Partition key of the failing row contains (col3) = (2024-12-25 00:00:00-05).
Adding a DEFAULT partition is very easy:
CREATE TABLE partman_test.time_taptest_table_default PARTITION OF partman_test.time_taptest_table DEFAULT;
\d+ partman_test.time_taptest_table
Partitioned table "partman_test.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (col3)
Partitions: partman_test.time_taptest_table_p20241118 FOR VALUES FROM ('2024-11-18 00:00:00-05') TO ('2024-11-19 00:00:00-05'),
partman_test.time_taptest_table_p20241119 FOR VALUES FROM ('2024-11-19 00:00:00-05') TO ('2024-11-20 00:00:00-05'),
partman_test.time_taptest_table_p20241120 FOR VALUES FROM ('2024-11-20 00:00:00-05') TO ('2024-11-21 00:00:00-05'),
partman_test.time_taptest_table_p20241121 FOR VALUES FROM ('2024-11-21 00:00:00-05') TO ('2024-11-22 00:00:00-05'),
partman_test.time_taptest_table_p20241122 FOR VALUES FROM ('2024-11-22 00:00:00-05') TO ('2024-11-23 00:00:00-05'),
partman_test.time_taptest_table_p20241123 FOR VALUES FROM ('2024-11-23 00:00:00-05') TO ('2024-11-24 00:00:00-05'),
partman_test.time_taptest_table_p20241124 FOR VALUES FROM ('2024-11-24 00:00:00-05') TO ('2024-11-25 00:00:00-05'),
partman_test.time_taptest_table_p20241125 FOR VALUES FROM ('2024-11-25 00:00:00-05') TO ('2024-11-26 00:00:00-05'),
partman_test.time_taptest_table_p20241126 FOR VALUES FROM ('2024-11-26 00:00:00-05') TO ('2024-11-27 00:00:00-05'),
partman_test.time_taptest_table_default DEFAULT
Now when we try and insert the data that failed before, it succeeds and we can see it is in the default table.
INSERT INTO partman_test.time_taptest_table (col3) VALUES ('2024-12-25'::date);
INSERT 0 1
SELECT * FROM partman_test.time_taptest_table_default;
col1 | col2 | col3
--------+-------+------------------------
«NULL» | stuff | 2024-12-25 00:00:00-05
(1 row)
Constraints with partition tables
The constraint on a normal partition is as you’d expect it to be, showing the lower and upper bounds.
keith@keith=# \d partman_test.time_taptest_table_p20241124
Table "partman_test.time_taptest_table_p20241124"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------------
col1 | integer | | |
col2 | text | | | 'stuff'::text
col3 | timestamp with time zone | | not null | now()
Partition of: partman_test.time_taptest_table FOR VALUES FROM ('2024-11-24 00:00:00-05') TO ('2024-11-25 00:00:00-05')
If we look at the default partition, we see that the constraint set up is not so simple.
\d+ partman_test.time_taptest_table_default
Table "partman_test.time_taptest_table_default"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition of: partman_test.time_taptest_table DEFAULT
Partition constraint: (NOT ((col3 IS NOT NULL) AND (((col3 >= '2024-11-18 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-19 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-19 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-20 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-20 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-21 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-21 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-22 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-22 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-23 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-23 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-24 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-24 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-25 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-25 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-26 00:00:00-05'::timestamp with time zone)) OR ((col3 >= '2024-11-26 00:00:00-05'::timestamp with time zone) AND
(col3 < '2024-11-27 00:00:00-05'::timestamp with time zone)))))
The constraint of a default partition in PostgreSQL can basically be thought of as an anti-constraint of all the other currently existing partitions. When a new partition is added, that anti-constraint is automatically updated to account for the new partition’s boundaries.
But what happens if we try to add a new partition that matches data in the default?
CREATE TABLE partman_test.time_taptest_table_p20241225 PARTITION OF partman_test.time_taptest_table FOR VALUES FROM ('2024-12-25') TO ('2024-12-26');
ERROR: updated partition constraint for default partition "time_taptest_table_default" would be violated by some row
We get a constraint violation because there is already data in the default partition that would match the new partition’s boundaries. PostgreSQL cannot allow there to be two possible partition routes for the same values.
Moving default data to a new child table
Because of these constraint violations, we must develop a process to be able to keep our data and get it moved to the proper partition: remove that data from the default partition, add the new child partition, then reinsert the data back via the parent so the data routes to the new partition. Thanks to PostgreSQL’s transactional DDL, this can all be done in a single transaction making it transparent to your users.
Here is an example of moving the data from the default to a new partition in a single transaction.
BEGIN;
CREATE TEMP TABLE clean_default_temp (LIKE partman_test.time_taptest_table_default);
WITH partition_data AS (
DELETE FROM partman_test.time_taptest_table_default RETURNING *
)
INSERT INTO clean_default_temp (col1, col2, col3) SELECT col1, col2, col3 FROM partition_data;
CREATE TABLE partman_test.time_taptest_table_p20241225 PARTITION OF partman_test.time_taptest_table FOR VALUES FROM ('2024-12-25') TO ('2024-12-26');
WITH partition_data AS (
DELETE FROM clean_default_temp RETURNING *
)
INSERT INTO partman_test.time_taptest_table (col1, col2, col3) SELECT col1, col2, col3 FROM partition_data;
DROP TABLE clean_default_temp;
COMMIT;
SELECT * FROM partman_test.time_taptest_table_default ;
col1 | col2 | col3
------+------+------
(0 rows)
SELECT * FROM partman_test.time_taptest_table;
col1 | col2 | col3
--------+-------+------------------------
«NULL» | stuff | 2024-12-25 00:00:00-05
(1 row)
SELECT * FROM partman_test.time_taptest_table_p20241225 ;
col1 | col2 | col3
--------+-------+------------------------
«NULL» | stuff | 2024-12-25 00:00:00-05
(1 row)
Large amounts of data in the default
This above example was rather simple for just a single row. However, if you have a large amount of data in the default, this could cause a noticeable disturbance to your users since these rows that are being moved will be locked until the transaction commits. This can be done in smaller batches, but to stay completely transparent to your users, the smallest transactional batch you could do would be the interval size of the partition set, in this case 1 day. You could do it in smaller transactional batches, but that would have to be done to a permanent table that you’re moving the data to and that data would be inaccessible to your users via their normal means. This is because you cannot add that new child partition until ALL the data that would go into it has been removed from the default. However, this isn’t even the most serious problem with data going into the default.
The way that PostgreSQL is able to tell you that you cannot add that new child partition is because at the time you try and attach one, PostgreSQL does a scan of the entire default partition to see if the new child partition’s boundaries match any data there. Even if you have an index on the default, PostgreSQL is going to have to scan the entire table, and most likely be using a costly sequential scan anyway. This means the lock obtained on the parent table to add a partition is held for the duration of the attach command’s transaction. If you’ve got billions of rows, this could possibly take minutes or even longer. This is why it is critical to keep an eye on any data going into any default partition table and move or remove it as soon as possible.
pg_partman
pg_partman is an open source extension for managing partitioning in PostgreSQL and adds several features to PostgreSQL’s built-in, declarative partitioning including automatically creating child partitions, including a default partition for every partitioned table set.
pg_partman check default
The pg_partman extension has a utility to check the default table for rows with the check_default() function. Let’s say we have 4 rows in our default table. Passing no parameters to this function will do a full count on all default partitions of all partition sets managed by pg_partman and return how many rows it found in each partition set.
SELECT * FROM partman.check_default();
default_table | count
-----------------------------------------+-------
partman_test.time_taptest_table_default | 4
However, if you pass false to this function, it will not do a full count and simply return a 1 if even a single row is encountered in any default partition (using a LIMIT 1 clause). This usage of the function can be used for regular monitoring of your partition sets.
SELECT * FROM partman.check_default(false);
default_table | count
-----------------------------------------+-------
partman_test.time_taptest_table_default | 1
Default row cleanup with pg_partman
The pg_partman procedure partition_data_proc() will automatically clean up your default partition. This procedure does the same steps shown above for native partitioning: moving the data to a temporary table, creating the necessary child tables based on the data found, then moving the data back.
Let’s look at our default partition with 4 rows of data.
select * from partman_test.time_taptest_table_default;
col1 | col2 | col3
--------+-------+------------------------
«NULL» | stuff | 2024-12-25 00:00:00-05
«NULL» | stuff | 2024-12-26 00:00:00-05
«NULL» | stuff | 2024-12-27 00:00:00-05
«NULL» | stuff | 2024-12-28 00:00:00-05
When we call the function partition_data_proc(), it commits after each child partition is created. If you do not give this procedure a source table, it assumes you are moving data out of the default partition for the given partition set.
CALL partman.partition_data_proc('partman_test.time_taptest_table');
NOTICE: Loop: 1, Rows moved: 1
NOTICE: Loop: 2, Rows moved: 1
NOTICE: Loop: 3, Rows moved: 1
NOTICE: Loop: 4, Rows moved: 1
NOTICE: Total rows moved: 4
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
Notice that the last line of advice is very important to ensure the statistics for your partition set have been updated and old rows cleaned up properly.
VACUUM ANALYZE partman_test.time_taptest_table;
Gaps in child partitions
Now we can see that the new child partitions have been made, the data has been moved to them, and the default partition is empty.
\d+ partman_test.time_taptest_table
Partitioned table "partman_test.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (col3)
Foreign-key constraints:
"fk_test" FOREIGN KEY (col2) REFERENCES partman_test.fk_test_reference(col2)
Partitions: partman_test.time_taptest_table_p20241118 FOR VALUES FROM ('2024-11-18 00:00:00-05') TO ('2024-11-19 00:00:00-05'),
partman_test.time_taptest_table_p20241119 FOR VALUES FROM ('2024-11-19 00:00:00-05') TO ('2024-11-20 00:00:00-05'),
partman_test.time_taptest_table_p20241120 FOR VALUES FROM ('2024-11-20 00:00:00-05') TO ('2024-11-21 00:00:00-05'),
partman_test.time_taptest_table_p20241121 FOR VALUES FROM ('2024-11-21 00:00:00-05') TO ('2024-11-22 00:00:00-05'),
partman_test.time_taptest_table_p20241122 FOR VALUES FROM ('2024-11-22 00:00:00-05') TO ('2024-11-23 00:00:00-05'),
partman_test.time_taptest_table_p20241123 FOR VALUES FROM ('2024-11-23 00:00:00-05') TO ('2024-11-24 00:00:00-05'),
partman_test.time_taptest_table_p20241124 FOR VALUES FROM ('2024-11-24 00:00:00-05') TO ('2024-11-25 00:00:00-05'),
partman_test.time_taptest_table_p20241125 FOR VALUES FROM ('2024-11-25 00:00:00-05') TO ('2024-11-26 00:00:00-05'),
partman_test.time_taptest_table_p20241126 FOR VALUES FROM ('2024-11-26 00:00:00-05') TO ('2024-11-27 00:00:00-05'),
partman_test.time_taptest_table_p20241225 FOR VALUES FROM ('2024-12-25 00:00:00-05') TO ('2024-12-26 00:00:00-05'),
partman_test.time_taptest_table_p20241226 FOR VALUES FROM ('2024-12-26 00:00:00-05') TO ('2024-12-27 00:00:00-05'),
partman_test.time_taptest_table_p20241227 FOR VALUES FROM ('2024-12-27 00:00:00-05') TO ('2024-12-28 00:00:00-05'),
partman_test.time_taptest_table_p20241228 FOR VALUES FROM ('2024-12-28 00:00:00-05') TO ('2024-12-29 00:00:00-05'),
partman_test.time_taptest_table_default DEFAULT
But, you will notice that we now have a gap between Nov 26 and Dec 25th.
pg_partman will only make new partitions based on the NEWEST partition and row data, in this case Dec 28th. It will not automatically fill in gaps to avoid potentially expensive automatic maintenance operations. However in many cases, you will be expecting data for these missing child partitions and will want to fill in the gaps.
pg_partman has a utility that you can run manually to do this: partition_gap_fill.
SELECT * FROM partman.partition_gap_fill('partman_test.time_taptest_table');
partition_gap_fill
--------------------
28
After running this, PostgreSQL returns the number of partitions that were created and, as you can see below, we now have a daily partitioned set fully covered from Nov 18, 2024 to Dec 28, 2024.
\d+ partman_test.time_taptest_table
Partitioned table "partman_test.time_taptest_table"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
col1 | integer | | | | plain | | |
col2 | text | | | 'stuff'::text | extended | | |
col3 | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (col3)
Foreign-key constraints:
"fk_test" FOREIGN KEY (col2) REFERENCES partman_test.fk_test_reference(col2)
Partitions: partman_test.time_taptest_table_p20241118 FOR VALUES FROM ('2024-11-18 00:00:00-05') TO ('2024-11-19 00:00:00-05'),
partman_test.time_taptest_table_p20241119 FOR VALUES FROM ('2024-11-19 00:00:00-05') TO ('2024-11-20 00:00:00-05'),
partman_test.time_taptest_table_p20241120 FOR VALUES FROM ('2024-11-20 00:00:00-05') TO ('2024-11-21 00:00:00-05'),
partman_test.time_taptest_table_p20241121 FOR VALUES FROM ('2024-11-21 00:00:00-05') TO ('2024-11-22 00:00:00-05'),
partman_test.time_taptest_table_p20241122 FOR VALUES FROM ('2024-11-22 00:00:00-05') TO ('2024-11-23 00:00:00-05'),
partman_test.time_taptest_table_p20241123 FOR VALUES FROM ('2024-11-23 00:00:00-05') TO ('2024-11-24 00:00:00-05'),
partman_test.time_taptest_table_p20241124 FOR VALUES FROM ('2024-11-24 00:00:00-05') TO ('2024-11-25 00:00:00-05'),
partman_test.time_taptest_table_p20241125 FOR VALUES FROM ('2024-11-25 00:00:00-05') TO ('2024-11-26 00:00:00-05'),
partman_test.time_taptest_table_p20241126 FOR VALUES FROM ('2024-11-26 00:00:00-05') TO ('2024-11-27 00:00:00-05'),
partman_test.time_taptest_table_p20241127 FOR VALUES FROM ('2024-11-27 00:00:00-05') TO ('2024-11-28 00:00:00-05'),
partman_test.time_taptest_table_p20241128 FOR VALUES FROM ('2024-11-28 00:00:00-05') TO ('2024-11-29 00:00:00-05'),
partman_test.time_taptest_table_p20241129 FOR VALUES FROM ('2024-11-29 00:00:00-05') TO ('2024-11-30 00:00:00-05'),
partman_test.time_taptest_table_p20241130 FOR VALUES FROM ('2024-11-30 00:00:00-05') TO ('2024-12-01 00:00:00-05'),
partman_test.time_taptest_table_p20241201 FOR VALUES FROM ('2024-12-01 00:00:00-05') TO ('2024-12-02 00:00:00-05'),
partman_test.time_taptest_table_p20241202 FOR VALUES FROM ('2024-12-02 00:00:00-05') TO ('2024-12-03 00:00:00-05'),
partman_test.time_taptest_table_p20241203 FOR VALUES FROM ('2024-12-03 00:00:00-05') TO ('2024-12-04 00:00:00-05'),
partman_test.time_taptest_table_p20241204 FOR VALUES FROM ('2024-12-04 00:00:00-05') TO ('2024-12-05 00:00:00-05'),
partman_test.time_taptest_table_p20241205 FOR VALUES FROM ('2024-12-05 00:00:00-05') TO ('2024-12-06 00:00:00-05'),
partman_test.time_taptest_table_p20241206 FOR VALUES FROM ('2024-12-06 00:00:00-05') TO ('2024-12-07 00:00:00-05'),
partman_test.time_taptest_table_p20241207 FOR VALUES FROM ('2024-12-07 00:00:00-05') TO ('2024-12-08 00:00:00-05'),
partman_test.time_taptest_table_p20241208 FOR VALUES FROM ('2024-12-08 00:00:00-05') TO ('2024-12-09 00:00:00-05'),
partman_test.time_taptest_table_p20241209 FOR VALUES FROM ('2024-12-09 00:00:00-05') TO ('2024-12-10 00:00:00-05'),
partman_test.time_taptest_table_p20241210 FOR VALUES FROM ('2024-12-10 00:00:00-05') TO ('2024-12-11 00:00:00-05'),
partman_test.time_taptest_table_p20241211 FOR VALUES FROM ('2024-12-11 00:00:00-05') TO ('2024-12-12 00:00:00-05'),
partman_test.time_taptest_table_p20241212 FOR VALUES FROM ('2024-12-12 00:00:00-05') TO ('2024-12-13 00:00:00-05'),
partman_test.time_taptest_table_p20241213 FOR VALUES FROM ('2024-12-13 00:00:00-05') TO ('2024-12-14 00:00:00-05'),
partman_test.time_taptest_table_p20241214 FOR VALUES FROM ('2024-12-14 00:00:00-05') TO ('2024-12-15 00:00:00-05'),
partman_test.time_taptest_table_p20241215 FOR VALUES FROM ('2024-12-15 00:00:00-05') TO ('2024-12-16 00:00:00-05'),
partman_test.time_taptest_table_p20241216 FOR VALUES FROM ('2024-12-16 00:00:00-05') TO ('2024-12-17 00:00:00-05'),
partman_test.time_taptest_table_p20241217 FOR VALUES FROM ('2024-12-17 00:00:00-05') TO ('2024-12-18 00:00:00-05'),
partman_test.time_taptest_table_p20241218 FOR VALUES FROM ('2024-12-18 00:00:00-05') TO ('2024-12-19 00:00:00-05'),
partman_test.time_taptest_table_p20241219 FOR VALUES FROM ('2024-12-19 00:00:00-05') TO ('2024-12-20 00:00:00-05'),
partman_test.time_taptest_table_p20241220 FOR VALUES FROM ('2024-12-20 00:00:00-05') TO ('2024-12-21 00:00:00-05'),
partman_test.time_taptest_table_p20241221 FOR VALUES FROM ('2024-12-21 00:00:00-05') TO ('2024-12-22 00:00:00-05'),
partman_test.time_taptest_table_p20241222 FOR VALUES FROM ('2024-12-22 00:00:00-05') TO ('2024-12-23 00:00:00-05'),
partman_test.time_taptest_table_p20241223 FOR VALUES FROM ('2024-12-23 00:00:00-05') TO ('2024-12-24 00:00:00-05'),
partman_test.time_taptest_table_p20241224 FOR VALUES FROM ('2024-12-24 00:00:00-05') TO ('2024-12-25 00:00:00-05'),
partman_test.time_taptest_table_p20241225 FOR VALUES FROM ('2024-12-25 00:00:00-05') TO ('2024-12-26 00:00:00-05'),
partman_test.time_taptest_table_p20241226 FOR VALUES FROM ('2024-12-26 00:00:00-05') TO ('2024-12-27 00:00:00-05'),
partman_test.time_taptest_table_p20241227 FOR VALUES FROM ('2024-12-27 00:00:00-05') TO ('2024-12-28 00:00:00-05'),
partman_test.time_taptest_table_p20241228 FOR VALUES FROM ('2024-12-28 00:00:00-05') TO ('2024-12-29 00:00:00-05'),
partman_test.time_taptest_table_default DEFAULT
Summary
- PostgreSQL does not make any child partitions automatically, including a default partition. If you’re using partitioning, it is recommended to have a default partition to catch mistakes in application code or in child partition creation. However it is very important to monitor the contents of those default partitions.
- pg_partman manages automatically creating child partitions for you, including the default partition if desired. The
check_defaultfunction can help you monitor the contents of default partitions. - If rows are found in the default, it is important to ensure these are reviewed ASAP. If the rows are invalid, they can simply be deleted. If they are important, devise a process to move them to the proper child partitions. pg_partman’s
partition_data_proccan assist with this.
The default partition is an incredibly useful tool to ensure you do not lose important data that may not be covered by existing child partitions. If you see data frequently going into the default partition, I highly advise reviewing your partition maintenance to ensure it is keeping up with the window of data that is regularly being ingested into that partition set. If the necessary child partitions always exist, you will have the best performance with the least maintenance.
Related Articles
- Smarter Postgres LLM with Retrieval Augmented Generation
6 min read
- Postgres Partitioning with a Default Partition
16 min read
- Iceberg ahead! Analyzing Shipping Data in Postgres
8 min read
- PostGIS Day 2024 Summary
8 min read
- Crunchy Data Warehouse: Postgres with Iceberg for High Performance Analytics
8 min read