Let’s continue to dive in PostgreSQL Concurrency. In the previous article of the series, Modeling for Concurrency, we saw how to model your application for highly concurrent activity. It was a follow-up to the article entitled PostgreSQL Concurrency: Isolation and Locking, which was a primer on PostgreSQL isolation and locking properties and behaviors.
Today’s article takes us a step further and builds on what we did in the previous articles in our series. After having had all the characters from Shakespeare’s A Midsummer Night’s Dream tweet their own lines in our database in PostgreSQL Concurrency: Data Modification Language, and having had them like a retweet a lot in PostgreSQL Concurrency: Isolation and Locking, it’s time to think about how to display our counters in an efficient way.
In this article, we’re going to think about when we should compute results and when we should cache them for instant retrieval, all within the SQL tooling. The SQL tooling for handling cache is a MATERIALIZED VIEW, and it comes with cache invalidation routines, of course.