Multi-master replication

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Friday, August 13th, 2021 at 9:00 AM, Zahir Lalani <ZahirLalani@oliver.agency> wrote:

Confidential

Hello all

Does anyone on the list have production experience of using PG in a multi-master setup – if so using which tools?

Thx

Zahir

I have sometimes considered it, but it always struck me that given there is no first-class support for it, multi-master in postgres is just an opportunity for so many foot-guns to enable to things to go terribly, terribly wrong.

Maybe you need to look at something like CockroachDB which has a degree of Postgres syntax compatability (https://www.cockroachlabs.com/blog/why-postgres/). N.B. Not speaking from experience here, CockroachDB is something I've wanted to play with for a long time but never had the time !

Confidential

Thx Laura

So here is the dilemma - everything in the cloud world tends toward horizontal scaling. We do that with PG using single master and multiple slaves. But we are write heavy and of course the load on the master is quite high. It does seem that multi-master has risks (everyone seems to talk about conflict resolution!) so I wonder whether the sensible route is simply to scale vertically on the master?

We are looking at pg_pool II to manage the current config - which I suppose will help with the connection pooling etc. But even it lends caution to the multi-master route for write heavy applications.

Z

On Fri, Aug 13, 2021 at 11:35:29AM +0000, Zahir Lalani wrote:

Confidential

Thx Laura

So here is the dilemma - everything in the cloud world tends toward horizontal scaling. We do that with PG using single master and multiple slaves. But we are write heavy and of course the load on the master is quite high. It does seem that multi-master has risks (everyone seems to talk about conflict resolution!) so I wonder whether the sensible route is simply to scale vertically on the master?

We are looking at pg_pool II to manage the current config - which I suppose will help with the connection pooling etc. But even it lends caution to the multi-master route for write heavy applications.

You might want to read this:

https://momjian.us/main/blogs/pgblog/2018.html#December_24_2018

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

On Fri, Aug 13, 2021, 5:05 PM Zahir Lalani <ZahirLalani@oliver.agency>
wrote:

Confidential

Thx Laura

So here is the dilemma - everything in the cloud world tends toward
horizontal scaling. We do that with PG using single master and multiple
slaves. But we are write heavy and of course the load on the master is
quite high. It does seem that multi-master has risks (everyone seems to
talk about conflict resolution!) so I wonder whether the sensible route is
simply to scale vertically on the master?

http://bdr-project.org/docs/stable/conflicts.html

There are claims from other databases that they offer multi master
(cockroachdb, yugabyte, etc) out of the box, but there is little clarity on
what would one has to compromise with.

You can always try sharding, postgres fdw can help you with that setup. In
my implementation, we agreed on write locally, read globally. That way
writes could scale without compromise, but reads would be scatter gather
(fdw support for parallel scan in pg14 is promising)

Then, there is citusdb that can help you shard seamlessly and rebalance too.

https://www.citusdata.com/

https://about.gitlab.com/handbook/engineering/development/enablement/database/doc/fdw-sharding.html

https://blog.timescale.com/blog/building-a-distributed-time-series-database-on-postgresql/

As you read more, multi master is not so simple, from what I read.
I think there needs to be a good amount of thoughts for long term growth of
apps, is sharding does not scale well.

In out case, apps were broken down from monoliths to small microservi es to
deal with growth issues, and now it works well as single db as growth is
not exponential ( also small dbs, less tuning, defaults work) but then one
has to upgrade all the dbs periodically to catch up on releases.

As this is a postgresql list, I should not be posting mongo, but never the
less a good read.
https://www.mongodb.com/developer/article/active-active-application-architectures/

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Friday, August 13th, 2021 at 12:35 PM, Zahir Lalani <ZahirLalani@oliver.agency> wrote:

Confidential

Thx Laura

So here is the dilemma - everything in the cloud world tends toward horizontal scaling. We do that with PG using single master and multiple slaves. But we are write heavy and of course the load on the master is quite high. It does seem that multi-master has risks (everyone seems to talk about conflict resolution!) so I wonder whether the sensible route is simply to scale vertically on the master?

Scaling on the master remains a popular choice these days even in the trendy world of cloud and horizontal scaling. See, for example Let's Encrypt - admittedly a MySQL/InnoDB infrastructure, but still master/replica - https://letsencrypt.org/2021/01/21/next-gen-database-servers.html

I suppose (depending on the nature of your data), you could perhaps use sharding, so multiple master/slaves with data (and hence load) spread across. Most likely you would need something like Redis at the frontend to push clients in to the right shard.

Conflict resolution (whether due to split brain or otherwise) is a big worry of mine with multi-master setups. The Googles and Facebook's of this world seem to be solving the problem on the basis of having super precise time sync setup on their database servers and use high-resolution timestamps to act as tie-break arbitrator. But of course that comes with significant expense as it needs PTP infrastructure.

BDS
Only using the free v1.0...

Sent from Yahoo Mail on Android

On Sat, 14 Aug 2021 at 2:07, Laura Smith<n5d9xq3ti233xiyif2vp@protonmail.ch> wrote: ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Friday, August 13th, 2021 at 12:35 PM, Zahir Lalani <ZahirLalani@oliver.agency> wrote:

Confidential

Thx Laura

So here is the dilemma - everything in the cloud world tends toward horizontal scaling. We do that with PG using single master and multiple slaves. But we are write heavy and of course the load on the master is quite high. It does seem that multi-master has risks (everyone seems to talk about conflict resolution!) so I wonder whether the sensible route is simply to scale vertically on the master?

Scaling on the master remains a popular choice these days even in the trendy world of cloud and horizontal scaling.  See, for example Let's Encrypt - admittedly a MySQL/InnoDB infrastructure, but still master/replica - https://letsencrypt.org/2021/01/21/next-gen-database-servers.html

I suppose (depending on the nature of your data), you could perhaps use sharding, so multiple master/slaves with data (and hence load) spread across.  Most likely you would need something like Redis at the frontend to push clients in to the right shard.

Conflict resolution (whether due to split brain or otherwise) is a big worry of mine with multi-master setups.  The Googles and Facebook's of this world seem to be solving the problem on the basis of having super precise time sync setup on their database servers and use high-resolution timestamps to act as tie-break arbitrator.  But of course that comes with significant expense as it needs PTP infrastructure.