Using Redis as a secondary index

We recently set out to build a bus booking website for one of our clients. We decided to use PHP 7 (Laravel), PostgreSQL 9.6, Redis 3.0, Beanstalkd as the primary architectural components. While we used PostgreSQL to store relational data such as Bus trips, User information, Booking details, Transactional info (from the payment gateway), we decided to utilize Redis as a secondary index to improve the overall performance and the results were impressive. We’ve always been a fan of open source projects like Redis, but the real-life performance improvement made us love Redis even more. This blog post explains the performance issues that we were facing and how we improved them.

Performance Matters

No matter how big or small your software product is, performance has now become crucial part of the overall architecture. Given the competition, most software products become irrelevant quickly because they’re either slow or unresponsive. Most users want instant-gratification and hence it’s mandatory to make performant software products if you want to stay in the business.

Mass insertion in RDBMS hurts performance

When tested from a user’s perspective, we found that the website was slow when we performed search. The 3rd party API which we integrated sent a huge JSON which would have the all the trips between source and destination and each trip object in the JSON would have complete trip information along with pick-up and dropping points of the trip. The problem was the mass insertion of those pick-up and dropping points in the Database. We decided to investigate further by putting appropriate log statements in the code where we performed mass insertion of these points. To our surprise, the insertion took nearly 20000 ms (~20 seconds) when inserting the records. We even put the whole insertion logic into a transaction and removed indexing from the tables but it barely improve the insertion time.

Redis to the rescue!

We decided to use Redis to store the points data and the performance gains have been huge since. We’re almost down to 1000 ms ( ~1 second) in storing the points data no matter how big the JSON is. The credit goes to PHP 7 as well, because PHP 7 is nearly 100% faster compared to previous versions. We also moved our filter logic to Redis by building secondary indexes on the filter terms. For example, we used keys of format: source:destination:date:travel_name and the values of such keys is the trip_id in the database. So whenever a filter is performed on travel_name, we simply build this key, get all the associated trip_ids as values and simply query the database in a WhereIn clause.

Conclusion

At the end of the day, modern web-apps needs to be performant in order to be productive and useful to the end users. Using tools like Redis as secondary indexes is definitely a way to go, in our opinion.