PostgreSQL Real-Time Invalidation

Cachemate includes built-in support for real-time cache auto-invalidation via PostgreSQL's native LISTEN / NOTIFY protocol.

When any database row is inserted, updated, or deleted, a lightweight Postgres trigger notifies your application process, and Cachemate instantly bumps the corresponding table version key in Redis — completely hands-free!

---

1. Setup PostgreSQL Triggers

Run the following SQL migration against your PostgreSQL database to create the notification function and attach triggers to your tables:

migrations/001_cache_invalidation_triggers.sql

-- 1. Create a generic notification trigger function
CREATE OR REPLACE FUNCTION notify_table_change()
RETURNS trigger AS $$
BEGIN
  -- Sends the physical table name as the notification payload
  PERFORM pg_notify('table_changes', TG_TABLE_NAME);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Attach triggers to any tables you want auto-invalidated
CREATE TRIGGER users_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH STATEMENT
EXECUTE FUNCTION notify_table_change();

CREATE TRIGGER orders_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION notify_table_change();

CREATE TRIGGER products_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION notify_table_change();

Statement-Level Triggers

Notice FOR EACH STATEMENT. This executes the notification once per SQL mutation rather than once per row, dramatically reducing overhead on bulk updates or batch inserts.

---

2. Start the Invalidation Listener

Import and invoke startPgInvalidationListener in your application initialization sequence:

src/server.ts

import { Client } from 'cachemate';
import { startPgInvalidationListener } from 'cachemate/pg-listen';

const client = new Client({
  memoryMaxMb: 128,
  redisUrls: [process.env.REDIS_URL!],
  registry: {
    entries: {
      users: { tag: 'u', versionKey: 'cm:ver:users' },
      orders: { tag: 'o', versionKey: 'cm:ver:orders' },
      products: { tag: 'p', versionKey: 'cm:ver:products' },
    },
  },
});

async function bootstrap() {
  // Start the Postgres LISTEN subscriber
  const listener = await startPgInvalidationListener({
    client,
    databaseUrl: process.env.DATABASE_URL!,
    channel: 'table_changes', // Optional, defaults to 'table_changes'
  });

  console.log('⚡ Real-time Postgres cache invalidator listening on [table_changes]');

  // Handle graceful shutdown
  const shutdown = async () => {
    console.log('Stopping Postgres invalidation listener...');
    await listener.stop();
    await client.close();
    process.exit(0);
  };

  process.on('SIGINT', shutdown);
  process.on('SIGTERM', shutdown);
}

bootstrap().catch(console.error);

---

Configuration Options

startPgInvalidationListener accepts the following options:

Option	Type	Default	Description
client	Client	Required	The Cachemate Client instance to invalidate table versions on.
databaseUrl	string	Required	PostgreSQL connection URI (postgres://user:pass@host:5432/db).
channel	string	'table_changes'	The PostgreSQL notification channel name.

---

How It Resolves Aliases

If your database table is named user_profiles but your application registry uses logical name users mapped via alias:

const registry: TableRegistry = {
  entries: {
    user_profiles: { tag: 'u', versionKey: 'cm:ver:user_profiles' },
  },
  alias: {
    users: 'user_profiles',
  },
};

When PostgreSQL sends NOTIFY table_changes, 'user_profiles', Cachemate automatically locates the physical table entry user_profiles, increments its version key cm:ver:user_profiles, and invalidates all related join and row caches immediately.