Hacker News Reader

Pretty sure they must, row based storage on disk would practically require it just to not completely waste all of the space you've just gained from deleting the column by leaving a gap on every single row.

View on HN

Adding a nullable column is constant time (ie: basically instant) in postgres and innodb, maybe also in other systems.

If adding a nullable column is free, it probably means that the DBMS is able to distinguish multiple layouts for the same table: existing rows in which the new column doesn't actually exist and is treated as NULL, and newly written rows in which there is space for the new column.

But dropping a column is different: even if the DBMS performs a similar smart trick (ignoring the value of the dropped column that is contained in old rows) space is still wasted, and it can only be reclaimed by rewriting old files.

Dropping a column in postgres is also instant, so yes, it uses the same trick.

Deleting a row is similar too - the row is not removed from the heap page and the database does not get smaller (though if that page gets rewritten the row is not kept). Last time I used innodb it didn't actually return free heap pages to the filesystem at all so no matter how much you deleted the database file never got smaller.

EDIT: Looks like that's still the case now: https://bugs.mysql.com/bug.php?id=1341

Reply on news.ycombinator.com