PG Partition Manager

PG Partition Manager (or pg_partman) can handle both time-based and serial-based partitions, and is a recommended tool for dealing with large data structures.

The pg_partman extension will simply split large tables into smaller pieces, for easier processing, alleviating CPU and memory consumption.

Once a table partition has been worked on, pg_partman will drop it to avoid memory overcrowding.

What is new in this release:

• If retention system is turned on, jobmon no longer logs entries if no retention work was actually done. Would previously just log that zero tables were dropped. If anything is dropped/uninherited, it will be logged as expected.
• Changed column "type" in part_config to "partition_type". "type" is a reserved word, but not currently strictly enforced (doesn't require double-quoting). This avoids any possible future issues. Also changed sub_type in part_config_sub to "sub_partition_type" for consistency
• Changed column "part_interval" in part_config & part_config_sub to "partition_interval" to be more consistent with above renamed column.
• Now uses new, more extensive GET STACKED DIAGNOSTIC feature added in 9.2 to provide more detailed errors when an exception is encountered. Previously when functions called other functions and a custom exception block was used, only the latest function called would report the error. Now a more full stack trace is available to see the original function that caused the error.
• Extensive documentation formatting improvements.
• A background worker process (BGW) has been added to pg_partman for general partition maintenance.
• There are no longer distinct "static" and "dynamic" partitioning modes. The features of each mode have been combined into a single trigger format.

What is new in version 2.0.0:

• If retention system is turned on, jobmon no longer logs entries if no retention work was actually done. Would previously just log that zero tables were dropped. If anything is dropped/uninherited, it will be logged as expected.
• Changed column "type" in part_config to "partition_type". "type" is a reserved word, but not currently strictly enforced (doesn't require double-quoting). This avoids any possible future issues. Also changed sub_type in part_config_sub to "sub_partition_type" for consistency
• Changed column "part_interval" in part_config & part_config_sub to "partition_interval" to be more consistent with above renamed column.
• Now uses new, more extensive GET STACKED DIAGNOSTIC feature added in 9.2 to provide more detailed errors when an exception is encountered. Previously when functions called other functions and a custom exception block was used, only the latest function called would report the error. Now a more full stack trace is available to see the original function that caused the error.
• Extensive documentation formatting improvements.
• A background worker process (BGW) has been added to pg_partman for general partition maintenance.
• There are no longer distinct "static" and "dynamic" partitioning modes. The features of each mode have been combined into a single trigger format.

What is new in version 1.8.4:

• New parent table name parameter to run_maintenance(). If set, skips all other tables for that maintenance run and only does the one given.
• New analyze parameter to run_maintenance().
• PG Partman now supports sub-partitioning. This allows automatic configuration to turn the child tables of an existing partition set into parent tables of their own partition sets.
• Analyze is no longer automatically run on the parent table after create_parent() is run.
• Added a --version argument to all python scripts. This tells you the minimum version of pg_partman this script is meant to work with.
• Made sure all scripts in bin folder are added to Makefile for installation.
• Make sure autovacuum is reset if SIGINT (Ctrl+C) is fired when using partition_data.py or undo_partition.py.
• Added howto.md file to doc folder with some more extensive examples.
• last_partition column in part_config table no longer in use. Dropped it.
• Renamed internal functions create_id_partition(), create_id_function(), create_time_partition() & create_time_function() to create_partition_id(), create_function_id(), create_partition_time() & create_function_time() respectively. This gives all functions a consistent naming pattern.

What is new in version 1.8.1:

• New parent table name parameter to run_maintenance(). If set, skips all other tables for that maintenance run and only does the one given.
• New analyze parameter to run_maintenance().
• PG Partman now supports sub-partitioning. This allows automatic configuration to turn the child tables of an existing partition set into parent tables of their own partition sets.
• Analyze is no longer automatically run on the parent table after create_parent() is run.
• Added a --version argument to all python scripts. This tells you the minimum version of pg_partman this script is meant to work with.
• Made sure all scripts in bin folder are added to Makefile for installation.
• Make sure autovacuum is reset if SIGINT (Ctrl+C) is fired when using partition_data.py or undo_partition.py.
• Added howto.md file to doc folder with some more extensive examples.
• last_partition column in part_config table no longer in use. Dropped it.
• Renamed internal functions create_id_partition(), create_id_function(), create_time_partition() & create_time_function() to create_partition_id(), create_function_id(), create_partition_time() & create_function_time() respectively. This gives all functions a consistent naming pattern.

What is new in version 1.7.2:

• Fixed bug in apply_foreign_keys() where new partition creation would fail when the partition set's schema is in the current search_path.
• Foreign key inheritance is now optional since more complex FK relationships may not work ideally with pg_partman's default method. New configuration option in part_config table and parameter to create_parent().

What is new in version 1.6.0:

• Clarify in docs that the id interval value passed to create_parent() must actually be in text type format.
• Changed drop & undo partition functions to use transaction based advisory locks.
• Removed need for internally used function create_next_time_partition() and therefore dropped the function.
• Simplified the create_time_partition() & create_id_partition() parameter lists.

What is new in version 1.5.1:

• Fixed create_parent() to actually insert the contraint_cols value passed into the function to the config table when using time based partitioning.

What is new in version 1.5.0:

• Check for valid parameter values in partition creation function.
• Added drop index concurrently option (--drop_concurrently) to reapply_indexes.py script. Only works for 9.2+.
• Changed run_maintenance() to use advisory transaction lock instead of session level lock.
• Fixed missing library import in python scripts.
• Organized documentation of functions.

What is new in version 1.4.0:

• New Python script (reapply-indexes.py) to re-apply indexes to child tables when they have changed on the parent. See docs for more info.
• New function to check the uniqueness of a column in a partition set (check_unique_column()). Helps to overcome the inability of a unique constraint to be applied efficiently across all partitions in a set. Does not prevent a unique violation, but provides a method to monitor for it happening.
• More pgTAP tests to ensure name trunucation process is working.
• Changed pgTAP tests to assume pgTAP is installed in public schema to try and avoid issues when it isn't.

What is new in version 1.1.0:

• New python scripts in extras folder to allow partition creation and undoing using smaller commit batches, as is suggested in the documentation for the partition_data_* and undo_partition_* functions. This helps avoid transaction locks when there is a large amount of data to move around. There are also options to commit more slowly and ease the load on very busy systems.
• Changed the ordering of batch arguments in partition_data_id() & partition_data_time(). This makes their order the same as the undo functions and is a more sensical order (I think anyway).
• Made partition functions quieter. No more notices and just returns number of rows moved.
• Changed the undo partition functions to remove partitions in the order they were originally created. They were doing it alphabetically before, which could cause an odd order for serial based partitioning (p100 would be before p2). Creation order may not remove them in ascending order of the data at first, which would be ideal, but it makes more sense than alphabetically.
• Bug fix: undo_partition() could return 0 prematurely if some of the partitions were empty. Will now automatically uninherit/drop any empty partitions and continue on if there are still child tables, not counting them against p_batch_count if given.

Requirements:

• PostgreSQL 9.1 or higher
• pg_jobmon 1.0 or higher