How good is BDR for producing Postgres sync?

Question

I have a system in which several satellites create financial transactions, and they need to synchronize with the main server. Satellites are remote servers that run Rails applications with a local Postgres database. The core is another Rails application with its own Postgres database. Satellites and the core have almost the same scheme (but are not identical). Everything is containerized (applications and database). Very rarely, the primary server updates some of the data that all satellites need. I currently have one satellite, but this number will increase to a couple (I do not think that more than 100 in the distant future). There is no problem of consistency or disagreement between the core and the satellites. The kernel will never update the same transaction as any satellite, and no satellites will update the same transaction as any other satellite. Moreover, financial transactions have uuid as their primary key.

Since this is a problem with several masters, I naturally ran into BDR. I have the following questions:

• Is BDR production ready and stable? I read about several competing technologies (such as Bucardo and Londiste). Will this really be part of Postgres 9.6?
• Can a BDR handle a disconnected model? I don’t think it will be very common, but my companions can be turned off within a few hours.
• Can BDR do selective synchronization? For example, Id only wants certain tables to be synchronized.
• Can a 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, which develops BDR .

This is not a replacement for standalone PostgreSQL, which you can use without modifying the application. See the Overview section of the manual.

I read about several competing technologies (e.g. Bucardo and Londiste).

They are all different. Various compromises. They are discussed there in the BDR leadership, but, of course, they take it with a grain of salt, since we can hardly claim to be impartial.

Will this really be part of Postgres 9.6?

No, definitely not. Where did you see this expression?

In the future (but not yet) there will be an extension added to add BDR to PostgreSQL 9.6 when it is ready. But this will not be part of PostgreSQL 9.6, it will be what you install on top.

Can a BDR handle a disconnected model? I don’t think it will be very common, but my companions can be turned off within a few hours.

Yes, it does a great job with time partitions and network outages, with some caveats around global sequences. See the manual for more details.

Can BDR do selective synchronization?

Yes. See the Replication Kits Guide.

The table structure is always replicated. So at the moment this is the initial contents of the table. But table changes can be replicated selectively, table by table.

For example, Id only wants certain tables to be synchronized.

Sure.

Can a BDR handle 100 satellites?

Not good. This is a grid topology that assumes that each satellite will talk to all other satellites. In addition, you will have 198 backends (99 players + 99 people) per node. Not really.

You really need a star-hub model where each satellite only talks to the center. It is not supported in BDR 1.0 and is not intended to be supported in BDR 2.0.

I think this is the best use case for pglogical or Londiste.

I cannot dwell on this here in more detail, since it is superimposed on the commercial consulting services in which I participate. The team I work with designs such things for clients as a professional service .