People scale postgres by horizontally sharding it at the app-tier, and end up reducing its query capabilities to a glorified K/V store... They do this routinely and think it's normal. Then they use other things like Cassandra or Cosmos DB to augment the scale gaps that this leaves out (see what OpenAI is doing). Then they typically run a search engine and a cache as well. People think all of this complexity is normal/unavoidable because their cloud providers will happily see them consume all these different services.
Does Postgres Scale?
71–80 of 105 posts
Re: Does Postgres Scale?
#72Earlier quoted context omitted.
The other thing is that now a days you scale way way further vertically before you scale horizontally (assuming you are not using a cloud provider) Everyone is hung up making their shit "scalable" like its a systems design interview at google in 2010. Now a days you get a box with 600+ cores and 4TB of RAM. That is going to cover a very very large percentage of most enterprises.
When you scale horizontally from day one, it usually gives fault tolerance for online service, and this story is not very friendly in case of vertically scaled PG.
Scaling vertically does not mean you cant have fault tolerance and I feel like your comment just makes my point.
Re: Does Postgres Scale?
#73Earlier quoted context omitted.
When you scale horizontally from day one, it usually gives fault tolerance for online service, and this story is not very friendly in case of vertically scaled PG.
Scaling vertically does not mean you cant have fault tolerance and I feel like your comment just makes my point.
My point is that in PG, fault tolerance requires untrivial investments.
If you pick horizontally scalable DB (foundationdb, cocroachdb, scylladb, tidb, etc) they all give you fault tolerance for free without much involvement from your side, because it is part of the nature of those DBs.
Re: Does Postgres Scale?
#74"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…
if the index updates are your bottleneck you can often get away with using a much lighter index (for example BRIN) at a cost of slightly slower queries. This is very often a great and much overlooked tradeoff.
Re: Does Postgres Scale?
#75This post conflates scalability and performance. PostgreSQL is fast on smallish systems, but try adding more CPU cores and you'll see performance gains will not be linear at all. Modern server can ship with 256 or more cores and a single instance of PostgreSQL will struggle to take advantage of these. 4-8 cores is no problem at all, though.
What kind of cases were you measuring? I would think that, e.g. 256 separate long-lived connections in a setup like that would scale less-than-linearly but not dramatically so?
Re: Does Postgres Scale?
#76"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…
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.
Re: Does Postgres Scale?
#77Earlier quoted context omitted.
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...
Last time I almost used a hash index in Postgres, I learned it was an incomplete feature and not crash-safe yet. This was v9.3? At that same time, MySQL had them and they were ok to use. Later that got fixed, but I haven't tried again since, just been using btree because it seemed like Postgres favored that and it has theoretical advantages too.
They are fully stable and perform very well in Postgres today. There are some caveats, but they don't result in any sort of hiccups or unpredictable behavior.
Re: Does Postgres Scale?
#78"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…
So you open transaction, insert multiple rows, commit? And you're not using any special xact settings like SERIALIZABLE mode, right? Normally you use COPY FROM if it's a huge number of rows in some batch process, but not like inserting a few rows handling a backend request or something. I would try filling up the table, forcing a vacuum, then timing the inserts afterwards. Not that you should need to vacuum in real u…
> So you open transaction, insert multiple rows, commit?
Pretty much, yes. High volume rapid multi-row INSERTs. Nothing special involved in the DDL or how the data goes into the table. Vacuuming the table (whether automatically or between every 10-20m rows) makes no difference. The indices still penalize the performance substantially the larger they grow.
Re: Does Postgres Scale?
#79"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…
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.
Re: Does Postgres Scale?
#80Earlier 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)
As far as I can tell, Postgres is not designed with this inclination towards doing lighter work when clients are waiting and piling up maintenance work to do in background. I think the background work it does is mostly running vacuum on tables now and then. Contrast that with ClickHouse, for example. It operates in a different niche than Postgres (OLAP instead of OLTP) – with their merge tree engine family [1] that d…
Oriole is under very heavy development since we acquired them. We cut a release just 2 weeks ago:
https://github.com/orioledb/orioledb/releases/tag/beta15
We expect it to be production ready this year