Bulk operations like CREATE INDEX, ALTER TABLE, or bulk loads can create
a lot of WAL. A lot of WAL at once can cause delays in replication.
For synchronous replication, this can make seemingly unrelated sessions
hang. But also for asynchronous replication, it will increase latency.
One idea to address this is to slow down WAL-generating maintenance
operations. This is similar to the vacuum delay. Where the vacuum
delay counts notional I/O cost before sleeping, here we would count how
much WAL has been generated and sleep after some amount.
I attach an example patch for this functionality. It introduces three
settings:
wal_insert_delay_enabled
wal_insert_delay
wal_insert_delay_size
When you turn on wal_insert_delay_enabled, then it will sleep for
wal_insert_delay after the session has produced wal_insert_delay_size of
WAL data.
The idea is that you would tune wal_insert_delay and
wal_insert_delay_size to your required performance characteristics and
then turn on wal_insert_delay_enabled individually in maintenance jobs
or similar.
To test, for example, set up pgbench with synchronous replication and
run an unrelated large index build in a separate session. With the
settings, you can make it as fast or as slow as you want.
Tuning these settings, however, is quite mysterious I fear. You have to
play around a lot to get settings that achieve the right balance.
So, some questions:
Is this useful?
Any other thoughts on how to configure this or do this?
Should we aim for a more general delay system, possibly including vacuum
delay and perhaps something else?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services