I did read this, before asking my question...
but... when you do setup a streaming replication, there is, in postgresql.conf, a variable:
max_standby_streaming_delay = 30s # max delay before canceling queries when reading streaming WAL;
as the secondary is, by nature, read only, I was wondering why it can be necessary to cancel a read request...
So, I read another time some locking doc, which states that the access share lock positioned by a read request DO block demands of exclusive locking
like vacuum full, table reorg, etc. thus blocking the streaming replication flow.
also, on the real world POV, while monitoring streaming replication lag, I see lags of 90 seconds, quite frequently.
and I was guessing what can block the streaming flow for that long.
--neither cpu nor network nor I/O looks saturated
--as its a production instance, and we dont do vacuum full or table reorg or... during business hours, obviously !
--but a bunch of read request are sent to the secondary, some of them a bit big in terms of multiple join, etc..
hence my original question.
So, if its not a locking scheme, what can block hundreds of transaction to get to the secondary ???
Marc MILLAS
Senior Architect
+33607850334