On Thu, 6 Jul 2023, Dimitrios Apostolou wrote:
+ Then I broke the command above in many small chunks
WITH rows AS (
DELETE FROM tbl_legacy AS t
WHERE (partition_key_column < $1)
RETURNING t.*
) INSERT INTO tbl SELECT * FROM rows;
COMMIT;
I increase the parameter $1 and keep going in a loop. At first this
goes OK, after one day though I notice that it has slowed down
significantly. My investigation shows that the simple query
SELECT min(partition_key_column) from tbl_legacy;
takes 38s, despite having an index on the column! A VACUUM fixes that,
so I guess the index has a ton of dead tuples. I guess autovacuum does
not do its job because the table is constantly busy.
Unfortunately VACUUM takes long (several hours) on this huge table, so I
can't add in the loop after the DELETE command.
Is there a better way to avoid the bloat in the loop above? Why can't
the DELETE command update the index by pointing the minimum element
beyond the dead tuples?
Any comments on this one? It annoys me that a simple loop deteriorated so
much and kept filling the table with bloat. What is that VACUUM does that
DELETE can't do, to keep the index fresh?
