Hello!
There is a problem connected with using standby for read-only queries in production and DML operations on primary.
On primary we use alter table command with statement_timeout and deadlock_timeout values about 10 - 50 ms with rertry until it would successfully be executed.
But the same locks would be replayed on standby - without timeouts.
So there we see locks and our standby pools are overflowed - it is an incident/lsr/problem for users.
There is a workaround to have 2 standbies:
Before altering table on primary pause 1st standby and switch read only queries to it.
Then alter table and wait until it would be replicated to 2-nd standby.
Switch read only queries to 2-nd standby and remove pause from 1-st.
--
Konstantin EvteevThere is a problem connected with using standby for read-only queries in production and DML operations on primary.
On primary we use alter table command with statement_timeout and deadlock_timeout values about 10 - 50 ms with rertry until it would successfully be executed.
But the same locks would be replayed on standby - without timeouts.
So there we see locks and our standby pools are overflowed - it is an incident/lsr/problem for users.
There is a workaround to have 2 standbies:
Before altering table on primary pause 1st standby and switch read only queries to it.
Then alter table and wait until it would be replicated to 2-nd standby.
Switch read only queries to 2-nd standby and remove pause from 1-st.
--
