Slow Zabbix? How to scale PostgreSQL for high data volume

The invisible bottleneck: why does Zabbix lag under heavy metrics?

As IT infrastructure grows, the volume of metrics collected per second explodes. Thousands of servers, routers, and services constantly send CPU, memory, and network traffic data. Zabbix, one of the most popular monitoring tools on the market, stores this avalanche of information in a relational database, usually PostgreSQL. In practice, this means the database history table receives millions of new rows every day, turning into a massive monster that is hard to manage.

Over the weeks, this single table grows so large that the database gets lost. Running a simple search for yesterday's memory usage chart requires scanning gigabytes or terabytes of unorganized data. Hard drives struggle with excessive read and write operations, known as I/O, and the Zabbix web interface starts responding with annoying slowness or even blank error screens. In this critical scenario, system administrators realize that a default installation cannot handle the load and that it is time to scale the architecture.

What is table partitioning and how does it solve the problem?

To solve extreme slowness without buying astronomical servers, the best strategy is table partitioning. In practice, partitioning means taking that gigantic history table and slicing it into dozens or hundreds of smaller tables separated by time intervals, such as days or weeks. Instead of keeping all company documents in one giant, disorganized box, you create separate folders for each day of the month. When the system needs to look up last Tuesday's history, it goes straight to Tuesday's folder, ignoring everything else.

In modern PostgreSQL, declarative partitioning makes this job much easier. The database creates a parent table that acts as an intelligent facade, while the actual data is stored in child tables behind the scenes. For Zabbix, nothing changes in how data is inserted or read, but behind the scenes, performance changes dramatically. When Zabbix needs to delete old data to free up space, the deletion process stops being an expensive operation and becomes the instant drop of an entire table, saving processing power and avoiding application freezes.

How to configure PostgreSQL partitioning for Zabbix history

Before applying any structural changes in production, make a complete backup of your database to avoid unpleasant surprises. The conversion process requires careful planning and should be executed during a low-activity maintenance window. The safest approach involves migrating older tables to the new date-partitioned structure while ensuring Zabbix continues writing without abrupt interruptions.

Below is a practical example of how to create a range-partitioned table in PostgreSQL to store numeric history data:

CREATE TABLE history (    itemid bigint NOT NULL,    clock integer NOT NULL,    value numeric(20,4) NOT NULL,    ns integer NOT NULL) PARTITION BY RANGE (clock);

Next, you need to create specific partitions for the desired periods, such as days or weeks, ensuring the database knows exactly where to put each incoming record from the Zabbix collector.

CREATE TABLE history_y2023m12 PARTITION OF history    FOR VALUES FROM ('1701408000') TO ('1703999999');

This division ensures that recent queries read only active partitions in memory, drastically accelerating response times on monitoring screens. To complement this implementation in production environments, see our related technical analyses on How to create a secure corporate tunnel using RustDesk and Network lagging? How to configure QoS and fq_codel.

Fine-tuning PostgreSQL caches for high performance

Beyond slicing data with partitioning, database performance depends directly on how much data it can keep in RAM. PostgreSQL has internal parameters controlling memory usage for caching and sorting, and default factory values are usually conservative, designed for modest computers rather than enterprise servers. Adjusting these parameters is the secret to preventing the database from querying physical disks on every click.

The shared_buffers parameter, for example, defines how much RAM PostgreSQL can use to store frequently accessed data blocks. On a dedicated Zabbix server, it is recommended to allocate about 25% to 40% of the machine's total memory for this buffer. Another crucial parameter is effective_cache_size, which tells the query planner how much memory the operating system has available for file caching. When properly configured, the database delivers complex queries almost instantly, drastically reducing hardware load.

Combining these database optimizations with advanced visualization tools and intelligent alerts ensures your infrastructure remains agile and resilient. To deepen your observability strategies, check out our guide on how to build predictive alerts in Grafana with Zabbix data. With a solid, partitioned, and well-cached foundation, monitoring stops being a burden and becomes the stability pillar of your enterprise environment.