Postgres is better in the I-need-concurrency case -- I think it's the greatest RDBMS that's ever been made and would like someone to prove me wrong some day.
SQLite's amazing too though, when you don't need concurrency (and most websites don't really -- especially the ones that should be scaling vertically instead of horizontally).
If you only ever have 1 database server and 1 application server, then SQLite is deterministically faster in the embedded single-server scenario. This is our configuration model for our application. I acknowledge this is not everyone's configuration model (today), but if you entertain the insanity for a moment you might find the performance uplift of a single-machine app to be staggering.
Query execution latency for SQLite on the same machine running on NVMe disk can be measured in microseconds. You will never see this kind of performance across the network, or even loopback. Thus, there is literally no way from an information theory perspective, that Postgres, SQL Server, Oracle, DB2, MongoDB, Dynamo, Aurora, et. al. could ever hope to beat the throughput of (properly-configured) SQLite operating embedded in the application itself.
You're right -- a few hours ago I thought it was worth editing and noting the scale-up-instead-of-out route, but thought better of it, figuring no one would bring it up. Postgres almost certainly can't beat SQLite when it's on one machine and concurrent writers are not an issue, and you've got some seriously fast hardware to make use of.
Need network? Postgres. Don't need network? SQLite.
Yes, but then you have to have a DB embedded in your application, which is a crap design for serious distributed systems with HA requirements and high load.
As in all things, it depends on your specific application. To make a broad statement that SQLite is crap for serious distributed systems is extremely presumptive about all potential applications.
Distributed systems are largely a mistake and amount to a lack of understanding regarding what is actually possible to extract from a single x86 server.
SQLite's amazing too though, when you don't need concurrency (and most websites don't really -- especially the ones that should be scaling vertically instead of horizontally).
Anyway here's some cool SQLite stuff:
- https://github.com/CanonicalLtd/dqlite
- https://github.com/rqlite/rqlite
- https://datasette.readthedocs.io/en/stable/
- https://www.sqlite.org/rtree.html
- https://github.com/sqlcipher/sqlcipher
- https://github.com/benbjohnson/litestream
- https://github.com/aergoio/aergolite
- https://sqlite.org/lang_with.html#rcex3
- https://github.com/sql-js/sql.js
- https://www.gaia-gis.it/fossil/libspatialite/index
- https://github.com/h3rald/litestore
- https://github.com/adamlouis/squirrelbyte
- https://github.com/chunky/sqlite3todot
- https://github.com/nalgeon/sqlite-plus/
- https://www.sqlite.org/json1.html#jsonpath