Sorry for the confusion I caused. The question about connection management and pg bouncer was a distraction and should have been addressed separately.
When having a mixture of OLTP and OLAP on the same primary databases, is there any benefit to declaring long running report type connections as SERIALIZABLE READ ONLY DEFERRABLE in terms of impact on logical or physical replication, autovacuum, etc even if the much heavier OLTP traffic is still running as the default read committed mode?
If the OLAP queries are moved to a physical read replica, I understand from this post ( https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ ) that there are chances that a query will be killed on the replica even with hot_standby_feedback is turned on. With them running on the same server, is the main concern (other than load) that vacuum type cleanup is delayed?
Maybe to sum up- If a long running report type query is run in default "read committed" mode and uses no temp tables / does no writes, would there be a benefit or change in behavior when using SERIALIZABLE READ ONLY DEFERRABLE mode?
When having a mixture of OLTP and OLAP on the same primary databases, is there any benefit to declaring long running report type connections as SERIALIZABLE READ ONLY DEFERRABLE in terms of impact on logical or physical replication, autovacuum, etc even if the much heavier OLTP traffic is still running as the default read committed mode?
If the OLAP queries are moved to a physical read replica, I understand from this post ( https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ ) that there are chances that a query will be killed on the replica even with hot_standby_feedback is turned on. With them running on the same server, is the main concern (other than load) that vacuum type cleanup is delayed?
Maybe to sum up- If a long running report type query is run in default "read committed" mode and uses no temp tables / does no writes, would there be a benefit or change in behavior when using SERIALIZABLE READ ONLY DEFERRABLE mode?
