It is good practice to regularly inspect the statements running in the hot path of your Postgres instance. One way to do this is to examine the pg_stat_statements view, which shows various statistics about the SQL statements executed by the Postgres server.
Among many other interesting columns, you will find the shared_blks_dirtied and shared_blks_written columns. They keep track of how many blocks, Postgres' smallest unit of I/O, in the shared buffer cache are modified by a statement and (ultimately) written to disk.
One observation you may make when inspecting the view is that SELECT queries show non-zero values in the shared_blks_written and shared_blks_dirtied columns. This seems counterintuitive at first as it indicates that a simple SELECT query, which you would expect to only read data, can in fact cause data to be written (keeping track of the statement statistics themselves is not what causes these writes).
This article discusses two mechanisms, setting hint bits for transaction state tracking and page pruning, that can cause writes by read-only statements. Before we can go into detail about these mechanisms themselves, however, we first have to understand how Postgres organises table data and manages concurrency.
Read more...