Re: WAL bandwidth

On 22/05/14 21:05, Jeff Janes wrote:
> 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?

yes

> 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.

That's what I want, to throttle the rate at which WAL is generated by
maintenance operations.

I take it, there is no such thing by now. Would it be a useful addition?

I am not sure if I have the time to implement it. I have had a cursory
look at the code before, just to find out how things work, but never
changed something. What do you think, is it complicated to implement?

Torsten