Low Latency for multi-million record database in a web app [closed]

Question

As a forewarning, this is for a school project with other school teammates, so my question is just for the sake of learning best practices. We are creating a recommendation system web app which currently consists of 3 services (react front-end, Flask server, postgres database), where the database we are using is just a simple 3-table many-to-many database (User table, junction table, item table). The junction table is 42 million rows and 2 columns, the User table is 1 million rows and 2 columns, and the Item table 2.3 million rows and 14 columns. We have a few different collaborative and content-based filtering inspired algorithms that we are working with. The 2 different methods are:

1. Given an item A from a user X, find all other users in the database which have also purchased item A and then find the most-often occuring item B that customers usually purchased with it.
2. Given an item A from user X, create a distance matrix from item A to every other item in the database to find the most similar algorithm.

These methods are easy to implement, but the querying time can be very slow, especially for method 1 since it requires querying the 42 million record table. I am looking for best practices on how to increase the web app response to something somewhat reasonable (less than 5 seconds) for each of these methods. One of the easiest options to implement would be to load the Item table into a Pandas DataFrame at start-up and use it as a on-disk cache of sorts, but this feels wrong and certainly not scalable. Are there other methods that are more commonly used? I'm not familiar at all with in-memory databases like Redis, but would something like that help give the speed-ups I need?

Answer 1

I'm not an expert on databases, but there is a fairly general approach to performance issues that is often applicable. These are typically applied in order, i.e. throwing hardware at the problem might not help much without appropriate indices.

Measure

Part of this is to establish a baseline to have a good idea if a change produced a benefit or not. Another part is to do profiling to find potential performance issues. For databases this typically involves some way to inspect and profile queries. This question deals with postgres query profiler

Algorithmic improvements

This typically involves smarter ways of doing this, reducing the computational complexity of the problem. In regards to databases this typically involves ensuring tables have appropriate indices, avoiding the need to scan entire tables. Another fairly common issue is doing filtering in the application instead of in the database, this can cause extra delays since more records need to be transferred.

Caching

If you are doing things repeatedly it might make sense to store the results in case they are needed again. In regards to websites and databases it might just be a front end that does some caching, or it might be something like a materialized view where more work is done up front, to reduce the work needed for each query. For all caching systems you will need some cache invalidation policy, and this is one of the potential harder problem in computing. But for a recommendation system it might be sufficient to just update the cache every day or so.

Hardware

If everything else is insufficient you can often throw hardware at the problem, i.e. more memory, faster disks, more CPUs etc. If a single machine is not sufficient, postgres and many other databases support sharding, but will probably work best when there is some partitioning so that the queries only need to access data in a single shard, so it might not be as applicable in your case.

Specialization

For some high performance systems it might make sense to write your own specialized solution. Systems like databases need to perform well for a wide variety of tasks. For some tasks there might be significant performance improvements with a custom solution, since this can make many assumptions not possible for a more general system.