CODEWITHSUNDEEP
postgresqlamazon-web-servicesnginxredismaterialized-views
HaDoMin
HaDoMin

Reputation: 311

Best caching strategy data that is updated frequently (Redis/Memcached vs Nginx/Varnish vs Materialized view)

I am currently running an AWS EC2 Ubuntu server that fetches data from a Postgres RDS database instance. One of the SQL queries used in a view function for a particular page has a lot of joins in it and runs quite slowly. I've tried to trim down the query and removed some joins that might be bit unnecessary but it still takes a little longer than desired to load up (at least 6 seconds). I'm currently looking at potential caching strategies to help speed up the service of the page.

I have considered using a Materialized View, however the data that is fetched by the original view function updates every 30 seconds on average, and I'm worried that implementing a trigger or regular cron job to refresh the MatView this often will take its toll on the database and might not be the best strategy for data that is updated and changed regularly (unless someone can suggest another way of updating the rows in the MatView that doesn't involve running a query that looks very similar to the original one)

I've tested Redis on an Elasticache instance so far and have been impressed with how it works, however I've also been recommended to look at Nginx and Varnish caching strategies as well.

I'm somewhat confused which caching strategy is best suited for this situation. Would Redis/Memcached on an Elasticache instance be a bit too heavyweight vs an implementation of Nginx/Varnish on an EC2 instance? Is it considered a bad idea to try and cache data that will change often on a Nginx cache?

Upvotes: 7

Views: 5345

Answers (1)

Stephen
Stephen

Reputation: 1805

Crudely, you would use a low-level cache like Redis or Elasticache to cache raw data (eg the result of the SQL query); whereas you would use a higher-level cache like Nginx or Varnish to cache the whole HTML page on which the data is being displayed. So which one is appropriate depends somewhat on your usecase. If you have one simple page (or page fragment) which contains the slow data, and that content is displayed the same to all users, then a high-level cache might be appropriate. If the content is subject to lots of little tweaks and reformats which would make a whole-page cache very fragmented, then a lower-level cache would be appropriate.

In reality, these technologies are not tied tightly to that high/low separation: you can store whole pages in Redis and individual data fragments in Varnish, so it's not as simple as that. But in general, decide what you want to cache before deciding how to cache it.

Even once you've decided what to cache, choosing the right technology will depend on lots of considerations. Elasticache on AWS has the advantage of being fully-managed and so will save you maintenance, but will probably be the most expensive to run (at least on a small/medium scale). Nginx caching with a filesystem backend would probably be quickest and cheapest to implement, but won't scale well (and will be awkward to refactor as your scale increases). Varnish and Redis are probably best implemented as separate EC2 instances, so sit somewhere in the middle.

Upvotes: 7

Related Questions