Redgate Flyway

Partitioning for PostgreSQL

FlywayDesktop v8.0.2 brought about support for declarative partitioning in PostgreSQL. This allows you to save and generate migrations for partitioned tables and partitions.

A partitioned table is a table divided into smaller, more manageable pieces called partitions such that each row exists in exactly one partition. Partitioned tables become a virtual entity with no storage, with real tables attached (these are partitions) which hold the data. This allows for benefits like enhanced query performance when scanning data from the relevant partition/s instead of the whole partitioned table or quicker bulk insertion/removal of data.

In FlywayDesktop, we can now detect if your database contains partitioned tables and partitions, and it appropriately models them as such, picking up on the "PARTITIONED BY" and "PARTITION OF" clauses.

In the schema model view, the hash_measurement table is appropriately recognised by FlywayDesktop as a hash partitioned table 


If you already had checked in a partitioned table which was not recognised as such in FlywayDesktop prior to v8.0.2, we will still be able to track it and craft the necessary script. The two following images capture this scenario with the first of these showing the schema model view where we notice that in the old schema model, "to_become_partitioned" was just a regular table but now FlywayDesktop accurately recognises it as a list partitioned table. The second of these images shows the script generated by Flyway Desktop to make your target database contain "to_become_partitioned" as a list partitioned table instead of a regular table:

In the schema model view, to_become_partitioned has been recognised as a table that used to be in the schema model as a regular table but is now picked up as a list partitioned table

Generated script for regular table to partitioned table conversion


One thing to note is that while we offer support for all the partitioning methods – RANGE, LIST, and HASH, we have chosen to filter out RANGE partitions (but keeping the RANGE partitioned tables). This is because we understand that customers tend to create RANGE partitions automatically and on a schedule, perhaps monthly or weekly via scripts run at the chosen frequency. As a result of this filtering, we have also chosen to filter out any objects the RANGE partitions themselves may have (eg: an index on a RANGE partition). If required, you can use an include filter to show the RANGE partitions. For more information on how this filtering is done, please refer to our filtering documentation at Tutorial - Update PostgreSQL and MySQL filters

Sub-partitioning

Sub-partitions, which are partitions of partitions, are also supported in FlywayDesktop:

In the schema model view, subpartitioning_1 is a sub-partition (a partitioned table which is also a partition) which is picked up as such by FlywayDesktop


Didn't find what you were looking for?