Repository files navigation

These functions are designed to create and maintain partitions in PostgreSQL with a minimal impact on the applicaton. The priority is to not impact the application. When multiple options are available the weakest lock possible is being used, when a heavy lock is required we use a timeout to prevent long lasting locks.

Every function in this project starts with a detailed comment on what the function does and how to use it.

We strongly encourage you to contribute to our repository. Find out more in ourcontribution guidelines

All function have been tested on postgres 13.

All function will be installed in the DBA schema. If you don't have this schema yet, create it by running sql/schema/schema.sql.

You can add the individual functions directly on the database frompsql with the\i command. Usepsql to login on your database and run

\i<fullpath to the function>.<filename>.sql

N.B. Some functions use other functions and the scriptpartition_maintenance.sql requires a set of tables to be created.

To create all the functions and the tables required to configure maintenance apply the following scripts in order from the root directory of the projectsql/schema/schema.sqlsql/tables/tables.sqlsql/functions/create_all_functions.sql

To test all functions run the following scripts from the project root foldertest/tables.sqltest/run_functions.sqltest/configuration.sqltest/cleanup.sql

The functions can be called as any other postgres function

select dba.<function_name>(arg1, arg2, ..., argN);

The functions in this project can

• Partition an existing table. The origional table will not partitioned itself, but becomes the first partition
• Add indexes to a partitioned table and all children
• Add foreign keys to a partitioned table and all children
• Add date constraints to a table partitioned on an integer column
• Count the number of available, unused partitions
• Get the details for the last partition
• Add new partitions to a partitioned table. The new partitions will have the same properties as the latest available partition
• Detach partitions from a partitioned table
• Drop detached partitions

Besides all the functions the project also contains the scriptpartition_maintenance.sql. This scripts requires two tables being created

• dba.partition_configuration
• dba.detached_partitions

The scripts performs the following tasks based on the configuration in the tabledba.partition_configuration.

• Add new partitions
• Add date constraints
• Detach partitions
• Drop detached partitions after a cool-down period

See the documentation within sql/tables/tables.sql for the configuration details.

If you have a feature request, or spotted a bug or a technical problem, create a GitHub issue.

MIT license. For more information, see the LICENSE file.