Unexplained writes

There are several situations where you can find yourself executing statements that only read against the database, yet find significant write volume happening on the database disk.

Parts of the buffer cache must be dirty before writes happen, so if any of that work is left hanging around it could be involved. Flushing everything out with a manual checkpoint is one way to assure that's not the cause of the writes.

A second small source of writes are the access time updates many operating systems do every time you read from a file. It's suggested these get turned off in Chapter 4, Disk Setup, and the actual volume of writes from that is pretty low anyway.

If the volume of writes is substantial, and this data was recently created, what you're mostly likely to run into are updates to the hint bits in the database. Hint bits are two bits stored in each row that indicate whether that row's associated transaction has committed or rolled back. If they aren't set, the database's commit log data (in pg_clog and possibly pg_subtrans) has to be consulted to determine whether the row is visible. Once going to all that trouble, the row is then updated with the correct hint bit information, so that expensive computation doesn't have to be done again. The result is now a dirty page that needs to be written out again, even though all you did was read it.

Executing something that checks every row in a table, including a full table SELECT, COUNT, or running VACUUM against it, will perform this visibility check and write out final hint bits for each row. Until that's happened, expect some steady write activity consisting of hint bit updates any time you're selecting against recently committed data. See http://wiki.postgresql.org/wiki/Hint_Bits for more information about the internals involved.

Don't forget about some simpler causes for writing when executing queries too: autovacuum running in the background after being triggered by recent activity can be writing things; and if your query is doing a sort operation or using temporary tables that spill over to disk, that will cause writes too.