I came across this from your blog post where you basically stated that you optimised for space.
This is a valid choice, but one that should be clearly stated on the README, as the tradeoff is that all those CASE statements will be a drag for every UPDATE, especially the more columns a table gets.
An alternative way to optimise this would be to INSERT the whole changed line into the table in all cases, and then run a maintenance script that would replace the duplicate values with NULL at a suitable window, before any vacuuming. This would make the table bloat and shrink, but would make writing to the table quicker.
Also, you’re adding a SELECT operation to every UPDATE and DELETE. I’m unaware of the specific internals of SQLite, but I’m pretty certain that this addition to the operation is not going to be O(1) and will significantly slow down updates and deletes in large tables.
That being said, this SELECT is all to do with the _version column. You may wish to consider:
Do you need the _version column at all? Is the timestamp enough?
Does the _version need to be sequential?
Does the _version need to be sequential in the table?
Can you enumerate it on the fly in your retrieval functions?
Can you replace a numerical _version with something that is calculated in deterministic time, such as a UUIDv7? This is still guaranteed to increase in value over time.
Can you use the row index number instead?
If you finally do decide to keep the _version column, at least consider creating an index over Id and _version to make the search quicker.
I came across this from your blog post where you basically stated that you optimised for space.
This is a valid choice, but one that should be clearly stated on the README, as the tradeoff is that all those
CASEstatements will be a drag for everyUPDATE, especially the more columns a table gets.An alternative way to optimise this would be to
INSERTthe whole changed line into the table in all cases, and then run a maintenance script that would replace the duplicate values withNULLat a suitable window, before any vacuuming. This would make the table bloat and shrink, but would make writing to the table quicker.Also, you’re adding a
SELECToperation to everyUPDATEandDELETE. I’m unaware of the specific internals of SQLite, but I’m pretty certain that this addition to the operation is not going to be O(1) and will significantly slow down updates and deletes in large tables.That being said, this
SELECTis all to do with the_versioncolumn. You may wish to consider:_versioncolumn at all? Is the timestamp enough?_versionneed to be sequential?_versionneed to be sequential in the table?_versionwith something that is calculated in deterministic time, such as a UUIDv7? This is still guaranteed to increase in value over time.If you finally do decide to keep the
_versioncolumn, at least consider creating an index overIdand_versionto make the search quicker.