PostgreSql Query Caching Logic

Question

So I have database with 140 million entries. First Query takes around 500ms, repeating the query takes 8ms. So there is some caching.

I would like to learn more about how long this cache exists what are the conditions it's refreshed etc - time or number of new entries made to the table.

Adding ~1500 new entries to table still makes queries around 10ms. But adding 150k will reset the cache. So there could be both, or only transaction count.

Anyone has a good resource, so that I could get more information on it?

I made this gist to make the load tests.

The query results are not cached. It is the underlying table data which is cached. If you don't know that, then the answers you got might not make much sense. — jjanes
– jjanes, Commented Jan 28, 2020 at 16:51
@jjanes If the table is scanned then why are not all the queries as fast? In this case is the real table that's cached the index partition? — Mihkel L.
– Mihkel L., Commented Jan 28, 2020 at 21:51
The blocks of the underlying index data can also be cached, just like the blocks of the underlying table. — jjanes
– jjanes, Commented Jan 28, 2020 at 22:21

Laurenz Albe · Accepted Answer · 2020-01-28 15:46:23Z

PostgreSQL uses a simple clock sweep algorithm to manage the cache (“shared buffers” in PostgreSQL lingo).

Shared buffers are in shared memory, and all access to data is via this cache. An 8kB block that is cached in RAM is also called a buffer.

Whenever a buffer is used, its usage count is increased, up to a maximum of 5.

There is a free list of buffers with usage count 0. If the free list is empty, anybody who searches a "victim" buffer to replace goes through shared buffers in a circular fashion and decreases the usage count of each buffer they encounter. If the usage count is 0, the buffer gets evicted and reused.

If a buffer is "dirty" (it has been modified, but not written to disk yet), it has to be written out before it can be evicted. The majority of dirty buffers get written out during one of the regular checkpoints, and a few of them get written out by the background writer between checkpoints. Occasionally a normal worker process has to do that as well, but that should be an exception.

Collectives™ on Stack Overflow

PostgreSql Query Caching Logic

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related