Speedtesting PostgreSQL vs SQLite
(1) By Simon Slavin (slavin) on 2022-06-28 20:17:14 [link] [source]
An interesting article involving comparing speeds of PostgreSQL and SQLite.
https://www.twilio.com/blog/sqlite-postgresql-complicated
Takeaway: One is not always faster than the other. Your benchmarking tells you things about your task running on your setup. Different task or setup, different speeds, different result, perhaps different decision.
(2) By anonymous on 2022-06-28 21:21:10 in reply to 1 [source]
While interesting, the author clearly glosses over configuration and fine tuning, specially when it comes to SQLite.
Pg comes with much large default sizes for page cache and the author even increases the worker memory while leaving SQLite at its default, a lot more conservative, settings.
I have extensively tested (and continue to test) Pg and SQLite on large data sets. And my experience is usually that for read queries, SQLite will be much faster for smaller point queries, but Pg will keep getting closer to it performance wise when the queries get more and more complex that they spend most of their time doing I/O, at that point they perform very similarly. That's given they are configured with similar cache sizes and such.
My take away is that if you have a mix of many short and some long queries, SQlite will shine above most alternatives.
(3) By EricG (EriccG) on 2022-06-29 05:41:13 in reply to 1 [link] [source]
The test results (notably the flat line for SQLite he remarks) indicate to me there is a constant overhead somewhere that is not mitigated for his SQLite tests, and from past experiences I would venture a guess that the connection to SQLite database is recreated each time (or often enough) rather than pooled or reused, while for PostgreSQL it is very likely pooled.
SQLite is usually fast enough many driver authors don't bother with automated pooling of connections, while for classic DBMS, if you don't pool, your driver is very inefficient (or you can even bork the server, such as for Oracle which just doesn't work too well with many connections/deconnections cycles). This lack of pooling for SQLite is also furthered by the need to support non-WAL SQLite DB, where pooling would be problematic at best.
However when pooling SQLite connections, along with a bigger page cache and automated prepared queries, it can allow query times in the sub-microsecond range for simpler queries, something which is just not achievable with other DBMS.