Avinash Vallarapu tunes a PostgreSQL implementation of the TPC-C workload:
Vacuum is almost always presented as a pain point, a culprit, and an over exaggerated source of performance problems in PostgreSQL. The MVCC implementation in PostgreSQL is different from Oracle, SQL Server, MySQL or MariaDB, and that implementation introduces two requirements of its own, freezing transaction IDs, which is largely seamless, and clearing dead tuples through the various forms of vacuum. At the same time the PostgreSQL community is far ahead in minimizing the impact of vacuum. Each release has introduced enhancements substantial enough that most users never realize vacuum is something they could tune at all, and the list of vacuum specific improvements is long enough to deserve an article of its own. Among all of those optimizations, one of the most often missed is how to avoid the need for vacuuming in the first place. That is achievable most of the time, and it is not new.
Pavan Deolasee worked on the idea through 2006 and 2007 and authored the concept of the Heap Only Tuple, or HOT. Simon Riggs, Heikki Linnakangas, Tom Lane and many other PostgreSQL core team members and contributors have written a great deal of enhancement around it since. In this article we look at what PostgreSQL HOT updates actually are, how fillfactor decides whether they succeed, and how we identify which tables benefit. We then put it to the test with a HammerDB benchmark using the HammerDB TPROC-C workload against PostgreSQL 18.4, six 60-minute runs across three dataset sizes, and the improvement from correctly applied PostgreSQL HOT updates is substantial.
Read on for an overview of HOT tables and the results of this experiment.