[chanks]

One thing people don't mention that often with Postgres, but is worth some attention, is the Heap-Only Tuples (HOT) optimization that was added in 8.3. During an UPDATE, Postgres will try to fit the new version of the row on the same page as the old one - if it's able to do this, it means the indices don't have to be updated at all.

So if you're expecting a table to have a heavy UPDATE load it may be a good idea to lower its fillfactor from the default of 100 (for example, ALTER TABLE foo SET (fillfactor = 95);). This means that once a page is 95% full no new rows will be inserted into it, and the remaining 5% will be available for updated rows. HOT will also do "mini-vacuums" on individual pages as needed to free up space.

Of course, it's up to you to decide whether this tradeoff is worth increasing the effective size of your table by ~5%. But even tables with fillfactors at the default of 100 will get some benefit from HOT.

More info: https://github.com/postgres/postgres/blob/master/src/backend...

Edit to add: One caveat is that HOT won't work if you UPDATE a column that is indexed - then, of course, Postgres will have to touch the index.