4

Background

We have an app that will write into a postgres db hosted in Frankfurt datacenter. The app is installed in each of the 8 sites we have around the world, from China, Korea, India , Germany, France and Mexico.

When connecting in Europe, to the Frankfurt database, the response times are good. However, when connecting from the northern part of China, the response time are plain slow. The China great firewall is delaying the response time and adding to that, the distance is a decisive factor.

We decided to setup a second database in Korea for our asian sites. The app in the Korean and Chinese site would be feeding the korean database. It reduced the latencies dramatically and worked like a charm.

Issue is there are no way to copy the data between the Korean database and the German database as bidirectional replication is not allowed.

We are now back to square one as we are unsure what steps to take as we only need a single database but we want decent response time. We don't want to rewrite the app.

Questions:

  • We want a solution where we can host a database and where there would be decent response time, for every site around the world. What other solutions other than RDS can we look at?
  • If we keep going with RDS, is there a datacenter that can manage decent time response for all over the world?

Not sure if this the right place to ask this question. If no, please leave a comment and I will delete the question.

Andy K
  • 119
  • 14
  • What happens when you try to enable database replication? – Burgi Jun 22 '17 at 09:37
  • hi @Burgi, we can only set replica with rds, which are read replication. However, the read cannot be accessed in an easy manner from the Frankfurt database in order to copy the data from the seoul replication to the Frankfurt db. – Andy K Jun 22 '17 at 09:42
  • There are lots of XY problems here. Why can't the read be easily accessed? I feel you would be having these issues even without the geography. – Burgi Jun 22 '17 at 09:51
  • @Burgi I have to disagree with you on your say that there a lot of XY problems here. If I was in the mid of a XY problem, I would be stupidly trying to carry replication with some sort of homegrown solution.My aim is simple `I have one database with many apps inserting into it, I want good response time, connection and writing, for these apps regardless of where they are located. End.` – Andy K Jun 22 '17 at 09:55
  • What latency do you have between Frankfurt and China? – Mikhail Khirgiy Jun 22 '17 at 16:23
  • Hi @MikhailKhirgiy, 7 minutes to connect and retrieve the datas. 10 seconds if the datacenter is located in Seoul. – Andy K Jun 22 '17 at 16:46
  • May be the creating a vpn tunnel China-Seoul-Frankfurt will help you? – Mikhail Khirgiy Jun 22 '17 at 17:46
  • @MikhailKhirgiy that was one of the idea. I need to check. – Andy K Jun 22 '17 at 17:50

1 Answers1

1

Both the distance and the meddling middleboxes add latency, there is no avoiding that.

There may be other locations to host the database with an acceptable latency compromise. That much latency will hurt response times though. Keep testing.

I understand multi-master replication solutions exist for PostgreSQL. This would not be in your current software and will likely not be included in a cloud offering. It would benefit from an experienced DBA, being more complicated and riskier than one instance.

Or move the client closer. Host via remote desktop or VDI local to the database. Possibly the interface being sluggish is tolerable when the load times for queries are much improved.

Finally, changing the application, despite how unappealing that is. At least profile the number of queries so the amount of network time is known. Reducing these could have wins. More difficult would be rethinking the design, perhaps read queries from the replica but writes go to the primary.

John Mahowald
  • 32,050
  • 2
  • 19
  • 34