Thread: Multi-master replication
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
‐‐‐‐‐‐‐ 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 inpostgres 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'vewanted 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 masterand multiple slaves. But we are write heavy and of course the load on the master is quite high. It does seem that multi-masterhas risks (everyone seems to talk about conflict resolution!) so I wonder whether the sensible route is simplyto 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. Buteven it lends caution to the multi-master route for write heavy applications. Z > -----Original Message----- > From: Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> > Sent: 13 August 2021 10:47 > To: Zahir Lalani <ZahirLalani@oliver.agency> > Cc: pgsql-general@lists.postgresql.org > Subject: Re: 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 ! >
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
singlemaster and multiple slaves. But we are write heavy and of course the load on the master is quite high. It does
seemthat multi-master has risks (everyone seems to talk about conflict resolution!) so I wonder whether the sensible
routeis 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?
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.
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.
‐‐‐‐‐‐‐ 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 singlemaster and multiple slaves. But we are write heavy and of course the load on the master is quite high. It does seemthat multi-master has risks (everyone seems to talk about conflict resolution!) so I wonder whether the sensible routeis 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 (andhence load) spread across. Most likely you would need something like Redis at the frontend to push clients in to theright shard. Conflict resolution (whether due to split brain or otherwise) is a big worry of mine with multi-master setups. The Googlesand Facebook's of this world seem to be solving the problem on the basis of having super precise time sync setup ontheir database servers and use high-resolution timestamps to act as tie-break arbitrator. But of course that comes withsignificant expense as it needs PTP infrastructure.
BDS
Only using the free v1.0...
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.