61–70 of 105 posts
4-8 cores is no problem at all, though.
Yes, you can scale it quite well vertically. But how about horizontally? It would be nice to have high availability, or even to be able to upgrade the OS and postgres itself without downtime.
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…
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)
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 covers all that, but my worry there would be that it would be non obvious and a foot gun. Doing it the way described above makes that break in semantics clear.
Earlier quoted context omitted.
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...
If you find yourself doing a lot of explicit transactions, it can be a sign that your schema isn't as normalized as it should be.
Earlier quoted context omitted.
Yep, this is what I think about when “scaling” is mentioned. Maybe I’m too distributed-compute brained, but throwing CPU at a db isn’t what I was hoping would be the answer.
So the point of distributed compute is to reduce the compute needed? I’ve generally found that distributed compute requires more compute than vertical scaling while getting clobbered by network bandwidth / latency. Theoretically with 2 to 10x compute required and in practice 100 to 500x
Horizontal scaling for reads on the other hand is much easier. If you have multiple replicas for high availability, you might as well put them to work. It can also reduce the risk for read heavy tasks interfering with transaction processing. You can even go a step further and replicate to a database that's optimized for analytical tasks.
Horizontal scaling for stateless applications (e.g. web servers or job processors) is often easier and more robust than vertical scaling, with little to no downsides.
Earlier quoted context omitted.
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…
I like this strategy a lot, but the performance of read queries suffer if they span partitions, correct? The issue I'm facing is a very large table, that is both write and read heavy, and the reads do not fall into a specific range of values for any particular column, so I don't think partitioning is an option.
It is a lot of admin work, but if you really need to scale up Postgres write throughput, I don't see many other options without increasing hardware costs.
I assume you have already picked the low-hanging fruit discussed in the neighboring comments - batch writes, make sure you are using COPY instead of INSERT, tune Postgres parameters adequately and use the fastest disk you can grab for the WAL.
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…
I wonder if you could make it so that queries read from both the index and the unindexed changes. It would be slightly slower but as long as the unindexed changes are kept small it might be fine.
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…
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)
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 does data deduplication in background.
There is one project of modernizing Postgres' storage engine called OrioleDB [2], but I think the company got acquihired by Supabase [3] and maybe the project has not been progressing very quickly since then.
[1] https://clickhouse.com/docs/engines/table-engines/mergetree-... [2] https://www.orioledb.com/ [3] https://supabase.com/blog/supabase-acquires-oriole
Earlier quoted context omitted.
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…
I like this strategy a lot, but the performance of read queries suffer if they span partitions, correct? The issue I'm facing is a very large table, that is both write and read heavy, and the reads do not fall into a specific range of values for any particular column, so I don't think partitioning is an option.
It scales beyond the needs that most people have in most situations. The constant problem is that "big scale" always means "larger than I've seen", so on any project larger than a person has encountered, they assume they need to pull out the big guns. Also, people worry about things like what happens if they really *do* scale 10 years from now. Neither is a practical concern for nearly anyone who will ever face this…
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.