Database-backed session store vs Redis: a pragmatic comparison

When you're building authentication for a new application, you need somewhere to store sessions. Two common answers are: a table in your existing relational database, or a dedicated Redis instance. Both work. The right choice depends on your read patterns, your existing infrastructure, your team's operational familiarity, and your tolerance for additional complexity. This post gives you the concrete tradeoffs rather than a generic "it depends."

What a session record actually contains

A session in a database (or Redis) typically holds a small amount of data: a session ID, a user ID, an expiry time, possibly device/IP metadata, and any ephemeral state you need to persist across requests (CSRF token, OAuth flow state). Sessions are not a general-purpose data store — if your session records are growing past a few hundred bytes, you're probably storing things that belong in the application database.

-- Minimal session table in Postgres
CREATE TABLE sessions (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      BIGINT REFERENCES users(id) ON DELETE CASCADE,
  data         JSONB NOT NULL DEFAULT '{}',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at   TIMESTAMPTZ NOT NULL,
  ip_address   INET,
  user_agent   TEXT
);

CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);

Read patterns: the critical difference

Sessions are read on every authenticated request. With Postgres, that's a primary-key lookup — fast, but a round-trip to the database. At 1,000 concurrent users each making 10 requests per minute, that's 10,000 session reads per minute against your database. For most applications this is negligible. For high-throughput APIs, it can become a bottleneck.

Redis stores everything in memory. A session lookup in Redis is typically 0.2–0.5ms. Postgres on the same hardware will be 1–5ms for a cached read. The performance gap matters when your API latency budget is tight or your session volume is very high. For a typical SaaS application handling thousands of requests per minute (not tens of thousands), the difference rarely matters in practice.

TTL management: EXPIRE vs cron

Redis has native TTL support via the EXPIRE command. Set a key's expiry and Redis automatically removes it when the time comes. No cleanup job required, no dead rows accumulating in storage.

import redis
import json
import uuid

r = redis.Redis(host='redis', port=6379, decode_responses=True)

def create_session(user_id: str, ttl_seconds: int = 86400) -> str:
    session_id = str(uuid.uuid4())
    session_data = {
        'user_id': user_id,
        'created_at': int(time.time()),
    }
    # Key expires automatically after ttl_seconds
    r.setex(f"session:{session_id}", ttl_seconds, json.dumps(session_data))
    return session_id

def get_session(session_id: str) -> dict | None:
    data = r.get(f"session:{session_id}")
    if not data:
        return None
    # Optionally extend the TTL on access (sliding window)
    r.expire(f"session:{session_id}", 86400)
    return json.loads(data)

With Postgres, you need to clean up expired sessions yourself. A simple approach is a scheduled job:

-- Run this on a cron schedule (every 15 minutes is reasonable)
DELETE FROM sessions WHERE expires_at < NOW();

-- Or use a partial index to make this cleanup fast
-- The index only covers rows where expires_at is in the past
-- (this is just an approximation — partial indexes can't use dynamic values)
-- Better: index on expires_at and let the DELETE range-scan the index

Pg_cron (a Postgres extension available on most managed services) or an application-level cron job work well. The downside is that expired sessions accumulate between cleanup runs. With an active application and a 24-hour TTL, this can be millions of rows if you have many users and a daily cleanup schedule. Run cleanup more frequently (every 5-15 minutes) to keep the table size manageable.

Serialization considerations

Redis stores strings; you'll serialize session data to JSON. Postgres with a JSONB column is similarly flexible. The difference is queryability: with Postgres, you can query the session data:

-- Find all active sessions for a user (for a "sign out all devices" feature)
SELECT id, ip_address, user_agent, created_at
FROM sessions
WHERE user_id = $1 AND expires_at > NOW();

-- Find sessions with a specific flag in their data
SELECT id FROM sessions
WHERE data->>'mfa_verified' = 'true' AND user_id = $1;

With Redis, you'd need to maintain a secondary index manually — a SADD user:{userId}:sessions {session_id} set that lets you look up all sessions for a user. This is manageable but is additional logic to maintain.

Session clustering: shared store vs sticky sessions

When you run multiple application instances behind a load balancer, sessions must be available on every instance. Two approaches:

Shared store: All instances read and write to the same session store (Redis or Postgres). The load balancer can send requests to any instance. This is simpler to operate and enables proper horizontal scaling.

Sticky sessions: The load balancer routes a given user's requests to the same instance every time (usually based on a cookie). Each instance keeps sessions in memory. This works but has failure modes: when an instance restarts, its users lose their sessions. Rolling deployments become more complex. Sticky sessions are an antipattern for modern cloud deployments.

The hybrid approach

A common production pattern is to use Redis as the session store but write session metadata (user_id, created_at, ip_address, last_seen_at) to Postgres for audit and "manage active sessions" UX. Redis handles the hot path; Postgres holds the durable record. This gives you the performance of Redis and the queryability of Postgres without storing full session state in both places.