1

A database has these cache hit ratios:

table A: 0.006
table B: 0.955
table C: 0.023

Tables A and C are history tables. No relationship, large content and no need of fast queries, only few read requests. I looked for a feature to tell Postgres to ignore cache for these tables, in vain.

Are things as easy as if tables A and C are removed from the database, it will automatically increase the cache hit ratio for table B? (assuming the same amount of data)

1 Answer 1

2

PostgreSQL always caches pages it reads, there is no way to avoid that. Dropping tables A and C may improve the cache hit ratio for table B, but not by much, because there is not much room for improvement. Perhaps there are some parts of table B that are not in constant use.

It seems to me that PostgreSQL is already doing what you want it to do: pages from tables A and C drop out of the cache, and pages from B mostly stay in cache.

4
  • Simpler question: should I have as target to have 1 for all tables (except large history tables) ? I am using heroku as provider, and a diagnose command gives me a red flag for the 3 cache hit ratios given above. I have today 15go of RAM, and it is fully used. Should I havec to scale to have available RAM?
    – Slim
    Apr 13, 2022 at 10:21
  • 1
    What is your pain? Is the performance bad? If all that bothers you is a red flag in a monitoring system, relax. Apr 13, 2022 at 10:31
  • OK, no big pain for now, but the load is increasing progressively. Need to check if things will still be good few times later, so flags should be here for that? About the performances, it's not so good because of wrong choices (big jsonb..), we are working on that but new releases fixing that are not coming very soon.
    – Slim
    Apr 13, 2022 at 17:11
  • 1
    If I/O performance is your problem, then you are probably right to expect some gain, but it is hard to say how much that will be. Apr 13, 2022 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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