It happens that many empty partitions accumulate in the database, and the further you go, the slower zabbix works.


use DBI;
use File::Pid;

my $pidfile = File::Pid->new({file => '/run/'});
die "Already running: $pid\n" if $pid = $pidfile->running();

$dbh = DBI->connect("dbi:Pg:dbname=zabbix", "zabbix", "password", {AutoCommit=>1,RaiseError=>1,PrintError=>0}) || die "Database connection not made: $DBI::errstr";


my $sth = $dbh->prepare("SELECT table_name FROM information_schema.tables WHERE table_schema = 'partitions' ORDER BY table_name ASC");

$sth->execute() or die $DBI::errstr;

printf ("Number of rows found: %s\n", $sth->rows);
while (my @row = $sth->fetchrow_array()) {
    my ($table) = @row;
    printf ("Analyze: partitions.%s\n", $table);
    my $stm = $dbh->prepare("SELECT COUNT(*) FROM partitions.$table");
    $stm->execute() or die $DBI::errstr;
    @counter = $stm->fetchrow_array();
    next if $counter[0] > 0;
    printf ("Drop partition %s with rows %d\n", $table, $counter[0]);
    $dbh->do("DROP TABLE partitions.$table;");



Running it once a day will remove empty partitions and everything will work faster.