SQLite: Past, Present, and Future(vldb.org) |
SQLite: Past, Present, and Future(vldb.org) |
SQLite constructs a transient index instead of a hash table in this instance
because it already has a robust and high performance B-Tree implementation at
hand, whereas a hash-table would need to be added. Adding a separate hash table
implementation to handle this one case would increase the size of the library
(which is designed for use on low-memory embedded devices) for minimal
performance gain.
The paper mentions implementing Bloom filters for analytical queries an explains how they're used. I wonder if this is related to the query planner enhancements that landed on SQLite 3.38.0 [2]
Use a Bloom filter to speed up large analytic queries.
As we were writing the paper, we did consider implementing hash joins in SQLite. However, we ultimately went with the Bloom filter methods because they resulted in large performance gains for minimal added complexity (2 virtual instructions, a simple data structure, and a small change to the query planner). Hash joins may indeed provide some additional performance gains, but the question (as noted above) is whether they are worth the added complexity.
The SQL course has almost no love by the students but so far it has been the most useful and interesting to me.
I was able to create some complex views (couldn't understand how to make materialized views in MySQL), but they were still very slow.
I decided to copy most of this forum DB to DuckDB (with Knime now, until I know better), and optimization with DuckDB seems pointless. It's very, very fast. Less energy usage for my brain, and less time waiting. That's a win for me.
My current dataset is about 40GB, so It's not HUGE, and sure people here in HN would laugh at my "complex" views, but so far I've reduced all my concerns from optimizing to how to download the data I need without causing problems to the server.
Usually OLAP at these scales is fast enough with SQLite or you can use DuckDB if you need a portable format before graduating to a full on distributed OLAP system.
DuckDB is significantly faster than SQLite because it has a vectorized execution engine which is more CPU efficient and uses algorithms which are better suited for analytical queries. If you implemented a scan operator for DuckDB to read SQLite files, it would still have better performance.
And yes it is fast!
Now the good news is that these days, conferences have an accompanying video associated with the paper, and that may be a good place to start for many. That video will be published on the conference website (https://vldb.org/2022/) in about a week.
(I tend to read most things on a screen and find two columns of small text tiring)
everyone: not all of your readers are domain experts. omissions like this are infuriating.
It realy depends what do you mean by that, yes it's shipping in every phones and browser, but I don't consider that as a database. Is the windows registry a database?
Oracle, MySQL, PG, MSSQL are the most widly used DB in the world, the web runs on those not SQLite.
"SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. [...] Since SQLite is used extensively in every smartphone, and there are more than 4.0 billion (4.0e9) smartphones in active use, each holding hundreds of SQLite database files, it is seems likely that there are over one trillion (1e12) SQLite databases in active use."
I found that claim to be fairly surprising, SQLite is pretty bad when it comes to transactions per second. SQLite even owns up to it in the FAQ:
>it will only do a few dozen transactions per second.
That is an extremely poor quote taken way out of context.
The full quote is:
FAQ: "[Question] INSERT is really slow - I can only do few dozen INSERTs per second. [Answer] Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second."
> Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.
If you need both transactions and OLAP in the same system, the prevalent way to deliver high performance on this (HTAP) workload is to make two copies of the data. This is what we did in the SQLite3/HE work (paper: https://www.cidrdb.org/cidr2022/papers/p56-prammer.pdf; talk: https://www.youtube.com/watch?v=c9bQyzm6JRU). That was quite clunky. This two copy approach not only wasted storage but makes the code complicated, and it would be very hard to maintain over time (we did not want to fork the SQLite code -- that is not nice).
So, we approached it in a different way and started to look for how we could get higher performance on OLAP queries working as closely with SQLite's native query processing and storage framework.
We went through a large number of options (many of them taken from the mechanisms we developed in an earlier Quickstep project (https://pages.cs.wisc.edu/~jignesh/publ/Quickstep.pdf) and concluded that the Bloom filter method (inspired by a more general technique called Look-ahead Information Passing https://www.vldb.org/pvldb/vol10/p889-zhu.pdf) gave us the biggest bang for the buck.
There is a lot of room for improvement here, and getting high OLAP and transaction performance in a single-copy database system is IMO a holy grail that many in the community are working on.
BTW - the SQLite team, namely Dr. Hipp (that is a cool name), Lawrence and Dan are amazing to work with. As an academic, I very much enjoyed how deeply academic they are in their thinking. No surprise that they have built an amazing data platform (I call it a data platform as it is much more than a database system, as it has many hooks for extensibility).
My advice: avoid MySQL like the plague. PgSQL and SQLite is all you ever need and all you ever want.
This is a big early career mistake. I've seen experienced developers use NoSql in a project where Sql is clearly a great fit, then waste lots of manpower to emulate things you get with Sql for free.
Of course one's career can fall into a success path that never depends on SQL, but not learning SQL deeply is not a safe bet.
I've seen things you wouldn't believe. Random deadlocks in multi-billion transaction reporting systems. Atomic transactions split into multiple commits in banking applications. Copying rows between tables instead of setting a flag on a row. All because highly paid programmers are scared of RDBs.
Yeah it is, until you're trying to manually create and maintain relations between documents in different schemas owned by different microservices.
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?
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.
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.
https://sqlite.org/appfunc.html
We currently use this path to offer a domain-specific SQL-based scripting language for our product.
Caching the query plan is also going to go further in performance optimisations than just “precompiling” the SQL to a AST.
I mean, I get it but the chances that it makes a noticeable difference are zero in almost every case. Also you'd have to change a lot of the existing tooling, at which point you might as well send a compiled agent or use stored procs?
The problem there is that the SQL query string is not parsed at compile time of the host program, so things that could be caught at compile-time are not, and things like appending strings to SQL strings in an unsafe way are much too easy to do.
It helps to think of SQL strings as an untrusted wire format. Yes, parsing is a pain, but it comes with two main benefits: (i) The wire format is human writable/interpretable, with all the accompanying benefits, and (ii) The wire format is easily extensible in a predictable way.
That latter one is particularly useful in keeping SQL's ecosystem open. Take a front-end library like SQLAlchemy or ScalikeJDBC for example. It's not practical for any one such library to support every extension provided by every database engine. SQL provides a fall-back for when you need a back-end feature that hasn't been implemented in any given front-end.
Also both LINQ language syntax and library methods are a builder paradigm for the expression tree. Valid, but still far from ideal representation of an AST.
The same reason language servers took off. Instead of one to one mapping, SQL enables one to many mapping with minor tweaks, allowing everyone to do whatever they want over a well known, well defined, mature abstraction.
In the same spirit, I may ask why we're not writing assembly or even machine code, and we have programming languages? Testers, sure, abstraction means clarity up to an extent, but why the developers themselves still use programming languages?
SQL-as-strings and SQL-as-AST are still the same thing. What is being proposed it not to write procedural code for record retrieval instead of declarative SQL.
Your code would get incredibly large and complicated if you had to specify any serious SQL query as a raw AST.
Many years ago, I was on a project that needed to add rows to a database with a hard 10µs limit. Each rows was just four integers, so the writing part was trivial. However, allocating the string, formatting the integers to strings, then parsing the resulting string often put us over the time limit. Every time the time limit was breached, we lost about five grand. Why we were using an SQL database for this at all is a story for a different time.
This querying framework is what powers translations and compilation into SQL and several other languages (depending on the datastore provider used).
EntityFramework is one of the most advanced ORMs out there and is supremely productive because of LINQ.
And yes, as usual, we have the amazing confusion of Microsoft Naming.
But query syntax is essentially just a way to use an ORM that looks closer to SQL but is strongly typed.
So no, it does not talk AST to the database.
The way I would do it is a MPSC setup wherein the single consumer holds an exclusive connection to the SQLite database and writes out transactions in terms of the batch size. Basically: BEGIN -> iterate & process event batch -> END. This is very very fast due to how the CPU works at hardware level. It's also a good place to insert stuff like [a]synchronous replication logic.
Completion is handled with busy/yield waiting on a status flag attached to the original event instance. You'd typically do a thing where all flags are acknowledged at the batch grain (i.e. after you committed the txn). This has some overhead, but the throughput & latency figures are really hard to argue with. It also has very compelling characteristics at the extremes in terms of system load. The harder you push, the faster it goes.
Really? That's not my experience over the last thirty years of programming. As soon as relational databases became something that everyone could use we all jumped in And not just programmers either, a lot of people who never wrote a line of code in their lives became adept at writing SQL in order to get around limitations in ERP systems for instance.
Is this problem something that afflicts only younger programmers?
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 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.
One is "SQL/CLI", which envisions that you provide a "module" of queries (parameterized and static), which you then compile with some database compiled tool, resulting in an object file you can link into your codebase that exports function calls for these queries.
The other is "Embedded syntax", which is basically "embed RAW SQL statements into your program, run a database provided pre-processor to convert it to normal ADA, C, COBOL, FORTRAN, MUMPS, Pascal, or PL/I code, which is then compiled normally. In theory, this is supposed to generate a "client module" like SQL/CLI, and insert the needed code to call into that module in place of the SQL.
For both of these the queries are considered to be "prepared" at compilation time, but this mostly amounts to syntax checking. It could theoretically also provide warnings about possible execution errors based on the current state of the database (for example, if you are trying to select some column that does not currently exist in some given table), but it could not treat these as errors since DML statements run by this or other programs may change the table before the query is actually executed.
The sql standard calls every form of passing a string version of a query to a database as "dynamic sql" (Not just say SQL constructed in say a store procedure and called with EXECUTE). This is because the database cannot statically analyze those queries at program compilation time, so you may need to link with additional libraries that include the query parser, etc. And supporting "dynamic sql" at all is an optional feature.
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.
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...
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.
Or a specific one for SQLite?
I'd be very surprised if generating the SQL query string and parsing it again was more than a trivial percentage of the query execution time, but happy to be proven wrong.
The real solution is to include an alternative to SQL that looks and works like Datalog including things like variables. That would make SQLite 100x more productive for programmers. But that will never happen.
On the other hand, if you were importing data and each line specified which table and column it had to go into you'd probably have to write a new SQL statement each time.
What did you do for a solution?
Because of its age, it does carry deprecated API components that are maintained solely for backward compatibility.
Notice how many _v2 and _v3 variants are present, denoting reworked aspects of the API:
https://sqlite.org/c3ref/funclist.html
A product of this age was designed (and redesigned) for specific needs. Unfortunately, your use case is not among them.
Of course, half those neutrons arrived within a 20ms window, so we had a buffer to handle the load. However, however, if the average remained above the limit, the buffer would fill up. There was a brief discussion of just ignoring events when the buffer was full, but that could introduce systematic errors in the data that would be impossible to detect, so it was better for the database to just crash.
The solution was to tighten the slits on the neutron beam to lower the count rate to the point that we never filled the buffer. Granted, we were testing a high flux technique that, so that was a bit of a disappointment. Everything else in the instrument could handle an order of magnitude more neutrons, except this database.
By the way, to be fair to the database designers, they were working with Clinton era tech and were probably told that the flux rate would never be a SUSTAINED 10,000 events per second.
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.
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.
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.