The reason I use Redis as a cache is raw speed. I would consider using this Postgres-based solution if the speed was comparable. The article leaves benchmarking up to the reader.
You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
21–30 of 82 posts
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#22The reason you build cache for most purposes, is fast data retrieval. A major component of "fast" is the latency of a disk read vs a memory read. Redis and memcached are in memory by design, so entirety of your cached table will be in memory. When you design a cache using postgres (assuming on an RDS instance), although data will be cached in memory based on access patterns, I don't believe there is a direct way to control what data gets cached, which makes this implementation slower for a lot of real world use cases.
This gets even slower when you have a distributed DB like AWS Aurora Postgres because your data on disk can be on different EBS volumes, so bringing it in memory can be slower than even RDS Postgres.
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#23Calling Postgres experts: Why, exactly, do we need to put a memory cache such as Redis in front of Postgres? Postgres has its own in-memory cache that it updates on reads and writes, right? What makes Postgres' cache so much worse than a dedicated Redis?
Redis is completely in memory, therefore all data is in memory. Postgres on the other hand does have a cache of it's own, does not give you fine controls over what stays in cache. What stays in cache depends on data access patterns. E.g. I cannot make an entire table of my choosing to be in cache.
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#24Why wouldn't you simply use SQLite (or some other in-memory flavor of SQL) instead of hacking the main Postgres db and adding load to the primary instance?
The author makes a valid point that there's something nice about using familiar tooling (including the SQL interface) for a cache, but it feels like there are better solutions.
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#25Calling Postgres experts: Why, exactly, do we need to put a memory cache such as Redis in front of Postgres? Postgres has its own in-memory cache that it updates on reads and writes, right? What makes Postgres' cache so much worse than a dedicated Redis?
Caching with postgres also lets you do cache invalidation via triggers!
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#26Why wouldn't you simply use SQLite (or some other in-memory flavor of SQL) instead of hacking the main Postgres db and adding load to the primary instance? The author makes a valid point that there's something nice about using familiar tooling (including the SQL interface) for a cache, but it feels like there are better solutions.
> hacking the main Postgres db and adding load to the primary instance
nobody said this had to be on the primary database server, and how is this hacking?
Is every app server going to have its own local "sqlite cache"? Or is it going to use one of the sqlite server/replication things? So why not just use PG?
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#27Calling Postgres experts: Why, exactly, do we need to put a memory cache such as Redis in front of Postgres? Postgres has its own in-memory cache that it updates on reads and writes, right? What makes Postgres' cache so much worse than a dedicated Redis?
Because you can’t scale out just the cache part of Postgres, one machine can only have so much memory
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#28Calling Postgres experts: Why, exactly, do we need to put a memory cache such as Redis in front of Postgres? Postgres has its own in-memory cache that it updates on reads and writes, right? What makes Postgres' cache so much worse than a dedicated Redis?
> Why, exactly, do we need to put a memory cache such as Redis in front of Postgres?
Maybe you don't want to run the same expensive queries all the time to serve your json API?
There's a million reasons you might want to cache expensive queries somewhere upstream from the actual database.
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#29For projects where I know the team will remain small (less than let's say 15 developers), I usually push to keep the architecture as simple as possible.
I've used something similar in the past, but kept the expiration code in the app code (Python) instead of using "fancy" Postgres features, like stored procedures. It's much easier to maintain since most developers will know how to read and maintain the Python code, that's also commited to the git repository.
Also, instead of using basic INSERT statements, you can "upsert".
INSERT INTO cache_items (key, created, updated, key, expires, value) VALUES (...) ON CONFLICT ON CONSTRAINT pk_cache_items DO UPDATE SET updated = ..., key = ..., expires = ..., value = ...;
And since you have control over the table, you can customize it however you want. Like adding categories of cache that you can invalidate all at once, etc.
Postgres is also pretty good at key/values.
In other words, I agree that using Postgres for things like caching, key/values, and even maybe message queue, can make sense, until it doesn't. When it doesn't make sense anymore, it's usually easy to migrate that one thing off of Postgres and keep the rest there.
Also, one benefit that's not often talked about is the complexity of distributed transactions when you have many systems.
Let's say you compute a value inside a transaction, cache it in Redis, and then the transaction fails. The cached valued is wrong. If everything is inside of Postgres, the cached value will also not be commited. One less thing to worry about.
Re: You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
#30For projects where I know the team will remain small (less than let's say 15 developers), I usually push to keep the architecture as simple as possible. I've used something similar in the past, but kept the expiration code in the app code (Python) instead of using "fancy" Postgres features, like stored procedures. It's much easier to maintain since most developers will know how to read and maintain the Python code, t…
> Let's say you compute a value inside a transaction, cache it in Redis, and then the transaction fails.
That just sounds like an application bug. Nothing should be done with the query result anyway until the transaction either completes our rolls back.