Memory Size Matters to PostgreSQL | Hacker News Reader

Memory Size Matters to PostgreSQL

pgdba.org

60 pointsPaulHoule11 comments

Interesting read, but I find it very lacking. PGSQL memory consists of lots of parts; shared_buffers, work_mem, maintenance_work_men, OS Cache size, etc. For those who are interested in a deeper Postgresql tuning tutorial, I suggest this[1] as it highlights a number of important tuning options - specifically for PGSQL 17. Once your DB has been running for a while, run the postgresql-tuner app[2] to see if you have correctly applied RAM settings[2]. The repo has been around for a long time, but I find the recommendations (especially the estimated RAM usage) really good.

[1] https://www.pgedge.com/blog/postgresql-performance-tuning

[2] https://github.com/jfcoz/postgresqltuner

Beware, the first link appears to be AI slop with at least some bogus information. For example, it says "While PostgreSQL 15 introduced basic WAL compression", but WAL compression has been around since before 15.

That's a bit unfair, don't you think? https://www.percona.com/blog/new-wal-archive-module-library-...

The pgedge article talks about WAL compression, not WAL archiving. It specifically mentions wal_compression parameter, which has been around since PostgreSQL 9.5: https://www.postgresql.org/docs/9.5/runtime-config-wal.html#...

I don't fully understand this article but this point stuck out as probably fractally wrong:

Modern DDR4 memories have a theoretical throughput of 25-30 GB/s. This is more realistically ranging between 5-10 GB/s. With a 100 GB full packed shared buffer the time required to perform one single full scan ranges between 3 and 20 seconds.

Obviously DDR5 now exists and servers have multiple memory channels giving total memory bandwidth more like 200-500 GB/s. An old rule of thumb is that a computer should be able to read its entire memory in one second, although these days it may be more like 1-4 seconds.

The clock replacement algorithm only needs to read metadata, so a full sweep of the metadata for 100 GB of buffers should be milliseconds not seconds. (If they're talking about a table scan instead then obviously reading from buffers is going to be faster than disk.)

Even saying 25-30GB/s is weird.

DDR4-3200 is ~26GB/s per channel, and is the upper end of what you'll see on ECC DDR4. DDR5-5600 is common now, and is ~45GB/s.

Zen 2/3 Epycs on SP3 have 8 channels, Zen 4/5 Epycs on the SP5 have 12 channels per socket, and with both you get to have two sockets. That'd be ~410GB/s on dual socket SP3 and ~1080GB/s on dual socket SP5.

So, yeah, RAM goes brrr.

Yeah, but that ram is (somewhat) divided by the number of cores which has also gone up. That's why AMD will sell CPUs with 1 or 2 cores per CCD (instead of 6,8, or 16). I think they have an F in the middle of the numbers.

It was a while since I run memtest86+ (probably it was DDR3) but a single pass of a single test took more than 1-4sec to make a pass (over 8-16Gb). Granted DDR4/5 faster but servers with 256Gb or 512Gb are common nowadays so full memory scan can take even more time.

I often use pgtune to set the memory size, just select the correct information

https://pgtune.leopard.in.ua/

I love working with postgres, but this is one array where it needs a lot more love. If I deploy MongoDB it takes the whole machine and does its thing. With postgres, you have extremely minimalistic defaults, and then you have to turn a zillion knobs before it does what it should.

For beginners, it is a huge footgun, that makes people assume bad performance while evaluating. For the experienced PG admin, it is an annoiance and a time waster. Oh, the VM just gained 64GB RAM? PG will sit there and stare at it.

Apart from that, basically everyone starts with the PG guidelines or a generated template(25% for this, 25% divided by number of sessions for that). Then you keep wondering how much performance you left on the table.

for the rest of us:

https://pgtune.leopard.in.ua/

Reply on news.ycombinator.com