On Thu, 2023-03-02 at 15:53 +0800, Navindren Baskaran wrote:
If the other column is updated, it depends. If the updated column is not
indexed and there is enough room for the new row version in the same
table block, the index doesn't have to be modified. Otherwise it is.
See https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/
Very interesting article, thanks Laurenz. I wasn't aware of HOT and its relation to fillfactor.
At the risk of highjacking this thread a bit, although still on topic I think.
What's the 2023 status of that zheap table storage work?
And more specifically about a particular data-model of mine.
I have a 3-level hierarchical parents-children-grandchidren table structure:
1) a "root" table, with tens to thousands (~200K max) of (small) rows.
2) a "folder" table, with 20 to 50 (small) rows *per* "root" row.
3) several "value" tables, with again a 20 to 100 (large to very large) rows per "folder" row.
The root and folder tables must maintain a "last modified" timestamp for their respective subtrees,
which must be maintained via triggers (how else?). That makes those tables Update-heavy no?
So from your article, those two tables, with smaller rows (and fewer rows total in general) should
have larger fillfactors to increase the chances of an HOT update? Am I interpreting your article
(and its linked articles) correctly for this situation? TIA, --DD