I used to work with LevelDB (with typescript). It's very easy and fast to develop with.<p>Recently I'm playing with sqlite, aim to save some disk space by normalizing the data.<p>I spend quite some time to define the tables and write insert queries in normalized format. I even try to "compact the data" by using a table to store all unique string then reference to it's primary key.<p>I suppose storing the data in the rigid and compact format should result in smaller file size but it seems I am wrong.<p>The data I'm storing are immutable request log (timestamp, type, and input) for command-sourcing style of server.
The current state of server are not persisted.
Each time the server is restarted, it just replay all the request logs to restore the state.<p>The architecture is inspired from cqrs and event sourcing, except the write model is simply the storage, the read model is in a single process's memory, and it's sourcing from the commands, not events. (Though some kind of attempt-commands spawn verified-commands and are skipped when replay to avoid applying side-effect twice)<p>File size for reference: JSON Files 409M, SQLite 114M, LevelDB 63M.<p>The SQLite file is smaller than LevelDB after zip, but access sqlite over zip file doesn't sound a good solution.
I believe LevelDB automatically passes blocks through snappy compression, hence the smaller size. SQLite has an extension[1] that will do something similar. I’ve never used it so I can’t comment on how it might work for your use case.<p>[1] <a href="https://www.sqlite.org/zipvfs/doc/trunk/www/readme.wiki" rel="nofollow">https://www.sqlite.org/zipvfs/doc/trunk/www/readme.wiki</a>
Why did you switch from LevelDB to SQLite, apparently without new features? Less than double the file size could be an acceptable price to pay for a performance improvement, or even to try out different technology.<p>Did you analyze where SQLite wastes space compared to LevelDB? If standard compression removes all redundancy, I'd guess there's simply some slack in pages and individual fields, or unnecessary indices.
Does size matter? Otherwise why bother? For example, you could probably save a bit of space by storing the timestamps as unix time stamps (if you are using strings), but it may not be a useful use of your time. Also, for larger databases (gigabytes) SQLite may catch up to LevelDB.
Did you try to run VACUUM?<p><a href="https://www.sqlitetutorial.net/sqlite-vacuum/" rel="nofollow">https://www.sqlitetutorial.net/sqlite-vacuum/</a>