Sqlite can quite easily do 5000+ insert+commits per second on typical NVMe drives.
Speed is rarely the constraint that makes it unsuitable for an application.
Sqlite can quite easily do 5000+ insert+commits per second on typical NVMe drives.
Speed is rarely the constraint that makes it unsuitable for an application.
Round trip time is actually much faster than Postgres, since there’s no need to touch the network. You can get massive single threaded throughput. In order to achieve comparable throughput in Postgres you need a large amount of concurrent connections, since each conn spends most of its time passing messages, deserializing etc (with a much larger total amount of overhead). There are a surprising amount of bottlenecks and misconfiguration that can tank performance of networked systems, particularly DBs.
Like you suggest, the reason for not picking SQLite is not reliability, speed, etc. Networked DBs allow decoupling between app and db servers, which have operationally different characteristics. But most importantly, you can have multiple apps access the same DB at the same time. Eg analytics, one off queries, any 3p app that interacts with your data directly.