How good is BDR for production Postgres sync?

Question

I have a system where multiple satellites create financial transactions and they need to sync up with a core server. The satellites are remote servers that run Rails apps with a local Postgres database. The core is another Rails app with its own Postgres database. The satellites and core have pretty much the same schema (but not identical). Everything is containerized (apps and database). Very rarely, the core server does update some data that all satellites need. Currently I have one satellite, but this number will grow to a couple (I don’t think more than 100 in the distant future). There is no problem of sequence or contention between the core and the satellites. The core will never update the same transaction as any of the satellites and no satellites will update the same transaction as any of the other satellites. Even better, the financial transactions have a uuid as the primary key.

Since this is a multi-master sync problem, I naturally came across BDR. I have the following questions:

1. Is BDR production ready and stable? I’m reading about several competing technologies (like Bucardo and Londiste). Will it really be part of Postgres 9.6?
2. Can BDR handle a disconnected model? I don’t think this will be very often, but my satellites could be disconnected for hours.
3. Can BDR do selective syncs? For example, I’d only want certain tables be sync-ed.
4. Could BDR handle 100 satellites?

Answer 1

Is BDR production ready and stable?

Yes, BDR 1.0 for BDR-Postgres 9.4 is production-ready and stable. But then I would say that since I work for 2ndQuadrant, who develop BDR.

It is not a drop-in replacement for standalone PostgreSQL that you can use without application changes though. See the overview section of the manual.

I’m reading about several competing technologies (like Bucardo and Londiste).

They're all different. Different trade-offs. There's some discussion of them in the BDR manual, but of course, take that with a grain of salt since we can hardly claim to be unbiased.

Will it really be part of Postgres 9.6?

No, definitely not. Where have you seen that claim?

There will in future (but is not yet) be an extension released to add BDR to PostgreSQL 9.6 when it's ready. But it won't be part of PostgreSQL 9.6, it'll be something you install on top.

Can BDR handle a disconnected model? I don’t think this will be very often, but my satellites could be disconnected for hours.

Yes, it handles temporary partitions and network outages well, with some caveats around global sequences. See the manual for details.

Can BDR do selective syncs?

Yes. See the manual for replication sets.

Table structure is always replicated. So are initial table contents at the moment. But table changes can be replicated selectively, table-by-table.

For example, I’d only want certain tables be sync-ed.

Sure.

Could BDR handle 100 satellites?

Not well. It's a mesh topology that would expect every satellite to talk to every other satellite. Also, you'd have 198 backends (99 walsenders + 99 apply workers) per node. Not pretty.

You really want a star-and-hub model where each satellite only talks to the hub. That's not supported in BDR 1.0, nor is it targeted for support in BDR 2.0.

I think this is a better use case for pglogical or Londiste.

I can't really go into more detail here, since it overlaps with commercial consulting services I am involved in. The team I work with designs things like this for customers as a professional service.