New Site, New Partman

Thanks to some help from my co-workers Elizabeth and David Christenson and their son, I got my site migrated from Wordpress to Hugo! Being a DBA, you’d think I wouldn’t mind having a database backing my website, but the simplicity of managing a static site like Hugo was much more appealing at this point. With a new site that’s far easier to manage, I’m hoping that will motivate me to get back to writing new content on a regular basis.

And with that, my next big announcement is regarding my main project PG Partman. With PostgreSQL 10 now EoL, that means native partitioning is in much better shape for all supported versions. I had a question up on my Github for quite a while seeing if anyone had a good use-case remaining for trigger-based partitioning. With the only answer I got being “it would take work to migrate”, that told me it was time to drop support for it.

Is trigger-based partitioning still necessary in pg_partman?

This blog post is an official announcement of the beta release for version 5.0.0. Looking back I realized just how long I’d been working on this project. My first commit in Sept 2012 and the first tagged release being v0.1.1 on Dec 16, 2012. Over 10 years ago!

Initial commit

It was one of the first projects I really dug deep into learning PL/PGSQL and reviewing all this code it definitely shows at some points. So in addition to dropping trigger-based partitioning, I’m going back and refactoring a lot of code to clean it up and make it much more consistent. The minimum version of PostgreSQL is also now set to 14. While this is relatively more recent compared to the currently supported versions, it helped to make some of the code refactoring easier for the long term. Hopefully this also encourages everyone out there using PostgreSQL and PG Partman to upgrade their major version to something more modern!

This is a request to anyone out there that has some time to please test this beta release. Especially the migration from any version you are currently running to the new one. The child-table suffixes have been greatly simplified and shortened and the specialized ISO weekly and quarterly partitioning modes have been removed.

Another major change is that data that exists in the default is no longer considered when deciding to make new child tables. This means maintenance will fail if a new child table’s constraint would match data in the default.

=# select partman.run_maintenance();
ERROR:  updated partition constraint for default partition "time_taptest_table_default" would be violated by some row
CONTEXT: SQL statement "ALTER TABLE partman_test.time_taptest_table ATTACH PARTITION partman_test.time_taptest_table_p20230615 FOR VALUES FROM ('2023-06-15 00:00:00-04') TO ('2023-06-16 00:00:00-04')"

The only remedy for this is to migrate the data out to the proper child table (using the partition_data functions) or deleting it.

=# call partman.partition_data_proc('partman_test.time_taptest_table', 100);
NOTICE:  Loop: 1, Rows moved: 11
NOTICE:  Total rows moved: 11
NOTICE:  Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL

=# select partman.run_maintenance();
 run_maintenance 
-----------------
 
(1 row)

It made a little sense to account for that data when it existed in the parent with the old trigger-based partitioning, but for native partitioning it is critically important that data not build up in the default. Every time a new child table is created, the data in the default table must be fully scanned to see if it matches the new child table’s constraint. A lot of data in the default can significantly impact the performance of that operation, causing it to take minutes or longer, potentially locking table access.

So to quickly review the major changes coming so far:

• Minimum version of PostgreSQL 14
• Dropped trigger-based partitioning
• Completely dropped ISO weekly and quarterly partition
• Many functions have had their parameters altered, renamed or removed to be more consistent across the code-base.
• Simplified all time-based partition suffixes to either pYYYYMMDD for intervals greater than or equal to 1 day and pYYYYMMDD_HH24MISS for less than 1 day
• Data in the default is no longer considered when making new child tables during maintenance
• Template table is now optional

Please see the update file for a full list of changes and some important upgrade notes. I had hoped to add more features as part of this release, but I figured they would be easier to implement once the major code factor was done and would let me get this version out quicker.

Extensive documentation for migration operations has been provided. In addition to code testing, documentation reviews would be greatly appreciated.

• pg_partman.md - Main reference documentation
• pg_partman_howto.md - A step-by-step guide with examples on usage of pg_partman
• pg_partman_5.0.0_upgrade.md - A guide for special circumstances when upgrading to version 5.0.0. As of this post, this is just to help with the deprecated weekly and quarterly methods.
• migrate_to_native.md - A guide to migrating to native partitioning. This is a general guide, and not just for pg_partman
• migrate_to_partman.md - A guide to migrating an existing natively partitioned table to be maintained by pg_partman. Note that if your table is trigger-based, you will have to use the migrate_to_native.md process first.
• fix_missing_procedures.md - If you happened to be running PG Partman on a version of PostgreSQL older than 11, procedures were not supported so they were not installed. If you’ve since upgraded to a more recent version of PostgreSQL, those procedures may still be missing. Depending on the updates you’ve run, some may exist, but this document will provide a guide to ensuring all procedures exist.

To install version 5.0.0-beta, you will have to first install an older version and then upgrade. This just makes it easier for me to maintain code changes for the new version since they are all contained in the update file. The final release will require updating all relevant sql files as well and I’d rather not do that while there still may be extensive updates required before the final release. Also note that there will NOT be an upgrade path from the beta to the final 5.0.0.

The 5.0.0-beta branch is available here - https://github.com/pgpartman/pg_partman/tree/5.0.0-beta

=# CREATE EXTENSION pg_partman schema partman;
CREATE EXTENSION
=# \dx pg_partman
                             List of installed extensions
    Name    | Version | Schema  |                     Description                      
------------+---------+---------+------------------------------------------------------
 pg_partman | 4.7.3   | partman | Extension to manage partitioned tables by time or ID
(1 row)

=# ALTER EXTENSION pg_partman UPDATE TO '5.0.0-beta';
ALTER EXTENSION
=# \dx pg_partman
                               List of installed extensions
    Name    |  Version   | Schema  |                     Description                      
------------+------------+---------+------------------------------------------------------
 pg_partman | 5.0.0-beta | partman | Extension to manage partitioned tables by time or ID
(1 row)

Please feel free to open issues or discussions on the Github repo as normal if you run into any problems testing the beta. I’m hoping to have it ready for the end of June barring anything unforeseen!