Index Postgres sessions for lookup and revocation

Index Postgres sessions for fast lookup and revocation by creating targeted single column, multicolumn, expression, and partial indexes that match your common lookup and revocation queries. This immediately reduces full table scans for session lookup by token, user, or expiry and makes selective revocation and garbage collection practical for production traffic.

Why index sessions

Database backed sessions are stored and queried frequently: find a session by token during authentication, list active sessions for a user, revoke a single session, or revoke all sessions for a user. Own Auth supports database backed sessions with absolute expiry, optional idle expiry, listing, individual revocation, and full user revocation, so query performance directly affects user visible latency and background maintenance work.

Typical session queries to optimize

• Lookup by session token: SELECT ... WHERE token_hash = ? LIMIT 1
• Lookup by session token with expiry check: WHERE token_hash = ? AND expires_at > now()
• List active sessions for a user: WHERE user_id = ? AND expires_at > now() ORDER BY last_active_at DESC
• Revoke a single session: UPDATE sessions SET revoked = true WHERE id = ?
• Revoke all user sessions: UPDATE sessions SET revoked = true WHERE user_id = ?

Design indexes to match these query shapes. When the WHERE clause tests equality on a single column, a single column B-tree index is usually the best choice. When queries filter by multiple columns, a multicolumn index ordered by the most selective equality predicates produces the best result. Use expression indexes when the query uses a function or an expression such as a hash column lookup.

Recommended indexes and when to use them

Start with these index patterns and adapt them to your workload and schema:

• Token lookup: create an index on the stored token hash column used by lookup. If your lookup is literally WHERE token_hash = $1, a single column B-tree index on token_hash is ideal.
• Token plus expiry: if you always check expires_at in the same query, use a multicolumn index on (token_hash, expires_at) so the planner can avoid touching rows that are already expired.
• User session listing: for frequent LIST queries by user ordered by last active time, use a multicolumn index on (user_id, last_active_at DESC) to support both the filter and the sort without extra work.
• Revocation by user: revoking all sessions for a user benefits from an index on user_id. If you commonly revoke only non revoked sessions, consider a partial index limited to revoked = false to make the index smaller and faster.

PostgreSQL supports multicolumn, expression, partial, and included column indexes. Use expression indexes when you store a derived value such as a token hash column computed in SQL or to match queries that apply a function in the WHERE clause. Partial indexes only store rows that match a predicate and are especially useful when queries repeatedly target a smaller active subset of rows, such as non revoked sessions or sessions with expires_at in the future. See the official PostgreSQL CREATE INDEX documentation and the partial index documentation for operational details and constraints.

-- 1) Token lookup by hashed token
CREATE INDEX ix_sessions_token_hash ON sessions (token_hash);

-- 2) Token lookup that also checks expiry (multicolumn)
CREATE INDEX ix_sessions_token_hash_expires ON sessions (token_hash, expires_at);

-- 3) Fast listing of active sessions for a user with ordering
CREATE INDEX ix_sessions_user_last_active ON sessions (user_id, last_active_at DESC);

-- 4) Partial index for only non-revoked active sessions
CREATE INDEX ix_sessions_user_active_partial ON sessions (user_id, last_active_at DESC)
  WHERE revoked = false AND expires_at > now();

Use partial indexes when a predicate significantly reduces the number of indexed rows. Partial indexes reduce index size and increase cache efficiency, but remember the predicate must exactly match the WHERE clause used by queries or the optimizer cannot use the index. The PostgreSQL partial index documentation explains these tradeoffs and how the planner uses predicates.

Expression indexes for token hashing and case normalization

If your application computes a token hash on the fly in SQL or normalizes input before comparison, create an expression index to match that expression. For example, if you use an expression like (sha256(token)) or lower(user_agent) in WHERE, an expression index on the same expression avoids a sequential scan. The CREATE INDEX documentation describes expression and included column index forms and their constraints.

-- Example expression index for a stored token computed from raw token
CREATE INDEX ix_sessions_token_expr ON sessions ((sha256(raw_token)));

-- Use the same expression in queries: WHERE sha256(raw_token_input) = token_expr

Practical revocation workflow with Own Auth methods

Use Own Auth server methods to perform safe revocation requests and rely on the database indexes to keep those operations efficient. For selective revocation flows, list candidate sessions, then revoke them. Keep revocation and audit writes small and index friendly so background jobs and user requests remain fast.

// Node.js example using the Own Auth public API
const list = await auth.listSessions({ filter: { userId: 'user-123', onlyActive: true } });
for (const s of list.items) {
  // revoke a single session by id
  await auth.revokeSession({ sessionId: s.id });
}

// Or revoke all sessions for a user
await auth.revokeAllSessions({ userId: 'user-123' });

Index design and query patterns work together. If you call auth.listSessions with filters that translate to WHERE user_id = ? AND expires_at > now(), the database index shown earlier lets PostgreSQL return just matching session rows without scanning the whole table. Similarly, auth.revokeSession and auth.revokeAllSessions should target rows that your indexes support so revocation updates are efficient.

OWASP recommends logging session lifecycle events and supporting explicit revocation and expiration to limit the lifetime and misuse of session identifiers.

Operational notes and maintenance

Monitor index bloat, write amplification, and cache hit ratios. Partial indexes reduce index bloat when the active set is much smaller than the table, but they add complexity to migration and predicate maintenance. Rebuild or reindex during low traffic windows if you notice degraded planner choices. Also track expired session cleanup: compacting old rows reduces table and index size and improves future scans.