There is something to be said for simpler B-Tree style databases like MySQL that are harder to screw up!
Also perhaps there could be an optional mode for Postgres that did something to the effect of immediate vacuuming for all modification statements. Collecting garbage as soon as its made. At the cost of slower statements, but no long term degradation. More predictable performance.
Like you, I've long wished I could pay a performance penalty on queries to collect garbage immediately rather than batching up this debt to pay all at once some time later.
[edited: I'd pay that cost on all queries, not just writes, if the performance were more consistent over time.]
The other way this is handled is to move dead rows to an undo log, and the min heap only contains the current version of any specific row, and a pointer to where to find the dead rows in the undo log.
The above approach keeps your main heap from bloating when updates and deletes happen, but it causes transactions which reference the old version of the rows to be slower, as well as slowing down rollbacks.
This is exactly what the zHeap project is doing along with the pluggable storage API.
I think that's consistent the parent's goal (and mine) of allowing ongoing requests to be slower in exchange for not having to pay a major cost later to vacuum.
The experience makes me wonder if the database is designed primarily using short-term, modest-scale workloads (which are much easier to test) and not behavior that only shows up after several weeks of a continuous, heavy workload. It obviously works for a lot of users.
It's true though that Postgres still needs work on performance features and optimizations.
Maybe you're a solo researcher doing data science.
I want my tools to be the best they can be. Always striving to be better. To push the limit and make things simply awesome.
I don't like defeatist attitudes or telling the user they are "holding it wrong".
Yes, of course we all want our tools to the best they can be, but that involves making tradeoffs in features because the maintainers don't have an infinite pool of time.
Postgres is a sufficiently advanced tool that if you're using it for any serious work you need to read the documentation for it. Ideally cover to cover, but at least skim the whole thing and pick out the important parts. The Postgres "Routine Database Maintenance Tasks" documentation  describes in great detail everything you need to do to keep your database working well.
Other than that, what is the complaint exactly? You do need to understand how your tools work if you want to use them correctly.
In that case they rejected a bugfix for something like 10 years, seemingly because of attitude issues.
Not that it forgives the fact the bug was reported 9 years ago and it just recently got fixed. I know personally I'd have messed it up.
It's nearly impossible to avoid deadlock in any reasonable complex use of MySQL.
It's usually the gap locks that bite you, I've found.
Oh, and do all your deletes by primary key. Deletes with joins are especially good for creating too many locks.
>docker run -it --rm jfcoz/postgresqltuner --host=dbhost --user=username --password=pass --database=testdb
sudo yum -y install postgresql-devel
sudo useradd -d /home/pgsqltun -m -s /bin/bash -c"pgsql tuner user" psqltun
sudo su - psqltun
wget -O- https://cpanmin.us | perl - -l $HOME/perl5 App::cpanminus local::lib
echo 'eval `perl -I $HOME/perl5/lib/perl5 -Mlocal::lib`' >> $HOME/.bash_profile
echo 'export MANPATH=$HOME/perl5/man:$MANPATH' >> $HOME/.bash_profile
Thus the “tuning” matches your actual use.
If there can be done guaranteed improvements to your DBs performance based on static analysis of the config-alone, why on earth is that not the standard config? That seems like a no-brainer.
There was a post earlier (https://news.ycombinator.com/item?id=19422554) that I assume inspired this post and it was someone who benefited by correcting the DBMS' very wrong assumptions about the cost of random IO, and this was always the Achilles heel of SQL Server as well with any sort of fast storage -- it absolutely refused to accept that anything could be faster than about 150 IOPS, and would try to brute force through with terrible approaches. In this case the guy could just change the configuration and it would properly calculate query plan costs and execute accordingly, where SQL Server, to my knowledge, still doesn't let you change this costing, nor does it dynamically determine it.
In the mysql world, mysqltuner has been kicking around for more than a decade now that spits out suggested configuration changes. I haven't had a need to use it in several years though https://github.com/major/MySQLTuner-perl
There was an interesting project around applying blackbox optimisation to MySQL, called OtterTune on which a paper was published a couple of years ago, https://blog.acolyer.org/2017/08/11/automatic-database-manag.... They got some good results out of it. Haven't attempted to use it myself: https://github.com/cmu-db/ottertune
There are just so many dials and switches that can be changed that can modify the performance of the database, it really has to be moving far beyond anything any one individual could possibly grok.
pgtune writes config given some parameters and doesn't actually look at running PG instances.
You could make use of things like IDE syntax highlighting, and in some cases, auto-fixing.
Glad to see they have a way to run it with docker, because I have too many bad memories of missing perl modules when trying to run perl scripts in the early 2000s (but that also tells you how outdated I am from perl-world)