Optimize Shared Buffers

In Managed PostgreSQL, you can set the shared_buffers parameter to determine the amount of memory allocated for disk page caching, which can increase performance and prevent locking. The cache preserves memory over multiple simultaneous sessions that may want to access the same blocks at the same time.

The shared_buffers parameter allocates memory on startup across all sessions and users. Its optimal setting depends on the database cluster’s available RAM, its working data set, and its workload.

Recommendations for Shared Buffers

For good performance on databases with 1 GB or more of RAM, we recommend setting shared_buffers to around 25% of the cluster’s memory. For databases with less than 1 GB of RAM, we recommend less than 25%.

Raising the value of shared_buffers does not always improve performance because the remaining free memory goes to queries and the filesystem cache. Setting the value to more than 40% of the cluster’s memory is unlikely to give better performance, with rare exceptions.

For best performance, aim for a shared_buffers cache hit rate between 97% and 99%. If the rate falls below 95%, your cluster may be affected by one of the following:

• Too little data activity to generate accurate stats (new database).
• shared_buffers set too low.
• Working set too large to fit within the 60% maximum shared_buffers_percentage.

Check Metrics

Managed PostgreSQL automatically tracks access patterns, updates shared_buffers with frequently accessed data, and removes data based on the least recently used (LRU) algorithm This is because, on average, around 20% of the data accounts for 80% of access reads.

Check Shared Buffers

To check your current shared_buffers value, run the following command:

SHOW shared_buffers;

Check Cache Hit Rate

To check your current shared_buffers cache hit rate, run the following command:

SELECT * FROM pg_statio_user_tables;

Which returns the following table which lists the different types of blocks hit and read:

relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16415 | public     | records |        1042770 |      88157826 |        184280 |     40282404 |               0 |              0 |              0 |             0
(1 row)

Then, calculate the database cache hit rate with the following:

SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as hit_ratio
FROM
  pg_statio_user_tables;

Which returns the following table:

   heap_read | heap_hit |         ratio
  -----------+----------+------------------------
     6942770 | 88157826 | 0.9883098315
  (1 row)

To achieve an optimal performance, the working set needs to fit in shared_buffers. If the working set exceeds the size of shared_buffers, then Managed PostgreSQL can’t cache the excess content. While shared_buffers_percentage has a maximum value of 60%, exceeding a value of 40% suggests more RAM is required.

For more information on shared buffers, see Resource Consumption in the PostgreSQL documentation.

Buffer Cache Extension

For a deeper examination into the contents of the shared_buffers, to see how your specific workload and objects are cached, enable the pg_buffercache extension:

CREATE EXTENSION pg_buffercache;

Then, run the following query to view an organized table of how many database blocks from tables (r), indexes (i), sequences (S), and other objects are currently cached:

SELECT c.relname, c.relkind
  , pg_size_pretty(count(*) * 8193) as buffered
  , round(100.0 * count(*) / ( SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent
  , round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
WHERE c.oid >= 16384
AND pg_relation_size(c.oid) > 0
GROUP BY c.oid, c.relname
ORDER BY 3 DESC
LIMIT 10;

Which returns the following table, where relname is the block, relkind is its source, and buffered, buffers_percent, and percent_of_relation describe how much storage its taking up:

 relname | relkind | buffered | buffers_percent | percent_of_relation
---------+---------+----------+-----------------+---------------------
 records | r       | 781 MB   |            99.7 |                27.2
 ...

Relations with object IDs (oid) values below 16,384 are reserved system objects.

You can also view queries’ cache hit performance by running the following EXPLAIN query:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * from records;

Which returns the following output:

                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on example.table  (cost=0.00..480095.20 rows=11207220 width=77) (actual time=0.158..16863.051 rows=11600000 loops=1)
  Output: id, "timestamp", data
  Buffers: shared hit=92345 read=275678 dirtied=10938
Query Identifier: 2582883386000135492
Planning:
  Buffers: shared hit=30 dirtied=2
Planning Time: 1.081 ms
Execution Time: 17467.342 ms
(8 rows)

Using the hit and read numbers listed under Buffers, you can calculate the cache hit ratio using the formula hit / (hit + read), which in this example shows that about 25% of this full table scan was in the shared_buffers.

Manually Cache Data

You may want to prepare shared_buffers in anticipation of a specific workload, such as a large analytical query set used for reporting. You can do this with the pg_prewarm extension:

CREATE EXTENSION pg_prewarm;

After creation, call the pg_prewarm function and pass the name of the table of data you want to manually cache:

SELECT * FROM pg_prewarm('example.table');

Which returns the following output:

pg_prewarm
------------
     368023

 SELECT pg_size_pretty(pg_relation_size('example.table'));
 pg_size_pretty
 ----------------
 2875 MB

In this case, 368,023 pages have been read into the cache (or approximately 2,875 MB).

If the shared_buffers size is less than pre-loaded data, only the tailing end of the data is cached, as the earlier data encounters a forced ejection, terminating the session.