Re: BUG #13484: Performance problem with logical decoding

Hi,

On 2015-07-06 19:29:30 +0200, olivier(dot)gosseaume(at)free(dot)fr wrote:
> Just after having submitted the bug report, i saw that when a
> transaction have 4096 or more operations (inserts for example), I see
> postgres generating files in pg_repslot/my_slot directory. Is that
> what you call "spilling to disk"?

Yes.

> To be more concise :
> - ONE transaction with 4095 operations -> consumed in 80mS
> - TEN transactions with 4095 operations each (so 40950 operations) -> 380mS (which as you said is very good -less than linear growth-)
> - ONE transaction with 4096 operations -> consumed in 4204mS (ouch ...). I confirm there are only 4096 changes in one transaction
> - TEN transactions with 4095 operations each (so 40950 operations) -> 34998mS, ouch again

FWIW, I can't reproduce those results at all. I just tried, and for me
all results are 34ms, 121ms, 149ms, 374ms. When using the streaming
interface there's no discernible delays at all.

> I need some sort of change data capture mechanism (CDC) to detect
> changes on data (async is ok for me as long as i get very low
> latency). I could use triggers but performance would suffer. Reading
> the WAL log seems like it could be a good approach.

Right, that sounds pretty typical.

> I also tried pg_recvlogical feeding a file, but got the exact same
> results so i'm stuck.

Are you stopping pg_recvlogical between the runs, or are you letting it
run? The point of using it is that it's a streaming, i.e. that you do
not need to pay to "startup" costs of logical decoding, which can be
noticeable.

> What is observe is that the spilling occurs, and
> when the .snap file is created then pg_recvlogical will consume data
> but it does take a long time exactly the same time as
> pg_logical_slot_get_changes in fact.

Another possibility is that there's some windows specific problem here.

> BTW there is also a bug in pg_recvlogical with option -f - (output to
> stdout), pg_recvlogical tries to flush with fsync which does not work
> on windows and display an error message.

Uh, that should obviously not be the case. Thanks for the report, will
fix.

Greetings,

Andres Freund