Re: Postgres Index and Updates

On Thu, Mar 2, 2023 at 10:08 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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