I would like to cache some database information in my remote servers. A lot of data does not change very often. The servers pool their datamodules.
I would like to cache some database information in my remote servers. A lot of data does not change very often. The servers pool their datamodules.
1. At each request the server gets, a generation counter could be read from the database (requiring a fetch). The counter value maintained by triggers in the database. If the counter did not change the cache is used otherwise the new value and the updated data is fetched.
2. I could use DB Events (Firebird). When the generation counter changes an event is sent to a thread in the server that immediately refreshes the cache. All requests then use the cache.
Option 1 will require a database operation for each request but will never "fail", i.e. deliver old data. Option 2 will be more efficient but i worry that an event could be missed by some reason and what to do then? A periodic pull would perhaps work in some circumstances, but not all.
1. At each request the server gets, a generation counter could be read from the database (requiring a fetch). The counter value maintained by triggers in the database. If the counter did not change the cache is used otherwise the new value and the updated data is fetched.
2. I could use DB Events (Firebird). When the generation counter changes an event is sent to a thread in the server that immediately refreshes the cache. All requests then use the cache.
Option 1 will require a database operation for each request but will never "fail", i.e. deliver old data. Option 2 will be more efficient but i worry that an event could be missed by some reason and what to do then? A periodic pull would perhaps work in some circumstances, but not all.
We currently use scheduled SQL Server Jobs to do the checks and the updates, but since we are considering a move to PostgreSQL, it will most likely become a separate task in our main service.
ReplyDeleteLocal server checks if there are newer LastUpdate row timestamps found in the central db than what it has locally, and if it is, copies the changed rows, but flag them as "in transit". Once copy is complete, the new rows are set as "available" and an event is created that makes the server and apps refresh the data.
We are largely avoiding triggers as they at times can cause cascading events.
We actually have several mechanisms like these - but common to them all is that they are pull based, not push based.
ReplyDeleteYes, triggers will be messy when replicating databases :). I might have to replicate in this project in the future, but at this point it's about minimizing SQL calls altogether in the outbound server layer (last before the internet).
ReplyDeleteI interpret "Once copy is complete, the new rows are set as "available" and an event is created that makes the server and apps refresh the data." as you saying that in the "last" layer you use the event-model (option 2).
Thanks!
That is correct. Any "provider" process needs to create events or gateways to ensure that a multi-component data exchange is done only when the entire data set is ready.
ReplyDelete