Stanchion

Column-oriented tables in SQLite

Why?

Stanchion is a SQLite 3 extension that brings the power of column-oriented storage to SQLite, the most widely deployed database. SQLite exclusively supports row-oriented tables, which means it is not an ideal fit for all workloads. Using the Stanchion plugin brings all of the benefits of column-oriented storage and data warehousing to anywhere that SQLite is already deployed, including your existing tech stack.

There are a number of situations where column-oriented storage outperforms row-oriented storage:

• Storing and processing metric, log, and event data
• Timeseries data storage and analysis
• Analytical queries over many rows and a few columns (e.g. calculating the average temperature over months of hourly weather data)
• Change tracking, history/temporal tables
• Anchor modeling / Datomic-like data models

Stanchion is an ideal fit for analytical queries and wide tables because it only scans data from the columns that are referenced by a given query. It uses compression techniques like run length and bit-packed encodings that significantly reduce the size of stored data, greatly reducing the cost of large data sets. This makes it an ideal solution for storing large, expanding datasets.

Example

Download the prebuilt dynamic library for your platform from a Release or build from source.

.load /path/to/libstanchion

CREATE VIRTUAL TABLE dnd_monsters
USING stanchion (
    id INTEGER NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    size INTEGER NOT NULL,
    challenge_rating FLOAT NOT NULL,
    SORT KEY (id)
);

INSERT INTO dnd_monsters (id, name, type, size, challenge_rating)
VALUES
    (1, 'Beholder', 'ABERRATION', 4, 13),
    (2, 'Gelatinous Cube', 'OOZE', 4, 2),
    (3, 'Mimic', 'MONSTROSITY', 3, 2),
    (4, 'Lich', 'UNDEAD', 3, 21);

-- Because the `dnd_monsters` table is column-oriented, the following query
-- only reads data from the `name`, `type`, and `challenge_rating` columns.
-- Data in the `id` and `size` columns are not accessed or scanned at all!
SELECT name
FROM dnd_monsters
WHERE type = 'UNDEAD' AND challenge_rating >= 18;

Status

Stanchion is in alpha. Things may not be fully working. The storage format may change in backwards incompatible ways. Do not use this in production... yet.

Build

Install Zig (master) and clone the stanchion repository. Then run:

zig build ext -Doptimize=ReleaseFast

The SQLite extension is the dynamic library named libstanchion in the zig-out directory.

Running tests

By default, tests use the system SQLite library. However, stanchion's build can optionally download and compile a specific version of SQLite and use that version when running tests. Pass -Dsqlite-test-version=$SQLITE_VERSION to the build for unit and integration tests. For example:

zig build test -Dsqlite-test-version=3.38.5

It is also possible to launch a SQLite shell for any version of SQLite (a convenience feature for debugging):

zig build sqlite-shell -Dsqlite-test-version=3.43.2

Usage

Load Stanchion

Stanchion is a Run-Time Loadable Extension that uses SQLite's virtual table system. To load an extension from the SQLite CLI, use the .load command. Check the documentation of the SQLite bindings you are using to see how to load an extension in your application. Here are some examples for different language bindings: sqlite3 for Python, rusqlite for Rust, sqlite3 for Ruby, and go-sqlite3 for Go.

Before loading stanchion (or any extension), you may first need to enable extension loading. Here are some examples for different language bindings: sqlite3 for Python, rusqlite for Rust, and sqlite3 for Ruby. Some bindings enable extension loading by default (e.g. go-sqlite3 for Go). For more information, see the SQLite documentation for the C API.

Stanchion is both a persistent and auto extension. It only needs to be loaded by one connection in a process and then it will automatically be loaded by all connections in the same process. Loading it from the other connections anyway is harmless. Connections in other processes connecting to the same SQLite database still need to load stanchion.

Create table

Creating a stanchion table works much like creating any table in SQLite:

CREATE VIRTUAL TABLE sensor_log USING stanchion (
    sensor_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    value FLOAT NULL,
    variance FLOAT NULL,
    severity INTEGER NOT NULL,
    SORT KEY (sensor_id, timestamp)
)

The USING stanchion phrase tells SQLite to create sensor_log as a virtual table that is implemented by stanchion.

The SORT KEY is required for all stanchion tables. It defines the clustered index, the order of the records in the table. The SORT KEY does not enforce uniqueness. Currently, the SORT KEY can only contain columns not expressions.

Stanchion tables do not support foreign keys, primary keys, check, or unique constraints. These constraints are generally less useful in the scenarios that column-oriented tables are more useful and they are not widely supported across column-oriented databases. However, some of or all of these constraints may be introduced to stanchion in the future as options.

Data types

The following table shows all stanchion data types. Boolean values are converted to integers when passed through SQLite. This allows them to be used from queries and through the SQLite API, which does not support a dedicated Boolean type. See Differences from SQLite: BOOLEAN type for more information about the BOOLEAN type.

| Stanchion type | SQLite type | Aliases | |-----------------|--------------|----------------------| | BOOLEAN | INTEGER | BOOL | | INTEGER | INTEGER | INT | | FLOAT | REAL | REAL, DOUBLE | | BLOB | BLOB | | | TEXT | TEXT | VARCHAR^*|

_{* Does not support character count}

There is no ANY type and all inserted values must match the declared column type. It is not possible to do dynamic typing in stanchion tables. Stanchion tables are roughly equivalent to SQLite tables declared as STRICT (without the any type).

There are a small number of aliases supported for widely used type names. Declaring a column with an alias is no different than declaring it with the canonical type name.

Add and query data

Inserting and querying data works like any other table in SQLite. Stanchion tables even work with features like the .import command for adding records to tables.

INSERT INTO sensor_log (sensor_id, timestamp, value, variance, severity)
VALUES
    (2064, 12433702443, 74.37, 1.06, 1),
    (2064, 12433703443, 73.12, 0.96, 1)

Values being inserted into Stanchion tables must be of the column's declared type. This is equivalent to declaring a SQLite table with the STRICT table option.

Updating and deleting records is not currently supported. Support for update and delete will be added in the future.

Stanchion tables can be used in all places that native tables can be used in SQLite. Consider the SORT KEY as a composite index when writing queries to improve query performance. In the following query, the sort key is used to reduce the amount of data scanned. And of course, only the sensor_id, timestamp, and value columns are read at all.

SELECT AVG(value)
FROM sensor_log
WHERE sensor_id = 2064 AND timestamp > 12433700000

Differences from SQLite

BOOLEAN type

Stanchion has a dedicated BOOLEAN type. Boolean values are used within stanchion as part of each nullable segment and exposed so it can also be used directly.

SQLite uses INTEGER to represent booleans. Stanchion converts BOOLEAN values to from INTEGER values when passed through SQLite. Querying a BOOLEAN stanchion column returns INTEGER SQLite values.

Clustered index by SORT KEY

In Stanchion, the order of the records in the table (aka the clustered index) is controlled by the SORT KEY. Currently, every table in stanchion must have an explicit SORT KEY made up of 1 or more columns (currently expresions are not supported). It is declared when the table is created and cannot be changed. Unlike a PRIMARY KEY, it does not enforce uniqueness.

This differs from SQLite where tables are sorted by the ROWID by default or by the PRIMARY KEY if the table is a WITHOUT ROWID table.

No uniqueness (UNIQUE or PRIMARY KEY) or foreign key constraints

This may change in the future. Implementing these will likely require external indexes. When/if a PRIMARY KEY is introduced, it will likley make sense to follow the lead of Clickhouse's MergeTree engine and require that the PRIMARY KEY must be a prefix of