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:
- 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.
- 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?