How to tune shared_buffers in PostgreSQL

Right-sizing shared_buffers improves PostgreSQL performance by keeping frequently accessed table and index pages in memory, reducing disk reads and lowering query latency on busy systems.

shared_buffers sets the size of PostgreSQL's shared buffer cache, a shared memory area used by all backends to cache data pages. When a page is requested, PostgreSQL checks this cache before reading from storage, so a well-sized buffer cache reduces repeated I/O. The operating system also caches file data in its page cache, so the best value balances database caching and OS caching instead of maximizing one at the expense of the other.

Changes to shared_buffers apply only after a server restart because the shared memory segment is allocated at startup. Increasing it too aggressively can push the host into swapping or trigger shared memory allocation failures that prevent PostgreSQL from starting. Memory headroom must also cover connection overhead plus per-query memory settings such as work_mem, parallel query memory, and maintenance operations.

Steps to tune shared_buffers in PostgreSQL:

Check the current shared_buffers value.

$ sudo -u postgres psql -Atc "SHOW shared_buffers;"
128MB

Record the current buffer cache hit rate for a baseline.

$ sudo -u postgres psql -c "SELECT datname,
       blks_hit,
       blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_percent
FROM pg_stat_database
WHERE datname = current_database();"
 datname  | blks_hit | blks_read | hit_percent
----------+----------+-----------+-------------
 postgres |     4170 |       148 |       96.57
(1 row)

Use psql -d <db> to measure a specific database.

Check the host memory size to budget shared_buffers.

$ free -h
               total        used        free      shared  buff/cache   available
Mem:           7.7Gi       1.5Gi       1.7Gi        85Mi       4.7Gi       6.1Gi
Swap:          1.1Gi       4.0Ki       1.1Gi

Select a new shared_buffers value with headroom for OS page cache plus PostgreSQL per-query memory.

A conservative starting point on Linux is around 25% of RAM, adjusted for workload, connection count, and the need to retain OS cache for sequential reads.

Oversizing can trigger swapping or shared memory allocation errors during startup.
Set the new value using ALTER SYSTEM.
```
$ sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '512MB';"
ALTER SYSTEM
```
ALTER SYSTEM writes to postgresql.auto.conf in the data directory and requires superuser privileges.

Rollback uses ALTER SYSTEM RESET shared_buffers plus a restart.

Confirm the setting is marked as pending restart.

$ sudo -u postgres psql -Atc "SELECT pending_restart FROM pg_settings WHERE name = 'shared_buffers';"
t

Restart the PostgreSQL service to activate the new shared_buffers value.
```
$ sudo systemctl restart postgresql
```
Restarting terminates active connections and may extend downtime if crash recovery runs on startup.

The unit name may be versioned on some distributions (for example postgresql@15-main or postgresql-15).

Related: How to manage the PostgreSQL service with systemctl on Linux

Verify the active shared_buffers value after restart.

$ sudo -u postgres psql -Atc "SHOW shared_buffers;"
512MB

Check for swapping after the change.
```
$ swapon --show
NAME          TYPE       SIZE USED PRIO
/var/lib/swap file      1024M   4K   -2
/loop3        partition  128M   0B   -3
```
Sustained swap activity usually indicates memory pressure and can negate the benefits of a larger shared_buffers value.

Re-run the cache hit query after representative workload completes.

$ sudo -u postgres psql -c "SELECT datname,
       blks_hit,
       blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_percent
FROM pg_stat_database
WHERE datname = current_database();"
 datname  | blks_hit | blks_read | hit_percent
----------+----------+-----------+-------------
 postgres |     6399 |       248 |       96.27
(1 row)

Statistics counters reset on restart, so compare after enough traffic to represent normal load.