Dear Postgresql experts,
Looking at vmstat output on my database server I have been suprised to
see lots of disk writes going on while it is doing what should be
exclusively read-only transactions. I see almost no disk reads as the
database concerned is small enough to fit into the OS disk cache.
I suspect that it might be something to do with temporary tables. There
are a couple of places where I create temporary tables to "optimise"
queries by factoring out what would otherwise be duplicate work. The
amount of data being written is of the right order of magnitude for this
to be the cause. I fear that perhaps Postgresql is flushing these
tables to disk, even though they will be dropped before the end of the
transaction. Is this a possibility? What issues should I be aware of
with temporary tables? Are there any other common causes of lots of
disk writes within read-only transactions? Is there any debug output
that I can look at to track this down?
Thanks in advance for any help that you can offer.
Regards,
Phil Endecott.