API queries at the speed of light

There are times in the life of a programmer when you discover that your best work is also your simplest; most of the times!

For me, that kind of work has been with my npm module object-encode. This module encodes basic JavaScript objects to a hash that you can pass around and decode using a seed that only you know. It is designed for efficiency and not crypto safety. So should be used with non sensitive data of course.

var objCodec = require('object-encode');

var object = {
  this : 'that',
  foo : 'bar',
  "null" : false,
  nested : {
    "level1" : {
      val : 'level 1 Val',
      "level2" : {
        val : "level 2 Val"
      }
    }
  }
};

var salt = ')*myNewAWESOME-salt254@%^&%';

//encode object using specified algorithm
var encodedString = objCodec.encode_object( object, 'base64', salt );

//decode string back to the object
var decodedObject = objCodec.decode_object(encodedString, 'base64', salt );

console.log(encodedString);
console.log(decodedObject);

At the time when I wrote the module I needed something that I could use to hold database partitioning details for a client with a heavily partitioned database. What we did is that when a user signed up, we would allocate them a unique key. That key was essentially an encoded hash with their db partition details.

This was a simple fix and it allowed the client to structure their database without worrying about the partitions or naming. We even added a field called geo, which was set to 'us' by default and would allow the client to grow to other data centers and have each user self-identify, efficiently by just glancing at their ID.

If you have used S3 for example, you know how you have to indicate the 'zone' that your server is located for S3 to locate your buckets. Object-encode helped us abstract all that information so that the client id effectively holds and yields that data on demand.

This module has been out there for 8+ years and no issues have been reported. Safe to say it is quite solid. More recently though, I got to use it on a different project and it held up even better than I expected.

What do you do when you have many users to service and no bandwidth?

So this client was running on 4GB of RAM and single 3 Gig CPU core on a shared VPS service. His biggest problem was that they had an API serving thousands of clients a an hour which had gradually become too slow. They needed it fixed asap. Their first, and most important requirement, was that I had to offer a solution that did not force them to migrate or require more resources. I was hesitant but accepted the challenge. I suspected that was a decision I would live to regret but hey, I love challenges!

A week after introductions to the team I sat down (do we still call virtual meets sit-downs? 😊) with Janice (not her real name) to discuss the database structure and how I would plug in. I quickly noticed, and pointed out that the database was poorly normalized, run badly optimized queries and was likely a huge bottle neck. I took time to dig in further and indeed that was a huge problem.

Don't touch the database!

I made my report to the client detailing the changes I needed on the database in order to proceed with any other code-based changes. The database layer was entirely managed my Janice and her team and even though she had agreed to my suggestions earlier, she got back to me with a firm "No!". "That database has been in place for over a decade. There's too much data there and the client is not comfortable if we touch any of it right now." She explained.

I proceeded to emphasize that we needed the changes. It was then that she reminded me that I had made a vow to the company founder, and her boss, that I would find a solution not too scary, risky or expensive.

Back to the drawing board.

Picking out the slow queries

We spent a couple of days running explain statements and benchmarking query speeds. Further, I used the amazing QuestDB to log a heft of data for those two days and see what insights I could pick out. I quickly confirmed my fears that the database was the biggest issue. Every time a client made a call, they needed to run expensive joins to validate them, check their subscription status, check and update rate limits as well as fetch the actual data that the API call was requesting. While I drafted and proposed database optimizations that we can make further down the road, once and if I managed to win the client over, I needed an immediate solution.

Object Encode to the rescue

So my proposed solution was to use object-encode as follows.

1. When a user makes an API call, we run the expensive queries once. (Not ideal but we shall fix this down the road)
2. We pick out all relevant data such as subscription details.
3. We then authorize the user, encode that data (non sensitive data) into a hash and pass that in our response cookies.
4. Any other interactions with the client simply takes this hash, decodes it and therefore does not need to hit the database.
5. We then added a ttl field to the cookie and also the object before hashing. This would invalidate the hash after an hour (for security reasons). So every once an hour we would run the expensive queries one time for every user.

Results

One of the APIS managed sports betting odds. Basically, people would (for a fee) check betting odds predicted by analyzing a lot of data on teams and their line-ups. The tricky part is that odds kept changing in real time, based on many other variables such as the weather, player substitutions and so on.

It wasn't a very busy API on most days, but that quickly changed during major football games, and that's when the servers choked. On average, we observed that users would keep refreshing their pages about 600-700 times during a game, resulting in twice as many API calls hitting the server. With a few lines of code, we reduced this to 1-2 expensive calls per football game (90 mins) since we invalidated the hash after 60 minutes.

This was the simplest solution and by implementing it, we saw the optimizations yield an overall 3,000% improvement in system performance. I hope the client soon agrees to re-modelling his database, getting more server resources and we can see the system perform even much better!

Overall, over 8 years later, I have enjoyed using very basic code that I wrote amidst "Daddy, come play!" demands on my now 11 year old son.

Happy Geeking!