Ask HN: SQLite, MySQL, or PostgreSQL? | Hacker News Reader

Ask HN: SQLite, MySQL, or PostgreSQL?

4 pointsddxxdd10 comments

My application is storing ~12GB of federal election data for personal research purposes. I simply want to be able to search for queries without having to wait several hours for the result (in contrast with my previous solution, which was searching the data line-by-line with python).

View on HN

R Programming language[1] has Rdata-Files included. With data.table[2] you have fast memory database for your local machine.

[1] https://bookdown.org/ndphillips/YaRrr/rdata-files.html

[2] https://cran.r-project.org/web/packages/data.table/vignettes...

Provided you can index the data properly, all three should work to speed up your queries.

Sqlite has the advantage of not needing to install a server, which makes it easier to bring it up as your datastore.

SQLite is perhaps a little easier because you only have one file to think about, backups, etc.

I use both but give preference to Postgres/PostGIS if I have to do mapping things.

MySQL is never a consideration for me.

Is there a particular reason why you never consider MySQL?

I am honestly thinking of reworking my project (for the 10th time, lol) to convert a bunch of interconnected list-of-objects in Python into SQL databases. I'm talking about data on tens of thousands of political candidates, data on hundreds of thousands of political committees/SuperPACS, data on hundreds of federal elections, etc.

I also just remembered that I have a MySQL server already installed on my computer.

Given that I am working with several gigabytes of highly interconnected data, would it be worth installing PostGres on my machine?

I don't consider MySQL since I've invested so much time to learn PostgreSQL and its ecosystem of extensions. MySQL is a proved database, but my money is simply on a different horse.

mysql is fine. but there again I prefer coke over pepsi and ford over gm

sqlite and datasette is a killer combo imo https://datasette.readthedocs.io/en/stable/

12GB is not a lot of data, not sure what was happening with your python that it took several hours. Don't get me wrong though, SQL seems like it could be the right tool for this use case.

If it's for local use, I would go with sqlite.

For production or for local use?

Local use.

Reply on news.ycombinator.com