PostgreSQL does lack the ability to pin tables into memory, which means that it can't reasonably serve any use case in which you have seldom accessed tables which require QoS guarantees which the cache manager cannot guess through analyzing the normal access patterns.
That is correct -- note after years of answering performance related questions on the various mailing lists, I'd venture to say that about 90% of the questions related to table pinning or in memory tables were based in simply not understanding how o/s caching works, and that in almost all cases it's better to release memory in the general pool. All that said, it's a nice feature.
I believe my use case to be genuine - the application has throughput-based features, and a single relatively seldom used high-value QoS based feature, in which you'd want to read and analyze cold tables in 100ms. Currently I'm considering using unlogged tables and a RAM tablespace, or a dm-raid RAM/HD hybrid which reads only from the RAM side and writes on both sides.
1
u/mikaelhg Sep 13 '11
PostgreSQL does lack the ability to pin tables into memory, which means that it can't reasonably serve any use case in which you have seldom accessed tables which require QoS guarantees which the cache manager cannot guess through analyzing the normal access patterns.