Hi Jeff, thanks for the insight.
<< And then the next question would be, once they are in the cache, why don't they stay there? For that you would have to know what other types of activities are going on that might be driving the data out of the cache.
>>
To give you an idea of the activity level, each physical machine hosts multiple DB’s with the same structure – one DB per client.
We run automated ETL processes which digests client feeds (E) normalizes them (T) and then stores them in our DB (L).
Looking at the stats from our audit log, the average feed load is 4 hours, divided up into 14 client sessions. Each session averages about 50 write (update, insert, no deletes) operations per second, representing 700 write operations per second. The ratio of reads per write is pretty high as the system goes through the transformation process.
Since I don’t know how this compares to other PG installations, the question of using periodic REINDEX and CLUSTER brings up these questions:
1) Because we are hosting multiple DB’s, what is the impact on OS and disk caches?
2) Is there an automated CLUSTER and REINDEX strategy that will not interfere with normal operations?
3) By PG standards, is this a busy DB - and does explain why the general caches expire?
Thanks,
Carlo