Ask HN: Is SQLite suitable for command sourcing application? | Hacker News Reader

Ask HN: Is SQLite suitable for command sourcing application?

7 pointsaabbcc12417 comments

I used to work with LevelDB (with typescript). It's very easy and fast to develop with.

Recently I'm playing with sqlite, aim to save some disk space by normalizing the data.

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.

I suppose storing the data in the rigid and compact format should result in smaller file size but it seems I am wrong.

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.

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)

File size for reference: JSON Files 409M, SQLite 114M, LevelDB 63M.

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.

[1] https://www.sqlite.org/zipvfs/doc/trunk/www/readme.wiki

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.

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.

My initial version is using a json object file for each request, with manually triggered "compact" which bundle multiple small json files into 8MB json array files to save disk space.

Recently read about sqlite being a replacement of fs for application data, hence experimenting with it. I used to with work with leveldb in other type of applications (blockchain, scraping and archive)

Most the the data in sqlite are not indices, except the "str" table has unique index on the string value.

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.

The size matter in my case. Above sample are only a portion of the actual data from multiple applications (sharing the same vm on cloud). They are not for-profit projects so I'd try to reduce the hosting cost.

Maybe s3-style of storage is better than vm-harddisk in this case?

P.S. I'm storing the timestamps in millisecond

Did you try to run VACUUM?

https://www.sqlitetutorial.net/sqlite-vacuum/

I tried to run VACUUM, the sample sqlite db file size dropped slightly from 116M to 110.8M.

Reply on news.ycombinator.com