Elizabeth Garrett Christensen: Postgres Performance Boost: HOT Updates and Fill Factor
There’s a pretty HOT performance trick in Postgres that doesn’t get a ton of attention. There’s a way for Postgres to only update the heap (the table), avoiding having to update all the indexes. That’s called a HOT update, HOT stands for heap only tuple. Understanding HOT updates and their interaction with page fill factor can be a really nice tool in the box for getting performance with existing infrastructure. I’m going to review HOT updates and how to encourage them in your Postgres updates. Heap Only Tuple (HOT) updates Modern versions of Postgres are able to perform HOT (Heap Only Tuple) updates. A HOT update occurs when a new version of a row can be stored on the same page as the original version, without the need to move the row to a new page. With HOT updates, if the updated row can still fit on the same data page as the original row, Postgres adds a new row on the same page, while keeping the old row data since it may still be in use by other processes. Postgres also adds a HOT chain link from the old row to the new row, so it can find the new row when a HOT update occurs. HOT updates and indexes So the way this normally works in PostgreSQL without HOT updates is if you have a table that is indexed, and one row(tuple) is updated, the update must be applied to the index. For HOT updates, Postgres will skip the update to the index IF you aren’t updating the index key. By skipping the index update step, HOT updates reduce the amount of disk I/O and CPU processing required for an update operation, leading to better performance, especially for tables with large indexes or frequent updates. Postgres 16 HOT updates and BRIN indexes Prior to Postgres 16, any index on an updated column would block updates from being HOT. An update in Postgres 16 makes HOT updates more feasible since BRIN (summarizing) indexes do not contain references to actual rows, just to the pages. This allows columns indexed with BRIN to be updated and still have HOT updates occur. Though some care shou[...]
Postgres有一个性能技巧叫做HOT更新，可以在不更新所有索引的情况下更新表。这可以提高性能，特别是对于具有大型索引或频繁更新的表。HOT更新发生在新版本的行可以存储在与原始版本相同的页面上。表的填充因子决定为未来更新保留多少空间，降低填充因子可以增加HOT更新的机会。然而，需要平衡，因为将填充因子设置得太低可能会导致浪费空间和增加磁盘I/O。开发人员可以使用pg_stat_user_tables目录来识别HOT更新并相应调整索引策略。降低填充因子可能会增加可测量的膨胀。除非索引是BRIN，否则无法对索引数据进行HOT更新。
