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

Does Postgres Scale?

dbos.dev

Post reply on HN
Print view View original

81–90 of 105 posts

Re: Does Postgres Scale?

#81

Post by justinclift »

daneel_w wrote: post #79
justinclift wrote: post #26

Earlier quoted context omitted.

What's the underlying filesystem(s) you're using for the data storage?

Ext4 with journaling. I've played around with XFS as well just to see what would happen. No major difference. I'm certain it's caused by the way Postgres builds its indices.

No worries. Just checking if it was ZFS.

While I really like ZFS personally for 99% of things, for "need maximum performance with PostgreSQL and we're already pushing high end hardware to the maximum" ZFS is reportedly the wrong choice.

Re: Does Postgres Scale?

#82

Post by daneel_w »

subhobroto wrote: post #15
daneel_w wrote: post #9

"Overall, we find a Postgres server can handle up to 144K of these writes per second. That’s a lot, equivalent to 12 billion writes per day." Based on a problem I'm facing with Postgres today, I wonder if this really progresses as linearly as the article wants to make it out. We're in the middle of evaluating Postgres as a replacement for MySQL, and experience notable slow-down for plain multi-row inserts due to inde…

You've given us some idea of the volume of your data but there's no mention of what's ingesting it or how. > during these stress tests the hardware is nowhere close to over-encumbered, and there's consistent headroom on both memory, CPU and disk I/O This assertion is likely wrong - you're likely skipping over some metrics that has clues to what we need to know. Here are some questions to get the discussion moving. -…

> This assertion is likely wrong

We can clearly see with iostat/iotop that the server and its storage isn't overworked.

> Is this PostgreSQL managed or self-hosted?

We're evaluating on our own bare metal. It's an 8c/16t Zen 3 with 32 GB of RAM. Storage (where everything incl. the WAL is) is NVMe and the drives' true sustainable/synchronous write speed is about ~750 MiB/second.

The specs are far, far higher than required for something this basic. Total memory usage when we begin observing the problem is a fraction of what's available. The MySQL production and test environments running this without hindrances actually have only half the amount of RAM.

> Are you using COPY or multi-row INSERT statements?

Multi-row INSERTs, one per transaction, with anything from 100 to 500 rows. Evaluation simulates the volumes we can see through our APIs on production, though it omits everything but Postgres itself in order to test Postgres' ingestion capability without other factors getting in the way - it's just Postgres and a light-weight data generator mimicking production data.

> What is the fillfactor, max_wal_size and checkpoint_timeout?

Not sure about the fill factor. Everything is running on default Postgres 17.9 as packaged for Debian 13, so that would be 1GB max_wal_size and checkpoint_timeout is commented out with a default of 5 minutes. Haven't gotten to any performance tuning yet, would be thankful for any suggestions to try out. The only thing we've tried is disabling auto-vacuuming to see if it was too frequent, causing i/o contention or otherwise hogging throughput. Not really any noticable change.

> Are random UUIDs (part of) the index?

No, neither v4 UUIDs nor b-tree-friendlier v7 UUIDs, but a couple of the indexed columns contain random integral numbers that can become quite sprawly inside a b-tree. We do observe somewhat better results indexing these with HASH indices instead, which also makes a lot more sense for that particular data and how we query it. For evaluation our outset was to stick with b-tree indices because that's what's used on the MySQL setup (InnoDB does not support on-disk hash indices).

> What's the iostat or wa during the slowdown?

When we reach the point where ingestion speed has shrunk to about a third we observe iowait peaks having grown to some 15%, which tells us the problem is likely Postgres spending more and more of its time shoveling in the indices rather than storing actual row data. Maximum written data at about 150 MB/second is just a fraction of what the NVMe drives can sustain. None of Postgres' individual processes ever top out anywhere close to 100% of a single core on the machine. Total memory usage is less than 2 GB, and here we suspect we have a lot of tuning to look into. To contrast, the MySQL setup is greedier with both CPU and memory usage, and an educated guess is that the major difference allowing it to keep ingesting hundreds of millions of rows without slowing down is that InnoDB, without us having to jump through any hoops, on one hand defers persisting new index data so that the DML can be finalized as quickly as possible, and on the other hand operates on its indices in a much more efficient way than Postgres.

Everything needed to reproduce the problem is in this paste, which contains a neutral version of the full table and indices, and a simplified version of the data generator: https://paste.debian.net/plainh/ddc819cb

Re: Does Postgres Scale?

#83

Post by daneel_w »

andersmurphy wrote: post #22
daneel_w wrote: post #9

"Overall, we find a Postgres server can handle up to 144K of these writes per second. That’s a lot, equivalent to 12 billion writes per day." Based on a problem I'm facing with Postgres today, I wonder if this really progresses as linearly as the article wants to make it out. We're in the middle of evaluating Postgres as a replacement for MySQL, and experience notable slow-down for plain multi-row inserts due to inde…

The problem is row locks when using interactive transactions over the network and contention. That can absolutely kill your performance with postgres, there's not really anything you can do to get around it (other than avoid interactive transactions). [1] [1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...

In this case that is not the problem.

Re: Does Postgres Scale?

#84

Post by daneel_w »

petergeoghegan wrote: post #37
keithnz wrote: post #27

Earlier quoted context omitted.

I don't think it really matters in terms of their question though, given MySql on the same specs doesn't have the problem and postgres does. Quite clearly it has something to do with indexes and what is the wall postgres is running into that causes the drop off on quite low amounts of rows. If the answer is just get more RAM, it kind of implies postgres is not really that scalable. Especially if the drop off is propo…

Why are you using hash indexes? They're much less widely used than standard B-Tree indexes. The bucket split code likely isn't very scalable [1]. I suggest testing the same workload with your existing hash indexes replaced with equivalent B-Trees. [1] https://github.com/postgres/postgres/blob/master/src/backend...

> Why are you using hash indexes?

On some data, for certain ways of using and querying that data, it makes a lot more sense than a b-tree. When we use hash indices for some of the columns in our test just to see how Postgres will perform, and run our test towards a quarter of a billion rows where Postgres' 32-bit hash indices have a relatively high chance of colliding, querying these hash indices and returning the single requested row is still instantaneous.

Re: Does Postgres Scale?

#85

Post by daneel_w »

justinclift wrote: post #81
daneel_w wrote: post #79

Earlier quoted context omitted.

Ext4 with journaling. I've played around with XFS as well just to see what would happen. No major difference. I'm certain it's caused by the way Postgres builds its indices.

No worries. Just checking if it was ZFS. While I really like ZFS personally for 99% of things, for "need maximum performance with PostgreSQL and we're already pushing high end hardware to the maximum" ZFS is reportedly the wrong choice.

We've reached the same conclusion in other situations. Great for reliable and scalable storage, not so great for focused intense i/o.

Re: Does Postgres Scale?

#86

Post by daneel_w »

giovannibonetti wrote: post #14
daneel_w wrote: post #9

"Overall, we find a Postgres server can handle up to 144K of these writes per second. That’s a lot, equivalent to 12 billion writes per day." Based on a problem I'm facing with Postgres today, I wonder if this really progresses as linearly as the article wants to make it out. We're in the middle of evaluating Postgres as a replacement for MySQL, and experience notable slow-down for plain multi-row inserts due to inde…

With some extra admin work, you can greatly increase your insert throughput, as long as the table load is comprised mostly of inserts: 1. Partition your table by range of a monotonic ID or timestamp. Notice the primary key will have to contain this column. A BIGINT id column should work fine; 2. Remove all the other indexes from the partitioned table. Add them to all the partitions, except the latest one. This way, t…

Thanks for sharing this "tactic". Will look into it.

Re: Does Postgres Scale?

#87

Post by [deleted] »

daneel_w wrote: post #76
bijowo1676 wrote: post #29

Earlier quoted context omitted.

problem is table design and write amplification. Every row insert triggers update into every index, so you get classic amplification problem. Separate your table into Cold (with all indexes and bells and whistles) and Hot (heap table with no indexes except PK). Insert as many rows as you want into Hot heap, and then move them in the background into cold in batches, so that index recalculation is amortized across many…

> problem is table design and write amplification. Every row insert triggers update into every index, so you get classic amplification problem. Yes, this is understood. In particular for b-trees that require some refurnishing when growing. What's less understood is why Postgres hasn't solved this in a way similar to how InnoDB solves it behind the scenes.

[deleted]

Re: Does Postgres Scale?

#88

Post by daneel_w »

SCdF wrote: post #63
appplication wrote: post #57

Earlier quoted context omitted.

That’s a really cool idea I had not heard before, thank you for sharing this. It also feels like the type of thing a db ought to be able to do under the hood. I wonder why this is not a config (though there’s a pg extension for everything so maybe it does exist)

It wouldn't be atomic, and so would break transaction semantics. If you committed a row update but didn't update the index, a subsequent query using the not yet updated index would not find the updated row correctly. It would also only work for certain types of indexes, you couldn't do it for uniqueness constraint for example. I do agree that in theory you could have some extension to the index declaration that cover…

My impression is that InnoDB (MySQL's primary storage engine) is doing something like this. We have never seen any slow-downs on adding to the data set I've discussed in this thread, even at hundreds of millions of rows, and per the nature of the system creating this data the majority of these rows are targeted for additional single-row DML within a few seconds of being inserted, with instantaneous effect.

Re: Does Postgres Scale?

#89

Post by subhobroto »

daneel_w wrote: post #82
subhobroto wrote: post #15

Earlier quoted context omitted.

You've given us some idea of the volume of your data but there's no mention of what's ingesting it or how. > during these stress tests the hardware is nowhere close to over-encumbered, and there's consistent headroom on both memory, CPU and disk I/O This assertion is likely wrong - you're likely skipping over some metrics that has clues to what we need to know. Here are some questions to get the discussion moving. -…

> This assertion is likely wrong We can clearly see with iostat/iotop that the server and its storage isn't overworked. > Is this PostgreSQL managed or self-hosted? We're evaluating on our own bare metal. It's an 8c/16t Zen 3 with 32 GB of RAM. Storage (where everything incl. the WAL is) is NVMe and the drives' true sustainable/synchronous write speed is about ~750 MiB/second. The specs are far, far higher than requi…

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!

Post reply on HN