Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: How do you update multiple disparate databases?

news.ycombinator.com

Post reply on HN
Print view View original

21–30 of 55 posts

Re: Ask HN: How do you update multiple disparate databases?

#21

Post by davismwfl »

maps7 wrote: post #20
rorykoehler wrote: post #15

Earlier quoted context omitted.

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?

It's probably just what they call it.

That's been my experience as well. People call it different things. I have met some that are down right religious about the name and chide you for not using their term, but in the end it is the same thing.

I have had people try to explain to me how they are different, but when you remove all their BS, it is the same thing.

Re: Ask HN: How do you update multiple disparate databases?

#22

Post by perlgeek »

So, the first solution to consider is if this needs a technical solution at all.

If it happens infrequently enough, you can have simply have a proceed "change mortgage rate" that touches the 4 legacy databases. Done.

If it does need a technical solution, you have to establish the leading system / source of truth / whatever you call it. This can be either an existing system or a new one. And then all the others need to follow the leading system.

A more distributed approach (every of the 4 legacy systems can generate events that the other 3 work with) likely won't work with 4 legacy systems. It's often hard enough to get high-fidelity events/deltas out of a single system.

Re: Ask HN: How do you update multiple disparate databases?

#23

Post by smacktoward »

maps7 wrote: post #20
rorykoehler wrote: post #15

Earlier quoted context omitted.

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?

It's probably just what they call it.

I have no idea about this particular case, but for a lot of older concepts the root of divergent terminology is IBM. IBM invented its own lingo for lots of things, and persisted in using it even when the rest of the world settled on a different term. (This seems to be a habit companies pick up when they become huge and dominant; see also Micro-speak and Google-speak.)

The thing everybody else called a "hard disk," for instance, was long referred to by IBMers as either a "Winchester disk" (see https://en.wikipedia.org/wiki/History_of_IBM_magnetic_disk_d... for the etymology on that one) or a "fixed disk."

Re: Ask HN: How do you update multiple disparate databases?

#24

Post by henriquez »

It would be hard to formulate a one-size-fits-all solution even if you were talking about relatively modern systems.

There’s a trade-off between having a manual vs. automated process in the best of circumstances. You might think intuitively that an automated process would be easier and more reliable, but depending on local factors for each of the databases, there might be issues like availability or locking that could make an automated solution more “fragile” than simply having an employee with domain expertise doing an update on a schedule. Reliably automating a process like this would require a lot of careful QA.

So throwing legacy systems into the mix would just aggravate this further. It may be that the cost/benefit of automating this just isn’t there. If it’s cheaper in terms of salary cost to do it by hand for N years than the one-time expense to automate, it may just not make sense to automate. (Obviously over an indefinite time scale it would almost always make sense to automate)

Re: Ask HN: How do you update multiple disparate databases?

#25

Post by todaysAI »

perlgeek wrote: post #22

So, the first solution to consider is if this needs a technical solution at all. If it happens infrequently enough, you can have simply have a proceed "change mortgage rate" that touches the 4 legacy databases. Done. If it does need a technical solution, you have to establish the leading system / source of truth / whatever you call it. This can be either an existing system or a new one. And then all the others need t…

There could be a rate-change once a week on average. Of course if there is a central bank change then probably every mortgage product will need to be changed that day.

Rates are the important data-item but some of the legacy DBs will need to be notified of changes like fees, as well.

Re: Ask HN: How do you update multiple disparate databases?

#26

Post by PaulRobinson »

perlgeek wrote: post #22

So, the first solution to consider is if this needs a technical solution at all. If it happens infrequently enough, you can have simply have a proceed "change mortgage rate" that touches the 4 legacy databases. Done. If it does need a technical solution, you have to establish the leading system / source of truth / whatever you call it. This can be either an existing system or a new one. And then all the others need t…

There might be an addendum to this good answer: OP seems to be thinking they all need to show the same rates at the same time, so in essence you're looking for a "distributed transaction", which, as we all know... well, exercise for the reader and all that.

However, one technique I've used which works with all your approaches and guarantees consistency if the data model allows it is to have "future value" entries.

If you just have a single value:

    mortgage_rate_percent: 3.0
And this is in 4 databases, there will be a period where they don't match. But if you put a future "valid_from" in there:

    mortgage_rate_percent: 3.0
    valid_from: 2020-04-01 00:00:00-0000
Well, you now have the ability to have the rates all input at different times in different databases, and by querying for only the most recent one (or use from/to dates and find the intersection), they all become active at the exact same moment in time (clock drift aside) as time passes. No magic needed.

This seems really obvious once you've seen it, but it's amazing how many people try and worry about source of truth, synchronisation and distributed transaction problems when in many cases (like this), the change can be planned so you can just say "this value will apply in the future" and you have ample time to make sure it gets synch'ed before that time and everything "just works"

Re: Ask HN: How do you update multiple disparate databases?

#27

Post by ransom1538 »

1) Create an internal endpoint for each of the databases to change the mortgage rate. Each one is different so you will need to do this 4 different times. EG: setMortgageRate() . This is PUSH not Pull. Create this too, while you are at it: getMortgageRate().

2) Then once this is standardized, write a for loop.

Re: Ask HN: How do you update multiple disparate databases?

#29

Post by codingdave »

maps7 wrote: post #17
redis_mlc wrote: post #2

Banks 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…

What do non-banks use?

The same. The concept of a system of record is fairly universal in the Enterprise IT world.

Re: Ask HN: How do you update multiple disparate databases?

#30

Post by Misdicorl »

Step 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 to source of truth. Load latest value from source of truth and commit the change to the downstream system.

Step 4) If GUID never shows up, transaction was rolled back in source of truth (or an error occurred trying to send to one of the other downstreams, aborting before transaction)- do something relevant for your use case. Alert somebody, carry on, whatever.

Post reply on HN