Partition maintenance routines

[postilion]

Thanks for the great write up. Very useful. What's not clear to me, however, is just what sort of ongoing maintenance is required for partitions. For example, do I need to occasionally undertake the steps in Partition Maintenance: Creating Future Partitions or Deleting Old Partitions? Or if I do those steps, will that take care of automating it?

I apologize if these are silly questions, but I haven't done anything with partitions in ages, and have never used pg_partman.

Thanks again, -nic

[Doctorbal]

Excellent question Nic. And yes I can definitely see how the guide doesn't make it crystal clear that the pg_partman background worker process ensures that maintenance is ongoing. In the following step I mention to create a config file for pg_partman. Within that config file you will see the following pg_partman settings:

enable_partition_pruning = on
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = 'zabbix'
pg_partman_bgw.dbname = 'zabbix'
pg_partman_bgw.analyze = off
pg_partman_bgw.jobmon = on

These are the settings necessary (along with the shared_preload_libraries including pg_partman_bgw being there) that create and delete partitions. Thus it is automated! I hope this answers your question. Regards, Andreas

[postilion]

Andreas, Thanks! Just what I wanted to hear. BTW, I'm interested in your plans for PostgreSQL 12 and Timebase and such, as remarked in the other open issue. Good news! -nic

[Doctorbal]

@postilion you bet. Hopefully Azure supports version 12 with the TimescaleDB extension soon!