Re: WAL bandwidth

On Thu, May 22, 2014 at 11:46 AM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

>
> On 05/22/2014 11:25 AM, Torsten Förtsch wrote:
>
>>
>> Hi,
>>
>> time and again I need to build indexes. If they are big, that generates
>> a lot of WAL data that needs to be replicated to streaming replication
>> slaves. Usually these slaves don't lag behind noticeably. So, the
>> application often reads from them. Well, unless I build indexes and,
>> thus, create a huge amount of WAL in a short period of time.
>>
>
Are these built CONCURRENTLY?

>
>> What I'd like to have is something where I can set the max. bandwidth
>> with which the index generating backend may generate WAL data. I seem to
>> remember to have seen a discussion about something similar but can't
>> recall where.
>>
>> Is there anything I can do about that problem in 9.3 or 9.4?
>>
>> I already have a function that waits for the streaming slaves to catch
>> up. But that mitigates the problem only at a very crude level. I'd like
>> to be able to set that bandwidth to, say, 10mbit/sec. Then I can be sure
>> that all my replicas are fine. How long the index creation takes, does
>> not matter.
>>
>
> This does not appear the domain of PostgreSQL as much as the domain of
> your OS and network layer.
>

The OS and network have little choice but to process the WAL in the order
it is generated. If you want to throttle the generation of WAL by
background maintenance operations so they don't interfere with the
processing of WAL generated by bread-and-butter transaction processing,
that is something that only PostgreSQL can do.

Cheers,

Jeff