SQLite – the Banana Slug of Embedded Databases

Earlier this month, I kicked off a series of blogs about SQLite. The first looked at the advantages that SQLite offers over both flat files and the heavier lift of an enterprise-grade SQL databases – and it does offer distinct advantages over both. To a point. And that point was five years ago.

Here’s the thing: If you’re a mobile or IoT developer, or if you’re extending out from the cloud to the edge with distributed applications and data, local embedded data management is a critical capability, and that’s where SQLite stood out for years. But while local and embedded data management is necessary – indeed critical – for modern edge data management systems, as implemented in SQLite, it is insufficient. Modern edge data management demands an ability to process and analyze data locally, to share it peer-to-peer, to move data between gateways, other intelligent machines, and even back into the cloud – and SQLite was never built to meet those demands.

Putting aside the challenges posed by the shared and distributed data requirements – we’ll touch on those in the next installment – let’s just examine SQLite’s limitations in the area of local data processing, starting with one of the most important, performance.

SQLite is just plain slow.

Eighteen months ago, we ran performance tests of Actian Zen — our Zero-DBA Embedded Nano-footprint database—against the latest SQLite distribution and found Zen to be faster by two orders of magnitude, depending on the operation being run. Okay, for indexed deletes, it was three orders of magnitude faster. We made an apples-to-apples comparison, running both on a Raspberry Pi 3, a small ARM-based single-board computer that you can buy from Amazon for under $50. Zen Core and SQLite are both free, and you can run this test for yourself.

Okay, so SQLite’s pokiness is not really news. Everyone in the SQLite community knows that SQLite is painfully slow. So why has it remained so popular? Practically speaking, it has largely been the only game in town. There are three reasons for this. First, it’s an open-source offering that’s been around for over two decades, so it is widely known. Second, it’s bundled with a lot of open-source developer kits, most notably Android. Finally, many database vendors at one time or another have packaged and rebranded SQLite as their “mobile” edition (read: MongoDB and Couchbase). There are even a few startups that literally slap their label on SQLite and sell services around it as their only market offering.

Here’s where we get back to those limitations: The sluggish performance that was good enough five years ago is simply not going to be good enough for the next five years (let alone any period after that). And the performance problems in SQLite are only going to get worse: The data management tasks at the Edge are going to grow more challenging over time, even for embedded applications.

Consider the increased need for local data persistence. It’s not just for simple caching anymore. Now data persistence is needed for computationally intensive local data processing and unsupervised machine learning. In these scenarios, we see a firehose of inbound streaming data that SQLite is just not robust enough to handle. Moreover, we see an increase in compute demands involving query, extraction, and analysis of existing patterns from the local database and/or those of external peers or upstream gateways.

And it’s not just an issue of the volume of streaming data or the sophistication of the analytics taking place. There’s also the issue of multiple applications using the same set of data simultaneously – or even a single upstream consumer subscribing and copying data from multiple downstream publishers. Both cases require a level of concurrency that is architecturally out of scope for SQLite. At best, it can be said that SQLite attempts to simulate concurrency with a lock on the entire data table to all users other than the one currently reading or writing to it – even if that read or write involves only a single row – instead of the granular lock a real ACID-compliant SQL database should provide.  The end result is that SQLite creates serious bottlenecks as data demands and volumes increase in practical IoT and mobile use cases.

Ratcheting up the horsepower of the compute platform behind SQLite will improve the performance of this aging campaigner only so much. What modern edge data management needs is a cheetah, and what we have in SQLite is banana slug.

And none of these scenarios are scenes from some distant future. Enterprises are encountering these challenges today in situations involving IoT grids with thousands of sensors and upstream gateways. Some of our competitors are seeing these challenges too, as more and more are looking to jettison SQLite as their mobile engine.

If you’ve tested SQLite against other databases or documented a change in the performance of your application when you moved from using flat files to SQLite, let us know. Feel free to drop me a line at lewis.carr@actian.com.

In the meantime, please read my next blog next on what the near-ubiquitous use of SQLite as a mobile database has meant for data sharing and movement from mobile and IoT at the edge to the cloud and data center.

Finally, if you’re ready to reconsider SQLite, learn more about Actian Zen.  Or, you can just kick the tires for free with Zen Core which is royalty-free for development and distribution.

About Actian Corporation

Actian makes data easy. We deliver cloud, hybrid, and on-premises data solutions that simplify how people connect, manage, and analyze data. We transform business by enabling customers to make confident, data-driven decisions that accelerate their organization’s growth. Our data platform integrates seamlessly, performs reliably, and delivers at industry-leading speeds.