PostgreSQL Shared Buffer, What Backend Developers Should Know

For us as Backend Developers it is important to find the optimal amount for shared_buffers depending on the machine resources.

What you are going to read in this article:

• What is PostgreSQL Shared Buffers
• Proper amount for Shared Buffers
• How to check if queries are checking Shared Buffers or Not?
• pgbuffercache
• pginspect
• conclusion

What is PostgreSQL Shared Buffer?

Shared Buffer is referenced as Database Cache Size in lots of places. Which is not correct! Shared Buffer is not a simple cache!

To understand what it is we need to take a look at PostgreSQL architecture for a moment:

PostgreSQL Internal Architecture

When you are sending a read or write request to Postgres, you are never interacting with the files directly!!!!

In order to read something, first those pages need to be loaded in shared_buffers! Very important.

Even if you are writing something, Postgres has to load those pages to the shared_buffers first, and then it writes the changes to those pages in memory, and marks those pages as Dirty. At a later point Postgres is going to flush your changes to the disk, in other words synchronizes pages to the disk. This makes writes much much faster than interacting with the disk each time we have a write. Also Postgres uses WAL(Write ahead Log) to prevent data loss. Appending to a file is considered a straightforward and fast operation and it is not slowing down the writes, also there is WAL buffers that batches these operations.

So Shared_Buffer is not just cache, it is the vital part of the architecture, we can only read from Shared_Buffers, we can only write to Shared_Buffers. Postgres itself has a process to write those Dirty Pages to disk. If you do not know what is a page in the Database check this link. Page is In simple words it is a unit of storing items in Postgres.

The Disk itself also has a caching system, if you request some parts of the disk more often, SSD for example is going to put those files in SSD Cache and you can access them much much faster(SSD Storages with DRAM has this capability).

Proper Amount for Shared Buffer

• Default Value: 128MB (Postgres 17)
• Minimum Value: 128KB (Postgres 17)
• It is recommended in the docs: If (RAM > 1GB) shared_buffers = 25%
• Official Docs: Values larger than 40% of RAM might NOT Help

If you have enough RAM, you could go with 25% choice and see how many percent of queries are hitting the cache.

After changing the Shared_Buffer we have to restart the Server!

In your Postgres Config file you can have something like this:

shared_buffers = 2GB

The point here is that the 25% percent is a rule of thumb, and should work, but the more important thing to do is to check out the percentage of Shared_Buffer hit.

You can use this query to find out:

SELECT
    sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0) AS cache_hit_ratio
FROM
    pg_stat_database;

You get a number between 0 and 1, if the number is near 1, it means most queries are returned from Shared_Buffer and not disk.

If you are using AWS RDS, in the Monitoring tab, you already have this value:

Buffer Cache Hit Ratio in AWS RDS Monitoring Tab

How to Check If Queries are using Shared_Buffer or not?

As always our main tool is explain.

Let’s create a table and put some random data in it:

Create TABLE test_temp_users (
 id serial primary key, 
 username varchar(50) NOT NULL,
 email VARCHAR(100) NOT NULL,
 password VARCHAR(100) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now let’s create some random users:

INSERT INTO test_temp_users (username, email, password)
SELECT
  'user' || gs, -- Generates usernames like user1, user2, ..., user1000
  'user' || gs || '@example.com', -- Generates emails like user1@example.com, user2@example.com, ..., user1000@example.com
  md5(random()::text) -- Generates random passwords
FROM
  generate_series(1, 1000000) AS gs;

Also run analyze, so Postgres get all the needed statistics for better queries:

ANALYZE test_temp_users;

Let’s run this query:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test_temp_users WHERE id > 1000000 AND id < 1000008;

This is the result:

Explain Buffers

It was able to get everything from shared_buffers.

I have tried a few other queries and in this one, as we can see, we had to read some pages from the disk.

Reading from the Disk and also Cache

Obviously if I run the same thing again, I get all pages from Shared_Buffers

Second Time Running the Query

Let’s check out size of our table:

SELECT pg_size_pretty(pg_total_relation_size('test_temp_users')) AS size;

Get Size of the Table

Table size is 126MB, We can check out our buffer size using this command:

SHOW shared_buffers;

in my case it was 128MB, so if this is the only table in our DB, after a while all queries will be from the Shared_Buffer. Let’s reduce the shared_buffer and restart the server. I am going to set it to 200KB.

putting this in postgresql.conf: shared_buffers = 200kB

Double check your shared_buffers after the restart. Now let’s do the queries again:

We are running the same query twice. First time it gets it from the disk, second time as expected from shared buffers. We are getting records between the range of id of 30000 and 301008.

Now if we change the query to get the records with id between range of 40000 and 401008, and then go back to the same query, we again get the results from the disk, because our shared_buffers is too low.

If we aim for higher amounts of rows, that exceeds the shared_buffers, we always get data from the disk(we do not get it from the shared_buffers directly, but as we explained it has to be shipped to the shared_buffers):

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test_temp_users WHERE id > 300000 AND id < 302000;

If the records size are bigger than shared_buffers we get them from the disk

PG_BUFFERCACHE

This is a very useful extension for observation of shared_buffers items. use this command to add this extension to the instance:

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

Now let’s get rows of this table using this query:

SELECT bufferid, relblocknumber, isdirty, usagecount FROM pg_buffercache LIMIT 10;

pg_buffercache Table

• BufferId is the unique identifier of a buffer.
• relblocknumber is the page number of table.
• isDirty: if we have written something on the page or not.
• usageCount: number of hits on the specific buffer.

We can sort the results based on usagecount, before doing this I am going to execute this query multiple times:

SELECT * FROM test_temp_users WHERE id > 300000 AND id < 300005;

Sorted Results in Shared_Buffers

Get Table Name in PG_Buffercache Results

You can use this query:

SELECT                                  
  b.bufferid,
  b.relfilenode,
  b.reltablespace,
  b.reldatabase,
  b.relblocknumber,
  c.relname,
  c.relkind,
  n.nspname AS schemaname
FROM
  pg_buffercache b
JOIN
  pg_class c
ON
  b.relfilenode = pg_relation_filenode(c.oid)
JOIN
  pg_namespace n
ON
  c.relnamespace = n.oid
WHERE
  b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY
  b.bufferid;

Now you can see that each buffer belongs to which table(relation).

Buffers and Table Name

Page Inspect

to enable this extension on Postgres you have to run this command:

CREATE EXTENSION IF NOT EXISTS pageinspect;

From the previous query, we have the relblocknumber. We have to find the records

SELECT
  lp,
  t_data::text
FROM
  heap_page_items(get_raw_page('test_temp_users', 0));

This is a sample of result:

Raw Data

The next step is to decode the raw data. We have the size of each column, so we can decode this data:

First data is this one:

\x010000000d7573657231257573657231406578616d706c652e636f6d4335306631323739376263343238336436303737333136653162323361396233310000004b9a78bb10cb0200

Data is in a format called Little Endian Format:

So for finding the id for example, we can use this simple code in Nodejs:

const { Buffer } = require("node:buffer");
function parse(hexData) {
    const binaryData = Buffer.from(hexData, "hex");
    let offset = 0;
    // Parse the `id` (serial integer, 4 bytes, little-endian)
    const id = binaryData.readInt32LE(offset);
    offset += 4;
    console.log(id);
}

We see 1 in the logs, the same applies to other columns, for some of them that have type of VARCHAR, we see the length of string first, and we can naviagate data.

Conclusion

Shared Buffer is a layer between the disk and backend processes. For setting the Shared Buffer we can go with the suggestions of documentation: if RAM is bigger than 1GB, we can go with 20–25 %, otherwise we should use lower percentages. For making sure that our Shared Buffer is efficient, we can use some queries to checkout how many of the queries are returned from Shared Buffer. If we want to find out what is exactly in the shared buffer, we can use Postgres extensions like pgbuffercache and pginspect.

[embed]F.25. pg_buffercache - inspect PostgreSQL buffer cache state F.25. pg_buffercache - inspect PostgreSQL buffer cache state # F.25.1. The pg_buffercache View F.25.2. The…www.postgresql.org

https://chatgpt.com/c/66ffe77d-d414-8012-a2c0-86d2b9b6908e

[embed]

[embed]

[embed]shared_buffers Best practices for using the shared_buffers parameter to optimize PostgreSQL database performance in Amazon RDS and…docs.aws.amazon.com

Neon Handling of Cache

[embed]What is the downside to increase shared buffer in PostgreSQL I've noticed a significant performance drop when data is not loaded into shared_buffer when querying PostgreSQL, the…stackoverflow.com

[embed]Determining the optimal value for shared_buffers using the pg_buffercache extension in PostgreSQL |… In OLTP databases, the buffer cache is memory allocated for storing cached data and dirty pages. Cached data speeds up…aws.amazon.com

[embed]

[embed]

[embed]

[embed]F.25. pg_buffercache - inspect PostgreSQL buffer cache state F.25. pg_buffercache - inspect PostgreSQL buffer cache state # F.25.1. The pg_buffercache View F.25.2. The…www.postgresql.org