Day 164 — Capstone: Persistence & Cache

Month 6 · Week 4 · ⬅ Day 163 · Day 165 ➡ · Journal index

🎯 Learning Objective

Back the repository port with Postgres (pgx) behind SQL migrations, and put a Redis cache-aside layer in front of reads so resolves are fast and the DB is shielded.

📚 Topics

• pgx connection pool · parameterized queries · migrations (goose/golang-migrate)
• Cache-aside (lazy load) with Redis · TTL · cache invalidation on write

📖 Reading / Sources

• pgx — PostgreSQL driver & toolkit
• database/sql vs pgx pool docs
• go-redis client
• AWS — Caching patterns: cache-aside

📝 Notes

• pgRepo is just another adapter implementing link.Repository; the service is untouched. Swapping memory→Postgres is one line in main → [[ports-and-adapters]].
• Always parameterize. db.QueryRow(ctx, "SELECT url FROM links WHERE code=$1", code) — never string-concatenate input → prevents SQL injection.
• A pgx pool (pgxpool.Pool) is safe for concurrent use; share one and pass ctx to every call so slow queries respect request cancellation → [[context]].
• Map driver errors to the port's sentinel: pgx.ErrNoRows → wrap as link.ErrNotFound with %w. Callers stay backend-agnostic → [[error-wrapping]].
• Cache-aside read path: check Redis → hit returns immediately; miss loads from Postgres, writes to Redis with a TTL, returns. Never cache a miss as an error; never cache a failed load → [[cache-aside]].
• Write path invalidates: on Save/update, delete (or overwrite) the cache key so the next read repopulates fresh. Stale cache is the classic cache bug.
• Hit counting is high-write/low-value-per-write → batch it (increment in Redis, flush to Postgres periodically) rather than a synchronous DB write per redirect.
• Schema lives in versioned migrations, applied at deploy, not via CREATE TABLE in app code → reproducible, reviewable schema history.

💻 Code Examples

The cache-aside concept is fully runnable in stdlib (LRU+TTL standing in for Redis):

// GetOrLoad: serve from cache, else load from the source and cache the result.
func (c *Cache) GetOrLoad(key string, load Loader) (string, error) {
    if v, ok := c.get(key); ok {
        return v, nil // HIT
    }
    v, err := load(key) // MISS -> hit the slow source
    if err != nil {
        return "", err // never cache failures
    }
    c.set(key, v)
    return v, nil
}

Full runnable cache-aside demo: examples/month-06/cacheaside · Run: go run ./examples/month-06/cacheaside

The real pgx + redis adapters (third-party — snippet only):

// internal/store/postgres/repo.go
func (r *Repo) ByCode(ctx context.Context, code string) (link.Link, error) {
    var l link.Link
    err := r.pool.QueryRow(ctx,
        `SELECT code, url, hits FROM links WHERE code = $1`, code,
    ).Scan(&l.Code, &l.URL, &l.Hits)
    if errors.Is(err, pgx.ErrNoRows) {
        return link.Link{}, fmt.Errorf("code %q: %w", code, link.ErrNotFound) // map to port sentinel
    }
    return l, err
}

// internal/cache/redis/cache.go — cache-aside Resolve.
func (c *Cache) Resolve(ctx context.Context, code string) (string, error) {
    if url, err := c.rdb.Get(ctx, "link:"+code).Result(); err == nil {
        return url, nil // HIT
    }
    url, err := c.repo.Resolve(ctx, code) // MISS -> DB
    if err != nil {
        return "", err
    }
    c.rdb.Set(ctx, "link:"+code, url, 10*time.Minute) // populate with TTL
    return url, nil
}

🏋️ Exercises / Practice

Exercise	Status	Link
linkstore — the in-memory adapter the pg adapter mirrors	✅	exercises/month-06/week-4/linkstore
lrucache — bounded LRU+TTL cache-aside with GetOrLoad	✅	exercises/month-06/week-4/lrucache

🐛 Mistakes Made

• Cached the write but forgot to invalidate on update → served a stale URL. Fix: delete the key on every mutation; let the next read repopulate.
• Did a synchronous DB UPDATE hits = hits + 1 on every redirect → killed throughput. Moved to a Redis counter flushed in batches.
• Leaked a connection by not using the pool's context-scoped acquire/release — pgx pool handles this if you pass ctx and don't hold rows open.

❓ Open Questions

• Should the cache layer be a decorator implementing Repository, or a separate Resolver? (Decorator keeps the service oblivious — leaning that way.)

🧠 Active Recall (answer without looking)

1. Q: In cache-aside, what must happen on a write, and why?
   A

Invalidate (delete or overwrite) the cache key. The cache holds a copy of DB state; if you change the DB without invalidating, the cache serves stale data until its TTL expires. Write-then-invalidate keeps reads correct. 2. Q: Why map pgx.ErrNoRows to link.ErrNotFound instead of returning it directly?

A

So callers stay backend-agnostic. The service and transports match the port's sentinel with errors.Is; if a Postgres-specific error leaked up, swapping to another store would break that matching. Wrapping with %w preserves the chain while presenting the port's contract.

🪶 Feynman Reflection

Persistence and caching are the same idea at two speeds: Postgres is the slow source of truth, Redis is a fast lossy copy. Cache-aside says "ask the fast copy first; if it doesn't know, ask the truth and teach the copy." The only hard part is remembering that when the truth changes, the copy is now lying — so every write must wipe what the cache thinks it knows.

🕳️ Knowledge Gaps

• Connection-pool sizing under load and statement-cache behavior in pgx — tune when I load-test the capstone.

✅ Summary

linkr now persists to Postgres via a pgx adapter behind migrations, with a Redis cache-aside layer that speeds reads and invalidates on writes. The in-memory linkstore and lrucache exercises prove the same contracts in stdlib.

⏭️ Next Steps / Prep for Tomorrow

• Day 165: wire observability (slog + Prometheus + OTel) and the deploy (Docker, compose, healthchecks, graceful shutdown).

---

Time spent	Difficulty	Confidence
90 min	🟦🟦🟦⬜⬜	🟦🟦🟦⬜⬜

Suggested commit: feat(linkr): pgx Postgres adapter + Redis cache-aside (day 164)