API Optimization

So far, we’ve built a simple REST API that exposes basic stock ticker resources, including historical data, tickers, and technical indicators such as SMAs. Our database consists of roughly 25 million rows and has a size of around 2GB. Although the database isn’t exceptionally large, the table sizes are significant enough to warrant exploring database optimizations.

Let’s examine our current API response times.

Response Times

Consider the following endpoint:

  • /historical/<string:ticker>
    • /historical/AABT-fake

We can review the response time for this request using Chrome Developer Tools and inspecting the network tab:

  • Slow Response Times

Currently, we have a response time of roughly 3 seconds. Yikes! That seems excessively long, especially for a simple database query. We don’t want these times to get even slower as the database grows over time, so let’s review indexing.

Database Indexing

If we consider our endpoint, you can see that we’re querying by ticker symbol. In fact, if we review our service layer, we can see this happening here:

historical = (
    self.database.session.query(Ticker)
    .filter(Ticker.symbol == symbol)
    .filter(Ticker.date >= from_date)
    .filter(Ticker.date <= to_date)
    .all()
)

One way we can improve performance on this endpoint is by creating an index on the symbol column of the table. If we review our current database, you can see that we are missing an index on our ticker table. I’m using DBeaver database client (it’s great) to manage this finance SQLite database:

  • DBeaver tables no index
  • DBeaver create index
  • DBeaver tables index

By creating an index on our ticker table, which is responsible for our historical data endpoint, we can review our new response times:

  • Fast Response Times

Wow! By indexing our data table on the symbol attribute, we now see a response time of 30ms. That’s almost 100 times faster! By creating appropriate indexes on our resource tables to match our client queries, you can achieve significant improvements on our API. This impacts not only our REST API but also our internal processes. Recall our SMA calculations? Well, by adding indexes here, any other process or system that needs to query this table by symbol will also see performance improvements.

Conclusion

A key design objective for any API is to be as responsive and low-latency as possible. In our demo project, we reviewed database indexing to achieve this. Our response times with indexed tables improved dramatically, from roughly 3 seconds to 30 milliseconds, showcasing a nearly 100x improvement.