Great write-up. It's the classic innovations dilema (squared) all over again - the simple things e.g. SQLite, CSV, etc. work far better than modern "state-of-the-art" offerings. Also doing my fair share e.g. trying to improve CSV - CSV v1.1 anyone? -> https://csv11.github.io and collecting text to SQLite dbs for football -> https://github.com/openfootball (aka football.db); beer -> https://github.com/openbeer (aka beer.db) and the world -> https://github.com/openmundi (aka world.db) All datasets / SQLite dbs in the public domain (no license, no copyright, no rights reserved), for example, and ready for use with Datasette. Thanks for the great (open) data publishing tool.
I've implemented this myself (poorly) for mutable data as I slowly began to lose my mind. For example a feed of users' posts (ordered uniquely for each user based on their personal preference) that can be individually created and deleted, and you don't have enough memory to cache all of the query results to be returned to each user immutably. As far as I can tell, this is an open problem.
One thing I'm interested in is a data format that's like CSV, so its streamable, has a binary compressed version, ascii version for viewing on the cli, and also fixed width per row so it can be memory mapped and have random access performed.
