Skip to content

Invalidating Cached Results by Tag

This recipe demonstrates how an application removes cached results from pg_semantic_cache when the data behind them changes. Each entry carries a list of tags, such as the names of the tables that the result was read from, and one call removes every entry with a given tag. The example is a psql script, because the extension is used entirely through SQL functions.

These examples are for pg_semantic_cache 0.1.0-beta4.

The script caches one result about orders and one about customers, then invalidates the results tagged with the orders table, and looks up both questions again. The script prints the following output:

Cached 2 results
Invalidated 1 entry tagged cookbook-orders
Orders question -> miss
Customers question -> hit
Removed 1 remaining cached entry

Preparing the Ingredients

The script needs the same server as the Caching a Query Result recipe: pg_semantic_cache with a vector dimension of 768, and the pgEdge Vectorizer creating embeddings with nomic-embed-text through Ollama. That recipe describes how to configure both extensions.

Setting Up the Script

The script stops at the first error, and prints plain lines rather than tables. psql variables hold the two questions that the script caches. The following block shows the start of the script:

-- Stop at the first error, and print plain lines rather than tables.
\set ON_ERROR_STOP on
\pset format unaligned
\pset tuples_only on

-- One question about orders and one about customers.
\set orders_question 'How many orders are waiting to be shipped?'
\set customers_question 'How many new customers registered this week?'

Getting the Embeddings

The script gets the embedding of each question before it changes the cache, so that a failure to reach the model leaves nothing in the cache. The following block gets both embeddings in one query:

-- Get the embedding of each question from the pgEdge Vectorizer before
-- changing the cache, so that a failure here leaves nothing behind.
SELECT pgedge_vectorizer.generate_embedding(:'orders_question')::text
           AS orders_embedding,
       pgedge_vectorizer.generate_embedding(:'customers_question')::text
           AS customers_embedding
\gset

Caching Tagged Results

The tags argument of semantic_cache.cache_query() takes an array of text labels. The script tags each result with the table that it describes, cookbook-orders or cookbook-customers, and with the cookbook and cookbook-invalidate-by-tag tags that identify the entries that the script created. The following block caches both results:

-- Tag each result with the table that it was read from, as well as the
-- tags that let the program find its own entries.
SELECT semantic_cache.cache_query(
           query_text      => :'orders_question',
           query_embedding => :'orders_embedding',
           result_data     => '{"orders_waiting": 17}',
           ttl_seconds     => 3600,
           tags            => ARRAY['cookbook', 'cookbook-invalidate-by-tag',
                                    'cookbook-orders']
       ) AS orders_id,
       semantic_cache.cache_query(
           query_text      => :'customers_question',
           query_embedding => :'customers_embedding',
           result_data     => '{"new_customers": 5}',
           ttl_seconds     => 3600,
           tags            => ARRAY['cookbook', 'cookbook-invalidate-by-tag',
                                    'cookbook-customers']
       ) AS customers_id
\gset
\echo Cached 2 results

Invalidating the Orders Results

The semantic_cache.invalidate_cache() function takes a tag argument, deletes every entry whose tags include that tag, and returns the number of entries that it deleted. The function also takes a pattern argument, which deletes every entry whose question text matches a LIKE pattern; with both arguments, the function deletes the entries that match either one. The following block invalidates the results tagged with the orders table:

-- The orders table has changed, so remove every result tagged with it.
SELECT format('Invalidated %s entry tagged cookbook-orders',
              semantic_cache.invalidate_cache(tag => 'cookbook-orders'));

An application can make the same call from a trigger on the orders table, or after a batch job that loads new orders, so that the cache never returns a result that is out of date.

Looking Up Both Questions

The script looks up each question with its own embedding, and a threshold of 0.95. The orders question now misses, because its entry is gone, and the customers question still hits. The orders question cannot match the customers entry instead, because the two questions are far apart: with the nomic-embed-text model in Ollama (digest 0a109f422b47), their similarity is about 0.61. The similarity depends on the embedding model, but a threshold of 0.95 leaves a wide margin. The following block looks up both questions:

-- Look up both questions again; only the customers result remains.
SELECT format('Orders question -> %s',
              CASE WHEN found THEN 'hit' ELSE 'miss' END)
  FROM semantic_cache.get_cached_result(:'orders_embedding', 0.95);

SELECT format('Customers question -> %s',
              CASE WHEN found THEN 'hit' ELSE 'miss' END)
  FROM semantic_cache.get_cached_result(:'customers_embedding', 0.95);

Removing the Entries

The script removes the entry that remains with the cookbook-invalidate-by-tag tag. The following block removes the entry:

-- Remove the entries that this program created.
SELECT semantic_cache.invalidate_cache(tag => 'cookbook-invalidate-by-tag')
       AS removed
\gset
\echo Removed :removed remaining cached entry

The script runs the steps that change the cache between BEGIN and COMMIT. If any step fails, psql stops, and PostgreSQL rolls back the transaction, so a failed run leaves no entry behind. Each lookup updates the hit and miss counters in the single row of the semantic_cache.cache_metadata table, so a lookup in an open transaction makes other sessions' lookups wait until the transaction ends; keep such transactions short.

Running the Example

The complete script is available in the cookbook repository. psql reads its connection settings from the standard PG* environment variables. Set the variables for your server, and then run the following command from the sql directory of the recipe:

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Viewing the Complete Program

The following block shows the complete script:

-- Ellie's Postgres Cookbook
-- Invalidating cached results by tag with pg_semantic_cache.
--
-- Copyright (c) 2026, pgEdge, Inc.
-- This code is released under the PostgreSQL License.

-- Stop at the first error, and print plain lines rather than tables.
\set ON_ERROR_STOP on
\pset format unaligned
\pset tuples_only on

-- One question about orders and one about customers.
\set orders_question 'How many orders are waiting to be shipped?'
\set customers_question 'How many new customers registered this week?'

-- Get the embedding of each question from the pgEdge Vectorizer before
-- changing the cache, so that a failure here leaves nothing behind.
SELECT pgedge_vectorizer.generate_embedding(:'orders_question')::text
           AS orders_embedding,
       pgedge_vectorizer.generate_embedding(:'customers_question')::text
           AS customers_embedding
\gset

-- Run every step that changes the cache in one transaction, so that
-- PostgreSQL rolls the new entries back if any step fails.
BEGIN;

-- Tag each result with the table that it was read from, as well as the
-- tags that let the program find its own entries.
SELECT semantic_cache.cache_query(
           query_text      => :'orders_question',
           query_embedding => :'orders_embedding',
           result_data     => '{"orders_waiting": 17}',
           ttl_seconds     => 3600,
           tags            => ARRAY['cookbook', 'cookbook-invalidate-by-tag',
                                    'cookbook-orders']
       ) AS orders_id,
       semantic_cache.cache_query(
           query_text      => :'customers_question',
           query_embedding => :'customers_embedding',
           result_data     => '{"new_customers": 5}',
           ttl_seconds     => 3600,
           tags            => ARRAY['cookbook', 'cookbook-invalidate-by-tag',
                                    'cookbook-customers']
       ) AS customers_id
\gset
\echo Cached 2 results

-- The orders table has changed, so remove every result tagged with it.
SELECT format('Invalidated %s entry tagged cookbook-orders',
              semantic_cache.invalidate_cache(tag => 'cookbook-orders'));

-- Look up both questions again; only the customers result remains.
SELECT format('Orders question -> %s',
              CASE WHEN found THEN 'hit' ELSE 'miss' END)
  FROM semantic_cache.get_cached_result(:'orders_embedding', 0.95);

SELECT format('Customers question -> %s',
              CASE WHEN found THEN 'hit' ELSE 'miss' END)
  FROM semantic_cache.get_cached_result(:'customers_embedding', 0.95);

-- Remove the entries that this program created.
SELECT semantic_cache.invalidate_cache(tag => 'cookbook-invalidate-by-tag')
       AS removed
\gset
\echo Removed :removed remaining cached entry

COMMIT;

Next Steps

The following resources provide more information about the topics in this recipe: