Hacker News Reader

Comment by sgu999 | Hacker News Reader

Other have asked, but I'd also be very interested in knowing more if you can share more details. I had to spent a non trivial amount of time convincing others to use SQLite instead of dumb files in similar situations, stories like yours are pure gold.

View on HN

Haven't seen these links mentioned elsewhere in this thread, but in case you haven't seen them yet:

* SQLite is 35% Faster Than The Filesystem [1]

* Writing to files safely is hard [2], and SQLite goes to great details to ensure consistency in case of application, OS, or computer crash [3]

[1]: https://sqlite.org/fasterthanfs.html

[2]: https://danluu.com/deconstruct-files/

[3]: https://sqlite.org/atomiccommit.html

There are a lot of SQLite praises in this thread, on the internet and on SQLite's website so here are a couple of reasons that drove me to choose it. My experience with traditional formats like CSV or raw binary is that they have a somewhat lower overhead to "get working" but are quite difficult to scale and adapt to changing needs, a phase most projects are bound to go through.

SQLite's reputation and adoption makes me comfortable to assume its operational correctness so I can focus on data itself, not reader/writer logics for it. Even though a SQLite database's schema may change, I'm glad knowing that the database file can still be easily opened and read many years into the future, something that custom formats may not guarantee.

On CSV: Syntax and library differences aside, SQLite almost works like a drop-in replacement for CSV since a single table database can be essentially treated as a glorified spreadsheet/CSV if wished. My post processing data pipeline is built with pandas so it is virtually the same for me to read a SQLite database vs. a CSV file.

I've had a huge amount of success using sqlite to collect and analyze logs. The obvious approach would have been to dump enormous csv or json files, but doing in sqllite gave me (a) much better reliability, and (b) the ability to come up with post-hoc analysis approaches without changing the main process.

I was really pleased to add it to my toolkit.

Reply on news.ycombinator.com