I researched a pretty long article about this and documented my findings here: https://www.rallyhealth.com/devops/database-updates-across-t...
Ask HN: How do you update multiple disparate databases?
41–50 of 55 posts
Re: Ask HN: How do you update multiple disparate databases?
#42Send 4 API calls to update the 4 databases. Name the bank so we know not patronize patronize it.
This works until one of the DBs is down, or someone queries two if them at the same time. Without rollback you get the TSB fiasco.
Re: Ask HN: How do you update multiple disparate databases?
#43CDC via Kafka Connect is one very powerful option.
Goldengate might have a higher billing rate.
Re: Ask HN: How do you update multiple disparate databases?
#44Step 1) Generate a GUID Step 2) Send a message to all the `downstream` systems (i.e. not the Source of Truth systems) with the change description and GUID Step 3a) Update source of truth with the new info, write the GUID to a log table in source of truth in the same transaction Step 3b,c,d) Downstream systems poll the source of truth for existence of the GUID in the log table indicating the change has been committed…
This seems entirely reasonable, but you forgot the 0'th step. Create a source of truth. I'd bet a 'source of truth' internal to the bank doesn't exist right now, and they're probably pulling something manually from an external 3rd party. And on with the regress.. Step -1) What form will this internal source of truth take, how much will it cost, and who builds that system? Step -2) Who gets to make that decision? My u…
That's table stakes as far as I'm concerned. If it can't be done quickly and correctly, you're in for a world of hurt. An organization in that position shouldn't be doing their own engineering. An individual in that situation should be brushing up their resume.
Re: Ask HN: How do you update multiple disparate databases?
#45There are no standard mechanisms for keeping heterogeneous databases in sync, or even DBs of the same types but with different schemas.
The standard thing to do is to have a source of truth, as described in various comments here like https://news.ycombinator.com/reply?id=22690991&goto=item%3Fi...
Sometimes you'll have multiple sources of truth for (hopefully non-overlapping) subsets of the data in question.
These things are messy. You have to do the hard work of writing sync tools. You want sync tools that work off of incremental / delta feeds, but you also need (slower) tools that can make-it-right based on the totality of your data -- at least the first time you sync two systems, and often also for auditing purposes to make sure that the incremental (fast) sync is working correctly.
Re: Ask HN: How do you update multiple disparate databases?
#46This simplest way possible. If the rate change can be processed via an endpoint that endpoint then updates the 4 databases. If talking to ACID databases, do a 2 phase commit to reduce the chance of them getting out of sync. Send me 10% of your consulting fee please :-)
Re: Ask HN: How do you update multiple disparate databases?
#47Earlier quoted context omitted.
This seems entirely reasonable, but you forgot the 0'th step. Create a source of truth. I'd bet a 'source of truth' internal to the bank doesn't exist right now, and they're probably pulling something manually from an external 3rd party. And on with the regress.. Step -1) What form will this internal source of truth take, how much will it cost, and who builds that system? Step -2) Who gets to make that decision? My u…
That's table stakes as far as I'm concerned. If it can't be done quickly and correctly, you're in for a world of hurt. An organization in that position shouldn't be doing their own engineering. An individual in that situation should be brushing up their resume.
[deleted]
Re: Ask HN: How do you update multiple disparate databases?
#48Banks use an ancient but powerful architecture called Source of Truth (SoT), or system of record. It's one of the first techniques developed to manage heterogeneous distributed databases. One master is picked as the final authority, then changes flow to other databases, replicas, etc. either using database tools or applications. Awareness of deadlines or how much latency (ie. when the updates are needed for each down…
I was in a client meeting a while back where one of their consultants corrected me with 'system of record' when I said source of truth. Your comment made me wonder where this distinction/difference was from? Do you know the history behind this?
Some good answers in this thread but I can flesh this out more.
The distinction and purpose is clearer when you think about aggregating data, such as the various Corona virus trackers are doing. This is a modern example but the scenario applies as far back as humans have been recording data. (And will apply as far forward as well, because physics)
Right now every hospital or testing center is recording the tests they give and their results. Plus they record how many inpatients, how many ICU beds, how many ventilators, etc.
Each hospital has a (at least one) database where that data is recorded.
Separately various news orgs and political offices are keeping track of all of the counts for their region, etc. So some number of times a day they call each of the hospitals to get their counts. (Ok, they don't actually call, but you get the idea). And they aggregate those counts.
Depending on the time that the organizations call the hospitals, a given hospital will give them a different count. The count As Of a particular time.
So different aggregation organizations may all have slightly different counts throughout the day.
You in following the progression of the cases will have picked some particular source that you choose to give you a trustworthy count.
So- the hospital, in keeping Records- is a System of Record.
The news orgs as Sources of Trustworthy information for consumers are Sources of Truth.
The two seem synonomous at first blush- and colloquially when people treat them interchangeably they really at referring to Source of Truth- but on reflection it should make sense that they serve quite different purposes and have quite different requirements. And once you the distinction you see it everywhere. Hope that helps.
Re: Ask HN: How do you update multiple disparate databases?
#49Step 1) Generate a GUID Step 2) Send a message to all the `downstream` systems (i.e. not the Source of Truth systems) with the change description and GUID Step 3a) Update source of truth with the new info, write the GUID to a log table in source of truth in the same transaction Step 3b,c,d) Downstream systems poll the source of truth for existence of the GUID in the log table indicating the change has been committed…
Is this a known pattern in distributed systems?
Re: Ask HN: How do you update multiple disparate databases?
#50Step 1) Generate a GUID Step 2) Send a message to all the `downstream` systems (i.e. not the Source of Truth systems) with the change description and GUID Step 3a) Update source of truth with the new info, write the GUID to a log table in source of truth in the same transaction Step 3b,c,d) Downstream systems poll the source of truth for existence of the GUID in the log table indicating the change has been committed…
Is this a known pattern in distributed systems?
Found that SOT, SOR and "distributed transactions" are the related topics.