John Moran wrote:
Is postgreSQL intelligent enough to discern that since the most frequently accessed data is invariably recent data, that it should store only that in memory, and efficiently store less relevant, older data on disk
When you ask for a database block from disk, it increments a usage count figure for that block when it's read into memory, and again if it turns out it was already there. Those requests to allocate new blocks are constantly decreasing those usage counts as they "clock sweep" over the cache looking for space that hasn't been used recently. This will automatically keep blocks you've used recently in RAM, while evicting ones that aren't.
The database doesn't have any intelligence to determining what data to keep in memory or not beyond that. Its sole notion of "relevant" is whether someone has accessed that block recently or not. The operating system cache will sit as a second layer on top of this, typically with its own LRU scheme typically for determining what gets cached or not.
I've written a long paper covering the internals here named "Inside the PostgreSQL Buffer Cache" at http://www.westnet.com/~gsmith/content/postgresql/ if you want to know exactly how this is all implemented.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
