Columnar virtual tables
Create SQLite virtual tables whose columns are stored separately for analytical access patterns.
Embedded analytics
Column-oriented analytics inside SQLite.
SQLite-Columnar adds a self-contained columnar storage and execution path to SQLite for analytical scans, grouped summaries, time-series rollups, and local dashboards.
SQLite-Columnar
SQLite-native extension
Best for
Embedded analytics
Repository
GitHubProject
What it is
SQLite-Columnar is a loadable extension that creates columnar virtual tables for analytical parts of an application while leaving SQLite's pager, btree, parser, VDBE, and shell untouched.
It stores each column separately, maintains stats and chunk metadata, and exposes specialized aggregation helpers for fast scans across wide datasets. On queries that can use columnar metadata and helpers, the included 10 million row variance benchmark reports speedups such as 130,583x faster `sum`, 125,194x faster `count`, and up to 273.20x faster clustered range filters with grouped aggregates.
Why it matters
Analytics without
moving data.
Row-oriented SQLite is excellent for transactions, point lookups, and small updates. Analytical workloads often read a few columns across many rows, so columnar layout avoids work the query does not need.
Benchmark results are workload-specific, but the reported gains are strongest where columnar layout should shine: few-column scans, aggregate metadata, grouped low-cardinality summaries, and clustered range filters. That makes embedded dashboards, IoT telemetry, audit logs, tenant-local analytics, ETL validation, and ML preprocessing practical inside the same SQLite deployment model.
Capabilities
Features and characteristics
Incremental analyze
Build and refresh per-column stats and chunk metadata only when data changes.
Aggregate helpers
Run specialized sum, average, count, min, max, and grouped helpers over columnar data.
Chunk zone maps
Skip rowid ranges that cannot match range filters after columnar metadata is analyzed.
Grouped analytics
Use table-valued helpers for grouped summaries without generic row materialization.
Massive speedups
The 10 million row variance benchmark reports 130,583x faster `sum`, 129,317x faster `avg`, 125,194x faster `count`, and up to 273.20x faster clustered range filters with grouped aggregates.
Sample code
Create a columnar analytics table
Load the extension, create a columnar virtual table, analyze it, and query fast aggregate helpers.
-- Load SQLite-Columnar
.load ./columnar
CREATE VIRTUAL TABLE sales USING columnar(
id INTEGER,
region TEXT,
category TEXT,
amount REAL,
cost REAL
);
INSERT INTO sales VALUES
(1, 'north', 'hardware', 10.0, 4.0),
(2, 'north', 'software', 20.0, 8.0),
(3, 'south', 'hardware', 5.0, 2.0);
SELECT columnar_analyze('sales');
SELECT columnar_sum('sales', 'amount');
SELECT k, "sum", "avg", "count"
FROM columnar_group_sum_avg_count('sales', 'region', 'amount')
ORDER BY k;