PG Partition Manager v2.0.0 – Background Worker, Better Triggers & Extension Versioning Woes
PG Partition Manager has been the most popular project I’ve ever done a significant amount of work on and I really appreciate everyone’s feedback for the roughly 2 years it’s been out there. I’ve got plenty more ideas for development and features and look forward to being able to move forward on them with this new major version released.
PostgreSQL 9.3 introduced the ability for user created, programmable background workers (BGW). 9.4 then introduced the ability to dynamically start & stop these with an already running cluster. The first thing that popped into my mind when I heard about this was hopefully having some sort of built-in scheduling system. There still hasn’t been a generalized version of anything like this, so in the mean time I studied the worker_spi contrib module. This is a very simple BGW example with a basic scheduler that runs a process with a configurable interval. This is basically all pg_partman needs for partition maintenance, and what required an external scheduler like cron before.
pg_partman_bgw.dbname
* Required. The database(s) that `run_maintenance()` will run on. If more than one, use a comma separated list. If not set, BGW will do nothing.
pg_partman_bgw.interval
* Number of seconds between calls to `run_maintenance()`. Default is 3600 (1 hour).
* See further documenation on suggested values for this based on partition types & intervals used.
pg_partman_bgw.role
* The role that `run_maintenance()` will run as. Default is "postgres". Only a single role name is allowed.
pg_partman_bgw.analyze
* Same purpose as the p_analyze argument to `run_maintenance()`. Set to 'on' for TRUE. Set to 'off' for FALSE. Default is 'on'.
pg_partman_bgw.jobmon
Same purpose as the p_jobmon argument to `run_maintenance()`. Set to 'on' for TRUE. Set to 'off' for FALSE. Default is 'on'.
The above are the new postgresql.conf options that pg_partman v2.0.0 can use to control the background worker it now comes with. These options can be changed at any time with a simple reload, but to start the BGW requires a restart of the database. This is my first venture into writing C, so it’s still very simplistic, and essentially does nothing different than an external scheduler would do. But now you no longer need that external scheduler if you don’t require calling run_maintenance() on any individual partition sets!
The BGW is completely optional and the Makefile has an option to not compile it if you’d just like to stick with the plain, plpgsql version of the extension.
Another major change with v2 is the contents of the partitioning trigger function. If you look back at my first post about pg_partman, you’ll see me mention there are two possible methods to writing the trigger: static & dynamic. You can read that post for more details. All you need to know now is that there are no longer distinct static & dynamic partitioning types. The different trigger methods have been combined into one trigger (thanks to a suggestion from Andrew Dunstan). An example below from the daily partitioning unit test:
keith=# \d+ partman_test.time_taptest_table
Table "partman_test.time_taptest_table"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------+------------------------+----------+--------------+-------------
col1 | integer | not null | plain | |
col2 | text | | extended | |
col3 | timestamp with time zone | not null default now() | plain | |
Indexes:
"time_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
time_taptest_table_part_trig BEFORE INSERT ON partman_test.time_taptest_table FOR EACH ROW EXECUTE PROCEDURE partman_test.time_taptest_table_part_trig_func()
Child tables: partman_test.time_taptest_table_p2015_06_03,
partman_test.time_taptest_table_p2015_06_04,
partman_test.time_taptest_table_p2015_06_05,
partman_test.time_taptest_table_p2015_06_06,
partman_test.time_taptest_table_p2015_06_07,
partman_test.time_taptest_table_p2015_06_08,
partman_test.time_taptest_table_p2015_06_09,
partman_test.time_taptest_table_p2015_06_10,
partman_test.time_taptest_table_p2015_06_11
Has OIDs: yes
keith=# \sf partman_test.time_taptest_table_part_trig_func
CREATE OR REPLACE FUNCTION partman_test.time_taptest_table_part_trig_func()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
v_count int;
v_partition_name text;
v_partition_timestamp timestamptz;
BEGIN
IF TG_OP = 'INSERT' THEN
v_partition_timestamp := date_trunc('day', NEW.col3);
IF NEW.col3 >= '2015-06-07 00:00:00-04' AND NEW.col3 < '2015-06-08 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_07 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-06 00:00:00-04' AND NEW.col3 < '2015-06-07 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_06 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-08 00:00:00-04' AND NEW.col3 < '2015-06-09 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_08 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-05 00:00:00-04' AND NEW.col3 < '2015-06-06 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_05 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-09 00:00:00-04' AND NEW.col3 < '2015-06-10 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_09 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-04 00:00:00-04' AND NEW.col3 < '2015-06-05 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_04 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-10 00:00:00-04' AND NEW.col3 < '2015-06-11 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_10 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-03 00:00:00-04' AND NEW.col3 < '2015-06-04 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_03 VALUES (NEW.*);
ELSIF NEW.col3 >= '2015-06-11 00:00:00-04' AND NEW.col3 < '2015-06-12 00:00:00-04' THEN
INSERT INTO partman_test.time_taptest_table_p2015_06_11 VALUES (NEW.*);
ELSE
v_partition_name := partman.check_name_length('time_taptest_table', 'partman_test', to_char(v_partition_timestamp, 'YYYY_MM_DD'), TRUE);
SELECT count(*) INTO v_count FROM pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name;
IF v_count > 0 THEN
EXECUTE 'INSERT INTO '||v_partition_name||' VALUES($1.*)' USING NEW;
ELSE
RETURN NEW;
END IF;
END IF;
END IF;
RETURN NULL;
END $function$
You can see that the trigger function starts out like the static method, explicitly giving the SQL statements for inserting into each child table that it handles. But then the final condition falls back to using the dynamic method to first see if the relevant child table exists and then inserting it there (otherwise it goes to the parent). So this hybrid trigger now allows the performance benefit of the static method and the flexibility of the dynamic method, only losing performance when you try and insert data outside of the premake range. The time-custom type still exists and still uses the lookup-table method from v1, so nothing has changed there. This new hybrid function is only available for the standard time intervals and serial partitioning.
For those updating from the v1.x.x version of pg_partman, this new trigger function will be put in place automatically the next time a new child table is created for a partition set. If you’d like to convert all your trigger functions immediately, see the v2.0.0 extension update file for some simple plpgsql that can do that for you. I couldn’t make updating the trigger functions part of the extension update itself because doing so would then make all the trigger functions part of the extension (that was a fun accident to run into during testing).
There have been some other fixes to the extension as well, but these are the biggest and most relevant to most users. Please see the CHANGELOG for more info.
I’ve also taken this time to make pg_partman only compatible with PostgreSQL 9.4+. It uses the dynamic BGW feature, so I couldn’t go with 9.3. And while you can compile the extension without the BGW feature, I did include some new code that is only compatibly with 9.2+. I know this will inconvenience some users, but with the way extension versions are managed within the database, I couldn’t see any easy way to maintain both a v1 & v2 branch. For every new v1 release I’d make, I’d also have to maintain a separate update file to get that version to v2 as well as having to go back and update every previous v1 -> v2 update to include any relevant changes for people sticking with v1 that want to change to v2 in the future. I just don’t have that kind of time for extension management and I could see it very easily leading to a mess if I didn’t find a way to keep all that versioning straight. I will still fix bugs in v1, but all new feature development will be on v2 only. Drawing the line at 9.4 also gives me the freedom to use the latest and greatest features in Postgres and not have to compromise for backward compatibility, at least for the near future.
Upgrading to 9.4 is definitely worth the time to do if you’d like to keep using this extension easily and get the new features it will have in the future. If you’re 2 or 3 major versions behind (or more) and are curious why upgrading is worth the time, I’ll be giving an updated version of my Don’t Forget the Elephant talk at this year’s PGOpen in Dallas, Tx.