Please don't top-post ... On 7/21/24 16:42, Ahmed Yarub Hani Al Nuaimi wrote: > That clearly explains the problem. But this got me thinking: what if we do > both index and heap optimization at the same time? > Meaning that the newly move heap tuple which is used to compact/defragment > heap pages would be followed by moving the index (creating and then > deleting) a new index tuple at the right place in the index data files (the > one that had its dead tuples removed and internally defragmented, aka > vacuumed). Deleting the old index could be done immediately after moving > the heap tuple. I think that this can both solve the bloating problem and > make sure that both the table and index heaps are in optimum shape, all of > this being done lazily to make sure that these operations would only be > done when the servers are not overwhelmed (or just using whatever logic our > lazy vacuuming uses). What do you think? >
I think this would run directly into the problems mentioned by Tom [1]. You say "immediately", but what does that mean? You need to explain how would you ensure a scan (of arbitrary type) sees *exactly( one of the heap/index tuples. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
