# Optimize Shared Buffers

PostgreSQL is an open source, object-relational database built for extensibility, data integrity, and speed. Its concurrency support makes it fully ACID-compliant, and it supports dynamic loading and catalog-driven operations to let users customize its data types, functions, and more.

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](https://en.wikipedia.org/wiki/Cache_replacement_policies#Least_Recently_Used_%28LRU%29) 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:

```sql
SHOW shared_buffers;
```

## Check Cache Hit Rate

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

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/runtime-config-resource.html) 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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.