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?
Keyword here is transactions, not processes. You can model any workload to be transaction-efficient, but it might not be easy.
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?
No durability guarantee is a showstopper for any serious use case
TFA 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?
It is certainly possible to have a single system that can effectively process high volumes of OLTP traffic while at the same time performing OLAP operations. While there are systems that are designed to do one or the other type of operation well, very few are able to do both. https://www.youtube.com/watch?v=F6-O9v4mrCc
TFA 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?
SQLite is significantly better at OLTP and being a blob strorage than DuckDB, and it doesn't want to sacrifice those advantages and compatibility if OLAP performance can be improved independently. In my experience for diverse workloads it is more practical to start with a row-based structure and incrementally transform it into a column-based one. Indeed in the paper there is a suggested approach that trades space for improved OLAP performance.
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...
Is it the SQL dialect there lacking or is it the built-in functions?
I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.
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.
I'm not talking about extra code, I'm talking about _layers_ of code. With PostgreSQL you're still sending data over TCP/IP or a UNIX socket, and are copying things around in memory. Compare that to SQLite that runs in the memory space of the program, thus no need for copying and socket traffic. There's just less middlemen (middlepersons?) with SQLite that are unavoidable with PostgreSQL. So less layers = less interpreting/serialization/deserialization/copying/... = higher performance. I will even argue that even if the SQLite query engine is slightly less efficient than PostgreSQL, you're still winning because of less memory copying going around.
Functionality-wise, SQLite's dialect is really lacking...
Is it the SQL dialect there lacking or is it the built-in functions? I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.
Depends on what easily means.
Sqlite can't do custom format date parsing and regex extract. How do we extend something like this?
If we go beyond a simple function to window function, I imagine it would be even harder.