Replace Redis with PostgreSQL

In my years working with cloud infrastructure, I’ve learned that sometimes the “right” tool isn’t always the best choice. This realization hit me hard when I replaced Redis with PostgreSQL for caching in a production system—and saw performance improve.

This isn’t a rant against Redis. It’s a story about understanding your workload, measuring what matters, and discovering that PostgreSQL caching can outperform dedicated cache solutions.

The Setup: Why We Had Redis

Our microservices architecture had the typical setup: PostgreSQL for persistent data, Redis for caching and session storage. It worked fine for years. We had separate clusters, different backup strategies, and two distinct sets of monitoring dashboards.

The Redis cluster handled about 100,000 operations per second—mostly GET requests for user sessions, API rate limiting, and cached database query results. Standard stuff.

The Problem: Operational Complexity

The issues weren’t dramatic. They were death by a thousand cuts:

• Two systems to maintain: Separate backup strategies, monitoring, alerting, and scaling patterns
• Data consistency challenges: Cache invalidation timing between PostgreSQL and Redis
• Network latency: Extra hop for every cached read after a database write
• Cost: Running Redis clusters across three availability zones wasn’t cheap

I started wondering: could PostgreSQL handle this workload directly?

Unlock PostgreSQL’s Caching Performance

Modern PostgreSQL has features that aren’t just for traditional database work:

Unlogged Tables

Unlogged tables skip write-ahead logging (WAL), making writes significantly faster. Perfect for cache data that doesn’t need durability:

CREATE UNLOGGED TABLE cache_store (
    key TEXT PRIMARY KEY,
    value JSONB NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_cache_expires ON cache_store(expires_at);

If the database crashes, unlogged tables lose their data. But that’s fine—it’s a cache.

JSONB for Flexible Storage

PostgreSQL’s JSONB type offers schema flexibility like Redis, with better querying:

-- Store any JSON structure
INSERT INTO cache_store (key, value, expires_at)
VALUES (
    'user:12345:profile',
    '{"name": "Alice", "email": "alice@example.com", "preferences": {"theme": "dark"}}'::JSONB,
    NOW() + INTERVAL '1 hour'
);

-- Query nested JSON efficiently
SELECT value->>'name' 
FROM cache_store 
WHERE key = 'user:12345:profile'
  AND expires_at > NOW();

In-Memory Performance with shared_buffers

PostgreSQL’s shared_buffers keeps frequently accessed data in memory. With enough RAM, hot data never hits disk:

# postgresql.conf
shared_buffers = 8GB
effective_cache_size = 24GB

On our 32GB database servers, we allocated 8GB to shared buffers. Monitoring showed our cache workload fit entirely in memory.

Migrate Safely from Redis to PostgreSQL

I didn’t just flip a switch. The migration was gradual and measured:

Phase 1: Dual-Write Testing (2 weeks)

Write to both Redis and PostgreSQL, read only from Redis:

def cache_set(key: str, value: dict, ttl: int = 3600):
    # Write to Redis (production)
    redis_client.setex(key, ttl, json.dumps(value))
    
    # Write to PostgreSQL (testing)
    try:
        pg_cursor.execute(
            "INSERT INTO cache_store (key, value, expires_at) "
            "VALUES (%s, %s, NOW() + %s * INTERVAL '1 second') "
            "ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, expires_at = EXCLUDED.expires_at",
            (key, json.dumps(value), ttl)
        )
    except Exception as e:
        logger.warning(f"PostgreSQL cache write failed: {e}")

This validated that PostgreSQL could handle the write load without impacting production.

Phase 2: Shadow Reads (1 week)

Read from both systems, compare results, and measure latency:

def cache_get(key: str) -> Optional[dict]:
    # Production read from Redis
    redis_value = redis_client.get(key)
    
    # Shadow read from PostgreSQL
    start = time.time()
    pg_cursor.execute(
        "SELECT value FROM cache_store WHERE key = %s AND expires_at > NOW()",
        (key,)
    )
    pg_latency = time.time() - start
    
    metrics.record("cache.postgresql.latency", pg_latency)
    
    return json.loads(redis_value) if redis_value else None

Average latency: Redis 0.3ms, PostgreSQL 0.5ms. Close enough for our use case.

Phase 3: Gradual Cutover (3 days)

Feature-flagged rollout to 10%, 50%, then 100% of traffic:

def cache_get(key: str) -> Optional[dict]:
    use_postgresql = feature_flags.is_enabled("postgresql_cache", default_percentage=0)
    
    if use_postgresql:
        pg_cursor.execute(
            "SELECT value FROM cache_store WHERE key = %s AND expires_at > NOW()",
            (key,)
        )
        result = pg_cursor.fetchone()
        return json.loads(result[0]) if result else None
    else:
        redis_value = redis_client.get(key)
        return json.loads(redis_value) if redis_value else None

No incidents. Monitoring showed consistent performance across the rollout.

Measure Performance Gains After Migration

After fully migrating and decommissioning Redis:

Performance:

• P50 latency: 0.4ms (was 0.3ms with Redis)
• P99 latency: 2.1ms (was 3.8ms with Redis)
• Throughput: 120,000 ops/sec (up from 100,000)

The P99 improvement surprised me. It came from eliminating network hops between services, PostgreSQL, and Redis.

Operational Simplicity:

• One database to backup, monitor, and scale
• Eliminated cache consistency issues—atomic transactions solve everything
• Reduced infrastructure cost by 30% (no separate Redis clusters)

Developer Experience:

• Joined queries between cached and persistent data: game changer
• SQL debugging tools beat Redis CLI any day
• Consistent connection pooling across the app

When PostgreSQL Caching Makes Sense

This isn’t universal advice. PostgreSQL as a cache works well when:

1. Your cache fits in memory: If working set exceeds available RAM, disk I/O kills performance
2. Read/write ratio is moderate: Pure read-heavy workloads (99%+ reads) might still favor Redis
3. You need transactional consistency: Atomic updates across cache and persistent data
4. Operational simplicity matters: Fewer systems to manage, monitor, and debug
5. Data relationships exist: JOINs between cached and persistent data are valuable

When to Stick with Redis

Keep Redis if:

• Massive scale: Billions of keys, petabytes of data, extreme throughput
• Pub/Sub required: Redis’s pub/sub is first-class; PostgreSQL’s LISTEN/NOTIFY is limited
• Data structures matter: Redis’s native lists, sets, sorted sets, and streams are powerful
• Sub-millisecond latency is critical: Pure in-memory systems are still faster
• You need specific Redis features: Lua scripting, geospatial indexes, etc.

Optimize PostgreSQL Cache Implementation

If you try PostgreSQL caching, here’s what worked for me:

Automatic Expiration with Background Worker

PostgreSQL doesn’t auto-expire keys like Redis. Schedule cleanup:

-- Delete expired cache entries every minute
CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'cache-cleanup',
    '* * * * *',  -- every minute
    $$DELETE FROM cache_store WHERE expires_at < NOW()$$
);

Or use a more aggressive approach with a background worker that runs every 10 seconds.

Connection Pooling is Essential

PostgreSQL connections are heavier than Redis connections. Use PgBouncer:

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=production

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

This reduced connection overhead by 90%.

Monitoring Cache Hit Rates

Track whether your cache is effective:

CREATE TABLE cache_metrics (
    metric_name TEXT,
    count BIGINT,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- In application code, track hits/misses
INSERT INTO cache_metrics (metric_name, count)
VALUES ('cache_hit', 1)
ON CONFLICT (metric_name)
DO UPDATE SET count = cache_metrics.count + 1, updated_at = NOW();

Export these to your monitoring system (Prometheus, Datadog, etc.) to visualize hit rates over time.

Lessons Learned

Measure, don’t assume: I almost didn’t try this because “everyone knows Redis is faster.” Measurement proved otherwise for our workload.

Simplicity compounds: Removing Redis eliminated entire classes of problems—cache consistency bugs, split-brain scenarios during network partitions, and complex backup coordination.

Right-size your tools: We didn’t need Redis’s scale. We needed caching. PostgreSQL delivered that with less operational overhead.

Incremental migration reduces risk: The phased rollout gave us confidence and easy rollback paths. Never big-bang infrastructure changes.

Conclusion: PostgreSQL Caching Works

Replacing Redis with PostgreSQL for caching wasn’t on my roadmap. It started as a late-night thought experiment and ended up simplifying our architecture and improving performance.

This isn’t about PostgreSQL being “better” than Redis. It’s about questioning assumptions, measuring actual workloads, and recognizing that PostgreSQL caching can deliver excellent results with fewer moving parts.

If you’re running both PostgreSQL and Redis for caching, consider testing this approach. Measure your workload, validate the results, and make data-driven decisions.

And if PostgreSQL caching doesn’t work for your workload? You’ll have data proving why Redis is the right choice. Either way, you win.