Shared Buffers:
Shared buffers is a PostgreSQL memory area used as a global cache to store frequently accessed data pages from tables and indexes, helping reduce disk I/O and improve performance.
All database sessions share this memory, so data once loaded can be reused by other queries quickly.
Identify the more shared buffer area is requreid with help of cache hit ratio:
SELECT blks_hit, blks_read,round(blks_hit::numeric / (blks_hit + blks_read), 4) AS hit_ratioFROM pg_stat_databaseWHERE datname = current_database();postgres=# SELECT blks_hit, blks_read,postgres-# round(blks_hit::numeric / (blks_hit + blks_read), 4) AS hit_ratiopostgres-# FROM pg_stat_databasepostgres-# WHERE datname = current_database(); blks_hit | blks_read | hit_ratio ----------+-----------+----------- 14758 | 431 | 0.9716(1 row)
Hit Ratio Meaning : 99% Good ✅ :90–99% Acceptable :< 90% Problem (disk reads high)
work_mem:
work_mem is a per-query memory setting used during operations like sorting, joins, and aggregations, allowing them to run in memory instead of disk.
If a query exceeds this memory, PostgreSQL spills data to temporary disk files, which slows down performance.
Identify work_mem bottleneck
-- Check Temp file usageSELECT datname, temp_files, pg_size_pretty(temp_bytes)FROM pg_stat_database;-- Find problemetic queriesSELECT temp_blks_written, queryFROM pg_stat_statementsWHERE temp_blks_written > 0ORDER BY temp_blks_written DESCLIMIT 10;
Steps to change the parameter
Login with adminuser
sudo -u postgres psql
Check the current memory setting in postgreSQL
# SHOW shared_buffers; shared_buffers ---------------- 32MB(1 row)# SHOW work_mem; work_mem ---------- 1MB(1 row)
Increase the value of Shared buffers
sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '256MB';"
Increase the value of Work_mem
sudo -u postgres psql -c "ALTER SYSTEM SET work_mem = '4MB';"
Note: ALTER SYSTEM writes to postgresql.auto.conf which overrides postgresql.conf settings.
Reload configuration without restart:
sudo -u postgres psql -c "SELECT pg_reload_conf();" pg_reload_conf ---------------- t(1 row)
Restart the PostgreSQL service because some parameter required full restart like shared buffer setting
sudo systemctl restart postgresql
Verify the changes:
sudo -u postgres psql -c "SHOW shared_buffers; SHOW work_mem;" shared_buffers ---------------- 256MB(1 row) work_mem ---------- 4MB(1 row)