Hi, I'm Kemal, a data guy with 15 years of experience in the software industry. Today, I want to explore an intriguing idea: Can Postgres replace Redis as a cache? This question has been making rounds, and I thought it was worth a deep dive to understand the potential and limitations of using Postgres in this role.

The Initial Surprise

Recently, I stumbled upon a discussion on Twitter where someone mentioned using Postgres as a message queue. This was already an interesting concept, but what caught my attention even more was the suggestion of using Postgres as a cache to replace Redis.

Postgres as a Cache

Stephan Schmidt shared an idea that seemed almost revolutionary: using Postgres for caching instead of Redis, utilizing UNLOGGED tables and the TEXT data type for JSON storage. He even suggested writing stored procedures to manage data expiry, similar to how Redis handles it.

As someone deeply familiar with both Postgres and Redis, this notion turned my understanding upside down. Redis is known for its speed and efficiency as a cache, capable of performing millions of operations per second. Could Postgres, my favorite relational database, really compete with Redis, my favorite non-relational database, in this domain?

Why Consider Postgres as a Cache?

Stephan Schmidt provided two compelling reasons for considering Postgres as a cache: reducing complexity and accelerating changes. But is there more to it?

Simplifying the Technology Stack

Postgres is widely used, open source, and likely already part of your application infrastructure. Using it as a cache can simplify your technology stack by eliminating the need to manage multiple database systems.

Familiar Interface

Postgres supports complex queries and indexing, making it easier to handle advanced data retrieval and transformation tasks directly within the cache layer. If your team is proficient in SQL, using Postgres for caching logic can be advantageous.

Cost-Effectiveness

In some cases, using existing Postgres resources for caching might be more cost-effective than deploying a separate solution like Redis. This can lead to better resource utilization, especially in environments with limited infrastructure budgets.

What Should a Caching Service Provide?

To determine if Postgres can replace Redis as a cache, we need to understand the key features of a caching service:

• Performance: High-performance caching solutions enhance application performance by making data access faster.
• Expiration: Automatically removing outdated data after a specified period to ensure up-to-date information.
• Eviction: Managing memory by automatically removing less frequently used data to make space for new entries.
• Key-Value Storage: Storing data as key-value pairs for quick retrieval.

Transforming Postgres into a Cache

According to Martin Heinz, Postgres can achieve these features using UNLOGGED tables. Here’s how it works:

Unlogged Tables and Write Ahead Log (WAL)

Unlogged tables in Postgres prevent specific tables from generating WAL, which records all changes before they are written to the database files. This reduces write operations and improves performance but means the data isn't persistent.

CREATE UNLOGGED TABLE cache (
    id serial PRIMARY KEY,
    key text UNIQUE NOT NULL,
    value jsonb,
    inserted_at timestamp
);

CREATE INDEX idx_cache_key ON cache (key);

Expiration with Stored Procedures

Expiration can be managed with stored procedures, though this introduces complexity. Stored procedures might be unfamiliar to modern developers who prefer to avoid embedding business logic in the database.

CREATE OR REPLACE PROCEDURE expire_rows (retention_period INTERVAL) AS
$$
BEGIN
    DELETE FROM cache
    WHERE inserted_at < NOW() - retention_period;

    COMMIT;
END;
$$ LANGUAGE plpgsql;

CALL expire_rows('60 minutes'); -- Removes rows older than 1 hour

Scheduling these procedures requires additional tools like pg_cron.

Eviction with Stored Procedures

To implement eviction, you could add a last_read_timestamp column and run a procedure to evict the least recently used (LRU) rows.

CREATE OR REPLACE PROCEDURE lru_eviction(eviction_count INTEGER) AS
$$
BEGIN
    DELETE FROM cache
    WHERE ctid IN (
        SELECT ctid
        FROM cache
        ORDER BY last_read_timestamp ASC
        LIMIT eviction_count
    );

    COMMIT;
END;
$$ LANGUAGE plpgsql;

CALL lru_eviction(10); -- Removes the 10 least recently accessed rows

Redis provides built-in eviction policies, whereas Postgres requires custom implementations, adding to the complexity.

Performance Comparison

Performance is crucial for a caching service. Greg Sabino Mullane compared the performance of UNLOGGED and LOGGED tables in Postgres, showing that UNLOGGED tables are faster for writes.

Write Performance

• Unlogged Table: Latency: 2.059 ms, TPS: 485,706
• Logged Table: Latency: 5.949 ms, TPS: 168,087

Read Performance

Reading from both table types in Postgres showed similar performance due to shared buffers, which store frequently accessed data in memory.

• Unlogged Table: Latency: 0.679 ms, TPS: 14,724
• Logged Table: Latency: 0.627 ms, TPS: 15,946

Redis Performance

Redis significantly outperforms Postgres in both reading and writing operations:

• Read Latency: 0.095 ms, RPS: 892,857
• Write Latency: 0.103 ms, RPS: 892,857

Should You Replace Redis with Postgres?

If you need a caching service to improve write performance, Postgres with UNLOGGED tables is a viable option. However, Redis excels in read performance and provides built-in features like expiration and eviction policies that are complex to replicate in Postgres.

Using Postgres as a cache might simplify management for some, but it doesn’t offer the same advantages as a dedicated caching service. Redis is designed for speed and efficiency, making it a superior choice for caching needs.

Conclusion

Based on my research, while Postgres can be optimized for caching, it doesn't match Redis's performance and built-in features. Redis remains the clear choice for a caching service due to its superior speed and simplicity.

Top References on Using PostgreSQL as a Cache Database

Exploring the idea of using PostgreSQL as a cache database is fascinating. To help you dive deeper into this topic, here are the top five online references that provide valuable insights and practical guides on this subject:

1. Martin Heinz's Blog - "You Don’t Need a Dedicated Cache Service — PostgreSQL as a Cache"

   • This detailed blog post by Martin Heinz explains how to use PostgreSQL as a caching solution. He covers the use of UNLOGGED tables and other techniques to optimize PostgreSQL for caching.
   • Read the blog post

2. Stephan Schmidt's Blog - "Just Use Postgres for Everything (How to reduce complexity and move faster)"

   • Stephan Schmidt advocates for using PostgreSQL for various purposes, including caching. He discusses how using a single database system can simplify your technology stack and improve efficiency.
   • Read the blog post

3. PostgreSQL Documentation - Unlogged Tables

   • The official PostgreSQL documentation provides comprehensive details on using UNLOGGED tables, which are crucial for configuring PostgreSQL as a cache.
   • Read the documentation

4. Greg Sabino Mullane's Blog - "PostgreSQL Unlogged Tables — Look Ma, No WAL!"

   • Greg Sabino Mullane offers an in-depth comparison of the performance between logged and unlogged tables in PostgreSQL, highlighting the benefits and trade-offs.
   • Read the blog post

5. Fast Key-Value Store With PostgreSQL

   • This guide explores using PostgreSQL as a key-value store, which is a common use case for caching databases. It covers indexing strategies and performance considerations.
   • Read the guide

Stay Connected

I hope you found this exploration insightful! If you enjoyed this post and want to learn more, feel free to find and connect with me on Linkedin...

Stay curious!

Kemal Cholovich