Partitioning PostgreSQL 9.1 on running environment

[shayani]

Hi all.

I have a Zabbix server, with 1 proxy (for now):

332 hosts
29055 items
348 NVPS

I use different servers for Zabbix server and PostgreSQL 9.1

The PostgreSQL server is a VM (in a cluster) with 4 CPUs, 8GB RAM and connected to a HP Storage (FC Disks, no SSD). The storage performance is OK for general use, but for PostgreSQL, is a bottleneck. The tables are getting huge and a simple Housekeeping (limited at MaxHousekeeperDelete=25) takes almost 1 hour. If I increase this value, god save me!!!

So, I read a lot about partitioning PostgreSQL to get a better performance, but is it possible to do it in a running environment? How to do it?

[f.koch]

hi,

you can take a look at https://github.com/keithf4/pg_partman

regards

[shayani]

Just to inform that I had great success with this technique: https://www.zabbix.org/wiki/Docs/how...topartitioning

[mrjoshuap]

hi,

you can take a look at https://github.com/keithf4/pg_partman

regards

Do you have any recommendations on using pg_partman? It seems to only work with id, not time based partitioning... Have you tried implementing it?

Thanks!

[Jason]

For time based i used this and it works rather well for us. https://www.zabbix.org/wiki/Docs/how...topartitioning

Not sure how it could be adapted for pre-existing data unless you used select into to move chunks of the data into partition tables manually?