You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)
martinheinz.dev
martinheinz.dev
It gives me anxiety how much truly awful advice is upvoted here. It feels like children playing in a sand pit, and then one of the children dumps a box of rat poison on the ground, and some kid says how rat poison is actually good for you because it contains minerals or something. So they all start quickly scarfing it down. You try to tell them rat poison is bad for them, and then several of the kids start defending rat poison, with one lecturing you for being so negative about rat poison. I guess I should just let the kids poison themselves, but it's a terrible thing to watch.
At least a couple of counterpoints on why this would be a bad idea and offer a better approach.
Feedback like root comment is important to keep pushing ideas forward, instead of a loop.
What feedback? All I see is someone smelling their own farts.
In my experience there are far more highly-overwrought bloated architectures deployed than there are overly-clever minimalist ones. Everyone wants to put the cool tools on their resume.
Overall for me this cache in postgres is a bad idea too
This is rarely the case.
Caching in Postgres is for smaller scale applications where the extra complexity of a dedicated memory cache isn’t worth the extra overhead.
But at least you bring up some actual limits that should to be considered.
DB load: part of the load on a DB is the network connection for the query, but a large part is also finding the data. If you have a complex query, storing a de-normalized form in a cache table would be a lighter load. (Provided that you aren’t limited by the network or number of concurrent connections)
I think that whether or not this is a good idea depends highly upon what scale you’re operating at. For high traffic applications, it’s not a good plan, but if you already have a DB setup and you have a lighter load, I could think of worse solutions.
It's easy to envisage systems where this would have no negative impact whatsoever.
I was looking around on the internet, found the article, thought it was interesting and posted it.
I think that's being constructive. You on the other hand haven't added anything to the discussion (caching/postgres), but rather are yelling at the clouds.
If you think the article is such a bad idea, provide some alternative links. If you're not in the mood to do that, just go to the next thing you might find interesting on the endless scroll that is HN.
Being a curmudgeon is frankly worse than having sub optimal articles.
OPs post was constructive. It indicates there might be a need for more heavy handed moderation and technical vetting of content before it reaches frontpage.
HN eats up all sorts of these posts from companies selling backend-as-a-service, low-code-web-UI-as-a-service, unreliable-replacement-for-Heroku's-free-tier-as-a-service, and so on...
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.
That just sounds like an application bug. Nothing should be done with the query result anyway until the transaction either completes our rolls back.
Realistically it is the norm to yolo updates at a service and if it fails then the whole thing 500s and things are just in an unexpected state. Often it is not even possible to guarantee successful rollback etc - if your update back to original state fails then what is the application state now? Undefined and potentially invalid, pretty much. Most people just replay the request again and hope it succeeds.
Obviously the right answer is “don’t do that” or “offload that complexity into graphQL or something” but in the real world… people don’t.
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?
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.
All of these questions go away or are greatly simplified with redis.
Similarly to Postgres, Redis replication is also async, which means that replicas can be out-of-sync for a brief period of time.
This however can come with a lot of issues if you started to use this to ensure consistency across many replicas. Writes are only as fast as the slowest replica, and any hickup on any replica could stall all writes.
What I wasn't sure about - IMO in such a situation, you should rather fix the application to deal with (briefly) stale information, and then you can throw either async postgres replicas at it.. or redis replication, or something based on memcache.
It's not worse, this is just a cheap way to increase performance without having to scale the main instance vertically.
If you have many inserts and deletes on a table, the table will build up tombstones and postgres will eventually be forced to vacuum the table. This doesn't block normal operation, but auto vacuums on large tables can be resource intensive - especially on the storage/io side. And this - at worst - can turn into a resource contention so you either end up with an infinite auto vacuum (because the vacuum can't keep up fast enough), or a severe performance impact on all queries on the system (and since this is your postgres-as-redis, there is a good chance all of the hot paths rely on the cache and get slowed down significantly).
Both of these result in different kinds of fun - either your applications just stop working because postgres is busy cleaning up, or you end up with some horrible table bloat in the future, which will take hours and hours of application downtime to fix, because your drives are fast, but not that fast.
There are ways to work around this, naturally. You could have an expiration key with an index on it, and do "select * from cache order by expiration_key desc limit 1", and throw pg_partman at it to partition the table based on the expiration key, and drop old values by dropping partitions and such... but at some point you start wondering if using a system meant for this kinda workload is easier.
If you can keep your entire working set in memory, though, then it probably doesn't matter that much.
By comparison an in memory kv cache is much more streamlined. They basically just need to move bytes from a hash table to a network socket as fast as possible, with no transactional concerns.
The semantics matter as well. PostgreSQL has to assume all data needs to be retained. Memcached can always just throw something away. Redis persistence is best effort with an explicit loss window. That has enormous practical implications on their internals.
So in practical terms this means they're in different universes performance wise. If your workload is compatible with a kv cache semantically, adding memcached to your infrastructure will probably result in a savings overall.
Sometimes it makes sense, when your workload is not going to hit the limits of your available hardware.
But generally you should be prepared to move everything you can out of the database, so database will not spend any CPU on things that could be computed on another computer. And cache is one of those things. If you can avoid hitting database, by hitting another server, it's a great thing to do.
Of course you should not prematurely optimize. Start simple, hit your database limits, then introduce cache.
The article talks about using Unlogged tables, they double write speed by forgoing the durability and safety of the WAL. It doesn't mention query speed because it is completely unaffected by the change.
that gave us breathing room to migrate the data we were interested in into a much smaller dedicated db for our purposes (the user data, used on every login and other operation in the system)
in a correctly specced RDS, MySQL can be set up to store all the data both in memory and on disk (note, I'm not referring to the ephemeral disk only db engine, I'm referring to giving the RDS the correct memory settings and parameters to store all the data both in memory and on disk)
once we did that, there was no difference in read speed between the Redis and just hitting the db, so we got rid of the Redis, which vastly simplified the architecture of the user service
= the article title is true, caching is often understood to be a default requirement but it kind of isn't if you architect things correctly
I lean towards fewer tools. Postgres is an awesome swiss army knife. In some cases, using it as a key-value store, a cache, or a messaging queue are fine. In other cases, they're not.
Examine how suitable this particular tool is for your particular use case and your particular requirements.
Maybe you have zero DevOps skills, a background in DB administration, and your cache backs an external API. Perhaps using postgres is the right solution.
Maybe you know enough about databases to be dangerous, but feel comfortable managing virtual clouds environments. Perhaps DynamoDB or a Redis instance is the way to go.
There is no one answer. However, often there is a good default answer.
P.S. The default answer changes over time. There's always the flavor-of-the-year. The technology landscape is also constantly shifting. My heuristic is to use lean towards the default answer that has been around the longest.
If you do, you're probably operating at a scale at which you have a whole big devops team to figure out, deploy, and manage your dedicated (blank). Or if you're really huge, you might be creating your dedicated (blank) in a way that's custom engineered around your problem.
Given the value of redis for caching and outside of caching, bur the ease and low cost of setting it up, I would say postgres as a cache seems niche.
It is niche, but it's a viable option for a lot of use cases if you need to limit the number of service dependencies.
Sure, you can do it, but why? I am all for removing complexities in my infrastructure. That said, Memcached and/or Redis are both rock solid and dead simple.
This just seems to be a waist of time 99.999% of use cases.
Re. Solid cache they effectively answered this in the post. The cost is less meaning they can cache more so p95 response time goes down. If you're not constrained my money you can just buy a bigger memcached or Redis of course.
If you're talking about moving things to Postgres /in general/, then cost and/or complexity are compelling reasons to do so. Small engineering teams, small budgets, simplifying and reducing cost can really help. Obviously it's not suitable for everyone but it's nice to have the option.
> On Basecamp, compared to our old Redis cache, reads are now about 40% slower. But the cache is 6 times larger, and running on storage that’s 80% cheaper.
It seems that their caching needs don't require the speed that Redis provides. Instead, they can get an overall performance improvement in their application if they cache more things -- that's why they want a bigger cache.
Based on this, it seems that Redis was too fast and too expensive. Being self-aware of your needs is key. This way, BaseCamp could tailor a solution that suits their needs.
You don't always need the fastest, the biggest, the most scalable, or the cheapest. If you know the thresholds, you can come up with a better tradeoff.
If it's to serve as a cache on an application level, memcache seems like it would be simpler and faster. While I do like Redis, I can see why you'd be careful introducing it. Redis can blur the boundaries between caching and data storage a bit, but it's just so handy.
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.
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?
I'm sure there are many cases when that makes sense, but there are many cases when that's also overkill. An in-memory cache inside your server will give you better performance, and a lot of less infrastructure maintenance complexity.
My issue with running PostgreSQL as cache would be its thread per connection model and downsides of MVCC for cache.
But it's highly voted on HN so may be I am missing something.
They just handwave benchmarking against cache databases as “out of scope”, that is very much in scope to claim I can use PG rather than redis.
Also a cache would impact the rest of the application (reading/writing to this cache consumes cpu and IO).
Also, missing an index on the inserted_at and there’s no uniqueness constraint on the key.
Overall, it reads like someone read about UNLOGGED and thought it could be useful for caching use cases. I don’t think anyone doubted that PG can be used a cache really, the trade offs would be a more interesting read than the POC
That’s the interesting part…
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.