2

Is there a way to exclude tables from being added to the shared_buffers?

I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment. There are some tables which we only write to but don't need to have fast read-access at the moment. For a few tables we would like to keep the latest rows in the memory. If we could only mark these tables to be added to the shared_buffer that would allow us to use the memory on the machine much more efficiently.

3

There is no stock way to micromanage shared buffers that way. And in my experience is not a very fruitful area of effort.

I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment.

Are you looking at shared_buffers itself (with pg_buffercache), or are you looking at the on-disk sizes of the tables?

Append-only tables should occupy a minimal amount of shared_buffers, as only pages which have been written to recently will be present. Once a page is full and so longer written to, it should age out pretty quickly. For example on a large pgbench run, pages from pgbench_history (an append-only log table) occupy less than 1% of the buffers. You could argue that it should age out even more quickly and take even less of the shared_buffers, but it hard to believe that it would really make a material difference.

For a few tables we would like to keep the latest rows in the memory

If they are used frequently (and why else would you want to keep them in memory?), they should stay in memory on their own. If they get shoved out of memory overnight by reporting/housekeeping tasks, maybe you can write some warm-up queries which specifically probes those rows, and schedule those warm-up queries to run right before your analysts start showing up in the morning.

Also keep in mind that shared_buffers is usually a smallish fraction of RAM, and PostgreSQL makes heavy use of the file-system cache, not just of its own shared_buffers. But the same arguments mostly apply to this as well, except that pg_buffercache can't give you any insight into it.

2
  • I am looking at the tables with pg_buffercache. I saw that append only were de-prioritized but some of them had so many updates that they would still occupy most of the shared_buffer. We would really need a solution to remove some tables completely from the buffer - just removing them every 24hrs is not enough I think.
    – jcfrei
    Aug 25 at 21:52
  • Append-only means INSERT. UPDATE is always going to need to read in the page containing the tuple which is getting updated. If the tuples getting updated are randomly scattered over very large tables, that means every update needs to read a different page. Maybe you can get the UPDATEs to happen in a more predictable order, like if you have updates to do in bulk you could pre-sort the by a key column and apply them in order. Or use partitioning to group frequently updated rows together.
    – jjanes
    Aug 25 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.