Impact of Adding a New Column on Table Bloat in PostgreSQL

Question

I'm working with PostgreSQL and have a concern regarding table bloat when adding a new column to an existing table. I understand that PostgreSQL uses Multi-Version Concurrency Control (MVCC) and autovacuum to reclaim space from old rows.

Here's the scenario:

Let's say I have a table named example_table with existing data, and I decide to add a new column, for example, new_column of a larger datatype:

ALTER TABLE example_table ADD COLUMN new_column VARCHAR(255);

Additionally, I will be updating the new column with new values:

UPDATE example_table SET new_column = 'some_value';

As far as I understand, after this operation, the old rows are marked as dead, and autovacuum will eventually reclaim the space. However, the old rows were of a smaller size compared to the new rows with the added column.

I have the following questions:

• How does PostgreSQL (specifically, Autovacuum) handle the space reclaimed from old rows, considering the size difference between old and new rows?
• Will this process potentially lead to table bloat since the dead tuples cannot be reused due to the size difference?
• Is it necessary to perform a VACUUM FULL to efficiently reclaim space in such a scenario, or will autovacuum handle it adequately?

Answer 1

If the new values are a constant literal, you could add the column already populated without creating any dead tuples:

BEGIN;
ALTER TABLE example_table ADD COLUMN new_column VARCHAR(255) default 'some value';
ALTER TABLE example_table alter column new_column drop default;
COMMIT;

If you have to do it as an UPDATE, then the free space generated will eventually be reusable. VACUUM will defragment the free space from multiple old tuples (in the same page) so it can be used for larger new tuples. Assuming of course there are enough old tuples on any one page to add up to that amount of free space. Doing this does require a "super-exclusive" lock on the page, but that is unlikely to be a long-term large-scale problem.