Really cool, the page is noticeably snappier. I'm wondering why they don't put the database on a network-attached block volume. DigitalOcean offers Volumes Block Storage, which is replicated across multiple hosts. Keeping SQLite there would allow the volume to be attached to a replacement Droplet rather than restoring from the latest nightly backup.
There's a really good list of lessons in the second half of this page which I think are really interesting (in general, but also in specific for this project). I don't have a Lobste.rs account but I have some comments about some of them so I'll add them here
* If you want to fail on full table scans in tests you could take advantage of EXPLAIN - though, because it relies on the built in analysis/stats this may exactly mimic production tables. I believe Ruby is dynamic enough that you could probably attach an EXPLAIN to all queries in the unit tests [0] and parse the output.
* For the three UDFs - Sqlite has a regexp module that you can build and load rather than using a udf in rails (probably a different version of regexp though, so user beware) [1] there is also an extensions_functions.c in the external contributions that provides 'stddev' [2] (or you could fake it with the percentile module). I'm confused why there's a udf for 'if' but it'd be trivial to build as C module, but I agree it's best to patch over this sort of thing separately from the migration, so perhaps limited benefit.
* 'I'm constantly surprised by the default choices of SQLite.' I think this is fair, but they're quite dedicated in not breaking existing installations, they even have pages dedicated to explaining some peculiar architecture [3]
> I'm confused why there's a udf for 'if' but it'd be trivial to build as C module, but I agree it's best to patch over this sort of thing separately from the migration, so perhaps limited benefit.
The only reason why I built it as a UDF was because I originally didn't find an if function. Though I'm not sure how I accomplished that thinking back :)
6 comments
[ 0.18 ms ] story [ 15.0 ms ] thread* If you want to fail on full table scans in tests you could take advantage of EXPLAIN - though, because it relies on the built in analysis/stats this may exactly mimic production tables. I believe Ruby is dynamic enough that you could probably attach an EXPLAIN to all queries in the unit tests [0] and parse the output.
* For the three UDFs - Sqlite has a regexp module that you can build and load rather than using a udf in rails (probably a different version of regexp though, so user beware) [1] there is also an extensions_functions.c in the external contributions that provides 'stddev' [2] (or you could fake it with the percentile module). I'm confused why there's a udf for 'if' but it'd be trivial to build as C module, but I agree it's best to patch over this sort of thing separately from the migration, so perhaps limited benefit.
* 'I'm constantly surprised by the default choices of SQLite.' I think this is fair, but they're quite dedicated in not breaking existing installations, they even have pages dedicated to explaining some peculiar architecture [3]
[0] https://www.sqlite.org/eqp.html
[1] https://sqlite.org/src/file/ext/misc/regexp.c
[2] https://sqlite.org/src/ext/contrib/ (ctrl-f for 'extensions-functions.c'. It has more than just stddev but you could strip the rest)
[3] https://www.sqlite.org/rowidtable.html I find this a really touching note 'The designer of SQLite offers his sincere apology for the current mess'
There's actually a built in if to sqlite: https://sqlite.org/lang_corefunc.html#iif
The only reason why I built it as a UDF was because I originally didn't find an if function. Though I'm not sure how I accomplished that thinking back :)