Sunbelt Computer Software

Cache Options

Caching configuration for PostgreSQL routines.

Overview

{
  "CacheOptions": {
    "Enabled": false,
    "Type": "Memory",
    "MemoryCachePruneIntervalSeconds": 60,
    "RedisConfiguration": "localhost:6379,abortConnect=false,ssl=false,connectTimeout=10000,syncTimeout=5000,connectRetry=3",
    "MaxCacheableRows": 1000,
    "UseHashedCacheKeys": false,
    "HashKeyThreshold": 256,
    "InvalidateCacheSuffix": null,
    "HybridCacheUseRedisBackend": false,
    "HybridCacheMaximumKeyLength": 1024,
    "HybridCacheMaximumPayloadBytes": 1048576,
    "HybridCacheDefaultExpiration": null,
    "HybridCacheLocalCacheExpiration": null,
    "Profiles": {
      "fast_memory": {
        "Enabled": false,
        "Type": "Memory",
        "Expiration": "30 seconds",
        "Parameters": [
          "user_id"
        ]
      },
      "shared_redis": {
        "Enabled": false,
        "Type": "Redis",
        "Expiration": "1 hour"
      },
      "date_range_hybrid": {
        "Enabled": false,
        "Type": "Hybrid",
        "Expiration": "5 minutes",
        "Parameters": [
          "from",
          "to"
        ],
        "When": [
          {
            "Parameter": "to",
            "Value": null,
            "Then": "skip"
          }
        ]
      }
    }
  }
}

Settings Reference

Setting	Type	Default	Description
`Enabled`	bool	`false`	Enable caching for routines.
`Type`	string	`"Memory"`	Cache type: `"Memory"`, `"Redis"`, or `"Hybrid"`.
`MemoryCachePruneIntervalSeconds`	int	`60`	How often to prune expired items from memory cache (in seconds).
`RedisConfiguration`	string	(see below)	Redis connection string. Used when `Type` is `"Redis"`, or when `Type` is `"Hybrid"` with `HybridCacheUseRedisBackend: true`.
`MaxCacheableRows`	int?	`1000`	Maximum number of rows that can be cached for set-returning functions. If a result set exceeds this limit, it will not be cached (but will still be returned). Set to `0` to disable caching for sets entirely. Set to `null` for unlimited (use with caution).
`UseHashedCacheKeys`	bool	`false`	When `true`, cache keys longer than `HashKeyThreshold` characters are hashed to a fixed-length SHA256 string. This reduces memory usage for long cache keys and improves Redis performance with large keys.
`HashKeyThreshold`	int	`256`	Cache keys longer than this threshold (in characters) will be hashed when `UseHashedCacheKeys` is `true`. Keys shorter than this threshold are stored as-is for better debuggability.
`InvalidateCacheSuffix`	string?	`null`	When set, creates an additional invalidation endpoint for each cached endpoint. The invalidation endpoint has the same path with this suffix appended.
`HybridCacheUseRedisBackend`	bool	`false`	When `Type` is `"Hybrid"`, enables Redis as the L2 (secondary/distributed) cache backend. When `false`, HybridCache uses in-memory only but still provides stampede protection.
`HybridCacheMaximumKeyLength`	int	`1024`	Maximum length of cache keys in characters (Hybrid cache only). Keys longer than this will be rejected.
`HybridCacheMaximumPayloadBytes`	int	`1048576`	Maximum size of cached payloads in bytes (Hybrid cache only). Default is 1 MB.
`HybridCacheDefaultExpiration`	string?	`null`	Default expiration for cached entries. Accepts PostgreSQL interval format (e.g., `"5 minutes"`, `"1 hour"`). If not set, individual endpoint `cache_expires` annotations are used, or entries don't expire.
`HybridCacheLocalCacheExpiration`	string?	`null`	Expiration for L1 (in-memory) cache in Hybrid mode. Set shorter than `HybridCacheDefaultExpiration` to refresh local cache more frequently from Redis. Accepts PostgreSQL interval format.
`Profiles`	object?	`null`	Named caching profiles. Each profile selects its own backend, default expiration, key parameters, and `When` rules. Endpoints opt in via the `@cache_profile` annotation. See Cache Profiles below.

Cache Types

Memory Cache

In-memory caching on the application server:

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Memory",
    "MemoryCachePruneIntervalSeconds": 60
  }
}

The MemoryCachePruneIntervalSeconds setting controls how frequently expired cache entries are removed.

Redis Cache

Distributed caching using Redis:

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Redis",
    "RedisConfiguration": "localhost:6379,abortConnect=false,ssl=false,connectTimeout=10000,syncTimeout=5000,connectRetry=3"
  }
}

See StackExchange.Redis Configuration for connection string options.

Hybrid Cache

HybridCache uses Microsoft's Microsoft.Extensions.Caching.Hybrid library to provide:

Stampede protection: Prevents multiple concurrent requests from hitting the database when cache expires
Optional Redis L2 backend: Can use Redis as a distributed secondary cache for sharing across instances
In-memory L1 cache: Fast local cache for frequently accessed data

Basic HybridCache (in-memory with stampede protection):

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Hybrid",
    "HybridCacheUseRedisBackend": false,
    "HybridCacheDefaultExpiration": "5 minutes"
  }
}

HybridCache with Redis backend:

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Hybrid",
    "HybridCacheUseRedisBackend": true,
    "RedisConfiguration": "localhost:6379,abortConnect=false",
    "HybridCacheMaximumKeyLength": 1024,
    "HybridCacheMaximumPayloadBytes": 1048576,
    "HybridCacheDefaultExpiration": "5 minutes",
    "HybridCacheLocalCacheExpiration": "1 minute"
  }
}

When HybridCacheUseRedisBackend is false (default), HybridCache works as an in-memory cache with stampede protection. When true, it uses Redis as the L2 distributed cache for sharing across multiple application instances.

When to use HybridCache:

When you need stampede protection (prevents thundering herd on cache expiry)
When running multiple application instances that need to share cache
When you want the best of both worlds: fast local cache + distributed storage

Cache Key Hashing

For improved performance with Redis cache, especially when routines have many or large parameters, you can enable cache key hashing:

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Redis",
    "UseHashedCacheKeys": true,
    "HashKeyThreshold": 256
  }
}

When enabled, cache keys exceeding the threshold are automatically hashed to a fixed 64-character SHA256 string, reducing:

Memory usage for storing long cache keys
Network transfer overhead with Redis
Redis server memory consumption

This is particularly recommended when:

Using Redis cache with routines that have many or large parameters
Caching routines with long SQL expressions
High cache hit rates where memory efficiency matters

Caching Set-Returning Functions

Caching now works for set-returning functions and record types, not just single scalar values. When a cached function returns multiple rows, the entire result set is cached and returned on subsequent calls.

Use MaxCacheableRows to limit memory usage:

{
  "CacheOptions": {
    "Enabled": true,
    "MaxCacheableRows": 1000
  }
}

If a result set exceeds this limit, it will still be returned but will not be cached.

Cache Invalidation Endpoints

NpgsqlRest can automatically create invalidation endpoints for each cached endpoint. When InvalidateCacheSuffix is configured, calling the invalidation endpoint with the same parameters removes the corresponding cache entry.

{
  "CacheOptions": {
    "Enabled": true,
    "InvalidateCacheSuffix": "invalidate"
  }
}

Example usage:

GET /api/get-user/?id=123           -> Returns cached user data
GET /api/get-user/invalidate?id=123 -> Removes cache entry, returns {"invalidated":true}
GET /api/get-user/?id=123           -> Fresh data (cache was cleared)

Key features:

Same authentication and authorization as the original endpoint
Same parameter handling - no need to know the internal cache key format
Works correctly with hashed cache keys
Returns {"invalidated":true} if cache entry was removed, {"invalidated":false} if not found

Cache Profiles

Cache profiles let you maintain multiple distinct caching policies in one application — different backends, expirations, key shapes, or per-parameter bypass conditions — and let endpoints opt into them via the @cache_profile annotation.

This is useful when one app needs:

Different cache backends for different data classes (e.g., Memory for hot per-user data, Redis for shared session data).
Different TTLs depending on input shape (e.g., historical queries cached for 1 hour, "until now" queries cached for 5 minutes).
Selective cache bypass (e.g., real-time queries with live=true always fetch fresh).

Overview

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Memory",                 // root cache (used by endpoints WITHOUT @cache_profile)
    // ... existing top-level fields ...
    "Profiles": {
      "fast_memory": {
        "Enabled": true,
        "Type": "Memory",
        "Expiration": "30 seconds",
        "Parameters": ["user_id"]
      },
      "shared_redis": {
        "Enabled": true,
        "Type": "Redis",
        "Expiration": "1 hour"
      },
      "timeseries": {
        "Enabled": true,
        "Type": "Memory",
        "Expiration": "1 hour",
        "Parameters": ["from", "to"],
        "When": [
          { "Parameter": "to", "Value": null, "Then": "5 minutes" }
        ]
      }
    }
  }
}

Endpoints without @cache_profile continue to use the root cache. Endpoints with @cache_profile <name> use the named profile.

Profile fields

Field	Type	Description
`Enabled`	bool	Default `false`. Set `true` to register the profile. Disabled profiles are skipped at startup with an Information log.
`Type`	string	`"Memory"`, `"Redis"`, or `"Hybrid"`. Required when `Enabled=true`.
`Expiration`	string?	Default expiration (PostgreSQL interval format, e.g. `"5 minutes"`, `"1 hour"`). Used when the endpoint has no `@cache_expires` annotation.
`Parameters`	string[]?	Default cache-key parameter list. Three semantics: `null`/missing → use all routine parameters; `[]` → URL-only cache (one entry per endpoint); `["x", "y"]` → use only these. The endpoint's `@cached p1, p2` annotation overrides this.
`When`	object[]?	List of conditional rules evaluated at request time (see below).

Backend pooling

All profiles of the same Type share a single backend instance: one Memory cache, one Redis connection, one HybridCache singleton. Backends are instantiated lazily — only types actually used (root + at least one enabled profile) get spun up. If no profile uses Redis and the root Type isn't Redis, no Redis connection is ever attempted, even if RedisConfiguration is set.

Cache entries written under a profile are prefixed with the profile name so two profiles sharing the same Memory backend cannot collide on the same routine + parameters. Endpoints without a profile have no prefix; existing pre-3.13 cache entries remain wire-compatible.

When rules

When is a list of rules evaluated against the request's resolved parameter values. Each rule has three fields:

Field	Type	Description
`Parameter`	string	Routine parameter name to inspect (matches against `ActualName` or `ConvertedName`). Required.
`Value`	scalar / array / null	Match condition. Scalar = exact match. Array = OR over entries. JSON `null` matches .NET `null`/`DBNull.Value` (does not match empty string). Other values are stringify-and-equal case-insensitive.
`Then`	string	Required action: the literal string `"skip"` to bypass the cache for this request, OR a PostgreSQL interval (e.g. `"30 seconds"`, `"5 minutes"`, `"1 hour"`) to override the entry's TTL when writing.

Rules are evaluated in declaration order; first match wins. If no rule matches, the entry is cached using the profile's Expiration.

Pattern: skip-on-condition

Bypass the cache entirely for some inputs:

"When": [
  { "Parameter": "to", "Value": null, "Then": "skip" }
]

When to is null/missing, no read or write happens — routine executes fresh. Common for "until-now" or "live" data.

Pattern: dynamic TTL

Different TTLs depending on input shape:

"When": [
  { "Parameter": "live", "Value": true,  "Then": "skip" },
  { "Parameter": "to",   "Value": null,  "Then": "5 minutes" }
]

live=true → bypass entirely (real-time mode).
live=false and to=null → 5-minute TTL (open-ended query).
Otherwise → fall through to the profile's Expiration (e.g. 1 hour for historical queries with both from and to).

Pattern: array-of-values

Match any of several values:

"When": [
  { "Parameter": "status", "Value": [null, ""], "Then": "skip" }
]

Matches when status is null OR empty string.

Validation

Misconfiguration is caught at startup so deploy issues surface early rather than silently disabling caching at runtime:

Connection pooler note

If you're using a connection pooler in transaction mode (PgBouncer, AWS RDS Proxy in transaction mode, Supabase Pooler), see WrapInTransaction — it's required for context-injection features but does not affect profiles directly.

Complete example

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Memory",
    "MaxCacheableRows": 1000,
    "InvalidateCacheSuffix": "invalidate",
    "Profiles": {
      "user_scoped_fast": {
        "Enabled": true,
        "Type": "Memory",
        "Expiration": "1 minute",
        "Parameters": ["user_id"]
      },
      "shared_long_term": {
        "Enabled": true,
        "Type": "Redis",
        "Expiration": "1 hour"
      },
      "timeseries_compute": {
        "Enabled": true,
        "Type": "Memory",
        "Expiration": "1 hour",
        "Parameters": ["from", "to", "live"],
        "When": [
          { "Parameter": "live", "Value": true,  "Then": "skip" },
          { "Parameter": "to",   "Value": null,  "Then": "5 minutes" }
        ]
      }
    }
  }
}

-- Uses root Memory cache (no profile)
comment on function get_app_settings() is 'HTTP GET
@cached
@cache_expires 1 hour';

-- Per-user 1-minute cache via fast Memory profile
comment on function get_my_dashboard(user_id int) is 'HTTP GET
@cache_profile user_scoped_fast';

-- Distributed Redis with 1-hour default
comment on function get_global_metrics() is 'HTTP GET
@cache_profile shared_long_term';

-- Mixed: long-cache historical queries, short-cache open-ended,
-- bypass entirely when `live` is true
comment on function compute_timeseries(from text, to text default null, live boolean default false) is 'HTTP GET
@cache_profile timeseries_compute';

Routine Annotations

Enable caching for specific routines using comment annotations:

cached

Mark a routine as cacheable:

comment on function get_products() is '
HTTP GET /products
@cached
';

Specify which parameters to use for the cache key:

comment on function get_product(p_id int) is '
HTTP GET /products
@cached p_id
';

If no parameters are specified, all parameters are used for the cache key.

cache_expires / cache_expires_in

Set cache expiration using interval format:

comment on function get_products() is '
HTTP GET /products
@cached
@cache_expires 5m
';

comment on function get_config() is '
HTTP GET /config
@cached
@cache_expires_in 1h
';

If no expiration is specified, cache entries never expire.

cache_profile

Select a named cache profile for the endpoint:

comment on function get_dashboard() is '
HTTP GET
@cache_profile fast_memory
';

@cache_profile implies caching — @cached is unnecessary alongside it but is still allowed and overrides the profile's Parameters list. See the dedicated @cache_profile annotation reference for full semantics.

Example Configuration

Production configuration with Redis:

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Redis",
    "RedisConfiguration": "redis-server:6379,password={REDIS_PASSWORD},ssl=true,abortConnect=false"
  }
}

Development configuration with memory cache:

{
  "CacheOptions": {
    "Enabled": true,
    "Type": "Memory",
    "MemoryCachePruneIntervalSeconds": 30
  }
}

Interval Format - Time/duration format reference
cached annotation - Enable caching on endpoints
cache_expires_in annotation - Set cache expiration
cache_profile annotation - Select a named cache profile per endpoint
Comment Annotations Guide - How annotations work
Configuration Guide - How configuration works

Next Steps

NpgsqlRest Options - Configure NpgsqlRest settings
Connection Settings - Configure database connections

Problem	Result
`@cache_profile` references an unknown name	Startup fails with single `InvalidOperationException` listing every unresolved name and the endpoints that referenced each
Profile registered but no endpoint references it	Information log: "registered but not used by any endpoint. Did you forget a `@cache_profile` annotation?"
Profile has missing/invalid `Type`	Warning, profile skipped
Profile has invalid `Expiration` (bad PG interval)	Warning, profile skipped
Profile name is empty/whitespace	Warning, profile skipped
`When` rule's `Parameter` isn't a routine parameter	Warning, rule dropped (other rules still apply)
`When` rule's `Parameter` isn't in the resolved cache-key list	Warning, rule dropped (otherwise different rule-evaluations would share a cache entry)
`When` rule has missing/invalid `Then`	Warning, rule dropped

Sunbelt Computer Software

PL/B Language Development and Support

Navigation Menu

Uh oh!

Cache Options

Overview

Settings Reference

Cache Types

Memory Cache

Redis Cache

Hybrid Cache

Cache Key Hashing

Caching Set-Returning Functions

Cache Invalidation Endpoints

Cache Profiles

Overview

Profile fields

Backend pooling

When rules

Pattern: skip-on-condition

Pattern: dynamic TTL

Pattern: array-of-values

Validation

Connection pooler note

Complete example

Routine Annotations

cached

cache_expires / cache_expires_in

cache_profile

Example Configuration

Related

Next Steps

See Also

Sunbelt Computer Software

PL/B Language Development and Support

Uh oh!

FilesExpand file tree

cache-options.md

Latest commit

History

cache-options.md

File metadata and controls

Cache Options

Overview

Settings Reference

Cache Types

Memory Cache

Redis Cache

Hybrid Cache

Cache Key Hashing

Caching Set-Returning Functions

Cache Invalidation Endpoints

Cache Profiles

Overview

Profile fields

Backend pooling

When rules

Pattern: skip-on-condition

Pattern: dynamic TTL

Pattern: array-of-values

Validation

Connection pooler note

Complete example

Routine Annotations

cached

cache_expires / cache_expires_in

cache_profile

Example Configuration

Related

Next Steps

See Also