Обсуждение: Postgres Index and Updates
Hello,
We would like to understand in which scenario an index on a table will be rebuilt. Example if we have a table, which has two columns and one if it is indexed, which of the below operation trigger an index rebuild:
1. updating the value of the indexed column.
2. updating the value of the non-indexed column.
Thank you
On Thu, 2023-03-02 at 15:53 +0800, Navindren Baskaran wrote: > We would like to understand in which scenario an index on a table will be rebuilt. I assume that you are meaning "updated" or "modified" rather than rebuilt from scratch. > Example if we have a table, which has two columns and one if it is indexed, > which of the below operation trigger an index rebuild: > > 1. updating the value of the indexed column. > 2. updating the value of the non-indexed column. If the indexed column is updated, a new index entry has to be added. 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/ Yours, Laurenz Albe
On Thu, Mar 2, 2023 at 10:08 AM Laurenz Albe <laurenz.albe@cybertec.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