Project: PostgreSQL + Redis + API Deploy

Introduction

This capstone wires PostgreSQL (source of truth), Redis (cache-aside), and a FastAPI service in Docker Compose. Reads hit Redis when warm; writes update Postgres and invalidate cache. Health checks prove the stack is ready before traffic arrives. Architecture mirrors MongoDB + Redis deploy with SQL instead of documents.

Prerequisites

• PostgreSQL With FastAPI
• Performance, VACUUM, and Monitoring
• Redis caching patterns
• Linux Docker introduction
• FastAPI Docker and CI optional

Architecture

Client → FastAPI (api) → Redis (cache)
              ↓ miss
           PostgreSQL (postgres)

Write path: Postgres first, then DEL cache key.

docker-compose.yml

Init SQL

init.sql (runs once on empty volume):

CREATE TABLE IF NOT EXISTS posts (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  body TEXT NOT NULL DEFAULT '',
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
INSERT INTO posts (title, slug, body, published) VALUES
  ('Welcome', 'welcome', 'Hello from Compose.', TRUE)
ON CONFLICT (slug) DO NOTHING;

Code explanation:

• docker-entrypoint-initdb.d scripts run only when data directory is empty
• Re-seed requires volume reset or migrations—Backup chapter

Environment Settings

app/core/config.py:

class Settings(BaseSettings):
    database_url: str = "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo"
    redis_url: str = "redis://127.0.0.1:6379/0"
    cache_ttl_seconds: int = 300

.env.example:

DATABASE_URL=postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo
REDIS_URL=redis://127.0.0.1:6379/0
CACHE_TTL_SECONDS=300

Lifespan: Pool + Redis

Health Check

SELECT 1 proves Postgres connectivity—common in load balancer probes.

Cache-Aside Read

Code explanation:

• Cache-aside: app checks Redis, on miss reads Postgres, fills cache
• TTL limits staleness if invalidation misses a key
• Redis caching patterns covers tradeoffs

Write + Invalidate

On slug change, delete both old and new keys—or use versioned cache keys.

Dockerfile (Minimal)

FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY app ./app
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

requirements.txt: fastapi[standard], psycopg[binary], psycopg-pool, redis, pydantic-settings.

Run Stack

docker compose up --build
curl http://127.0.0.1:8000/health
curl http://127.0.0.1:8000/by-slug/welcome
curl http://127.0.0.1:8000/by-slug/welcome   # second hit from Redis

Production Notes

Deliverables

• docker compose up healthy postgres, redis, api
• GET /health returns postgres + redis ok
• Second read of same slug faster (Redis hit—check logs or redis-cli MONITOR)
• PATCH invalidates cache; next GET shows new title

FAQ

Postgres vs MongoDB in this project?

Same cache pattern—Postgres enforces schema and SQL; MongoDB flexible documents—What Is PostgreSQL.

Cache stampede?

Singleflight or short TTL + probabilistic early refresh—advanced; start with TTL + delete on write.

Redis down?

Fallback to Postgres only—degrade gracefully; do not fail reads if cache optional.

Where is MySQL version?

Same Compose shape—swap postgres image and DATABASE_URL; SQL dialect differs.

CI pipeline?

FastAPI Docker and CI—add docker compose run integration test hitting /health.

Next steps?

Docker and Operations, Troubleshooting.