3

We have a large legacy Java-based project, the availability of certain features throughout the application is determined by its corresponding value in a "feature_enabled" table in a SQL database. Much like Windows enables features based on Registry values.

Lately, we have been working to significantly reduce the number of SQL queries made in a specific part of the app. So we wrote a caching class that calls the query once and caches the results. Unfortunately, we can't rewrite the entire functions as other parts of the app are dependent on it. So, when our team calls the function (which executes the SQL query), we supply the entire class with the cached table property. The function then simply checks if the property has been set. If so, then uses the data from there, otherwise runs a fresh query.

Problem is we would like to add this caching technique as a feature which can be enabled like others. Querying the DB every time the cached db is called to see if the feature is enabled would undo whatever query saving we are doing. So my question is what is a good pattern of design to accomplish this?

4
  • I'm a little unsure what you're asking. Is it more like, How do I allow my caching mechanism to be turned off? Or, is it more like, How do I write a single caching mechanism to be easily applied to individual entities (or features) as-needed?
    – svidgen
    Aug 8 '16 at 16:25
  • 2
    I'm not sure which language you are developing your application with, but in a OO language, it does seem like a problem for the Decorator pattern. At boot, you check the config to see if the caching is enabled. If it is, you instantiate a CachedQuerier(NormalQuerier), else you instantiate just a NormalQuerier. The process is completely transparent to the rest of the application, which doesn't care if the value is cached or not. Aug 8 '16 at 17:09
  • The second paragraph is confusing. Could you paraphrase it to make it clear? Which are "the functions"? Aug 8 '16 at 17:13
  • Is the application web-based? Aug 8 '16 at 17:16
2

Do you need to be able to arbitrarily turn the "Caching Enabled" feature on or off while the app is running?

If you do need to change it during runtime:

Here are a few possibilities:

  • Create a separate, single row table that only holds this one value. This will minimize the cost of the query. In the real-world, the advantage you get from this may not even been measurable, especially if the config table contains just a few (dozen) rows.
  • Put this flag in a configuration file on the file system. Lot's of problems here. You'll have to continually watch the file for changes, which takes time and adds complexity. Plus, there's the problem of making sure the config file is properly deployed everywhere.
  • Build a way for the configuration editor to tell the running app that the caching flag has changed. This probably only makes sense if the editor and the main app are part of the same process. If they are, just add a NotifyCachingSettingChanged method to your caching class.

If you don't:

Have the function check its own cached tabled to see if the feature is enabled. If it is, return the cached table. If not, go query the DB for the table. When you need to change the caching flag, change it in the DB and the restart the app's process to empty the cache and force a reload.

1

When you find yourself in a polling situation (like wishing you knew when something changes) it's time to turn to events. What are events? Well at the heart it's polling, done better.

Let's say you have ten things that enter the database that you wish you knew the minute they changed. You could hit the database ten times a minute. Or you could just query once a minute.

This is how interrupts work in your computer's architecture. One register with many bits is checked in a tight loop each clock cycle. If any bit is non zero something is interrupting normal program flow and waiting to be dealt with. Which bit tells you what's waiting.

Done at the database level this scheme could be done with a string. Considering query overhead a reasonably sized string should perform nearly as well as an int. So keep a time stamped table of named events. It works like this:

  • Query the table for events newer than your last check.
  • Delete events older than your last check.
  • Deal with events (query whatever needs to be queried)

The order you feel like putting that in has more to do with debugging than performance.

You'll need some way to create events in the event table. That could be done in the db itself or it can be done in whatever updates the db. When you update the Foo table you could also drop a timestamped Foo string in the event table. When you delete the Bar entry from the Baz table you could drop a timestamped Baz-Bar string in the event table. It's gloriously asynchronous and scales very well. 10, 100, 10000 kinds of events load the db the same way.

Now you know when a cached value is out of date so you can update it timely.

If you're not the only one watching these events deleting gets a bit tricky. It is possible to come up with a scheme to register observers of events and only delete them once everyone has consumed them but that's brittle. One observer goes down and the DB is filling up. Better to define a window of time that events will live for and delete ones that have left the window.

Of course all of this could be avoided if you could find a way to use an alternative to the DB such as a messaging system. Make no mistake, this is a hack. It's a hell of a lot better hack than polling everything individually but it's still a hack.

3
  • I don't think you're answering the question that was asked ...
    – svidgen
    Aug 8 '16 at 16:14
  • @svidgen I read the question as wanting a way to ensure cached values are updated without polling the database every time a value is needed. How do you read it? Aug 8 '16 at 17:37
  • It's a little ambiguous to be, but, I read as either How do I treat caching itself as a feature? or How do I apply caching to features/entities as-needed? ... i.e., If feature X is slow, how can I set up caching so that caching entities X, Y, and Z become trivial updates...
    – svidgen
    Aug 8 '16 at 17:41
1

A completely different way to solve the polling problem is to wrap the DB. The wrapper exposes the same interface the DB would. Have everything talk to the DB wrapper. The wrapper talks to the DB saying whatever was said to it.

You might even use a traffic sniffer for this. That way nothing needs to be reconfigured. Just find a way to send it out as a stream.

The advantage here is now nether the DB nor the updating processes need to know what values are tied to events. Only the wrapper does.

Now the wrapper can send out events to registered listeners. Do this over rest and the listener can be on any box.

You could have the wrapper filter down to just what the observers care about to keep traffic down but to make sure this doesn't slow the DB if there are many observers you may want to dedicate one box for an unfiltered feed.

This is closer to how big data people solve this problem. Depending on your real needs consider this approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.