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?