5 ms·

> To delete a column, SQLite have to completely overwrite the table - so the operation is not fast. But it’s still nice. Can someone with more knowledge/experi

> To delete a column, SQLite have to completely overwrite the table - so the operation is not fast. But it’s still nice. Can someone with more knowledge/experience ELI5, please? Is this essentially how it's done in other db engines? TIA

masklinn 6y ago
postgresql does essentially nothing on a drop column, in part because it doesn't use fixed-size tuples: > The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. but if you VACUUM FULL (or CLUSTER) it will immediately rewrite the entire table. Also note that storing a null means forcing a null bitmap for every row (even if it's not otherwise used).
awestroke 6y ago
In postgresql: The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.
- airstrike 6y ago
  > The space will be reclaimed over time as existing rows are updated. Or as you VACUUM, correct? I think it lets you specific a column name too
  - masklinn 6y ago
    VACUUM just marks tuples as free spaces so they can be reused. This is part of > The space will be reclaimed over time as existing rows are updated. because of MVCC, updating a row really inserts a new row and the old one eventually becomes free space (once a vacuum comes around to marking it). VACCUM FULL, however, will rewrite the entire table.
    - airstrike 6y ago
      Good to know, thank you very much
derefr 6y ago
In many engines, row-tuples are materialized from rows by having the query planner turn the table’s metadata into a mapping function. With this approach, you get a bunch of things “for free”—the ability to reorder columns, rename columns, add new nullable all-NULL columns or default-constant all-default-valued columns, all without doing any writing. Rows instead get rewritten when the DB builds a new version of them for some other reason (e.g. during UPDATE) or during some DB-specific maintenance (e.g. during VACUUM, for Postgres.) I don’t believe SQLite works this way. It gives you literally what’s in the encoded row, decoded. I believe this allows it to be either zero-copy or one-copy (not sure which), but it has the trade off of disallowing these fancy kinds of read-time mapping. IMHO it’s a trade off that makes sense, on both sides. Client-server DBMS inherently need to eventually serialize the data and send it over the wire, so fewer copies doesn’t get you much, while remapping columns at read time might get you a lot. SQLite can hand pointers directly to the app it’s embedded in, so “direct” row reads are a great advantage, while—due to the small size of most SQLite DBs—the need for eager table rewrites on ALTER TABLE isn’t even very expensive.

masklinn 6y ago

postgresql does essentially nothing on a drop column, in part because it doesn't use fixed-size tuples: > The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. but if you VACUUM FULL (or CLUSTER) it will immediately rewrite the entire table. Also note that storing a null means forcing a null bitmap for every row (even if it's not otherwise used).

awestroke 6y ago

In postgresql: The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.

airstrike 6y ago
> The space will be reclaimed over time as existing rows are updated. Or as you VACUUM, correct? I think it lets you specific a column name too
- masklinn 6y ago
  VACUUM just marks tuples as free spaces so they can be reused. This is part of > The space will be reclaimed over time as existing rows are updated. because of MVCC, updating a row really inserts a new row and the old one eventually becomes free space (once a vacuum comes around to marking it). VACCUM FULL, however, will rewrite the entire table.
  - airstrike 6y ago
    Good to know, thank you very much

derefr 6y ago

In many engines, row-tuples are materialized from rows by having the query planner turn the table’s metadata into a mapping function. With this approach, you get a bunch of things “for free”—the ability to reorder columns, rename columns, add new nullable all-NULL columns or default-constant all-default-valued columns, all without doing any writing. Rows instead get rewritten when the DB builds a new version of them for some other reason (e.g. during UPDATE) or during some DB-specific maintenance (e.g. during VACUUM, for Postgres.) I don’t believe SQLite works this way. It gives you literally what’s in the encoded row, decoded. I believe this allows it to be either zero-copy or one-copy (not sure which), but it has the trade off of disallowing these fancy kinds of read-time mapping. IMHO it’s a trade off that makes sense, on both sides. Client-server DBMS inherently need to eventually serialize the data and send it over the wire, so fewer copies doesn’t get you much, while remapping columns at read time might get you a lot. SQLite can hand pointers directly to the app it’s embedded in, so “direct” row reads are a great advantage, while—due to the small size of most SQLite DBs—the need for eager table rewrites on ALTER TABLE isn’t even very expensive.