-   [](https://platform.exaloop.io/)
-   [Cookbook](https://platform.exaloop.io/cookbook/)
-   Cache a PostgreSQL Lookup in Redis

# 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](https://platform.exaloop.io/db/redis/client/#connections-and-security) and [PostgreSQL certificate verification](https://platform.exaloop.io/db/postgres/client/#connections-and-security); keep credentials out of source code and command history.

## Read Through the Cache

```python title="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")
```

```sh title="Terminal"
exaloop --with postgres --with redis run -release catalog.codon
```

```text title="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.

## Related Guides

-   [PostgreSQL client transactions](https://platform.exaloop.io/db/postgres/client/#transactions)
-   [Redis pipelines and bulk reads](https://platform.exaloop.io/db/redis/client/#large-collections-and-bulk-work)
-   [MySQL client applications](https://platform.exaloop.io/db/mysql/client/) for an alternative authoritative store

Source: https://platform.exaloop.io/cookbook/cache-aside/