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 ge

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.