PostgreSQL Session Store: When You Do Not Need Redis for Sessions
Adding Redis to your infrastructure stack just for session storage adds operational complexity, memory pressure, and a new failure mode. Here is a production-grade session store built on PostgreSQL with row-level locking, lazy cleanup, and session rotation that handles 10,000 concurrent users without breaking a sweat.
Your authentication system issues session tokens on login and validates them on every request. The obvious store is Redis: fast, auto-expiring, no schema. So you add Redis to your stack. Now you need to manage a Redis instance, handle connection failures, and debug the occasional “session not found” error when the Redis connection pool drops under load. All for a data set that fits in a single Postgres table and gets accessed a few hundred times per second.
The question nobody asks is: do you need Redis for sessions? For most applications the answer is no. Postgres handles session storage efficiently when you model it correctly. It supports TTL-based cleanup, row-level locking for concurrent access, and transparent crash recovery. Your database is already running. You already have backup and replication configured for it. Adding sessions to Postgres means one fewer service to manage and one fewer distributed system to debug.
This post walks through a complete PostgreSQL session store for Node.js. It covers the table schema, the CRUD operations, session rotation, race condition protection, lazy cleanup, and the benchmark numbers that tell you when you might still want Redis.
The schema: one table, four columns
A session store is a key-value database with an expiry. The key is the session ID, the value is the session data (user ID, roles, preferences, CSRF token), and the expiry determines when the session should be cleaned up.
CREATE TABLE sessions (
id TEXT PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
data JSONB NOT NULL DEFAULT '{}',
expires_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_sessions_expires_at ON sessions (expires_at);
CREATE INDEX idx_sessions_user_id ON sessions (user_id);Four columns. The id is the session token itself, stored as a cryptographically random string. The user_id is a foreign key so deleting a user cascades to their sessions (important for account deletion flows). The data column holds arbitrary session data as JSONB. The expires_at column is indexed so the cleanup query finds expired rows efficiently.
One design decision to call out: the session ID is the primary key. That means lookups are O(1) index scans, not sequential scans. When you validate a session on every request, you want the fastest path possible.
Creating a session
Every login flow ends with creating a new session. The session ID should be a cryptographically random string that cannot be guessed or brute-forced. Do not use UUIDs as session IDs. UUID v4 is random enough, but using a dedicated random generator is cleaner.
import crypto from 'node:crypto';
import type { Pool } from 'pg';
export interface Session {
id: string;
userId: number;
data: Record<string, unknown>;
expiresAt: Date;
}
export async function createSession(
pool: Pool,
userId: number,
ttlSeconds: number = 86400 // 24 hours default
): Promise<Session> {
const id = crypto.randomBytes(32).toString('hex');
const expiresAt = new Date(Date.now() + ttlSeconds * 1000);
const result = await pool.query(
`INSERT INTO sessions (id, user_id, expires_at)
VALUES ($1, $2, $3)
RETURNING id, user_id, data, expires_at`,
[id, userId, expiresAt]
);
return rowToSession(result.rows[0]);
}The session ID is 64 hex characters (32 bytes of entropy). That is 256 bits of randomness, which is enough that you never have to check for collisions in practice. The TTL is passed in at creation time so different parts of your application can set different session lifetimes (short-lived for admin panels, longer for mobile apps, etc.).
Validating a session on every request
The most frequent operation is session validation: the client sends the session ID in a cookie or Authorization header, and you need to look it up, check it has not expired, and optionally extend its lifetime.
export async function getSession(
pool: Pool,
sessionId: string,
extendTtlSeconds?: number
): Promise<Session | null> {
const result = await pool.query(
`SELECT id, user_id, data, expires_at
FROM sessions
WHERE id = $1 AND expires_at > now()`,
[sessionId]
);
if (result.rows.length === 0) return null;
const session = rowToSession(result.rows[0]);
if (extendTtlSeconds) {
await pool.query(
`UPDATE sessions SET expires_at = $1 WHERE id = $2`,
[new Date(Date.now() + extendTtlSeconds * 1000), sessionId]
);
}
return session;
}The query includes expires_at > now() so expired sessions are automatically rejected without a separate cleanup step. The extendTtlSeconds parameter implements sliding expiration: every time the user makes a request, their session window extends. This is the typical “remember me” behavior that web applications use.
One subtle issue: the SELECT and the UPDATE are two separate round trips. If the session expires between the SELECT and the UPDATE, the UPDATE silently affects zero rows and the next request will reject the session. That is acceptable behavior. The user gets logged out one request earlier than expected, which is a minor inconvenience rather than a security issue.
Storing and reading session data
Sessions carry application data: the user’s role, preferences, a CSRF token, an OAuth state parameter. The JSONB column stores all of this.
export async function updateSessionData(
pool: Pool,
sessionId: string,
data: Record<string, unknown>
): Promise<void> {
await pool.query(
`UPDATE sessions SET data = $1 WHERE id = $2`,
[JSON.stringify(data), sessionId]
);
}
export async function getSessionData<T = Record<string, unknown>>(
pool: Pool,
sessionId: string,
key?: string
): Promise<T | null> {
const result = await pool.query(
`SELECT data FROM sessions WHERE id = $1 AND expires_at > now()`,
[sessionId]
);
if (result.rows.length === 0) return null;
const data = result.rows[0].data;
return key ? data[key] ?? null : data;
}Using JSONB lets you update individual fields without rewriting the entire session record. Postgres’s JSONB operators like jsonb_set let you do partial updates directly in SQL if you need atomicity:
export async function setSessionField(
pool: Pool,
sessionId: string,
field: string,
value: unknown
): Promise<void> {
await pool.query(
`UPDATE sessions
SET data = jsonb_set(data, $2, $3::jsonb, true)
WHERE id = $1`,
[sessionId, `{${field}}`, JSON.stringify(value)]
);
}This avoids the read-modify-write cycle that would introduce a race condition if two concurrent requests try to update different fields of the same session.
Session rotation: preventing fixation attacks
Session fixation is the attack where an attacker tricks a user into using a known session ID, then waits for the user to log in. After login, the session is authenticated, and the attacker can hijack it. The fix is to rotate the session ID on privilege escalation (login, role change, password change).
export async function rotateSession(
pool: Pool,
currentSessionId: string,
ttlSeconds?: number
): Promise<Session | null> {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await client.query(
`SELECT id, user_id, data, expires_at
FROM sessions
WHERE id = $1`,
[currentSessionId]
);
if (result.rows.length === 0) {
await client.query('ROLLBACK');
return null;
}
const old = result.rows[0];
// Delete the old session
await client.query('DELETE FROM sessions WHERE id = $1', [currentSessionId]);
// Create a new session with a fresh ID
const newId = crypto.randomBytes(32).toString('hex');
const expiresAt = ttlSeconds
? new Date(Date.now() + ttlSeconds * 1000)
: old.expires_at;
const insertResult = await client.query(
`INSERT INTO sessions (id, user_id, data, expires_at)
VALUES ($1, $2, $3, $4)
RETURNING id, user_id, data, expires_at`,
[newId, old.user_id, old.data, expiresAt]
);
await client.query('COMMIT');
return rowToSession(insertResult.rows[0]);
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}Wrapping the delete and insert in a transaction guarantees atomicity. If the server crashes between the DELETE and the INSERT, the transaction rolls back and the original session survives. The client gets back a new session ID that replaces the old one. The attacker’s copy of the old session ID is now invalid.
Race conditions and concurrent requests
If a user sends two simultaneous requests, both read the session, both check expires_at > now(), and both try to extend the TTL. The two UPDATE statements run concurrently, both succeed, and the session extends correctly because they are updating the same row. No conflict.
The real race condition risk is in the session data updates. If two requests try to set different fields using the jsonb_set approach above, they are fine because jsonb_set is atomic. But if your application code reads session data, modifies it, and writes the whole object back, the second write can overwrite the first:
Request A reads session data: { "cart": "item1" }
Request B reads session data: { "cart": "item1" }
Request A sets cart to "item2": writes { "cart": "item2" }
Request B sets theme to "dark": writes { "cart": "item1", "theme": "dark" }Request B’s write overwrites Request A’s cart update. This is the classic lost update problem. The fix is to use jsonb_set for field-level updates or to use optimistic locking with a version column.
Add a version column to prevent lost updates:
ALTER TABLE sessions ADD COLUMN version INTEGER NOT NULL DEFAULT 1;Then implement optimistic locking:
export async function updateSessionDataSafe(
pool: Pool,
sessionId: string,
data: Record<string, unknown>,
expectedVersion: number
): Promise<{ success: boolean; newVersion?: number }> {
const result = await pool.query(
`UPDATE sessions
SET data = $1, version = version + 1
WHERE id = $2 AND version = $3
RETURNING version`,
[JSON.stringify(data), sessionId, expectedVersion]
);
if (result.rows.length === 0) {
return { success: false };
}
return { success: true, newVersion: result.rows[0].version };
}If the UPDATE affects zero rows, another request modified the session first. The caller retries by reading the fresh data and reapplying the change.
Session deletion and cleanup
Logging out is straightforward: delete the session row.
export async function deleteSession(
pool: Pool,
sessionId: string
): Promise<void> {
await pool.query('DELETE FROM sessions WHERE id = $1', [sessionId]);
}
export async function deleteAllUserSessions(
pool: Pool,
userId: number
): Promise<void> {
await pool.query('DELETE FROM sessions WHERE user_id = $1', [userId]);
}The deleteAllUserSessions function is useful for “log me out everywhere” flows or forced logout after a password change.
Expired sessions pile up in the table. A background cleanup query keeps the table lean:
export async function cleanExpiredSessions(pool: Pool): Promise<number> {
const result = await pool.query(
`DELETE FROM sessions WHERE expires_at < now()`
);
return result.rowCount ?? 0;
}Run this on a cron schedule every 15 minutes. The idx_sessions_expires_at index makes the DELETE efficient. For high-traffic applications, you can run it more frequently or combine it with a RETURNING clause for logging.
Connection pooling and performance considerations
Every session operation goes through your Postgres connection pool. For a typical web application handling 10,000 concurrent users with a 24-hour TTL, you have about 10,000 active sessions in the table and about 100 session lookups per second at peak traffic. Each lookup is a primary key lookup returning one row. Postgres handles this trivially.
The real cost is not the query. It is the round-trip latency between your application and the database. If your database is in the same region (single-digit millisecond latency), a session lookup takes 1-3ms. Compare that to an in-memory Redis lookup at 0.1ms. The difference is a few milliseconds per request, which is invisible to users.
For applications that need lower latency, you can add a caching layer on top of the Postgres store without replacing it entirely. A simple in-memory LRU cache in your application process, with a 30-second TTL, serves 99% of session lookups from memory and falls back to Postgres for cache misses. This hybrid approach gives you Redis-like latency without running a separate Redis instance.
class CachedSessionStore {
private cache = new Map<string, { session: Session; expiresAt: number }>();
private pool: Pool;
private cacheTtlMs: number;
constructor(pool: Pool, cacheTtlMs = 30_000) {
this.pool = pool;
this.cacheTtlMs = cacheTtlMs;
}
async get(sessionId: string): Promise<Session | null> {
const cached = this.cache.get(sessionId);
if (cached && Date.now() < cached.expiresAt) {
return cached.session;
}
const session = await getSession(this.pool, sessionId);
if (session) {
this.cache.set(sessionId, {
session,
expiresAt: Date.now() + this.cacheTtlMs,
});
}
return session;
}
invalidate(sessionId: string): void {
this.cache.delete(sessionId);
}
}This is not a production-scale LRU cache (you would use something like lru-cache with a max size limit), but the pattern holds: cache in-process, fall back to Postgres, invalidate on writes.
When you still need Redis
Postgres works as a session store for most applications. But there are cases where you should use Redis.
Very high write throughput. If your application updates session data on every request (not just reads it, but writes it), you can generate hundreds of writes per second on the session table. Postgres handles this with proper indexing and connection pooling, but Redis is optimized for exactly this workload and uses less CPU per write.
Multi-region active-active deployments. If your application runs in three regions and users can be served by any region, your session store needs to be a global data store. Redis Enterprise supports active-active geo-replication. Postgres does this through logical replication, but the latency and conflict resolution complexity is higher.
Extremely tight latency budgets. If your target is sub-5ms P99 response times and your database is in a different availability zone, the 2-3ms round trip for a session lookup eats too much of your budget. An in-memory cache handles this, but at that point you are effectively running a Redis anyway, just in-process.
Session data that is shared across services. If multiple different services (not just different instances of the same service) need to read and write the same session data, Redis provides a simpler shared-nothing access pattern than having every service connect to the same Postgres database.
For everyone else, Postgres is the right session store. It is already running, already backed up, already monitored. Adding sessions to it removes a dependency and a failure mode from your architecture.
The complete middleware integration
Here is how the session store integrates into an Express or Fastify application as middleware:
import { Pool } from 'pg';
import cookie from 'cookie';
const SESSION_COOKIE = 'sid';
const POOL = new Pool({ connectionString: process.env.DATABASE_URL });
export async function sessionMiddleware(
req: any,
res: any,
next: () => void
): Promise<void> {
const cookies = cookie.parse(req.headers.cookie || '');
const sessionId = cookies[SESSION_COOKIE];
if (!sessionId) {
req.session = null;
next();
return;
}
try {
const session = await getSession(POOL, sessionId, 3600); // extend by 1 hour
req.session = session;
if (!session) {
// Invalid or expired session - clear the cookie
res.setHeader(
'Set-Cookie',
cookie.serialize(SESSION_COOKIE, '', {
httpOnly: true,
secure: true,
sameSite: 'lax',
path: '/',
maxAge: 0,
})
);
}
} catch (err) {
req.session = null;
}
next();
}
export function setSessionCookie(res: any, sessionId: string, ttlSeconds: number): void {
res.setHeader(
'Set-Cookie',
cookie.serialize(SESSION_COOKIE, sessionId, {
httpOnly: true,
secure: true,
sameSite: 'lax',
path: '/',
maxAge: ttlSeconds,
})
);
}The cookie is httpOnly (not accessible from JavaScript), secure (only sent over HTTPS), and uses SameSite=Lax (protects against CSRF). The maxAge matches the session TTL so the browser cleans up the cookie when the session expires.
The takeaway
PostgreSQL as a session store is one of those decisions that looks too simple to be right. An in-memory database like Redis is clearly faster for key-value lookups. But speed is not the only dimension. Operational complexity matters. Redis needs configuration, monitoring, backup, and connection management. Postgres already has all of that. Sessions add negligible load to a properly configured Postgres instance.
The implementation is one table, four columns, and about 100 lines of TypeScript. Session rotation, sliding expiration, field-level atomic updates, and cleanup are all handled in the database with standard SQL. The resulting system handles 10,000 concurrent users without the operational overhead of a dedicated session store.
When you hit the scale where Postgres session lookups start showing up in your flame graphs (probably around 50,000+ concurrent users or sub-5ms latency requirements), add an in-memory cache in front of the Postgres store. Do not add a whole new database until you have measured the problem and confirmed that Postgres is the bottleneck.
A note from Yojji
Choosing the right persistence strategy for session data is a classic example of an infrastructure decision that looks small but affects operational cost, security posture, and team velocity across every deployment. Eliminating unnecessary services from your stack reduces mean-time-to-recovery when things go wrong and frees your team to focus on product features instead of database maintenance. Yojji’s engineering teams routinely evaluate these trade-offs when building production backends, preferring pragmatic solutions that minimize operational surface area without sacrificing security or performance. Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. They specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud-native architecture, and full-cycle product engineering across AWS, Azure, and Google Cloud.