Hacker News Reader

Comment by eo3x0 | Hacker News Reader

What's the latest in regards to multi-master (production ready) setups in the PostgreSQL world these days? (i.e without manual or application level sharding) Last I checked, the "solutions" that were out there weren't ready for prime time.

View on HN

They are working hard to get logical replication in for PG 10 (next major release), but there are still a lot of open items: https://wiki.postgresql.org/wiki/PostgreSQL_10_Open_Items

I don't know if logical replication = multi-master but I know it is a major piece of the puzzle.

What kind of load (number of concurrent app users) your app has so it needs multi-master db?

It is quite easy to setup powerful pg master node (32 CPU threads, fast SSDs) and many slave nodes for read-only queries. That setup can handle quite a lot of transactions.

I'm always curious when people ask about "multi-master" what exsctly they mean and with which tradeoffs. What use case do you envision?

It means you can write to any node.

That is what people think they want when they do not understand the tradeoffs involved, typically.

Multi-master works wonderfully until it does not. Then you have a terrible mess of inconsistent data.

There can't be high availability if there is only ever one node that accepts writes.

There can't be sharding if there is only ever one node that performs writes.

I'm genuinely curious what you mean here when you say high availability? Do you mean that during a distributed consensus failover timeframe, it is unacceptable for writes to be queued at the client?

It means that the system is still available when one node is dead.

When you say that there can't be sharding without more than one one node taking writes, I totally see where you're coming from. And I agree with you. Typically that means that there is a logical isolation between which data goes to which shard so this avoids conflicts too. Usually when people talk about "multimaster" I do not believe they're thinking of this particular scenario but I may be wrong. I'm not familiar but does postgres not shard?

There are two cases of multi-master.

- All nodes have all data (no sharding).

- No nodes have all data (sharding).

Okay, but in your use case, what should happen when two nodes get conflicting writes at approximately the same time?

Depends on the system. Read the documentation.

Well since you were asking about this topic, I guess what I was trying to get at is what is it that you particularly were hoping to see from the community?

SQL doesn't work well in a distributed setup. Give up on SQL and it gets easier.

The most popular hack for PostgreSQL is citusdb https://www.citusdata.com/ , of course it comes with many limitations and drops half of SQL

That's FUD, citus doesn't "drop half of sql". Anything that can be resolved to a single node works exactly like normal postgres, and the main multinode things you want (e.g. aggregations) work transparently. The actual limitations are usually easy to work around.

https://docs.citusdata.com/en/v6.1/reference/sql_workarounds...

I guess they improved significantly since my last work around it.

Reply on news.ycombinator.com