CACHE_PROFILE

Select a named cache profile for an endpoint.

A cache profile bundles together a cache backend (Memory / Redis / Hybrid), a default expiration, the cache-key parameter list, and per-parameter conditional rules ("when X is null, bypass cache" or "when status='draft', cache 30 seconds"). Profiles are defined once in Cache Options configuration and selected per endpoint via this annotation.

@cache_profile implies caching — you don't also need @cached. Both @cached and @cache_expires annotations remain valid; when present they override the profile's defaults.

Syntax

code

@cache_profile <name>

The annotation accepts exactly one profile name. The name must match a profile defined in CacheOptions.Profiles and registered with "Enabled": true. Unknown names cause startup to fail with a single error listing every unresolved name and the offending endpoints.

Examples

Basic usage

sql

create function get_dashboard()
returns json
language sql
begin atomic;
select dashboard_data();
end;

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

The fast_memory profile (defined in CacheOptions.Profiles) supplies the backend, expiration, and any conditional rules.

Combined with cached / cache_expires

sql

comment on function get_user_report(user_id int, year int) is
'HTTP GET
@cache_profile shared_redis
@cached user_id, year
@cache_expires 1 hour';

The annotations override the profile's defaults:

• @cached user_id, year → cache key uses these params (overrides profile's Parameters list).
• @cache_expires 1 hour → entry TTL is 1 hour (overrides profile's Expiration).

The profile still supplies the cache backend (Redis in this case) and any When rules.

Multi-tenant search_path pattern

A profile that bypasses the cache when no end date is supplied — the request asks for "until now" data, which changes constantly:

jsonc

// in appsettings.json
"CacheOptions": {
  "Enabled": true,
  "Profiles": {
    "timeseries": {
      "Enabled": true,
      "Type": "Memory",
      "Expiration": "1 hour",
      "Parameters": ["from", "to"],
      "When": [
        { "Parameter": "to", "Value": null, "Then": "5 minutes" }
      ]
    }
  }
}

sql

comment on function compute_timeseries(from text, to text default null) is
'HTTP GET
@cache_profile timeseries';

Behavior per request:

• Both from and to present → 1-hour cache (historical query, safe to cache long).
• to is null → 5-minute cache (open-ended query; data may update at the matching cadence).

Tiered TTL by user role

jsonc

"CacheOptions": {
  "Profiles": {
    "tier_aware": {
      "Enabled": true,
      "Type": "Hybrid",
      "Parameters": ["tier"],
      "When": [
        { "Parameter": "tier", "Value": "free",  "Then": "5 minutes" },
        { "Parameter": "tier", "Value": "pro",   "Then": "1 hour" },
        { "Parameter": "tier", "Value": "admin", "Then": "skip" }
      ]
    }
  }
}

sql

comment on function get_account_data(tier text) is
'HTTP GET
@cache_profile tier_aware';

• Free tier → cached 5 minutes.
• Pro tier → cached 1 hour.
• Admin tier → never cached (always fresh).

Behavior

• @cache_profile implies @cached — explicit @cached is unnecessary.
• The profile's Cache (backend instance) is used instead of the root DefaultRoutineCache.
• The profile's Expiration is used unless overridden by @cache_expires.
• The profile's Parameters list is used as the default cache-key set unless overridden by @cached <list>.
• The profile's When rules are evaluated at request time; first match wins. Rules can "skip" (bypass cache) or override TTL with a PostgreSQL interval.
• Cache entries written under a profile are prefixed with the profile name, so two profiles sharing the same backend (e.g., two Memory profiles) cannot collide.
• The cache invalidation endpoint (when InvalidateCacheSuffix is configured) routes through the same profile backend.

Validation

Misconfiguration is caught at startup:

Problem	Result
Unknown profile name referenced by @cache_profile	Startup fails with single error listing every unresolved name + offending endpoints
Profile registered but no endpoint references it	Information-level log warning
When rule references a parameter that's not in the cache-key list	Rule dropped at startup with Warning (other rules still apply)
Multiple @cache_profile arguments (e.g. @cache_profile a b)	Annotation ignored with Warning; one name only

Related

• Cache Profiles configuration — full reference for profile fields and When rules
• CACHED — bare cached annotation (no profile)
• CACHE_EXPIRES_IN — per-endpoint TTL override
• Interval Format — time/duration format reference

See Also

• Cache Options — top-level cache backend and profile configuration