SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
I am 99% sure SQLite is going to win unless you actually care about data durability at power loss time. Even if you do, I feel I could defeat Postgres on equal terms if you permit me access to certain ring-buffer-style, micro-batching, inter-thread communication primitives.
Sqlite is not great at dealing with a gigantic wall of concurrent requests out of the box, but using a little bit of innovation in front of SQLite can solve this problem quite well. The key is resolve the write contention outside of the lock that is baked into the SQLite connection. Writing batches to SQLite on a single connection with WAL turned on and Sync set to normal is pretty much like operating at line speed with your IO subsystem.
SQLite will handle a power loss just fine.
From https://www.sqlite.org/howtocorrupt.html:
"An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or even a power failure occurs in the middle of a transaction, the partially written transaction should be automatically rolled back the next time the database file is accessed. The recovery process is fully automatic and does not require any action on the part of the user or the application."
From https://www.sqlite.org/testing.html:
"Crash testing seeks to demonstrate that an SQLite database will not go corrupt if the application or operating system crashes or if there is a power failure in the middle of a database update. A separate white-paper titled Atomic Commit in SQLite describes the defensive measure SQLite takes to prevent database corruption following a crash. Crash tests strive to verify that those defensive measures are working correctly.
It is impractical to do crash testing using real power failures, of course, and so crash testing is done in simulation. An alternative Virtual File System is inserted that allows the test harness to simulate the state of the database file following a crash."
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.
So write contention from multiple connections is what you're talking about, versus a single process using sqlite?
sqlite is as good at durability as any non-replicated database, though you can configure it to be non-durable (most other databases too tbf).
https://www.sqlite.org/pragma.html#pragma_synchronous
By default WAL mode can rollback committed transactions in cases of power failure, but you can do `PRAGMA synchronous = FULL` to trade speed for durability.
It might work, but I reckon 90% of web applications live beneath this relatively small threshold and 80% probably don't even reach 50 q/s.
This is a good analogy, but there are still lot of wire heavy scenario a real database like postgresql or mysql will have better throughput than redis.
I say "maybe" because even there, SQLite is much more limited in terms of query-planning (very simple statistics) and the use of multiple indexes.
That's assuming we're talking about reads, PostgreSQL will win for write-heavy workloads.
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.
Unfortunately for many database workloads you are overestimating the relative cost of this factor.
> even if the SQLite query engine is slightly less efficient than PostgreSQL
And this is absurd - the postgresql query engine isn't just "slightly" more efficient. It is tremendously more sophisticated. People using a SQL datastore as a glorified key-value store are not going to notice - which seems to be a large percentage of the sqlite install base. It's not really a fair comparison.
> but SQLite is a strange beast that needs a special diet to perform best.
I don’t see what is strange about it - for large datasets it’s the same complexity issues as anywhere else.
Not sure specifically what your comment is trying to add, since I acknowledged the type of use case SQLite excels in - those where roundtripping are a dominating cost and “k-v” stores, ie simple queries. My entire point is that those are a common but still niche use case.
I know citus can provide columnar tables but I can't find columnar indexes for regular row-based tables in their docs. (use case of keeping an OLTP table but wanting to speed up a tiny subset of queries)
Closest thing I could find was Swarm64 for columnar indexes but it doesn't seem to be available anymore.
For read-mostly to read-only OLTP workloads, read latency is the most important factor, so I predict SQLite would have an edge over PostgreSQL due to SQLite's lower complexity and lack of interprocess communication.
For write-heavy OLTP workloads, coordinating concurrent writes becomes important, so I predict PostgreSQL would provide higher throughput than SQLite because PostgreSQL allows more concurrency.
For OLAP workloads, it's less clear. As a client-server database system, PostgreSQL can afford to be more aggressive with memory usage and parallelism. In contrast, SQLite uses memory sparingly and provides minimal intra-query parallelism. If you pressed me to make a prediction, I'd probably say SQLite would generally win for smaller databases. PostgreSQL might be faster for some workloads on larger databases. However, these are just guesses and the only way to be sure is to actually run some benchmarks.
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 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.
At this point, we nlmight as well use postgres.
If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2]
[0]: https://ricardoanderegg.com/posts/extending-sqlite-with-rust...
This seems like I can add a lot more functions to it, not just regex extract.
Came here to complain and learned something useful.
SQLite is an in-process database. You can give it a callback func to execute. So your regex-extract can literally just call a function in your code: https://sqlite.org/appfunc.html
edit: Python's stdlib documentation concisely shows how easy this can be: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... Basically every SQLite library should have something similar. This extreme ease of extending is a big part of why SQLite has so little built-in.
Also, I genuinely dislike how loose SQLite is with allowed syntax. Probably it's preference. But even interactively I prefer to know immediately that I messed up a query. SQLite is so forgiving I've often wasted time trying to understand why my results are nonsense (because I typoed in the query and SQLite didn't fail the query).
But I also strongly dislike Python for that reason and I know where you stand there. Maybe SQLite/PostgreSQL is similar to the dynamic/static language preference divide.
- https://til.simonwillison.net/sqlite/sort-by-number-of-json-...
- https://til.simonwillison.net/sqlite/function-list#user-cont...
https://sqlite.org/appfunc.html
We currently use this path to offer a domain-specific SQL-based scripting language for our product.