Re: UPDATEDs slowing SELECTs in a fully cached database

lars <lhofhansl@yahoo.com> wrote:

> Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
> and then running the SELECTs indeed shows a similar slowdown.
>
> Interestingly I see very heavy WAL traffic while executing the
> SELECTs. (So I was confused as to what caused the WAL traffic).

Hint bit changes aren't logged, so if it was that you would be
seeing writes to the heap, but not to the WAL.  Clean-up of dead
tuples is logged -- this is probably the result of pruning dead
tuples.  You could probably reduce the impact on your SELECT
statements at least a little by making autovacuum more aggressive.

At some point you could see the overhead of autovacuum having some
impact on your SELECT statements, so you may need to experiment to
find the "sweet spot" for your load.

-Kevin