How big is the "buffer" in PostgreSQL

Question

I use the pg_buffercache module to find pigs eating my RAM cache. For example, when I run this query:

 SELECT c.relname, count(*) AS buffers FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = c.relfilenode AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) GROUP BY c.relname ORDER BY 2 DESC LIMIT 10; 

I found that sample_table uses 120 buffers.

How much is 120 buffers in bytes?

Answer 1

PostgreSQL has a hard-coded block size of 8192 - see the predefined variable block_size . Previously, this was a number that you need to remember when you edited the configuration to specify shared_buffers , etc., But the config now supports suffixes such as MB , which will do the conversion for you.

Perhaps in hard work, you can change block_size to other values. For a smaller number of applications, there may be a more optimal size, but the number of places in which the code assumes the size is large.

Answer 2

According to what Edmund said, we can make this choice in our schema database:

 SELECT c.relname, Pg_size_pretty(Count(*) * 8192) 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 Pg_relation_size(c.oid) > 0 GROUP BY c.oid, c.relname ORDER BY 3 DESC LIMIT 10;