I'm currently developing an edtech application, which is based on a technique I call 'vibecoded'. My current setup involves fetching a course that includes all of its lessons, the files within those lessons, and the chapters associated with those files. This is done through three nested loops featuring a caching mechanism (getSetAsync) that looks up cached chapters using the key format `file:chapters:{fileId}`. When a chapter isn't found in the cache, a database call is made to retrieve it. This method is causing significant latency, around 4000 ms due to the nested structures.
Recently, I've optimized my SQL query to retrieve all the chapters in a single operation, avoiding the N+1 query issue. However, my challenge is that this bulk query does not allow for a preliminary cache lookup, which complicates the data retrieval process. Previously, the chapter CRUD operations in the cache relied heavily on the file IDs, but now I'm moving towards fetching all files first and looping through them to get the respective chapters, which, while slightly more efficient, does not completely eliminate the overhead. I'm looking for strategies to improve this caching approach without reverting to the slower methods.
To clarify, the current setup fetches lessons and then files for those lessons; if a chapter isn't in the cache, a DB query runs inside the loop for each file. Although I've switched to a single optimized SQL query using indexes and `JOIN LATERAL`, the need to first check the cache is lost. I'm hoping this explanation highlights my dilemma and I'm eager to learn how to better manage caching in my application design.
4 Answers
It sounds like you've got quite the architecture challenge on your hands! If your single optimized query is working well and consistently fast, I'd recommend adjusting your caching strategy to reflect your read patterns. Instead of focusing on individual file IDs for caching chapters, consider caching at the lesson or course level. This way, you can minimize the cache complexity and avoid the N+1 problem altogether. By using versioned aggregate keys for chapters, you can invalidate the cache when CRUD operations occur. This could greatly simplify your approach!
I faced a similar issue before, and what worked for me was implementing a background loading system. If your data isn't too heavy, consider loading chapter data asynchronously before the user actually interacts with it. Additionally, ensure you're not only relying on cache but also looking into utilizing search indexes in your database, which could improve query performance. It’s all about finding the right balance between caching and direct queries!
I'm curious about what you mean by 'cache'. It seems like a critical part of your setup isn’t clear. Are you using a caching layer for media files, or is it mainly focused on query results? If your request is taking 4000 ms, is that the total response time or just the query execution? Understanding the source of delay can really help tailor caching strategies.
Have you considered using a different structure for your data loading? If lessons are videos, maybe caching just references initially and loading the actual files in the background could help. It seems like a more dynamic approach might alleviate some of the latency you’re experiencing. Right now, if you're doing m x n queries, that's definitely something to look at optimizing further!
That sounds interesting! Could you share how you implemented the async loading? I'm curious about the practical steps on that.