Postgresql – pglogical for OLAP queries

olappostgresqlreplication

I am looking to setup a replica for OLAP queries. Currently in my prod I have a master (OLTP node) replicating to a slave via streaming replica. I don't want to use streaming replica for OLAP queries because I would probably need different set of indexes and permissions on OLAP db. So I am planning to setup a pglogical replica.

Will there be any performance hit on master because of this?

Also I am planning to have master replicating to both a streaming replica (for HA) and a pglogical replica (for OLAP). Is it possible?

Any other implications/side-effects I should be worried about?

Best Answer

Yes, there's some performance impact on the master because of logical decoding. It's not usually too bad.

You can have both streaming physical replication for HA/failover and pglogical in use at the same time. However, currently if you have to fail over, pglogical can't switch to reading from the promoted replica so you'll have to re-create the subscription before you re-enable writes to the master after failover if you want to ensure consistency.