Docker postgres work_mem not using ram

Question

I have set shared_buffers to 256MB.

Using pg_buffercache extension I see that all of the buffers are in use:

SELECT pg_size_pretty(COUNT(*)*8192) as used FROM pg_buffercache;
 used
----------------
 256 MB

Now the problem is that when I use docker stats to view the database container memory it shows:

NAME CPU % MEM USAGE / LIMIT MEM % 
db 0.00% 31.07MiB / 1GiB 3.03%

Where is the shared_buffers memory stored? Shouldn't be in ram and displayed in docker stats?

Answer 1

This has nothing to do with work_mem, of course.

pg_buffercache has one row for every buffer slot, whether it has ever been used for anything or not. Use count(relfileode), not count(*), to count just the ones that are actually used.

I don't think there is any way with pg_buffercache to get the high water mark, the closest might be something like:

select max(bufferid) from pg_buffercache where relfilenode is not null;

But that will give too low an answer (for your purposes) if all the highest numbered bufferid belonged to a table that got dropped or truncated, for example.

• cdalxndr: Note that if I do a group by relfilenode: SELECT relfilenode::regclass::text, COUNT(*)*8192 FROM pse.pg_buffercache group by relfilenode::regclass::text I get usage by table and if I sum them I get the same issue, higher than reported ram usage.
• jjanes: GROUP BY doesn't remove NULLs, it just generates a group for them.