Nelson's log

Don’t store JSON blobs in text columns in sqlite3

I decided to try something naïve and put big JSON blobs in a sqlite3 database. About 1700 of them, 150k each, data from League of Legends matches. I knew this was dumb but I did it anyway. And it was dumb, like 300MB of dumb. To be fair sqlite3 handles this just fine, but the single big file makes backups and caching awkward. And I wasn’t even using the column!

I rewrote the code to store the JSON blobs as gzip files instead. 36MB instead of 300MB, and each file doesn’t ever change once written. Just nicer all around. Kind of a pain to remove the data though. sqlite3 doesn’t let you drop columns so you have to make a temporary table to remove it. And even then the file didn’t get smaller on disk; I ended up doing a dump / restore to reclaim the space.

I still want sqlite-for-JSON, a simple JSON datastore. I see the JSON1 module made it into sqlite3 3.9, no longer draft. I should try it out. It’s not compiled in by default. Also I assume it’s not compressed. sqlite3 does have compressed database support, but only as a paid extension.