Postgresql – Why does a SELECT statement dirty cache buffers in Postgres

cacheexplainpostgresql

I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output.
From the documentation, "dirtied" indicates:

The number of blocks dirtied indicates the number of previously
unmodified blocks that were changed by this query; while the number of
blocks written indicates the number of previously-dirtied blocks
evicted from cache by this backend during query processing.

This sounds to me like the process of marking a block dirty should only happen when updating data though. My query is a SELECT, however, and only reads data. I would imagine it would only report hits or reads. I am obviously mistaken. What exactly is happening in this situation, though?

Best Answer

This has a simple reason.

In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits.

There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.