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?

  • 4
    How are you querying the tables? Does the junction table have appropriate indexes? Are you performing the entire Method 1 calculation as a single query that can be handled by Postgres itself, or are you issuing one query per customer and aggregating the results in your app? Because if Postgres can handle everything directly, there is no reason to believe that Pandas could perform any quicker.
    – amon
    Nov 25 at 17:42
  • 1
    Forty-two million records is not a lot. I can think of several possibilities: item_uuid is not indexed, IN clause is slow (use a JOIN instead), "algorithms" is where the time is really being spent. Nov 25 at 18:00
  • 1
    @RocketSocks22 Postgres already caches a lot in memory. Your process is probably slow because you're issuing multiple queries that need to ship a lot of data back and forth. Try to combine your first two queries into a single query via a JOIN or a CTE and try to limit the number of items that are returned. E.g. can your recommendation algorithm restrict itself to the top 1000 items, where ranking can be determined easily within the query?
    – amon
    Nov 25 at 18:04
  • 1
    It sounds like the bulk of my problem is that I should focus on reducing my operations into a single query and optimizing the query itself Nov 25 at 18:25
  • 1
    There is no braindead, general, catch-all "best practice" for such kind of problems. This boils simply down to specific optimization of your indexes and queries. Here on this site, you sometimes may get unspecific strategic help how to do optimizations in general,, but to actually solve your issues, write a question either on Stackoverflow or dba.stackexchange.com, provding the gory details about your schema, already provided indexes, the SQL you tried and your measurings.
    – Doc Brown
    Nov 26 at 13:44

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.


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.


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.


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.


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.

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