Trick for removing table bloat without requiring a full table lock and production halt

remove_table_bloat_async_with_inheritance.md

Given a table bar that we need to compact while maximizing availability of the data in it and minimize load/IO during compaction.

First, if there are any tables with foriegn key columns referencing columns in bar those foreign keys need to be disabled, e.g:

ALTER TABLE foo DISABLE TRIGGER fkey_bar_id

Now we can work on compacting the data by copying it to a new table.

SQL to create a new, empty table and have the current table inherit from it:

BEGIN;

ALTER TABLE bar RENAME TO bar_old;

CREATE TABLE bar (LIKE bar_old INCLUDING ALL);

ALTER TABLE bar_old INHERIT bar;

ALTER SEQUENCE bar_id_seq OWNED BY bar.id;

COMMIT;

Now we have a new, empty table but queries against it will include all the existing data in bar_old thanks to the inheritance link.

Next, loop over batches (e.g. id ranges) from bar_old, deleting them and inserting them into bar (pseudocode):

min_id := 0
batch_size := 10000

while true
  -- This query will:
  -- DELETE up to batch_size rows from bar_old ordered on id
  -- INSERT the rows deleted from bar_old into bar
  -- return a result set consisting of the ids of the rows inserted into bar
  result = execute_sql('WITH del AS (
                          DELETE FROM bar_old
                          WHERE id IN (SELECT id
                                       FROM bar_old
                                       WHERE id > #{min_id}
                                       ORDER BY id
                                       LIMIT #{batch_size})
                          RETURNING *)
                        INSERT INTO bar
                          SELECT * FROM del
                        RETURNING id;')
  break if result.count < batch_size
  min_id := result.ids.max

Confirm no more rows in bar_old, this should return nothing

SELECT max(id) FROM bar_old;

Drop the old table

DROP TABLE bar_old;

• Fix up index and foreign key names (if needed) on the new bar table

• Re-enable any foreign keys previously disabled. This is going to be doing a lot of reading while holdinng a lock on foo to check every value of foo.bar_id against the bar.id values but as long as both sides are properly indexed it shouldn't be too bad.

ALTER TABLE foo ENABLE TRIGGER fkey_bar_id