SQLite 3.50+ Binary JSONB, STRICT Table & Generated Indexes

Supanote Core Team @supanote · 2 min read

plain (sql) 1 day ago · 46 lines · 4 views

Artifact

Verified Blueprint SQLite 3.50+

Cut SQLite JSON CPU overhead by 50%+ using binary JSONB BLOB storage, STRICT tables, VIRTUAL generated columns, and jsonb_set updates.

✓ Stores pre-parsed binary JSONB inside a STRICT table with CHECK(json_valid(attrs, 4)) validation
✓ Indexes hot JSON attributes via zero-storage VIRTUAL generated columns for sub-millisecond lookups
✓ Uses SQLite 3.50+ localized jsonb_set / jsonb_replace to mutate nested keys without re-parsing text

Quick Fetch

-- SQLite 3.45 - 3.50+ High-Speed Binary JSONB + STRICT Table + Generated Index Pattern
-- Binary JSONB avoids repeated text parsing and enables localized in-place mutations in 3.50+

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

-- 1. STRICT Table Storing Binary JSONB Payload + Zero-Disk VIRTUAL Generated Columns
CREATE TABLE IF NOT EXISTS user_sessions (
    session_id   TEXT PRIMARY KEY,
    user_id      INTEGER NOT NULL,
    -- Store binary JSONB BLOB; flag '4' in json_valid enforces RFC-8259 OR SQLite JSONB format
    metadata     BLOB NOT NULL DEFAULT (jsonb('{}')) CHECK (json_valid(metadata, 4)),
    -- Virtual generated columns compute on read & power standard B-tree indexes with 0 byte storage overhead
    plan_tier    TEXT GENERATED ALWAYS AS (metadata ->> '$.plan') VIRTUAL,
    region_code  TEXT GENERATED ALWAYS AS (metadata ->> '$.geo.region') VIRTUAL,
    updated_at   INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;

-- 2. Partial & Composite B-Tree Indexes Over Virtual JSONB Fields
CREATE INDEX IF NOT EXISTS idx_sessions_plan_user
    ON user_sessions (plan_tier, user_id);

CREATE INDEX IF NOT EXISTS idx_sessions_eu_region
    ON user_sessions (region_code, updated_at DESC)
    WHERE region_code IS NOT NULL;

-- 3. Insert Converting Incoming Text JSON Into Compact Binary JSONB Once on Write
INSERT INTO user_sessions (session_id, user_id, metadata)
VALUES (
    'sess_0195f410a9c2',
    1042,
    jsonb('{"plan":"pro","mfa":true,"geo":{"region":"eu-central","asn":24940},"quotas":{"runs":12}}')
);

-- 4. Localized In-Place Mutation (SQLite 3.50+ optimizes jsonb_replace / jsonb_set internal byte shifts)
UPDATE user_sessions
SET metadata   = jsonb_set(metadata, '$.quotas.runs', (metadata ->> '$.quotas.runs') + 1),
    updated_at = unixepoch()
WHERE session_id = 'sess_0195f410a9c2';

-- 5. Read Back Clean Text JSON When Returning to HTTP/API Layer (Uses idx_sessions_plan_user!)
EXPLAIN QUERY PLAN
SELECT session_id, user_id, json(metadata) AS metadata_json
FROM user_sessions
WHERE plan_tier = 'pro';

1-- SQLite 3.45 - 3.50+ High-Speed Binary JSONB + STRICT Table + Generated Index Pattern

2-- Binary JSONB avoids repeated text parsing and enables localized in-place mutations in 3.50+

4PRAGMA journal_mode = WAL;

5PRAGMA synchronous = NORMAL;

7-- 1. STRICT Table Storing Binary JSONB Payload + Zero-Disk VIRTUAL Generated Columns

8CREATE TABLE IF NOT EXISTS user_sessions (

9 session_id TEXT PRIMARY KEY,

10 user_id INTEGER NOT NULL,

11 -- Store binary JSONB BLOB; flag '4' in json_valid enforces RFC-8259 OR SQLite JSONB format

12 metadata BLOB NOT NULL DEFAULT (jsonb('{}')) CHECK (json_valid(metadata, 4)),

13 -- Virtual generated columns compute on read & power standard B-tree indexes with 0 byte storage overhead

14 plan_tier TEXT GENERATED ALWAYS AS (metadata ->> '$.plan') VIRTUAL,

15 region_code TEXT GENERATED ALWAYS AS (metadata ->> '$.geo.region') VIRTUAL,

16 updated_at INTEGER NOT NULL DEFAULT (unixepoch())

17) STRICT;

19-- 2. Partial & Composite B-Tree Indexes Over Virtual JSONB Fields

20CREATE INDEX IF NOT EXISTS idx_sessions_plan_user

21 ON user_sessions (plan_tier, user_id);

23CREATE INDEX IF NOT EXISTS idx_sessions_eu_region

24 ON user_sessions (region_code, updated_at DESC)

25 WHERE region_code IS NOT NULL;

27-- 3. Insert Converting Incoming Text JSON Into Compact Binary JSONB Once on Write

28INSERT INTO user_sessions (session_id, user_id, metadata)

29VALUES (

30 'sess_0195f410a9c2',

31 1042,

32 jsonb('{"plan":"pro","mfa":true,"geo":{"region":"eu-central","asn":24940},"quotas":{"runs":12}}')

33);

35-- 4. Localized In-Place Mutation (SQLite 3.50+ optimizes jsonb_replace / jsonb_set internal byte shifts)

36UPDATE user_sessions

37SET metadata = jsonb_set(metadata, '$.quotas.runs', (metadata ->> '$.quotas.runs') + 1),

38 updated_at = unixepoch()

39WHERE session_id = 'sess_0195f410a9c2';

41-- 5. Read Back Clean Text JSON When Returning to HTTP/API Layer (Uses idx_sessions_plan_user!)

42EXPLAIN QUERY PLAN

43SELECT session_id, user_id, json(metadata) AS metadata_json

44FROM user_sessions

45WHERE plan_tier = 'pro';

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related SQLite 3.50+ & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

SQL SQLite

High-Performance SQLite Production PRAGMAs

Maximum performance SQLite pragmas for WAL mode, concurrency, and memory buffers.

SQL SQLite

SQLite FTS5 Full-Text Search Schema & Sync Triggers

External content FTS5 virtual table with unicode61 tokenizer and insert, update, and delete synchronization triggers.

BASH SQLite 3.50+

SQLite 3.50+ Live Remote Replication Script (sqlite3_rsync)

Automate bandwidth-efficient live SQLite 3.50+ backups over SSH using official sqlite3_rsync page-level delta transfers and quick_check.

SQL Google BigQuery

BigQuery FinOps: PHYSICAL vs LOGICAL Storage Savings Calculator

Audit every BigQuery dataset via INFORMATION_SCHEMA.TABLE_STORAGE to calculate exact monthly savings from switching to PHYSICAL billing.

Interactive Developer Workbench: Online JSON Formatter, Validator & Viewer

Validate, pretty-print, minify, and inspect JSON API responses with exact syntax error pinpointing.

Open JSON Formatter →