7 ms·

You Don't Need a Dedicated Cache Service – PostgreSQL as a Cache (2023)

74 points by John23832 3y ago

deleted 3y ago
[deleted]
BiteCode_dev 3y ago
Systems that are used as cache such as redis evict keys with some randomness to avoid scanning all of them. They also evict keys you read that expired without you having to check for that. And they don't store both a key and an id. 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.
- Rapzid 3y ago
  Postgres doesn't need to scan them all because it has indexes. It also has a number of random sampling strategies available. 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.
bjornsing 3y ago
> Benchmarking and comparing UNLOGGED tables against Memcached or Redis is out-of-scope of this article That’s the interesting part…
- netcraft 3y ago
  yeah exactly - write isnt generally the concern, its the read that makes or breaks a cache...
- deleted 3y ago
  [deleted]
williamdclt 3y ago
It’s a weird article. It claims that one only needs Postgres but then presents a half-baked solution: the takeaway I got is “it’s not HA or horizontally scalable, and it’s probably slow” so it sounds like I _do_ need a dedicated cache service? 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
- deleted 3y ago
  [deleted]
bedobi 3y ago
at a previous gig, we put a Redis cache in front of a slow monolithic Microsoft SQL Server db 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
whalesalad 3y ago
> Benchmarking and comparing UNLOGGED tables against Memcached or Redis is out-of-scope of this article
- CharlesW 3y ago
  That was my "why bother writing this" moment also.
antimora 3y ago
Before reading the article I thought they author would talk about caching the query results automatically, which most DBs do that.
- deleted 3y ago
  [deleted]
techscruggs 3y ago
This appears to be riding the [Solid Cache](https://dev.37signals.com/solid-cache/ https://dev.37signals.com/solid-cache/) idea that DHH has started espousing. At the core of this idea is that SSD's are sufficiently fast for caching. Sure, this may work for 37signals, but that is because they manage their own hardware. That is the exception and not the rule. And, per [this discussion two weeks ago](https://news.ycombinator.com/item?id=39443679 https://news.ycombinator.com/item?id=39443679). SSD's aren't as fast for most of us that leverage "the cloud". 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.
- wlll 3y ago
  > Sure, you can do it, but why? 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.
- koliber 3y ago
  In general, caches need to be fast. However, the Solid Cache article you linked says: > 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.
osigurdson 3y ago
All "Postgres as an X" are valid to a point. If your use case doesn't require anything else and you prefer to build / test / maintain dedicated (a little snowflakey probably) logic, fine. Unfortunately, few such articles discuss the edges at which these solutions no longer work.
- ants_everywhere 3y ago
  Do you mean just in general, or are there known edge cases where Postgres performs poorly as a cache?
- koliber 3y ago
  I like the idea of "defining the edges of suitability." Just because you can use it, does not mean you should. 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.
  - osigurdson 3y ago
    I can say, using pg as a time series database works completely fine for < 1B rows. If you have significantly more than that, it is a good idea to start thinking about alternatives.
slt2021 3y ago
[flagged]
api 3y ago
You almost always don't need a specialized or dedicated (blank). You can usually get away with using some boring off the shelf tech that can do the job, even if it isn't doing the job as perfectly as a dedicated (blank). Any additional cost in resources is probably offset by the savings in cognitive load and admin hours. 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.
Spiwux 3y ago
Calling 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?
- kapperchino 3y ago
  Because you can’t scale out just the cache part of Postgres, one machine can only have so much memory
  - 10000truths 3y ago
    If you have a second machine, why not just put a Postgres read replica on it? Letting the WAL deal with replica consistency is much simpler than making the client responsible for keeping an external cache in sync, and you get the benefit of keeping everything in Postgres.
    - dpedu 3y ago
      Either I pay a performance penalty waiting for my cache entry to be synced to the replica, or I risk reading stale data from the replica, no?
- darth_avocado 3y ago
  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.
- candiddevmike 3y ago
  Caching with postgres also lets you do cache invalidation via triggers!
- VWWHFSfQ 3y ago
  > 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.
koliber 3y ago
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.
darth_avocado 3y ago
The 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.
ctc24 3y ago
Why 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.
- VWWHFSfQ 3y ago
  > 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?
  - ctc24 3y ago
    That's a bit of a strawman argument. Per the post, you can't leverage this on a read replica, it has to be run on primary. So you're going to stand up and manage a full new Postgres instance for this? 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.
- 0x457 3y ago
  Because SQLite is in process. Usually, when you start thinking about cache, you have more than one application server. Each application server running its own cache make cache invalidation a nightmare (I worked in a company where one genius did that and caused a lot of troubles). Don't show me any sqlite replication things because that's not how you want your cache to work. My issue with running PostgreSQL as cache would be its thread per connection model and downsides of MVCC for cache.
antoineleclair 3y ago
For 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.
- VWWHFSfQ 3y ago
  > 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.
  - antoineleclair 3y ago
    Transactions can fail because they conflict with other transactions happening at the same time. It's not an application bug. It's real life transactions happening on a production system. It's normal for that to happen all the time. The app can retry, etc., but it should be expected to happen. Having to deal with distributed transactions is not something easy. Especially when they're part of many different systems. For example, you'd have to wait until the transaction commits successfully before setting the value in the cache, which makes it hard to read. Also, life in general happens. Compute a value, cache it, save things to the database, make API calls, and then a network error happens cancelling everything that you've just done. Having code that handles this kind of possibility is relatively hard to write/read.
mrweasel 3y ago
So presumably the reason why you'd do something like this is if you have expensive queries or calculations, but in that case you could use materialized views instead, depending on your data and queries obviously. 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.
0xbadcafebee 3y ago
Sometimes I feel like the articles posted here are elaborate trolls. Like they wanted to know what the purpose of a cache is and how/when to implement one, but they didn't want to do research, so they came up with the worst idea they could imagine and blogged about it, hoping someone on HN would tell them the right way (or just laughing at people trying to correct them). 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.
- luibelgo 3y ago
  Not super constructive either, the beauty of having things like HN is to be able to discuss ideas, rather than just point to them as wrong. At least a couple of counterpoints on why this would be a bad idea and offer a better approach.
  - mrd3v0 3y ago
    There are way more terrible ideas than good ones, and that means treating all of them equally is a one-way road to HN losing the one thing it has going: new ideas. Feedback like root comment is important to keep pushing ideas forward, instead of a loop.
    - thunky 3y ago
      > Feedback like root comment What feedback? All I see is someone smelling their own farts.
  - 0xbadcafebee 3y ago
    I would honestly love to educate people here. But when the rabble come together to downvote you and dismiss what you're saying, the general perception from any reader is going to be that I'm wrong because so many people are disagreeing with me. Despite the fact that I'm literally an expert and they're not. So it's useless. (And just hurts my soul.)
shrubble 3y ago
I was checking out used rack servers on ebay and servers with 768GB RAM are not very expensive, they are under $3k USD... seems that if you could cache 256GB to 512GB of Postgres into RAM you could do pretty well...
- 0cf8612b2e1e 3y ago
  If you own the hardware, pricing becomes an entirely different question. Cloud will make you pay heavily for RAM where this strategy of using disk backed cache suddenly shines.
nodesocket 3y ago
Curious in the db schema, why bother with an id as the primary key? Just make the key the primary key.
sidcool 3y ago
The explanation is not convincing enough. Good to know info but I can't see this useful for a large scale production system. There is no description of cache invalidation strategies. But it's highly voted on HN so may be I am missing something.

74 points by John23832 3y ago

deleted 3y ago

[deleted]

Systems that are used as cache such as redis evict keys with some randomness to avoid scanning all of them. They also evict keys you read that expired without you having to check for that. And they don't store both a key and an id. 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.

Rapzid 3y ago
Postgres doesn't need to scan them all because it has indexes. It also has a number of random sampling strategies available. 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.

bjornsing 3y ago

> Benchmarking and comparing UNLOGGED tables against Memcached or Redis is out-of-scope of this article That’s the interesting part…

netcraft 3y ago
yeah exactly - write isnt generally the concern, its the read that makes or breaks a cache...
deleted 3y ago
[deleted]

williamdclt 3y ago

It’s a weird article. It claims that one only needs Postgres but then presents a half-baked solution: the takeaway I got is “it’s not HA or horizontally scalable, and it’s probably slow” so it sounds like I _do_ need a dedicated cache service? 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

deleted 3y ago
[deleted]

bedobi 3y ago

at a previous gig, we put a Redis cache in front of a slow monolithic Microsoft SQL Server db 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

whalesalad 3y ago

> Benchmarking and comparing UNLOGGED tables against Memcached or Redis is out-of-scope of this article

CharlesW 3y ago
That was my "why bother writing this" moment also.

antimora 3y ago

Before reading the article I thought they author would talk about caching the query results automatically, which most DBs do that.

deleted 3y ago
[deleted]

techscruggs 3y ago

This appears to be riding the [Solid Cache](https://dev.37signals.com/solid-cache/ https://dev.37signals.com/solid-cache/) idea that DHH has started espousing. At the core of this idea is that SSD's are sufficiently fast for caching. Sure, this may work for 37signals, but that is because they manage their own hardware. That is the exception and not the rule. And, per [this discussion two weeks ago](https://news.ycombinator.com/item?id=39443679 https://news.ycombinator.com/item?id=39443679). SSD's aren't as fast for most of us that leverage "the cloud". 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.

wlll 3y ago
> Sure, you can do it, but why? 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.
koliber 3y ago
In general, caches need to be fast. However, the Solid Cache article you linked says: > 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.

osigurdson 3y ago

All "Postgres as an X" are valid to a point. If your use case doesn't require anything else and you prefer to build / test / maintain dedicated (a little snowflakey probably) logic, fine. Unfortunately, few such articles discuss the edges at which these solutions no longer work.

ants_everywhere 3y ago
Do you mean just in general, or are there known edge cases where Postgres performs poorly as a cache?
koliber 3y ago
I like the idea of "defining the edges of suitability." Just because you can use it, does not mean you should. 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.
- osigurdson 3y ago
  I can say, using pg as a time series database works completely fine for < 1B rows. If you have significantly more than that, it is a good idea to start thinking about alternatives.

slt2021 3y ago

[flagged]

api 3y ago

You almost always don't need a specialized or dedicated (blank). You can usually get away with using some boring off the shelf tech that can do the job, even if it isn't doing the job as perfectly as a dedicated (blank). Any additional cost in resources is probably offset by the savings in cognitive load and admin hours. 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.

Spiwux 3y ago

Calling 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?

kapperchino 3y ago
Because you can’t scale out just the cache part of Postgres, one machine can only have so much memory
- 10000truths 3y ago
  If you have a second machine, why not just put a Postgres read replica on it? Letting the WAL deal with replica consistency is much simpler than making the client responsible for keeping an external cache in sync, and you get the benefit of keeping everything in Postgres.
  - dpedu 3y ago
    Either I pay a performance penalty waiting for my cache entry to be synced to the replica, or I risk reading stale data from the replica, no?
darth_avocado 3y ago
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.
candiddevmike 3y ago
Caching with postgres also lets you do cache invalidation via triggers!
VWWHFSfQ 3y ago
> 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.

koliber 3y ago

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.

darth_avocado 3y ago

The 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.

ctc24 3y ago

Why 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.

VWWHFSfQ 3y ago
> 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?
- ctc24 3y ago
  That's a bit of a strawman argument. Per the post, you can't leverage this on a read replica, it has to be run on primary. So you're going to stand up and manage a full new Postgres instance for this? 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.
0x457 3y ago
Because SQLite is in process. Usually, when you start thinking about cache, you have more than one application server. Each application server running its own cache make cache invalidation a nightmare (I worked in a company where one genius did that and caused a lot of troubles). Don't show me any sqlite replication things because that's not how you want your cache to work. My issue with running PostgreSQL as cache would be its thread per connection model and downsides of MVCC for cache.

antoineleclair 3y ago

For 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.

VWWHFSfQ 3y ago
> 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.
- antoineleclair 3y ago
  Transactions can fail because they conflict with other transactions happening at the same time. It's not an application bug. It's real life transactions happening on a production system. It's normal for that to happen all the time. The app can retry, etc., but it should be expected to happen. Having to deal with distributed transactions is not something easy. Especially when they're part of many different systems. For example, you'd have to wait until the transaction commits successfully before setting the value in the cache, which makes it hard to read. Also, life in general happens. Compute a value, cache it, save things to the database, make API calls, and then a network error happens cancelling everything that you've just done. Having code that handles this kind of possibility is relatively hard to write/read.

mrweasel 3y ago

So presumably the reason why you'd do something like this is if you have expensive queries or calculations, but in that case you could use materialized views instead, depending on your data and queries obviously. 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.

0xbadcafebee 3y ago

Sometimes I feel like the articles posted here are elaborate trolls. Like they wanted to know what the purpose of a cache is and how/when to implement one, but they didn't want to do research, so they came up with the worst idea they could imagine and blogged about it, hoping someone on HN would tell them the right way (or just laughing at people trying to correct them). 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.

luibelgo 3y ago
Not super constructive either, the beauty of having things like HN is to be able to discuss ideas, rather than just point to them as wrong. At least a couple of counterpoints on why this would be a bad idea and offer a better approach.
- mrd3v0 3y ago
  There are way more terrible ideas than good ones, and that means treating all of them equally is a one-way road to HN losing the one thing it has going: new ideas. Feedback like root comment is important to keep pushing ideas forward, instead of a loop.
  - thunky 3y ago
    > Feedback like root comment What feedback? All I see is someone smelling their own farts.
- 0xbadcafebee 3y ago
  I would honestly love to educate people here. But when the rabble come together to downvote you and dismiss what you're saying, the general perception from any reader is going to be that I'm wrong because so many people are disagreeing with me. Despite the fact that I'm literally an expert and they're not. So it's useless. (And just hurts my soul.)

shrubble 3y ago

I was checking out used rack servers on ebay and servers with 768GB RAM are not very expensive, they are under $3k USD... seems that if you could cache 256GB to 512GB of Postgres into RAM you could do pretty well...

0cf8612b2e1e 3y ago
If you own the hardware, pricing becomes an entirely different question. Cloud will make you pay heavily for RAM where this strategy of using disk backed cache suddenly shines.

nodesocket 3y ago

Curious in the db schema, why bother with an id as the primary key? Just make the key the primary key.

sidcool 3y ago

The explanation is not convincing enough. Good to know info but I can't see this useful for a large scale production system. There is no description of cache invalidation strategies. But it's highly voted on HN so may be I am missing something.