MMS • Sergio De Simone
Article originally posted on InfoQ. Visit InfoQ
HC-tree is a project aimed to build a new backend for SQLite specifically optimized for high-concurrency and lead-follower style replication. While still experimental, HC-tree can be used as an SQLite drop-in replacement, albeit with limitations.
SQLite is sometimes used as the core of a client/server database system. While it works reliably well in such cases, the database backend module that it uses to store b-tree structures in its database file was not designed with this case in mind and can be improved upon in several ways.
In particular, SQLite does not fully support the possibility of multiple simultaneous writers. Even when using its begin-concurrent extension, which uses page-level locking to allow multiple writers, SQLite may incur in locking conflicts and thus needs to serialize all commit operations.
HC-tree, on the contrary, is designed to support dozens of concurrent writers, say the team behind it, thanks to its optimistic row-level locking.
HC-tree offers similar MVCC based optimistic concurrency to SQLite with the begin-concurrent extension, except that it validates transactions based on the keys and logical ranges of keys accessed instead of the set of pages.
The goal of the project is being at least as fast as SQLite for the single-threaded case, so that running concurrent writes can really bring a performance advantage. As the official benchmarks show, HC-tree easily outperforms stock SQLite in a number of different concurrent scenarios and is on a par with respect to single-writer cases.
Another goal of HC-tree is improving SQLite support for replication beyond what is permitted by the standard sessions extension, which enables serializing the content of a committed transaction to send it to a different database. To this aim, HC-tree will promote the sessions extension to be part of the backend and add support to manage leader-follower transactions, meaning that changes can be applied more quickly from the leader database to followers because no validation is required.
As a last note regarding improved features, HC-tree aims to replace 32-bit page numbers with 48-bit page numbers in future to go beyond SQLite limit of 16TiB.
HC-tree is still a work in progress and while it can be used for experimentation and assessing whether it is a good replacement for an existing SQLite-based solution, it still has a number of limitations, including missing support for BEGIN EXCLUSIVE
, reduced efficiency for transactions that do not fit entirely in main memory, and others that you can check out on the project’s official page.