Is trigger-based partitioning still necessary in pg_partman?

[keithf4]

Throwing this question out to the community... is there still a need for trigger-based partitioning with the time & integer-based partitioning that pg_partman supports?

Just trying to get a sense of usage cases that may still be out there. With 9.6 dropping out of community support during the next major release cycle, all supported major versions of PostgreSQL will have native partitioning built in. It seems to me that the most recent versions of PG have most cases covered where trigger-based partitioning used to be needed. Also, the template system in pg_partman seems to be able to cover most cases where native is still missing a feature (unique indexes, unlogged tables, table storage options, etc).

I'm asking this since the trigger-based partitioning is a large, and rather complicated part of pg_partman and if it's not necessary anymore, I would like to see about possibly dropping it in a future major version release.

Thanks!

[keithf4]

To continue trying to spur discussion here, a tentative plan is now in place to drop trigger-based partitioning from pg_partman upon the EOL date of PostgreSQL version 10 which is currently set to November 10, 2022 (https://www.postgresql.org/support/versioning/). This target is chosen due to PG11's fairly robust partitioning support compared to PG10 and to give another year+ from this point to see if any further considerations need to be made to continue supporting trigger-based partitioning in PostgreSQL.

[keithf4]

Note that removal of trigger-based partitioning does possibly mean that pg_partman does not have to rely solely on the suffix to manage pre-making and retention. Could then allow child tables that don't follow pg_partman's naming convention since pg_partman would instead look directly at the catalogs to determine if a child table needs to be created/dropped.

[keithf4]

November 10th has been reached, and PG10's support ended, with no use-cases brought up to continue supporting trigger-based partitioning.

Plans are now in place to begin work on removing trigger-based partitioning as part of version 5.x of pg_partman.

[andyatkinson]

@keithf4 Removing it in a new version while still supporting an older version for a period of time makes sense to me.

Note a similar discussion for pgslice ankane/pgslice#45

[keithf4]

I'll support bug fixes in the old version for a limited time. Unfortunately supporting multiple versions of extensions, while still keeping them upgradable between those versions, is very challenging so I won't be able to do it for long.

[lhopcroft123]

I believe I have one use-case for trigger based partitioning, unless I'm missing a better way that someone might suggest.

If there is a very large (hundreds of GB, billions of rows) table of time series data that currently isn't partitioned and we want to partition it (say monthly), assuming we want to use native/declarative partitioning we have two options according to doc/pg_partman_howto_native.md, that is offline and online partitioning.

Offline partitioning

We can't use offline partitioning because as the docs say:

the data is not accessible to both the new and old table from a single object

i.e. clients that are currently set up to query the table will (once the new partitioned table is set up and the name switched over) only see data that has already been migrated. Data that is yet to be migrated will not be accessible and migration could take a long time.

We might get around this by duplicating data between the old and new tables, and then only switching the names over once the migration is complete (and then dropping the old table) but this has the disadvantage of duplicating a large table and requiring lots of storage (cost).

Online partitioning

Online partitioning allows us to turn the existing table in to the default partition of a new partitioned table, this allows us to partition the data with minimal downtime and is largely transparent to end users, but is not really a workable solution either for a few reasons:

You DO NOT want to leave data in the default partition set for any length of time and especially leave any significant amount of data. [...] it has to check EVERY entry in the default which can take quite a long time even with an index if there are billions of rows. During this check, there is an exclusive lock on the entire partition set.

Also,

The partition_data_proc() can handle moving the data out of the default. However, it cannot move data in any interval smaller than the partition interval when moving data out of the DEFAULT.

We might have lots of data for any given month/partition, so we can't really migrate such a large amount of data in one go.

Proposed use case using trigger based partitioning

In order to migrate the existing table to use partitions, I think a possible way might be the following, which uses trigger based partitioning (at least until the migration is complete).

1. Set up trigger based partitioning as per doc/pg_partman_howto_triggerbased.md
2. Use a before update trigger to migrate data accross to the partitioned tables in chunks of arbitrary size as required as per Cybertec blog post

Basically, this involves altering the existing table (and its partitions) to add a new column:

ALTER TABLE my_original_table ADD COLUMN in_partition BOOL;

Then add a trigger (similar to the insert trigger created by pg_partman for inserting data in to partitions instead of the parent table) but one that fires BEFORE UPDATE. This then does an insert/upsert on the appropriate table partition, and deletes from the parent table inside the trigger function. We can migrate a chunk of data by updating rows in the parent table e.g. by setting in_partition=true, and we can check migration progress by counting the proportion of the table rows WHERE in_partition vs total number of rows.

3. Once the migration is complete, we can drop the BEFORE UPDATE trigger and the in_partition column.
4. Now the parent table is empty, we can migrate from trigger based to native partitioning as per doc/migrate_to_native.md

[keithf4]

I will say that offline partitioning's intention is that the entire process is offline. If the data needs to be available at any point during the migration, that is considered an online migration. At least as far as pg_partman is going to interpret the meanings of offline vs online.

The proposal to use a trigger to help with migration is a great idea! But I think that using it as a migration method is just that and I don't see a need to keep around the entire trigger-based partitioning mechanism just for that. Perhaps could see about making a specialized trigger-based migration process based on existing trigger-based methods.

One thing I do want to work on after 5.x is out is a process to change interval sizes on existing partition sets. This could make migration easier by starting off with a smaller partition interval to allow smaller batches of data to move. Then migrate to a larger interval. I'm hoping that will help mitigate some of the issues.

Will keep this in mind! Thank you!