Caching query results vs Querying each time (finding a middle ground)

Question

This is kind of an expansion on a previously asked question

Some background info:

Querying Over 5,000 items from SQL database that eventually get sorted into a list depending on who the user is. One problem is that users are allowed to have customized lists which means that filtering these 5,000+ items in a uniform way is probably impossible.

Also, important that the amount of users (and users wanting to have custom item lists) is constantly growing, so there will be an unprecedented amount of ways to sort. Basically, we won't be paying attention to filtering this growing list.

Two methods we've come up with so far: -Option 1: Cache item list PER USER (and filter the items needed after) OR -Option 2: Cache the list once for the whole site (every user accesses this one cached list)

The issue with Option 1 is that there will be duplicated lists in cache. Although we do not have an amount of users that will strain our server's memory, we obviously want our system to have scalability.

The issue with Option 2 is that there will be duplicated items in the single, cached list. With the amount of users we have, this can quickly turn into a disorganized list full of duplicated items (i.e. 5,000 items can quickly turn into 10,000 based on a few user's custom item preferences)

Is there a "middle ground" for dealing with this situation? One that combines some benefits of querying and caching.

Even if a response is a link to a potentially helpful source, I would appreciate it as I am kind of at a fork in the road.

Looking to open up new possible paths in my brainstorming.

Answer 1

Some may not feel this is not an answer, but it is a way to derive a better answer.

Collect data on user behavior. Many users may prefer a default configuration. If so, it would make sense to cache this list. I've seen some apps that are highly configurable, but I just use it the way it came out of the box. I'm sure there are hundreds of settings for Microsoft Word that 90% of users never bother using.

Otherwise, pull it from the database/disk. Depending on the size of the db and your particular RDBMS, most of your data may be in memory anyway (I know SQL Server does this.). This is where you'll start seeing any bottlenecks. You can address those later.

Don't be too anxious to solve a problem too soon. It may not be a problem. The wrong problem or the wrong solution. As much as you may want some sort of "best practice" or standard use case, there probably isn't one that matches your situation.

Answer 2

You should cache the query by the query parameters. In this case user id.

However, the point of caching is to prevent running the same query multiple times in quick sucession.

This would usualy be caused by each user running the same query per request. When you have 1000 users on the site and each making requests at the same time and running a getMenu query for example, wont all hit the database and overload your db. The first one runs and the rest get the cached result. Your Db has to run a query per second instead of 1000 queries per second.

Assuming your code is reasonably well written though, you wouldnt expect a single request to make the same call lots of times, or a single user to make many multiple requests for the same thing. As each user is making a unique query they will all hit the db. They dont benefit from a differnt user already having made the call. If you cache al the results, you dont really see much benefit and will run out of memory.

so caching is unlikely to help you much.

I think the comments hit the nail on the head that your custom per user list is inherrently unscaleable.

Maybe you can divide it into a standard list and per user modifications? or opt in groups of items?

OR

you can cache the list client side. ie a cookie. this allows your effective memory to scale with your users.