[xsc]

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]

This 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]

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

This sounds like a great topic for your research, your article, and your passion to share with others if it exists. Get to work.

[yellowapple]

I 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?