Ask HN: SQLite, MySQL, or PostgreSQL?

4 points by ddxxdd 6y ago ↗ HN

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).

10 comments

[ 4.3 ms ] story [ 19.9 ms ] thread

pwg 6y ago ↗

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.

gshdg 6y ago ↗

For production or for local use?

ddxxdd 6y ago ↗

Local use.

e2le 6y ago ↗

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

simplecto 6y ago ↗

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.

ddxxdd 6y ago ↗

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?

simplecto 6y ago ↗

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.

boznz 6y ago ↗

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

boshomi 6y ago ↗

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...

bockris 6y ago ↗

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.