undefined | Better HN

0 pointssubhobroto10d ago0 comments

That's a phenomenal script that absolutely belongs on a dba.stackexchange question and even the PostgreSQL mailing list.

I strongly encourage you to post to https://dba.stackexchange.com/, as a HN thread is the wrong place for this discussion (there's a lot of tuning ahead of you and others who are in your situation in the future might skip this nested thread) but be forewarned, the TLDR from dba.stackexchange will either be a quick "you need atleast 64 GB RAM for your PostgreSQL usecase" or there will a better, thorough discussion of increasing max_wal_size and lowering the fillfactor (which is what you're looking for). The ideal answer should even walk you through BRIN (vs btree) indexes. I'm asking you to post there because it will enumerate the WHY of all of these. At that point you can make an informed decision if this all would be worth it.

Now I empathize that MySQL is doing a phenomenal job at only a quarter of that (16 GB you said?) but not for the reasons you might think (and one can certainly argue, nor should an end user care!). MySQL's method of buffering (InnoDB change buffers) and its clustered index gives you the performance you like when suddenly doing bursty writes aka "write sprint". I need you to be aware of that (and dba.stackexchange responses will certainly address that).

I would have written a lengthy post on what to do next but I must first ask:

- Why are you evaluating PostgreSQL in this case when MySQL seems to work well? For example: does it feel like your aggregations are getting slower? As you can see, with PostgreSQL, you will have different set of tradeoffs (RAM, tuning, VACUUM)

- Are there real, limiting business constraints that force you to operate on less than 64 GB of RAM given your volume and throughput expectations (like FF limitations, or these are smaller machines on the edge, etc)

- If you can, as an experiment, while you write your dba.stackexchange question and for the PostgreSQL mailing list, you can tweak multiple parameters and tell me what you see:

- I'm concerned you have a `shared_buffers = 128MB`. Set it to `shared_buffers = 8GB` (give the B-trees room to live in RAM)

- Increase `max_wal_size = 16GB` (stop the checkpoint flooding and let Postgres "breathe" during your batch inserts).

- Increase `checkpoint_timeout = 30min` (set 30min to the actual window it takes; also, this is temporary but this should push checkpoints out so they don't interrupt your "write sprint").

- Set `maintenance_work_mem = 2GB` (should speed up index creation and vacuuming).

- Lower `fillfactor` on those specific 8 indexes from the default 100 down to 70. The B-trees should now have the ability to absorb those inserts better.

This should get you in the right direction, googling for the right documentation, but there's even more ahead of you, including a separate discussion about BRIN (vs btree) indexes.

> defers persisting new index data so that the DML can be finalized as quickly as possible

"defers persisting" might get misread as if it doesn't write to disk - it does but you're close and you will uncover more :)

MySQL defaults are specifically tuned for your "write sprint" usecase, infact, to actually mask the IO latency of secondary index updates but if your real usecase is not just large sudden bursts of writes to a table that has a btree index, you certainly will appreciate this effort. Happy weekend!

0 comments

daneel_w8d ago

Thanks a bunch for all of the config tuning advice. Can see huge improvements in both flattening the creeping slow-down and really pushing back the point where we eventually hit the wall. Just lowering the fill factor on the indices made a substantial difference even before permitting Postgres to use more RAM. Using BRIN indices for certain columns, as suggested by several others in the discussion, also resulted in some smaller but still valuable speed-ups. It's obvious by now that a careful combination of curated choices greatly matters for the full scope of this data. On the whole, in fact, it looks like all of the combined advice clear this hurdle up so much that we can no longer say we observe Postgres having an actual problem dealing with our current volumes and intensity of ingestion.

We run quite a few of these setups, so cost absolutely plays a big part in the size and selection of hardware. We're evaluating Postgres because of a mergers and acquisitions event prompting us to consolidate portions of the platforms for the sake of savings and reducing the overall "sprawl". Conversely we're also testing how the other party would fare if some of their stuff were to run under MySQL instead of Postgres. There's a chance we end up leaving things just as they are.

subhobrotoOP8d ago

> On the whole, in fact, it looks like all of the combined advice clear this hurdle up so much that we can no longer say we observe Postgres having an actual problem dealing with our current volumes and intensity of ingestion.

I am extremely happy for you!

This is fantastic news. I would really appreciate a followup post on DB SE and/or PostgreSQL ML because I assure you, there are others in your position. A deeply nested HN thread is not something that will be visible - you would be saving a lot of people heartburn.

Also remember, we just got started! The tips I gave you earlier is just a drop i the bucket. In the days of AWS RDS, knowledge about these are a "lost art" but running PostgreSQL yourself is extremely empowering. If you could make the posts, please do and I also urge you to test out https://github.com/pgsty/pigsty

j / k navigate · click thread line to collapse

0 comments

daneel_w8d ago

subhobrotoOP8d ago

I am extremely happy for you!

j / k navigate · click thread line to collapse