PostgreSQL as a Cache (When Not to Use Redis)

Redis is the default answer when someone talks about caching. But for 60% of problems, PostgreSQL with good indexing is simpler, more predictable, and cheaper to operate.

The fear is latency. The reality is that a B-tree index in PostgreSQL on SSD isn't as slow as it seems.

The Problem with "Always Redis"

Each additional database means:

• One more service to maintain in high availability
• Different replication, backup, and recovery policies
• Eviction policies you need to understand and tune
• Another point of failure in your system
• More complexity in your deployment

For most cases (user sessions, feature flags, rate limiting, leaderboards), PostgreSQL solves it. And if it solves it, why add complexity?

When PostgreSQL Beats Redis

Latency: a SELECT with an index in PostgreSQL on SSD can do ~10-50 microseconds. Redis GET can do ~5-10 microseconds in memory. The real difference in your application's SLA? Probably zero.

Consistency: if you care that your data is correct (and you should), PostgreSQL with ACID is simpler. Redis is eventually consistent and you have to deal with stale reads.

Operational simplicity: it's literally one fewer service. You already have PostgreSQL. Use it.

Code: PostgreSQL as a Cache

-- Cache table with TTL
CREATE TABLE cache (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  expires_at TIMESTAMP NOT NULL
);

-- Index for fast lookup (only active keys)
CREATE INDEX idx_cache_active ON cache(key)
  WHERE expires_at > NOW();

-- Function to clean up expired entries
CREATE OR REPLACE FUNCTION cleanup_expired_cache()
RETURNS void AS $$
BEGIN
  DELETE FROM cache WHERE expires_at <= NOW();
END;
$$ LANGUAGE plpgsql;

-- Schedule cleanup every 5 minutes (requires pg_cron)
SELECT cron.schedule('cleanup-cache', '*/5 * * * *',
  'SELECT cleanup_expired_cache()');

Basic operations:

-- Fetch with TTL check
SELECT value FROM cache
WHERE key = $1 AND expires_at > NOW();

-- Insert or update (upsert) with TTL
INSERT INTO cache (key, value, expires_at)
VALUES ($1, $2, NOW() + INTERVAL '1 hour')
ON CONFLICT (key) DO UPDATE
SET value = $2, expires_at = NOW() + INTERVAL '1 hour';

-- Explicit delete (to invalidate before expiration)
DELETE FROM cache WHERE key = $1;

Connection pooling with PgBouncer (essential to avoid saturating connections):

[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

When Redis Really Wins

• Rate limiting: Redis is built for this. INCR is atomic and fast.
• Session storage with real timeout: Redis expiration is more efficient than polling in PG
• Leaderboards/sorted sets: in-memory structured data is simpler
• Pub/sub: if you need messaging, Redis is better than polling in PG

The Decision

Default: PostgreSQL

Migrate to Redis when:

• Your latency benchmark shows that PG can't meet your SLA
• You have the budget to invest in distributed infrastructure
• One of the "When Redis Really Wins" scenarios above is your use case

Hybrid approach: use PG for "warm" cache (data you access occasionally) and Redis for "hot" cache (data you access constantly). Only migrate the part that really needs it.

Monitoring

Key metric: SELECT latency at the 99th percentile on your cache table.

-- What's my p99 latency?
EXPLAIN ANALYZE
SELECT value FROM cache
WHERE key = 'your-key' AND expires_at > NOW();

If p99 > your SLA latency, then consider Redis. Not before.

One database you know well > two databases you'll have to troubleshoot.