Stanchion

[rui.ms.ramos]

If you use sqlite you may know that it updates data at the row level. I've just bumped into this project that aims to bring column-oriented storage to SQLite and would like to share with you.

This is the project description:

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.

You can find more information on the official Github repo:

• https://github.com/dgllghr/stanchion