The Complete Guide to Database Caching Strategy: In the End, It's All Invalidation

• Introduction
• 1. What to Check Before You Add a Cache
• 2. The Cache You Already Have — shared_buffers and the OS Cache
• 3. Materialized Views — The Computed Cache Inside the Database
• 4. Criteria for Adopting an External Cache
• 5. The Four Failure Modes of Invalidation
• 6. The Write-Path Ordering Problem
• 7. Cache Stampedes and Defenses
• 8. What to Measure
• Quiz: Test Your Understanding
• Conclusion
• References
• Further Reading

Introduction

This blog already has The Complete Guide to Redis Caching Strategies, an article that covers a catalog of patterns for how a cache and the system of record hand data back and forth — Cache-Aside, Read-Through, Write-Through, and Write-Behind.

This post looks at caching from a different angle: it works up from the database side, one existing layer at a time. The order matters, because before you ever decide to bolt an external cache on top, you first need to confirm that the cache layers PostgreSQL already gives you for free are actually being used properly. PostgreSQL already ships with its own shared_buffers, sits on top of the operating system's own page cache, and already has a computed cache available in the form of materialized views. If you pile Redis on top of all of that while these existing layers are sitting there idle and untuned, the underlying performance problem does not get solved at all — all you have done is create a brand-new consistency problem on top of the performance problem you started with.

And roughly half of this post is devoted entirely to invalidation, because the hard part of adopting a cache was never filling it — it is emptying it at exactly the right moment. You can memorize every pattern name in the catalog and still get this part wrong, and the moment you do, users see stale data.

The reference engine throughout is PostgreSQL 18, and every parameter default and every locking behavior cited in this post was confirmed directly against the PostgreSQL 18 documentation, not assumed from memory or from an older version.

1. What to Check Before You Add a Cache

When someone proposes "reads are slow, so let's just add a cache," there are three questions that need answering before anyone writes a line of code.

Question 1 — is the slowness coming from computation or from I/O? If it is slow because the same expensive result keeps getting produced over and over, a cache is genuinely the answer. If it is slow because there is no index and every query has to scan the entire table, a cache only postpones the problem instead of solving it — the same slow query runs all over again the moment the cache entry expires, and you are back where you started.

Question 2 — what does the read-to-write ratio actually look like? If reads overwhelmingly dominate, the hit rate stays high and invalidation happens rarely. If writes are frequent, the cache keeps getting invalidated over and over, the benefit shrinks toward nothing, and about all you have gained is a new source of consistency risk.

Question 3 — how long is it acceptable for users to see stale data? Whether the honest answer is a few seconds or several minutes changes the entire design. And if the honest answer is "never, under any circumstance," then what you need is a different solution entirely, not a cache.

Add a cache before you have answered these three questions, and sooner or later you will get a bug report asking "why do I sometimes see old values here?" — and you will not be able to find the root cause, because the real root cause is the missing analysis, not a bug in the code.

2. The Cache You Already Have — shared_buffers and the OS Cache

PostgreSQL uses its own internal buffer pool together with the operating system's page cache, and the two work together as a single two-layer system whether you think about them that way or not. Understanding this two-layer structure is what gives you your very first real caching optimization, before you have added a single new moving part to the system.

The default value of shared_buffers is 128MB. The documentation's tuning guidance is explicit: "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system. There are some workloads where even larger settings are effective, but because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM will work better than a smaller amount."

There is a common misunderstanding that shows up here, over and over, in review after review. effective_cache_size does not allocate any memory at all, and never has. All it does is change the planner's assumptions about how much data is already cached. It is nothing more than an estimate of how much data is likely to be cached at any given moment — including the operating system's own cache, not just PostgreSQL's buffer pool — and its default value is 4GB. On a server with 256GB of memory, if this value is simply left at its out-of-the-box default, the planner will assume that an index scan is going to hit disk heavily, and it will lean toward a sequential scan instead, even in cases where an index scan would actually have been far cheaper.

Here's how you measure the hit rate.

-- Per-table buffer hit rate, lowest first
SELECT schemaname, relname,
       heap_blks_read, heap_blks_hit,
       CASE WHEN heap_blks_hit + heap_blks_read = 0 THEN NULL
            ELSE round(heap_blks_hit * 100.0 / (heap_blks_hit + heap_blks_read), 2)
       END AS heap_hit_pct,
       CASE WHEN idx_blks_hit + idx_blks_read = 0 THEN NULL
            ELSE round(idx_blks_hit * 100.0 / (idx_blks_hit + idx_blks_read), 2)
       END AS idx_hit_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 20;

One caveat is worth spelling out here. What "read" actually means in this view is a block that was not sitting in PostgreSQL's own buffer pool and therefore had to be requested from the operating system — it does not necessarily mean a block that hit physical disk. If that block happened to already be sitting in the OS page cache, there was no actual disk I/O involved at all. So if you judge disk load from this number alone, without accounting for the OS cache sitting underneath it, you will end up overestimating it, sometimes by a wide margin.

Since PostgreSQL 16, the pg_stat_io view gives you a considerably more accurate picture than pg_statio_user_tables can offer on its own. It breaks reads, hits, evictions, and fsyncs down separately by backend_type, object, and context, so you can actually tell what kind of activity generated the I/O in the first place. If you see a large number of entries where context is bulkread or vacuum, that is not evidence of a cache shortage at all — it is simply normal bulk activity doing exactly what it is supposed to do.

3. Materialized Views — The Computed Cache Inside the Database

For data that is "expensive to produce but does not change very often" — aggregate results are the obvious example — a materialized view is very often a better fit than reaching straight for an external cache. There are three separate reasons for this: you can join it directly in SQL just like any other table, you can put an index on it, and you can express the entire invalidation logic as a refresh schedule instead of scattering that logic through application code.

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT tenant_id,
       date_trunc('day', created_at) AS sales_day,
       count(*)         AS order_count,
       sum(total_amount) AS total_amount
FROM orders
WHERE status = 'PAID'
GROUP BY 1, 2;

-- a unique index is required for CONCURRENTLY refreshes
CREATE UNIQUE INDEX uq_mv_daily_sales
  ON mv_daily_sales (tenant_id, sales_day);

Warning: Running REFRESH MATERIALIZED VIEW without CONCURRENTLY takes a full ACCESS EXCLUSIVE lock on the view. In the documentation's own words: "the update affecting many rows will use fewer resources and complete faster, but it may lock out other connections trying to read from the materialized view." In plain terms, every single request that tries to query that view simply stops dead for however many minutes the refresh takes to finish. If that view is read by anything in your production service, you must use CONCURRENTLY — there is no safe way around it.

The requirements for using CONCURRENTLY at all are spelled out precisely in the documentation: "This option may only be used if there is at least one UNIQUE index on the materialized view which uses only column names and includes all rows; that is, it must not be an expression index or include a WHERE clause." On top of that, you also need to remember that CONCURRENTLY "may only be used on a materialized view that has already been populated," and that even with this option turned on, "only one REFRESH may run at a time against any one materialized view" — it buys you concurrent reads during a refresh, not concurrent refreshes of the same view.

-- a refresh that doesn't block reads
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

The documentation also calls out the performance trade-off honestly, rather than pretending CONCURRENTLY is free. It notes that CONCURRENTLY "may be faster in cases where a small number of rows are affected" — which, read the other way around, means that when you are recomputing the entire view from scratch, it can actually end up slower than a plain refresh would have been. For a nightly batch job that fully recomputes the view every single day regardless of what actually changed, running it without CONCURRENTLY during a quiet window may well be the better choice. The deciding factor is not a fixed rule but simply whether there is any traffic actually reading that view during that particular window.

There are also clear cases where a materialized view is simply the wrong tool for the job. If the refresh interval genuinely needs to be measured in seconds rather than minutes, if the results differ for every individual user rather than being shared across everyone, or if the computation cannot be expressed in SQL at all, you need an external cache or some other structure entirely — a materialized view will not stretch to cover it.

4. Criteria for Adopting an External Cache

An external cache only really enters the picture once you have done everything described in Sections 2 and 3 above and it still is not enough on its own. Here are the criteria you can actually use to make that call, rather than guessing.

Signals that adoption makes sense

• Lookups against the same key repeat overwhelmingly (you can expect a high hit rate)
• The original computation is expensive and the result is small
• Seconds-scale eventual consistency is acceptable for the domain
• The database is actually hitting its CPU or I/O limits

Signals that adoption should wait

• The lookup key differs every time, so the hit rate looks likely to be low
• Writes are frequent, so invalidation keeps firing
• Exposing stale data has financial or safety consequences
• You haven't tried index tuning or query rewriting yet

The last item on that second list deserves particular emphasis, more than any of the others. A cache does not actually solve a performance problem — it only relocates that problem somewhere else, and only temporarily. The moment the cache is empty for any reason at all (right after a deploy, a cache node restart, the aftermath of a large-scale invalidation), the full weight of the load goes straight through to the database with nothing standing in front of it anymore. If the underlying query cannot handle that load on its own, the service falls over at exactly that moment, with essentially no warning beforehand. A cache is an optimization for the steady state of normal operation, not a disaster-preparedness measure you can lean on when things already start going wrong.

5. The Four Failure Modes of Invalidation

This section is the heart of the post. The ways cache consistency actually breaks down in practice can be sorted into a small, fixed number of categories, and almost every incident you will ever see traces back to one of them.

Failure mode 1 — the window between updating the database and deleting the cache entry. If another request happens to read the cache during the gap between those two steps, it sees the old value, plain and simple. That window is short, often just milliseconds, but it is never actually zero. Given enough traffic passing through it, it will happen — not as some rare edge case, but as a near statistical certainty over time.

Failure mode 2 — a race between a read and a write. This one is nastier. Request A gets a cache miss and reads value V1 from the database. Immediately after that, request B updates the value to V2 and deletes the cache entry. Then A, arriving late with stale data in hand, writes V1 into the cache. The result: the old value V1 sits in the cache until it eventually expires on its own. Because the delete happened before A's late write ever arrived, that earlier delete does nothing to clear it — there is nothing left standing in the way by that point.

Failure mode 3 — a transaction that rolls back. If your code deletes the cache entry first and only afterward updates the database, and that transaction happens to roll back, the cache is left empty while the underlying data is still sitting in its old state. The next lookup will refill the cache with that old value, so the system does eventually become consistent again on its own — but if even one user saw a value in between that never actually became real, that is a confusing experience to explain after the fact.

Failure mode 4 — a partial failure. The database update succeeds without any trouble, but the cache delete fails on account of a network error, a timeout, or the cache node simply being unreachable at that particular moment. Without some kind of retry mechanism built in, the old value just sits there in the cache, completely unchanged, until it eventually expires.

Here is the practical, field-tested response to all four of these modes at once.

• Delete, do not fill. On the write path, never put a freshly computed new value directly into the cache — only ever delete the existing entry. The instant you write a new value from the write path, you have made yourself a participant in the failure-mode-2 race described above, whether you meant to or not.
• Defer the delete until after the commit, never before it. Deleting the cache entry from inside the transaction creates exactly the inconsistency described in failure mode 3 the moment that transaction rolls back. Delete only once you have confirmed the commit itself has actually succeeded. Recording the invalidation event in an outbox table as part of the very same transaction, and having a separate worker process that outbox afterward, guards against failure modes 3 and 4 simultaneously, with one single mechanism.
• Always set an expiration, with no exceptions. Even when invalidation itself fails for whatever reason, expiration is still standing there as the last line of defense. A cache entry configured with no expiration at all will, sooner or later, cause a real production incident — that is not a risk you are taking, it is a guarantee you are making yourself.
• Consider a delayed double-delete. Delete the entry once immediately after the update completes, and then delete it again a second time after a short delay has passed. This mops up exactly the kind of late-arriving stale write described in failure mode 2. It does not close the window completely, but the cost you pay for it is tiny relative to the risk it removes.

6. The Write-Path Ordering Problem

Reframing everything from Section 5 as a pure ordering problem gives you four possible options in total, and each one carries a different kind of risk.

The fourth pattern is the standard, and should be treated as the default. Do not reach for any of the other three unless you have a genuinely specific reason to.

And there is one rule on top of all this that you must never break: never put a cache operation inside a database transaction, under any circumstance. There are two separate reasons for this. First, if the transaction rolls back for any reason, the cache operation you already performed does not roll back along with it — the two systems simply do not share a rollback mechanism. Second, and more dangerously, while your code is waiting for the cache server to respond, the database transaction sits there open the entire time, and that open transaction blocks VACUUM from reclaiming dead rows anywhere in the database. Even a few seconds of unlucky network latency at that point ends up affecting the health of the entire database, not just the one table involved.

-- an outbox that records invalidation events atomically with the transaction
CREATE TABLE cache_invalidation_outbox (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cache_key   text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz
);

CREATE INDEX idx_cache_outbox_pending
  ON cache_invalidation_outbox (created_at)
  WHERE processed_at IS NULL;

A worker can consume this table with the queue pattern from Section 4 — FOR UPDATE SKIP LOCKED. That way, invalidation happens "only when the database commit has actually succeeded, and always at least once."

7. Cache Stampedes and Defenses

The moment the cache entry for a genuinely popular key expires, every single request that happened to be looking up that key hits a cache miss at exactly the same instant and goes straight through to the database, all together. Thousands of identical queries per second can arrive all at once, in a single burst. This is what is known as a cache stampede.

There are three defenses worth having in place.

First, add a random offset to the expiration time. Spread expirations out so that entries created at roughly the same moment do not all expire at that same moment later on. This is by far the cheapest option available, and also one of the most effective.

Second, lock things down so that only one single request is allowed to recompute the value. Only one of the requests that missed actually performs the expensive computation; every other request either waits for it to finish or serves the stale value in the meantime. If you are running multiple application processes, you need some form of distributed lock for this, and PostgreSQL's transaction-level advisory locks work quite well for exactly this purpose.

-- grant recompute rights to only one request
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('recompute:daily_sales:42'));
-- only the request that gets true runs the heavy aggregation and fills the cache
COMMIT;

It genuinely matters that you use the transaction-level function here, and not the session-level one. Sitting behind a connection pool, a session-level lock can easily be left behind, unreleased, long after the connection that took it has moved on to serve someone else entirely.

Third, refresh ahead of expiration, before anyone actually needs to wait for it. Once the remaining TTL on an entry drops below some configured fraction of its original lifetime, recompute it in the background ahead of time, quietly, before it ever actually expires. User-facing requests are then always served straight from the cache, with nobody ever hitting a miss. This fits dashboard-style workloads with fairly predictable traffic patterns particularly well.

One more thing on top of all three of these. Decide in advance, on paper, what should happen when the entire cache layer dies outright. If the cache server stops responding altogether, do you fall back to the source of truth automatically? If you do fall back, can the database actually absorb that load without falling over itself? And if it cannot absorb it, do you instead fail some requests fast on purpose, rather than let everything queue up and time out? Making this decision for the first time in the middle of a live incident is already too late to matter.

8. What to Measure

Run a cache without any metrics attached to it, and you simply cannot see whether it is actually helping or quietly hurting you. At an absolute minimum, you need the following four numbers in front of you at all times.

Hit rate. A low rate means the cache isn't earning its keep; an excessively high rate (say, 99.99%) can mean there wasn't much load to begin with. Breaking it down by key group matters — an overall average usually tells you nothing.

How often the original query runs. Look at how much calls in pg_stat_statements dropped before versus after adopting the cache.

SELECT calls, round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows, left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

pg_stat_statements only works once you register it in shared_preload_libraries and install the extension. The default for pg_stat_statements.max is 5000, and the default for track is top.

Invalidation lag. The gap between when the data changed and when the cache was actually cleared. If you're using an outbox table, this is measured directly as the difference between created_at and processed_at.

Load on the source right after the cache is empty. Record the database load right after the cache is cleared, on a regular basis — every deploy, for example. If that number is close to the database's capacity, the service can't survive without the cache. That fact needs to be managed as a risk in its own right.

Quiz: Test Your Understanding

BEGIN;
UPDATE products SET price = 19900 WHERE id = 42;
-- the application calls Redis DEL products:42 here
COMMIT;

CREATE UNIQUE INDEX uq_mv_daily_sales ON mv_daily_sales (tenant_id, sales_day);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

Conclusion

The real difficulty in cache design was never on the filling side — it has always lived on the emptying side. And the emptying problem, underneath all the pattern names, is fundamentally a distributed-systems ordering problem. There are two separate writes going to two different stores, and there is no mechanism available anywhere that guarantees the order those two writes land in — that is the actual essence of the problem, and it does not go away no matter which framework or library you reach for. Memorizing pattern names does not solve it, and never will.

The practical takeaway fits in three lines, and is worth committing to memory: Before adding any cache at all, check the cache layers already sitting inside the database. On the write path, delete after commit and never fill. Assume invalidation will eventually fail, and pair expiration times with an outbox as a matter of course, not as an afterthought.

To experiment with aggregate queries and materialized views yourself, use the Postgres Playground; to compare aggregation performance on analytical workloads, try the DuckDB Playground.

References

• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (retrieved 2026-08-15)
• PostgreSQL 18, REFRESH MATERIALIZED VIEW: https://www.postgresql.org/docs/18/sql-refreshmaterializedview.html (retrieved 2026-08-15)
• PostgreSQL 18, Monitoring Database Activity: https://www.postgresql.org/docs/18/monitoring-stats.html (retrieved 2026-08-15)
• PostgreSQL 18, pg_stat_statements: https://www.postgresql.org/docs/18/pgstatstatements.html (retrieved 2026-08-15)
• PostgreSQL 18, Explicit Locking (Advisory Locks): https://www.postgresql.org/docs/18/explicit-locking.html (retrieved 2026-08-15)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (retrieved 2026-08-15)

Further Reading

• Previous: The Complete Guide to Connection Pooling — the contract a pooling mode makes
• Next: The Complete Guide to Bulk Data Processing — COPY and chunked batches
• The Complete Guide to Redis Caching Strategies — a catalog of cache patterns
• The N+1 Problem, Fully Dissected — the classic problem a cache is good at hiding
• Postgres Playground — experiment with materialized views
• DuckDB Playground — compare aggregation workloads