0

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?

1 Answer 1

1

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.

4
  • Thanks. "It's not usually too bad." How can I measure how much bad it can go? Is there any study on TPS before and after logical decoding. Nov 8, 2016 at 8:15
  • Is it possible to do the pglogical replication from the replica (instead from the master) and would it have any advantage? Nov 8, 2016 at 11:01
  • 1
    @ypercubeᵀᴹ Right now, no you can't. I'm posting a patch to PostgreSQL v10 to allow logical decoding from standby soon. If you want that feature, help test and review the patch. Keep an eye on pgsql-hackers. Required patches are logical decoding timeline following (already posted), catalog_xmin hot standby feedback (already posted) and decoding on standby (pending). Get involved and help review and test if you care about this feature. Nov 9, 2016 at 0:37
  • @SiddharthPrakashSingh 2ndQuadrant has posted some benchmarks on pglogical and BDR performance, but I don't think anyone's done anything focusing specifically on performance impact on the master. I'm working with a customer on exactly that at the moment though. If it's important to you feel free to get in touch with 2ndQuadrant (who I work for) about how we can help you. Nov 9, 2016 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.