Result Caching¶
How to cache query results with fraiseql.caching: a PostgreSQL-backed cache with
TTL expiry and explicit invalidation. The reference is the
Caching Specification; this page shows how to use it.
Overview¶
fraiseql.caching stores results in a PostgreSQL UNLOGGED table:
- No extra service: the cache lives in the database you already run, and every application instance using that database shares it.
- TTL expiry: each entry has an
expires_at; expired entries are never returned. - Explicit invalidation: by key, by
*pattern, or by view (CachedRepository.invalidate_view).CachedRepository.execute_functioninvalidates the views of the entity the SQL function writes. - Tenant isolation:
CachedRepositoryputs the repository context'stenant_idin every key.
It is opt-in, and you wire it yourself: create_fraiseql_app has no caching
setting, and there is no FraiseQLConfig setting or FRAISEQL_CACHE_* environment
variable for it. You build the cache in Python and hand a CachedRepository to your
resolvers as info.context["db"].
What it doesn't do: it doesn't cache whole GraphQL responses (CachedRepository
caches each find / find_one call), class mutations (@fraiseql.mutation class
...) invalidate nothing, and the CASCADE rules of the pg_fraiseql_cache extension
don't invalidate its entries (see pg_fraiseql_cache).
The speed-up depends on how expensive the query behind an entry is: a hit is one primary-key lookup in the cache table plus the transfer of the stored value. Measure it on your own queries.
Quick Start¶
Standalone¶
from psycopg_pool import AsyncConnectionPool
import fraiseql
from fraiseql.caching import CacheConfig, CachedRepository, PostgresCache, ResultCache
from fraiseql.db import FraiseQLRepository
@fraiseql.type(sql_source="v_user") # a view with an id column and a data JSONB column
class User:
id: int
name: str
status: str
pool = AsyncConnectionPool("postgresql://user:pass@localhost/mydb", open=False)
await pool.open()
# Backend: the UNLOGGED table fraiseql_cache, created on first use
postgres_cache = PostgresCache(pool)
# Get-or-set logic, TTLs and hit/miss statistics
result_cache = ResultCache(backend=postgres_cache, config=CacheConfig(default_ttl=300))
# The repository wrapper; tenant_id goes into every cache key
base_repo = FraiseQLRepository(pool, context={"tenant_id": "tenant-123"})
cached_repo = CachedRepository(base_repo, result_cache)
users = await cached_repo.find("v_user", "users", mandatory_filters={"status": "active"})
users = await cached_repo.find("v_user", "users", mandatory_filters={"status": "active"}) # hit
print(bytes(users)) # b'{"data":{"users":[{"__typename":"User","id":1,...}]}}'
print(result_cache.get_stats()) # CacheStats(hits=1, misses=1, errors=0)
find and find_one take the wrapped repository's arguments (view_name,
field_name, info, then where, order_by, limit, offset,
mandatory_filters...) and return what it returns: a RustResponseBytes. On a hit
it holds the same bytes as the response that was cached.
FastAPI Integration¶
create_fraiseql_app opens the database pool; build the cache in a lifespan
(which runs once the pool is open) and return a CachedRepository as db from a
context_getter:
import contextlib
from collections.abc import AsyncIterator
from fastapi import FastAPI, Request
import fraiseql
from fraiseql.caching import CachedRepository, PostgresCache, ResultCache
from fraiseql.db import FraiseQLRepository
from fraiseql.fastapi import create_fraiseql_app
from fraiseql.fastapi.dependencies import get_db_pool
@fraiseql.type(sql_source="v_user")
class User:
id: int
name: str
@fraiseql.query
async def users(info) -> list[User]:
return await info.context["db"].find("v_user", "users", info, cache_ttl=60)
@contextlib.asynccontextmanager
async def lifespan(app: FastAPI) -> AsyncIterator[None]:
backend = PostgresCache(get_db_pool())
await backend.initialize() # create the table, detect pg_fraiseql_cache
app.state.cache = ResultCache(backend)
yield
async def context_getter(request: Request, user=None) -> dict:
# The tenant comes from the authenticated user, never from a client argument
tenant_id = user.metadata.get("tenant_id") if user else None
repo = FraiseQLRepository(get_db_pool(), context={"tenant_id": tenant_id})
return {"db": CachedRepository(repo, request.app.state.cache), "user": user}
app = create_fraiseql_app(
database_url="postgresql://user:pass@localhost/mydb",
types=[User],
queries=[users],
lifespan=lifespan,
context_getter=context_getter,
)
What context_getter returns is merged over fraiseql's default context, so keys you
don't return (loader_registry, config...) keep their defaults. Pass info to
find: the key then covers the GraphQL selection, so { users { id } } and
{ users { id name } } get separate entries.
PostgreSQL Cache Backend¶
UNLOGGED Tables¶
PostgresCache creates this table (and index) on its first operation:
CREATE UNLOGGED TABLE IF NOT EXISTS fraiseql_cache (
cache_key TEXT PRIMARY KEY,
cache_value JSONB NOT NULL,
expires_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS fraiseql_cache_expires_idx ON fraiseql_cache (expires_at);
An UNLOGGED table skips the write-ahead log, so writes are cheaper than in a
regular table. The trade-offs:
- PostgreSQL empties the table after a crash (not after a clean restart), which a cache can afford.
- It isn't replicated to standbys: use the primary's pool for the cache.
Extension Detection¶
PostgresCache detects the pg_fraiseql_cache extension on its first operation,
whichever it is (ping() included), or on initialize():
cache = PostgresCache(pool)
await cache.initialize() # or: await cache.ping()
if cache.has_domain_versioning:
print(f"pg_fraiseql_cache {cache.extension_version} detected")
else:
print("pg_fraiseql_cache not installed")
It reads extversion from pg_extension. If the query fails (permissions, say), it
logs a warning and sets has_domain_versioning to False.
Configuration¶
PostgresCache Options¶
from fraiseql.caching import PostgresCache
cache = PostgresCache(
connection_pool=pool, # a psycopg_pool.AsyncConnectionPool
table_name="fraiseql_cache", # may be schema-qualified: "app.fraiseql_cache"
auto_initialize=True, # the first operation creates the table if missing
)
With auto_initialize=False no operation creates the table: create it yourself (the
SQL above), or call await cache.initialize() once, which always creates it.
Operations on a missing table raise PostgresCacheError, which ResultCache logs and
counts as an error.
ResultCache Options¶
from fraiseql.caching import CacheConfig, ResultCache
result_cache = ResultCache(
backend=postgres_cache,
config=CacheConfig(
enabled=True, # False: get_or_set always runs the function
default_ttl=300, # seconds, when a call gives no TTL
max_ttl=3600, # every TTL is capped to this
cache_errors=False, # on a backend read error, don't try to store the result
key_prefix="fraiseql",
),
)
Statistics are always kept (result_cache.get_stats()); there is no switch for them.
The configuration is a plain dataclass: result_cache.config.default_ttl = 600
changes the default for later calls.
There is no environment variable for any of this. Read your own and pass them in:
import os
config = CacheConfig(
enabled=os.environ.get("APP_CACHE_ENABLED", "true") == "true",
default_ttl=int(os.environ.get("APP_CACHE_TTL", "300")),
)
CachedRepository Options¶
from fraiseql.caching import CachedRepository
cached_repo = CachedRepository(base_repository=base_repo, cache=result_cache)
# Custom TTL for this call (still capped to max_ttl)
users = await cached_repo.find(
"v_user", "users", cache_ttl=600, mandatory_filters={"status": "active"}
)
# Bypass the cache: read the view, store nothing
users = await cached_repo.find(
"v_user", "users", skip_cache=True, mandatory_filters={"status": "active"}
)
find_one takes the same two options. A find_one that finds nothing (None)
isn't cached.
Cache Cleanup¶
Expired rows aren't returned, but they stay in the table until
cleanup_expired() deletes them (it returns how many). Nothing runs it for you;
start a task in your lifespan:
import asyncio
import contextlib
from collections.abc import AsyncIterator
from fastapi import FastAPI
from fraiseql.caching import PostgresCache, ResultCache
from fraiseql.fastapi.dependencies import get_db_pool
async def purge_expired(backend: PostgresCache, every: float = 300) -> None:
while True:
await backend.cleanup_expired()
await asyncio.sleep(every)
@contextlib.asynccontextmanager
async def lifespan(app: FastAPI) -> AsyncIterator[None]:
backend = PostgresCache(get_db_pool())
app.state.cache = ResultCache(backend)
cleanup = asyncio.create_task(purge_expired(backend))
yield
cleanup.cancel()
Multi-Tenant Security¶
Tenant Isolation in Cache Keys¶
CachedRepository reads tenant_id from the wrapped repository's context and puts
it second in every key:
base_repo = FraiseQLRepository(pool, context={"tenant_id": "tenant-123"})
cached_repo = CachedRepository(base_repo, result_cache)
await cached_repo.find("v_user", "users", mandatory_filters={"status": "active"})
# key: fraiseql:tenant-123:v_user:q:fa9e468b99987da0
Without a tenant_id in the context, the key has no tenant part
(fraiseql:v_user:q:<hash>), and every caller with the same arguments shares the
entry. In a multi-tenant application that means one tenant can be served another
tenant's cached rows, even when row-level security filters the view on
app.tenant_id: a hit doesn't query the view. Always build the repository with the
tenant id, taken from the authenticated context, never from a client argument:
# Correct: one entry per tenant
base_repo = FraiseQLRepository(pool, context={"tenant_id": tenant_id})
# Wrong in a multi-tenant application: one entry for every tenant
base_repo = FraiseQLRepository(pool, context={})
The same tenant_id also sets the app.tenant_id session variable when the
repository queries the database.
Cache Key Structure¶
<prefix>:<tenant_id>:<view>:q:<hash> find
<prefix>:<tenant_id>:<view>:one:q:<hash> find_one
<prefix>:<view>:q:<hash> find, no tenant_id in the context
<prefix> is CacheConfig.key_prefix (fraiseql). The hash covers field_name,
the keyword arguments (where, order_by, limit, mandatory_filters...) and, with
info, the GraphQL selection (fields, aliases, fragments, variables).
Keys you build yourself (with ResultCache.get_or_set or cached_query) get a tenant
only if you put one in them; see Cache Key Strategy.
Invalidation¶
Writes Through the Repository¶
execute_function(name, input) and execute_function_with_context(...) on a
CachedRepository run the SQL function, then invalidate the entries of the entity
it writes, for every tenant. fn_update_user (or update_user,
app.fn_update_user) invalidates the views user, users, v_user, v_users,
tv_user and tv_users, in any schema; a multi-word entity (fn_create_blog_post)
also those of its last word (post).
await cached_repo.execute_function("fn_update_user", {"id": 1, "name": "Alicia"})
# Cache miss: reads the view again and stores the new result
users = await cached_repo.find("v_user", "users", mandatory_filters={"status": "active"})
A function-based mutation that calls execute_function on info.context["db"]
goes through this path:
@fraiseql.mutation
async def rename_user(info, id: int, name: str) -> bool:
await info.context["db"].execute_function("fn_update_user", {"id": id, "name": name})
return True
Class Mutations and Other Writes¶
A class mutation (@fraiseql.mutation class UpdateUser) calls its SQL function
without going through the repository, so it invalidates nothing; nor does a write
made with raw SQL, by another service, or by a trigger. Rely on the TTL, or
invalidate from your code after the write:
await cached_repo.invalidate_view("v_user") # every tenant
await cached_repo.invalidate_view("v_user", all_tenants=False) # the context's tenant only
await cached_repo.invalidate_for_function("fn_update_user") # what execute_function does
By Key or Pattern¶
ResultCache deletes one key, or every key matching a pattern:
await result_cache.invalidate("fraiseql:top_sellers") # one key
await result_cache.invalidate_pattern("fraiseql:tenant-123:*") # every key of a tenant
A pattern's * becomes SQL LIKE's %, so a _ in a pattern also matches any one
character. Both methods log errors instead of raising them.
pg_fraiseql_cache Extension¶
pg_fraiseql_cache is a separate PostgreSQL extension (see
PostgreSQL Extensions) that keeps per-tenant
domain version counters, bumped by triggers, and CASCADE rules between domains.
FraiseQL doesn't install it:
psql -d mydb -c "CREATE EXTENSION pg_fraiseql_cache;"
When it is installed (has_domain_versioning is True), PostgresCache offers:
get_domain_versions(tenant_id, domains): the current versions, e.g.{"user": 42}({}without the extension).set(key, value, ttl, versions={...}): stores{"result": value, "versions": ..., "cached_at": ...}. Without the extension, or withoutversions, the value is stored as is.get(key)returns theresultpart of such an entry;get_with_metadata(key)returns(result, versions)(versionsisNonefor a plain entry).setup_table_trigger(table, domain_name=None, tenant_column="tenant_id"): calls the extension'sfraiseql_cache.setup_table_invalidation.register_cascade_rule(source, target)andsetup_auto_cascade_rules(cache, schema)(rules derived from the GraphQL schema bySchemaAnalyzer; see the specification).
Without the extension, setup_table_trigger and register_cascade_rule log a
warning and do nothing; register_cascade_rule returns False, and
setup_auto_cascade_rules returns 0, the number of rules it actually registered.
ResultCache, cached_query and CachedRepository never pass versions and never
compare them: with or without the extension, their entries are invalidated only by
TTL and by the invalidation calls above. If you want version-checked entries, you
call set(..., versions=...), get_with_metadata and get_domain_versions on the
PostgresCache yourself.
Usage Patterns¶
Pattern 1: Repository-Level Caching¶
Every find / find_one through the wrapper is cached; writes through
execute_function invalidate:
cached_repo = CachedRepository(base_repo, result_cache)
users = await cached_repo.find("v_user", "users", mandatory_filters={"status": "active"})
user = await cached_repo.find_one("v_user", "user", where={"id": {"eq": user_id}})
await cached_repo.execute_function("fn_update_user", {"id": user_id, "name": "Alicia"})
Pattern 2: Explicit Cache Control¶
get_or_set(key, func, ttl=None) returns the stored value, or awaits func(),
stores its result and returns it. The value must be JSON-serializable: a value
json.dumps can't encode (a datetime, a UUID, a dataclass) isn't stored, and the
failure is logged and counted in stats.errors.
key = result_cache.key_builder.build_key("active_users", tenant_id=tenant_id, limit=10)
# 'fraiseql:tenant-123:active_users:limit:10'
async def load_active_users() -> list[dict]:
async with pool.connection() as conn:
cur = await conn.execute(
"SELECT data FROM v_user WHERE tenant_id = %s AND status = 'active'"
" ORDER BY id LIMIT 10",
(tenant_id,),
)
return [row[0] for row in await cur.fetchall()]
active_users = await result_cache.get_or_set(key, load_active_users, ttl=300)
ResultCache has no separate get / set; for those, call the backend
(result_cache.backend.get(key), .set(key, value, ttl)).
Pattern 3: Decorator-Based Caching¶
cached_query(cache, ttl=None, key_func=None) wraps an async function in
get_or_set:
from fraiseql.caching import cached_query
@cached_query(result_cache, ttl=600)
async def user_names(status: str) -> list[str]:
async with pool.connection() as conn:
cur = await conn.execute(
"SELECT data->>'name' FROM v_user WHERE status = %s ORDER BY id", (status,)
)
return [row[0] for row in await cur.fetchall()]
await user_names(status="active") # computed and stored
await user_names(status="active") # from the cache
await user_names(status="active", skip_cache=True) # computed, not stored
The default key is the function name followed by its positional arguments and its
sorted keyword arguments (user_names:status:active): no prefix and no tenant. Pass
key_func (called with the keyword arguments) to build your own:
@cached_query(
result_cache,
ttl=600,
key_func=lambda tenant_id, status: f"fraiseql:{tenant_id}:user_names:{status}",
)
async def tenant_user_names(tenant_id: str, status: str) -> list[str]: ...
Don't put cached_query on a GraphQL resolver: its default key would include the
info object's text, and find returns RustResponseBytes, which isn't JSON. Use
CachedRepository in resolvers.
Pattern 4: Conditional Caching¶
Choose per call with skip_cache and cache_ttl:
async def find_users(repo: CachedRepository, info, **kwargs):
# Lookups by id are cheap: don't cache them
if "id" in kwargs.get("where", {}):
return await repo.find("v_user", "users", info, skip_cache=True, **kwargs)
return await repo.find("v_user", "users", info, cache_ttl=300, **kwargs)
Cache Key Strategy¶
Key Components¶
CacheKeyBuilder builds the keys of ResultCache.key_builder. Use it for keys you
build yourself:
from fraiseql.caching import CacheKeyBuilder
key_builder = CacheKeyBuilder(prefix="fraiseql")
key_builder.build_key(
"v_user",
tenant_id="tenant-123",
filters={"status": "active", "role": "admin"},
order_by=[("created_at", "desc")],
limit=10,
offset=0,
)
# 'fraiseql:tenant-123:v_user:role:admin:status:active:order:created_at:desc:limit:10:offset:0'
The parts are joined with :: the prefix, the tenant id (when given), the query
name, each filter sorted by field, order:field:direction, limit:N, offset:N,
then any extra keyword arguments. Pass order_by as (field, direction) tuples.
Key Normalization¶
Filters are sorted by field, so their order doesn't matter:
key1 = key_builder.build_key("v_user", tenant_id="t1", filters={"status": "active", "role": "admin"})
key2 = key_builder.build_key("v_user", tenant_id="t1", filters={"role": "admin", "status": "active"})
assert key1 == key2
Filter Serialization¶
filters={"user_id": UUID("00000000-0000-0000-0000-000000000000")}
# → user_id:00000000-0000-0000-0000-000000000000
filters={"created_after": datetime(2025, 1, 1)}
# → created_after:2025-01-01T00:00:00
filters={"age": {"gte": 18, "lt": 65}} # operator dict, sorted by operator
# → age:gte:18:lt:65
filters={"status": {"in": ["pending", "active"]}} # list of strings: sorted, joined
# → status:in:active,pending
filters={"ids": [UUID(int=1), UUID(int=2)]} # other list: 8-character MD5 prefix
# → ids:5ce60e84
filters={"is_active": True}
# → is_active:true
filters={"deleted_at": None}
# → deleted_at:null
Pattern-Based Invalidation¶
build_mutation_pattern(name) returns <prefix>:<name>:*:
key_builder.build_mutation_pattern("v_user")
# 'fraiseql:v_user:*'
That pattern matches only keys without a tenant part. It doesn't match
fraiseql:tenant-a:v_user:.... For CachedRepository entries, call
invalidate_view("v_user"); for keys you built with a tenant, pass your own
pattern:
await result_cache.invalidate_pattern("fraiseql:*:v_user:*") # every tenant
await result_cache.invalidate_pattern(f"fraiseql:{tenant_id}:*") # one tenant, every view
Monitoring & Metrics¶
Cache Statistics¶
ResultCache.get_stats() (not async) returns this process's counters;
PostgresCache.get_stats() (async) describes the table, shared by every instance:
stats = result_cache.get_stats() # CacheStats(hits=..., misses=..., errors=...)
print(f"Hit rate: {stats.hit_rate:.1f}%") # 0-100
print(f"Hits: {stats.hits}, Misses: {stats.misses}, Errors: {stats.errors}")
result_cache.reset_stats()
table = await postgres_cache.get_stats()
print(f"Total entries: {table['total_entries']}")
print(f"Expired entries: {table['expired_entries']}")
print(f"Active entries: {table['active_entries']}")
print(f"Table size: {table['table_size_bytes'] / 1024 / 1024:.2f} MB")
A growing errors count usually means values that can't be stored (not JSON) or a
backend that can't be reached.
PostgreSQL Monitoring¶
-- Cache table size
SELECT
pg_size_pretty(pg_total_relation_size('fraiseql_cache')) AS total_size,
pg_size_pretty(pg_relation_size('fraiseql_cache')) AS table_size,
pg_size_pretty(pg_indexes_size('fraiseql_cache')) AS index_size;
-- Entries
SELECT
COUNT(*) AS total_entries,
COUNT(*) FILTER (WHERE expires_at > NOW()) AS active_entries,
COUNT(*) FILTER (WHERE expires_at <= NOW()) AS expired_entries
FROM fraiseql_cache;
-- Entries per tenant and view (CachedRepository keys)
SELECT
split_part(cache_key, ':q:', 1) AS key_prefix,
COUNT(*) AS entries
FROM fraiseql_cache
GROUP BY key_prefix
ORDER BY entries DESC
LIMIT 20;
-- Entries expiring per hour
SELECT
date_trunc('hour', expires_at) AS hour,
COUNT(*) AS entries_expiring
FROM fraiseql_cache
WHERE expires_at > NOW()
GROUP BY hour
ORDER BY hour;
Prometheus Metrics¶
FraiseQL exports no metric for this cache. You can publish the counters with
prometheus_client:
from prometheus_client import Gauge
cache_hits = Gauge("app_result_cache_hits", "ResultCache hits in this process")
cache_misses = Gauge("app_result_cache_misses", "ResultCache misses in this process")
cache_errors = Gauge("app_result_cache_errors", "ResultCache backend errors in this process")
cache_entries = Gauge("app_result_cache_entries", "Unexpired rows in the cache table")
cache_hits.set_function(lambda: result_cache.get_stats().hits)
cache_misses.set_function(lambda: result_cache.get_stats().misses)
cache_errors.set_function(lambda: result_cache.get_stats().errors)
async def update_cache_gauges() -> None: # call it periodically, e.g. from purge_expired
stats = await postgres_cache.get_stats()
cache_entries.set(stats["active_entries"])
Logging¶
import logging
logging.getLogger("fraiseql.caching").setLevel(logging.INFO)
The loggers are fraiseql.caching.postgres_cache and fraiseql.caching.result_cache.
At INFO you see, for example:
pg_fraiseql_cache not installed, using TTL-only cachingor✓ Detected pg_fraiseql_cache v1.0.0PostgreSQL cache table 'fraiseql_cache' initializedCleaned 145 expired cache entriesInvalidated 3 cache keys matching: fraiseql:*:v_user:*(one line per pattern: anexecute_functioncall writes several)
At WARNING: Cache get error for key ... and Cache set error for key ... (the
call still returns the function's result). At ERROR: Failed to get cache key
'...': ... and the other backend failures.
Best Practices¶
1. Always Set tenant_id¶
# Correct: tenant_id in the repository context
repo = FraiseQLRepository(pool, context={"tenant_id": tenant_id})
# Wrong in a multi-tenant application: entries shared by every tenant
repo = FraiseQLRepository(pool, context={})
2. Choose Appropriate TTLs¶
| Data | Changes | TTL |
|---|---|---|
| Reference data (countries, currencies) | Rarely | up to max_ttl |
| Catalogs, profiles | Hours | 600-1800 s |
| Aggregates, rankings | Minutes | 60-300 s |
| Real-time data | Seconds | don't cache, or 5-30 s |
recent_orders = await cached_repo.find("v_order", "orders", cache_ttl=60)
categories = await cached_repo.find("v_category", "categories", cache_ttl=1800)
max_ttl (3600 by default) caps every TTL: raise it in CacheConfig for data that
may live longer than an hour.
3. Use skip_cache for Real-Time Data¶
admin_stats = await cached_repo.find("v_admin_stats", "adminStats", skip_cache=True)
4. Invalidate on Writes¶
Write through cached_repo.execute_function(...), or call invalidate_view after a
write the cache doesn't see (class mutations included).
5. Monitor Cache Health¶
import logging
logger = logging.getLogger(__name__)
async def check_cache_health() -> None:
table = await postgres_cache.get_stats()
# Many expired rows: cleanup_expired isn't running often enough
if table["expired_entries"] > 10_000:
logger.warning("High expired entry count: %s", table["expired_entries"])
# Large table: keys with unbounded cardinality, or large values
if table["table_size_bytes"] > 1_000_000_000:
logger.warning("Cache table large: %s bytes", table["table_size_bytes"])
# Low hit rate (this process): TTLs too short, or keys that never repeat
stats = result_cache.get_stats()
if stats.total > 1000 and stats.hit_rate < 50:
logger.warning("Low cache hit rate: %.1f%%", stats.hit_rate)
6. Vacuum the Cache Table¶
Autovacuum covers UNLOGGED tables. After a large cleanup_expired() you can run:
VACUUM ANALYZE fraiseql_cache;
7. Partition Large Caches¶
PostgresCache uses an existing table as is, so you can create a hash-partitioned one
yourself. The parent keeps the primary key (set relies on ON CONFLICT
(cache_key)), and each partition is created UNLOGGED (PostgreSQL doesn't make a
partition unlogged because its parent is, and PostgreSQL 17 rejects UNLOGGED on a
partitioned table):
CREATE TABLE fraiseql_cache (
cache_key TEXT PRIMARY KEY,
cache_value JSONB NOT NULL,
expires_at TIMESTAMPTZ NOT NULL
) PARTITION BY HASH (cache_key);
CREATE UNLOGGED TABLE fraiseql_cache_0 PARTITION OF fraiseql_cache
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE UNLOGGED TABLE fraiseql_cache_1 PARTITION OF fraiseql_cache
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE UNLOGGED TABLE fraiseql_cache_2 PARTITION OF fraiseql_cache
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE UNLOGGED TABLE fraiseql_cache_3 PARTITION OF fraiseql_cache
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE INDEX fraiseql_cache_expires_idx ON fraiseql_cache (expires_at);
On a partitioned table, PostgresCache.get_stats() reports table_size_bytes as 0
(the parent has no storage of its own); sum the partitions' sizes instead.
Troubleshooting¶
Low Cache Hit Rate¶
Symptom: result_cache.get_stats().hit_rate is low.
Causes:
- TTLs too short.
- Keys that differ between calls that should share one: different arguments, selections or variables give different keys.
- Invalidation after every write of a frequently written entity.
Solutions:
# Longer default TTL (still capped to max_ttl)
result_cache.config.default_ttl = 600
# Key cardinality: many entries for few views means keys that rarely repeat
table = await postgres_cache.get_stats()
print(f"Total entries: {table['total_entries']}")
Inspect the keys themselves with the "entries per tenant and view" query above.
Stale Data¶
Symptom: cached data doesn't reflect recent changes.
Causes:
- The write didn't go through
CachedRepository.execute_function(a class mutation, raw SQL, another service). - The function name doesn't name the view's entity (
fn_rename_memberwritingv_user). - TTL too long.
Solutions:
# Invalidate after the write
await cached_repo.invalidate_view("v_user")
# Or a shorter TTL for frequently changing data
users = await cached_repo.find("v_user", "users", info, cache_ttl=30)
The pg_fraiseql_cache extension doesn't help here: CachedRepository doesn't check
domain versions.
Nothing Is Cached¶
Symptom: stats.misses and stats.errors grow together, with Cache set
error warnings.
Cause: values that aren't JSON-serializable, passed to get_or_set or
cached_query. Return dicts, lists, strings and numbers (convert datetime and
UUID to strings). CachedRepository stores RustResponseBytes for you.
Cache Table Growing¶
Symptom: the cache table keeps growing.
Causes:
cleanup_expired()isn't running.- Keys with unbounded cardinality (one per user input).
- Large cached results.
Solutions:
-- Table size
SELECT pg_size_pretty(pg_total_relation_size('fraiseql_cache'));
-- Manual cleanup
DELETE FROM fraiseql_cache WHERE expires_at <= NOW();
VACUUM fraiseql_cache;
Run cleanup_expired() more often (the every argument of purge_expired above),
and use skip_cache=True for queries whose results are large or never repeat.
Connection Pool Exhaustion¶
Symptom: PoolTimeout errors, or requests waiting for a connection.
Cause: every cache operation borrows a connection from the pool, on top of the connections the queries use.
Solution: give the cache its own, smaller pool:
from psycopg_pool import AsyncConnectionPool
cache_pool = AsyncConnectionPool(db_url, min_size=1, max_size=5, open=False)
await cache_pool.open()
cache = PostgresCache(cache_pool)
Cache Table Corruption¶
Symptom: unexpected errors from the cache table.
Solution: drop it (it only holds cache entries). PostgresCache recreates it on
its next first operation (a new process, or await cache.initialize()):
DROP TABLE IF EXISTS fraiseql_cache;
-- Or recreate it yourself:
CREATE UNLOGGED TABLE IF NOT EXISTS fraiseql_cache (
cache_key TEXT PRIMARY KEY,
cache_value JSONB NOT NULL,
expires_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS fraiseql_cache_expires_idx ON fraiseql_cache (expires_at);
A running PostgresCache creates its table only once: after a drop, its operations
fail (logged, counted as errors) until the table exists again.
Extension Not Detected¶
Symptom: has_domain_versioning is False although the extension is installed.
Causes:
- The extension is installed in another database.
- Detection hasn't run yet: it runs on the first operation, or
initialize(). - The detection query failed (a warning
Failed to detect pg_fraiseql_cache extensionis logged).
Solutions:
-- Is it installed in this database?
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_fraiseql_cache';
async def check_cache_extension(pool) -> None:
cache = PostgresCache(pool)
await cache.ping() # runs the first-use setup, detection included
print(f"Extension detected: {cache.has_domain_versioning}")
print(f"Extension version: {cache.extension_version}")
Next Steps¶
- Caching Specification - Reference for
fraiseql.caching - Performance Optimization - Full performance stack (Rust, APQ, TurboRouter)
- Multi-Tenancy - Tenant-aware caching patterns
- Monitoring - Production monitoring setup
- Security - Cache security best practices