Skip to main content
Markdown

Cache a PostgreSQL Lookup in Redis

Reduce repeated reference-data queries by caching PostgreSQL lookups in Redis. PostgreSQL remains the authoritative store, while expiring Redis keys serve recent values. Both clients execute in the same native Codon application; neither server needs a Codon extension.

Uses: PostgreSQL and Redis SDKs. Services: development PostgreSQL and Redis. Difficulty: advanced.

This is a bounded-staleness cache, not a transactionally consistent view across the two systems. Use authoritative reads for decisions that cannot tolerate an old price, such as final checkout or financial posting.

Prepare Development Connections​

Use your normal libpq configuration to select a development PostgreSQL database where your account can create temporary tables. Redis defaults to localhost port 6379; set REDIS_PORT or REDIS_SOCKET for another local test instance. The program writes only a temporary PostgreSQL table and a uniquely prefixed Redis key with a 60-second expiry. It deletes that key on successful completion.

No database superuser or server-side native module is needed. Do not point this local, unauthenticated Redis configuration at a production service. For remote connections, configure Redis ACLs and verified TLS and PostgreSQL certificate verification; keep credentials out of source code and command history.

Read Through the Cache​

catalog.codon
import os
import time
import exaloop.databases.postgres as postgres
from exaloop.databases.redis import Redis

def price(connection, cache, prefix: str, sku: str) -> Optional[int]:
    key = prefix + sku
    cached = cache.get(key, int)
    if cached is not None:
        print("redis", cached)
        return cached
    with connection.query("SELECT cents FROM cookbook_prices WHERE sku = $1", (sku,)) as result:
        row = result.fetchone(Tuple[int])
    if row is None:
        return None
    cents = row[0]
    cache.set(key, cents, ex=60)
    print("postgres", cents)
    return cents

prefix = "cookbook:prices:" + str(os.getpid()) + ":" + str(time.time()) + ":"
with postgres.connect() as connection:
    connection.execute("CREATE TEMP TABLE cookbook_prices (sku text PRIMARY KEY, cents bigint NOT NULL CHECK (cents >= 0))")
    connection.execute("INSERT INTO cookbook_prices VALUES ($1, $2)", ("SKU-7", 12500))
    with Redis(port=int(os.environ.get("REDIS_PORT", "6379")),
               unix_socket_path=os.environ.get("REDIS_SOCKET", "")) as cache:
        first = price(connection, cache, prefix, "SKU-7")
        second = price(connection, cache, prefix, "SKU-7")
        assert first == 12500 and second == 12500
        assert price(connection, cache, prefix, "unknown") is None
        cache.delete(prefix + "SKU-7")
Terminal
exaloop --with postgres --with redis run -release catalog.codon
Output
postgres 12500
redis 12500

The second lookup is a cache hit. An unknown SKU returns None; a zero price would still be a valid cached value. SQL parameters are bound separately from the query string. Redis writes set the value and expiry in one command.

Decide the Consistency Contract​

In a real application, use a stable namespace containing tenant identity and the price-list or schema version. The unique prefix here isolates test runs; using it in production would prevent sharing cache entries. Apply authorization before the lookup so one tenant cannot retrieve another tenant's cached data.

Expiry alone allows stale data. A delayed reader can also populate a stale value after a concurrent database update and cache invalidation. For stronger rules, use immutable versioned keys and verify the authoritative version, or bypass the cache. A Redis transaction cannot make a PostgreSQL update atomic with a cache mutation.

This example propagates Redis failures rather than silently changing behavior. An optional-cache service may instead fall back to PostgreSQL on connection failures, with concurrency limits and monitoring to prevent a database overload. Do not catch every error: malformed cache values and SQL failures need their own policy. Likewise, do not retry an uncertain database write as though it were a failed read.

For a high-volume service, add hit/miss and latency metrics, bounded TTL jitter, and a policy for concurrent cache misses. Missing SKUs are not cached here; negative caching requires a distinct representation and its own expiry. One client connection belongs to one worker at a time; these SDKs do not supply a shared connection pool.