Cache Options

Caching configuration for PostgreSQL routines that return single result sets.

Overview

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

Settings Reference

Setting	Type	Default	Description
Enabled	bool	false	Enable caching for routines.
Type	string	"Memory"	Cache type: "Memory" or "Redis".
MemoryCachePruneIntervalSeconds	int	60	How often to prune expired items from memory cache (in seconds).
RedisConfiguration	string	(see below)	Redis connection string. Only used when Type is "Redis".

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.

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/{p_id}
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 PostgreSQL interval format:

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

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

Supported interval formats:

• 5 minutes or 5min
• 1 hour or 1h
• 30 seconds or 30s
• 1 day or 1d

If no expiration is specified, cache entries never expire.

Limitations

WARNING

Only routines that return a single result set can be cached. Routines returning tables (RETURNS TABLE) or sets (RETURNS SETOF) cannot be cached.

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
  }
}

Related

• cached annotation - Enable caching on endpoints
• cache_expires_in annotation - Set cache expiration
• Comment Annotations Guide - How annotations work
• Configuration Guide - How configuration works

Next Steps

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