Postgres hit ratio: should big history tables be removed from the production database?
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)
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.