SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
Functionality-wise, SQLite's dialect is really lacking...
SQLite: Past, Present, and Future
11–20 of 147 posts
Re: SQLite: Past, Present, and Future
#12SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
The documentation offers some advice on this:
Re: SQLite: Past, Present, and Future
#13i wish it had an optional server for more concurrent and networked transactions in the cloud
Re: SQLite: Past, Present, and Future
#14SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
Postgres obviously. Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores. If you don't have significant write contention, SQLite every time.
Where is write contention coming from if it's operated locally?
Re: SQLite: Past, Present, and Future
#15TFA appears to be about adapting SQLite for OLAP workloads. I do not understand the rationale. Why try to adapt a row-based storage system for OLAP? Why not just use a column store?
Re: SQLite: Past, Present, and Future
#16Earlier quoted context omitted.
Postgres obviously. Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores. If you don't have significant write contention, SQLite every time.
Where is write contention coming from if it's operated locally?
SQLite is "single" threaded for writes.
Re: SQLite: Past, Present, and Future
#17Earlier quoted context omitted.
Postgres obviously. Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores. If you don't have significant write contention, SQLite every time.
Where is write contention coming from if it's operated locally?
... you can get tons of requests on a server?
Re: SQLite: Past, Present, and Future
#18SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
Postgres obviously. Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores. If you don't have significant write contention, SQLite every time.
Here's sqlite doing 100 million inserts in 33 seconds which should fit into nearly every workload, though it is batched. https://avi.im/blag/2021/fast-sqlite-inserts/
So write contention from multiple connections is what you're talking about, versus a single process using sqlite?
Re: SQLite: Past, Present, and Future
#19SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
SQLite is always going to win in that category just from the fact that there are less layers of code to be worked through to execute a query.
> just from the fact that there are less layers of code to be worked through
This is not an invariant. I've seen be true, and I've seen it be false. Sometimes that extra code is just cruft yes. Other times though it is worth it to set up your data (or whatever) to take advantage of mechanical sympathies in hot paths, or filter the data before the expensive processing step, etc.
Re: SQLite: Past, Present, and Future
#20SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
SQLite is always going to win in that category just from the fact that there are less layers of code to be worked through to execute a query.
As long as you turn it into a throughput race instead of a latency race, PostgreSQL can definitely win. SQLite has a primitive query builder and a limited selection of query execution steps to choose from. For instance, all joins in SQLite are inner loop joins. It can't do hash or merge joins. It can't do GIN or columnstore indexes. If a query needs those things, PostgreSQL can provide them and can beat SQLite.