Busk: Created page with "``` Partitioning is the definitive answer when you have a large amount of hosts. When I was hitting about 500 VPS I was able to avoid partitioning on PgSQL running this script..."

2020-11-06T09:53:09Z

Created page with "``` Partitioning is the definitive answer when you have a large amount of hosts. When I was hitting about 500 VPS I was able to avoid partitioning on PgSQL running this script..."

New page

```
Partitioning is the definitive answer when you have a large amount of hosts. When I was hitting about 500 VPS I was able to avoid partitioning on PgSQL running this script every hour:

Code:
#!/bin/sh
( flock -n 9 || exit 1
psql -U postgres zabbix -c 'DELETE FROM trends_uint t WHERE ctid IN ( SELECT t.ctid FROM trends_uint t LEFT JOIN items i ON i.itemid = t.itemid WHERE to_timestamp(t.clock) < (current_date - ((i.trends)::interval)) LIMIT 10000);'
psql -U postgres zabbix -c 'DELETE FROM history_str h WHERE ctid IN ( SELECT h.ctid FROM history_str h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 10000);'
psql -U postgres zabbix -c 'DELETE FROM history_text h WHERE ctid IN ( SELECT h.ctid FROM history_text h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 100000);'
psql -U postgres zabbix -c 'DELETE FROM history h WHERE ctid IN ( SELECT h.ctid FROM history h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 300000);'
psql -U postgres zabbix -c 'DELETE FROM history_uint h WHERE ctid IN ( SELECT h.ctid FROM history_uint h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 500000);'
) 9>/var/lock/00-zabbix-history-purge.lock
It basically purge data from the largest tables which is expired according to the item history settings.
```

Zabbix Maintenance - Revision history

Busk: Created page with "``` Partitioning is the definitive answer when you have a large amount of hosts. When I was hitting about 500 VPS I was able to avoid partitioning on PgSQL running this script..."