51 ms·
Adding a nullable column is constant time (ie: basically instant) in postgres and innodb, maybe also in other systems.
by calpaterson 6y ago
Adding a nullable column is constant time (ie: basically instant) in postgres and innodb, maybe also in other systems.
- HelloNurse 6y agoIf 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.
- calpaterson 6y agoDropping 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 https://bugs.mysql.com/bug.php?id=1341