@cachein/postgres

PostgreSQL backend for CacheIn with automatic TTL cleanup via pg_cron.

Features

• 💾 Persistent Caching - Store cache entries in PostgreSQL
• 🔄 Automatic Cleanup - TTL-based expiration with pg_cron scheduled jobs
• 🔁 Retry Logic - Configurable exponential backoff for transient errors
• ⏱️ Timeouts - Per-operation timeouts to prevent hanging
• 📊 LISTEN/NOTIFY - Optional real-time cache invalidation via PostgreSQL
• 🎯 Transaction Support - Proper isolation for concurrent access

Installation

npm install @cachein/core @cachein/postgres
# or
bun install @cachein/core @cachein/postgres

Note: This package requires @cachein/core as a peer dependency.

Requirements

• PostgreSQL 12 or higher
• pg_cron extension (for automatic TTL cleanup)

Quick Start

import { CacheIn } from "@cachein/core";
import { PostgresBackend } from "@cachein/postgres";
import postgres from "postgres";
import { z } from "zod";

// Create PostgreSQL connection
const sql = postgres("postgresql://user:pass@localhost:5432/mydb");

// Create backend
const backend = new PostgresBackend({
  sql,
  table: "cache_entries",
  cleanupSchedule: "*/5 * * * *", // Clean expired entries every 5 minutes
});

// Create client
const client = new CacheIn(backend, {
  defaultTtl: 300000, // 5 minutes
});

// Use it
const userSchema = z.object({
  id: z.number(),
  name: z.string(),
});

const userCache = client.bind("users", userSchema);

await userCache.set("123", { id: 123, name: "John Doe" });
const user = await userCache.get("123");

Configuration

PostgresBackendConfig

interface PostgresBackendConfig {
  sql: Sql; // postgres.js connection
  table?: string; // Table name (default: "cache")
  logger?: boolean | Logger; // Enable logging
  cleanupSchedule?: string; // Cron schedule for TTL cleanup (default: "*/5 * * * *")
  timeout?: number; // Operation timeout in ms (default: 5000)
  retries?: RetryOptions; // Retry configuration
}

Retry Options

interface RetryOptions {
  maxRetries?: number; // Max retry attempts (default: 3)
  baseDelay?: number; // Base delay in ms (default: 100)
  maxDelay?: number; // Max delay in ms (default: 5000)
}

Database Setup

The backend automatically creates the required table and pg_cron extension:

CREATE TABLE IF NOT EXISTS cache_entries (
  namespace TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT NOT NULL,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (namespace, key)
);

CREATE INDEX IF NOT EXISTS idx_expires_at ON cache_entries(expires_at)
  WHERE expires_at IS NOT NULL;

CREATE EXTENSION IF NOT EXISTS pg_cron;

TTL Cleanup

The backend uses pg_cron to automatically delete expired entries:

const backend = new PostgresBackend({
  sql,
  cleanupSchedule: "*/10 * * * *", // Every 10 minutes
});

Cron schedule format: minute hour day month weekday

Examples:

• */5 * * * * - Every 5 minutes
• 0 * * * * - Every hour
• 0 0 * * * - Daily at midnight

Tiered Caching

Combine with in-memory backends for better performance:

import { TieredCacheBackend, LruBackend } from "@cachein/core";
import { PostgresBackend } from "@cachein/postgres";

const backend = new TieredCacheBackend([
  new LruBackend({ maxSize: 1000 }), // L1: Fast LRU cache
  new PostgresBackend({ sql }), // L2: Persistent storage
]);

const client = new CacheIn(backend);

Error Handling

The backend includes retry logic for transient PostgreSQL errors:

const backend = new PostgresBackend({
  sql,
  retries: {
    maxRetries: 5,
    baseDelay: 200,
    maxDelay: 10000,
  },
  timeout: 10000, // 10 second timeout
});

License

MIT © Jonas