3 ms·
It seems a solid first step would be to use EXPLAIN to understand query planning/scanning issues. Assuming 6.5M rows/day*1.5yr=~3.5B rows, how large is the dat
by xsc 4y ago
It seems a solid first step would be to use EXPLAIN to understand query planning/scanning issues.
Assuming 6.5M rows/day*1.5yr=~3.5B rows, how large is the dataset in both SQLite and pg? With indexes, is it fitting in RAM?
Linode 2GB plan is shared vcpu, so results will vary depending on who is sharing it with your service.
- miguelgrinberg 4y agoThis isn't the topic of the article so I haven't included it, but I have taken several "solid steps" to optimize this application throughout its year and a half of being in production. This article is about one of them only, how I designed a test system to evaluate the two databases head to head.
- yellowapple 4y ago> This isn't the topic of the article Query planning seems like it should be the topic of the article. SQLite and PostgreSQL do their query planning very differently - not to mention that SQLAlchemy is probably generating different queries. I can't really fathom how one can meaningfully "evaluate the two databases head to head" while seeming to actively avoid how they plan/optimize/execute queries.
- dataspun 4y agoThis sounds like a great topic for your research, your article, and your passion to share with others if it exists. Get to work.
- yellowapple 4y agoI don't have access to the code used in the article. Therefore, no amount of research on my part is capable of answering why PostgreSQL was faster than SQLite in the author's situation - certainly not without the queries and the plans / EXPLAINs thereof, those being the specific thing I and multiple others have suggested as something the article could've provided and which would've made it an actually interesting head-to-head. Meanwhile, if all you have to offer in response to such a suggestion is "hurr durr do it yourself", then why bother commenting at all?