Monitoring PostgreSQL Memory Performance with Zabbix
A comprehensive implementation guide for database administrators and system engineers to monitor, analyze, and optimize PostgreSQL memory usage using the official Zabbix PostgreSQL templates.
Proper memory management in PostgreSQL is essential for optimal query performance and system stability. Under-allocating memory leads to high disk I/O, slow query responses, and frequent temporary file creation. Over-allocating memory can lead to severe system memory pressure, aggressive swap usage, or Out-Of-Memory (OOM) killer terminations.
This guide details how to leverage the PostgreSQL by Zabbix Agent 2 (or standard Zabbix Agent) template to gain full visibility into shared buffers, work memory, cache efficiency, and OS-level memory metrics.
To collect database-level memory metrics without over-privileging the monitoring service, create a dedicated, read-only monitoring user (zbx_monitor) in PostgreSQL.
Connect to your PostgreSQL cluster via psql as a superuser and execute:
-- Create dedicated monitoring user
CREATE USER zbx_monitor WITH PASSWORD 'your_secure_password';
-- Grant required permissions for Zabbix metrics collection
GRANT pg_monitor TO zbx_monitor;
Allow the Zabbix agent to connect locally or over the network by adding an entry to pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD
host postgres zbx_monitor 127.0.0.1/32 scram-sha-256
host postgres zbx_monitor ::1/128 scram-sha-256
Reload PostgreSQL configuration to apply changes:
SELECT pg_reload_conf();
Navigate to the Macros tab on the Host configuration page and set the required database parameters:
| Macro | Recommended Value | Description |
|---|---|---|
| {$PG.URI} | tcp://127.0.0.1:5432 | Connection URI or socket path for Postgres |
| {$PG.USER} | zbx_monitor | Dedicated Zabbix monitoring role |
| {$PG.PASSWORD} | your_secure_password | Password set for zbx_monitor |
| {$PG.DATABASE} | postgres | Default database for initial connection |
The Zabbix template collects key PostgreSQL and operating system memory metrics. Monitoring these items helps isolate buffer pool issues from work memory spillages.
| Performance Indicator | Zabbix Item Key | Target Benchmark | Metric Description |
|---|---|---|---|
| Buffer Cache Hit Ratio | pgsql.cache.hit[*] | > 95% | Percentage of read operations served from RAM (shared_buffers) rather than disk. |
| Temp File Generation | pgsql.db.stat.temp_bytes[*] | Minimal / Stable | Volume of data written to disk due to insufficient work_mem during sorts/hashes. |
| Shared Buffers Utilization | pgsql.buffers.alloc[*] | Continuous monitoring | Allocation rate of shared memory blocks for cached table and index data. |
| OS Memory Utilization | vm.memory.utilization | < 85% | Percentage of overall system RAM in use. |
| Swap Usage | system.swap.size[*,pfree] | > 80% free | Indicates system paging activity, which can severely degrade Postgres response time. |
To detect memory bottlenecks before service degradation occurs, configure custom or template-level triggers in Zabbix.
Use collected Zabbix metrics to guide postgresql.conf tuning decisions:
┌───────────────────────────────┐
│ Zabbix Metric Analysis │
└───────────────┬───────────────┘
│
┌───────────────────────────┴───────────────────────────┐
▼ ▼
[ Cache Hit Ratio < 90% ] [ High Temp Bytes / Disk Spills ]
│ │
▼ ▼
Increase `shared_buffers` Increase `work_mem`
(Up to 25% of Total System RAM) (Tune per session / query basis)
After applying new memory settings in PostgreSQL: