You're right UPDATEs may be an issue (because we handle them essentially as DELETE+INSERT). Generally speaking, row churn in the table alone is not an major issue - it's easy to clean up by vacuum, and it will be reused for new data. And you can limit the amount of bloat by tweaking the autovacuum parameters.
What's more painful is bloated indexes (e.g. due to UPDATEs that modify indexed columns), because that's much harder / more expensive to get rid of.
The thing is - this is part of the MVCC design, and it has some significant advantages too. It's not like the alternative approaches have no downsides.
I use PostgreSQL in an embedded device. There is a high insertion rate, and eventually when the disk starts to get full I need to get rid of old rows.
Using plain DELETE and VACUUM does not work. The deletes aren't fast enough to keep up with the inserts, and vacuuming reduces performance to the point that I have to drop data that is waiting to be inserted. This is on a high performance SSD and I've tuned postgresql.conf. (Bigger/better hardware is not possible in my application).
Instead, I think partitions with DROP PARTITION are the only way to handle high volume row churn. Dropping a partition is practically instant and incurs no vacuum penalty.
Not sure what postgresql.conf tuning you've tried, but in general we recommend making autovacuum more frequent, but performing the cleanup in smaller chunks. Also, batching inserts usually helps a lot. But maybe you've already tried all that. There's definitely a limit - a balance between ingestion and cleanup.
Don't wait until the disk gets full.
Autovacuum works great for most small to moderate sized databases. And it works great for larger databases with a few tweaks.
* http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin...
At the moment, PostgreSQL keeps all versions of all tuples in its heap files. Inserts, updates, and deletes all result in addition of new tuples to the heap, and the engine keeps track of which transactions can see which tuples. The vacuum process deletes tuples which are no longer needed, but in the meantime, there is bloat.
zheap would keep only the latest version of each tuple in its heap files. When a tuple got updated or deleted, the engine would move the old version into separate storage, the "undo" log. A vacuum process would need to clean up the undo log, but the heap would remain unbloated.
This is obviously very practical. But it's a shame that it introduces an asymmetry, where some transactions will be reading tuples from the heap, and some will need to root around in the undo log.
I note that this is the approach that Oracle has always used - as explained in this fine article by the same chap who wrote the blog post about zheap above:
https://www.enterprisedb.com/blog/databases-different-approa...
How does their design differ?
What are the tradeoffs to consider?
Some storage engines allow for in-place updates of records if the fields are fixed-length or if the updated variable-length value fits in the existing space. I've used some of those (e.g., MS SQL Server) to good effect in high-update scenarios.
I used to work in online gambling where we had plenty of row churn and not much bloat at all without having to use any of the workarounds.
1) You need an embedded relational database with a small foot print. Here PostgreSQL cannot compete with SQLite.
2) You have an application which does not support PostgreSQL.
3) You have petabytes worth of data. Here you want to look into something like Greenplum, a PostgreSQL fork.
Citus and other community extensions can help if you want to go distributed and fault tolerant
this MVCC implementation couple with the way indexing works also causes write amplification. the indices have pointers to the physical place where the row is stored (some other databases might instead record the primary key and do a lookup on the primary key to get the record). so if you update a row and it causes it to move to a different physical page (you need to keep the original row for MVCC so there needs to be space for the new row in the current page) then you need to update all the indices as well. PG has some optimisation around trying to write the update to the same physical page to reduce the number of writes.
That and perhaps global consistency aka "sort of but not really defeating the CAP theorem using atomic clocks and all that" in Spanner.
1) Horizontal scaling: In some cases it's doable using streaming replication, but it depends if you need to scale reads or writes. Or if you need distributed queries. There are quite a few forks and/or projects built on PostgreSQL that address different use cases (CitusDB, Greenplum, Postgres-XL, BDR, ...). And the features slowly trickle back.
One reason why it's like this is extensibility/flexibility - the project is unlike to hard-code one particular approach to horizontal scaling, because that would not work for the other use cases. So we need something that does not have that effect, which takes longer. It's a bit annoying, of course.
2) Storage systems: We don't really have a way to do that now - there are extensions using FDW to do that, but I'd say that's really a misuse of the FDW interface, and it has plenty of annoying limitations (backups, MVCC, ...). But it's something we're currently working on so there's hope for PG12+: https://commitfest.postgresql.org/20/1283/
Here are some of the success stories. https://www.citusdata.com/customers/
1. Incrementally updated materialized views (in MS SQL Server, these are called 'indexed views').
2. SQL Server Management Studio is better than anything I've seen elsewhere.
3. Reporting and analytics (SSRS/SSAS) built in. These are actually pretty good, but not the approach I would recommend.
4. Very solid clustering (I haven't used PostgreSQL's clustering, so this point might be out of date).
The license fees are steep (not as steep as Oracle's...) though, so you've got to really want those features.
I think report builders tools miss the sweet spot. Instead, build the specific reports that your business needs. Or, do a regular export to flat files (tab delimited causes fewest problems) and let people build what they need in Excel/R/whatever. Or both.
In practice, I try SQLite first, and fall back to Postgres if I need concurrent writes.
OpenStack used to support both MySQL and Postgres, but they removed the Postgres support, which is a decision that completely baffles me.
Lack of case and accent insensitive collations is inexcusable at this point. Is there anyone that enjoys sprinkling every bit of SQL with upper() comparisons?
Non materialized CTEs. I like materialized CTEs sometimes and wish MSSQL had them as an option but I also need it to work the other way.
Native point-in-time recovery out of the box. This looks like it might be possible but the process doesn't fill me with a lot of confidence.
Better connection scaling.
Ability to load custom TS dictionaries in user space (for hosted postgres on google/aws/etc..)
Does using a citext column not solve this?
Use a tool like pgbackrest, and everything just works really well.
We had Rackspace servers in the place I worked a couple of years ago and they didn't offer Postgress as far as I remember. (You could install and manage it on a virtual machine but then you had to do everything yourself, while they had managed MySQL servers available).
Also setting up master / slave replication seems easier with MySQL as I understand - I never tried with Postgress but did with MySQL.
The need to manually update clustered indexes, and the fact that an exclusive lock is needed to do so, is also annoying.
MySQL out of the box is more secure and easier to configure than Postgresql because of pg's public and the legacy config files.
It's also much easier to hire DBAs.
You can also index ARRAY columns, meaning if you need a "tags" feature it's ridiculously easy to implement, no need for additional tables.
There's also PostGIS, which has no MySQL analog, and makes doing actual real-world GIS work possible.
There's also neat stuff like exclusion constraints: http://nathanmlong.com/2016/01/protect-your-data-with-postgr...
And there's the ability to do all kinds of neat geospatial queries with PostGIS - eg "list all properties within X distance from the curvy coastline".
The real knowledge gap would come into things like all the various tactics you need to learn to get MySQL to produce good plans for your queries. PG has a much smarter optimizer which is harder to control - if it does the wrong thing, it's harder to encourage it to do the right thing, and philosophically they don't support query hints. OTOH, worst case in PG is usually much better than worst case on MySQL.