Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

martinheinz.dev

Post reply on HN
Print view View original

51–60 of 82 posts

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

#51

Post by [deleted] »

VWWHFSfQ wrote: post #28
Spiwux wrote: post #20

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?

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

[deleted]

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

#52

Post by renegade-otter »

Spiwux wrote: post #20

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?

Add read replicas before doing cache "optimization", because cache timing bugs are a special kind of hell.

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

#53

Post by zomnoys »

tetha wrote: post #43
Spiwux wrote: post #20

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?

Postgres can develop problematic behavior if you have high churn tables - tables with lots of deletes on them. 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 i…

I believe the author addresses this by making the table `UNLOGGED`. https://www.crunchydata.com/blog/postgresl-unlogged-tables. These have less overhead.

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

#54

Post by 0cf8612b2e1e »

shrubble wrote: post #35

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

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.

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

#55

Post by jasonwatkinspdx »

Spiwux wrote: post #20

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?

The buffer pool in a rdbms ends up intimately connected with the concurrency control and durability protocols. There's also a variety of tradeoffs in how to handle conflicts between transactions (steal vs no steal, force vs no force, etc). You need deadlock detection or prevention. That creates a necessary minimum of complexity and overhead.

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.

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

#57

Post by vbezhenar »

Spiwux wrote: post #20

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?

Because Redis is almost infinitely scalable while Postgres is not. You have limited vertical scalability budget for your database. The more things you put into your database, the more budget you spending on things that could be done elsewhere.

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.

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

#58

Post by stonemetal12 »

Spiwux wrote: post #20

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?

Even though PG caches it is still doing all the things to run the query. It is like saying why does a 3d render take so long to render an image when the same image saved to a PNG opens so much faster.

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.

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

#59

Post by singron »

VWWHFSfQ wrote: post #37
antoineleclair wrote: post #36

Earlier quoted context omitted.

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. Fo…

Right but postgres isn't going to help with this if the application developer isn't doing safe and proper transaction management in the first place. What you described is a bug in the application logic for when and how to update the cache.

It's super hard to get this right. E.g. if you only update the cache after the transaction commits, you might commit without updating the cache, or if 2 writers interleave, the first one to commit might make the final update to the cache with a stale value.

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

#60

Post by inopinatus »

0xbadcafebee wrote: post #32

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 advic…

This is Cunningham’s Law. https://en.wikipedia.org/wiki/Ward_Cunningham#%22Cunningham'...
Post reply on HN