postgres declare partitioning pg_partman - ghdrako/doc_snipets GitHub Wiki

Partition Manager(pg_partman)

It is simple and straight-forward time partitioning.

• https://github.com/pgpartman/pg_partman
• https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md
• https://www.percona.com/blog/partitioning-in-postgresql-with-pg_partman-serial-based-trigger-based/
• https://www.percona.com/blog/postgresql-partitioning-made-easy-using-pg_partman-timebased/

With declarative partitioning, much of the partitioning management is automated, but for example, creating new partitions still requires manual intervention—unless you're using tools like pg_partman. Pg_partman helps to automate the creation and management of partitioned tables and partitions hrough a SQL API. Although new partitions aren’t added and removed automatically, this can be managed by adding another extension like pg_cron to schedule jobs.

A few examples:

• It’s essential to ensure that the necessary partitions have been created when ingesting data to avoid a No Partition of Relation Found for Rowerror, which may block your writes.
• If your workload involves sporadic or irregular data ingestions, you’ll need to ensure you aren't creating excessive, unnecessary partitions, as they could degrade query performance and lead to table bloat.
• You must ensure that there are no gaps or overlaps between partitions, especially when dealing with manual partition modifications.
• If you want to implement a retention policy to regularly drop old partitions regularly, you'll need to set this up.
• If you need to alter the schema of your tables, such as adding or dropping columns, you'll often have to handle these changes manually to ensure they propagate correctly to all partitions.