SQLite JSON का पूरा indexing speed के साथ उपयोग कैसे करें: generated columns से high-speed queries

• SQLite की JSON capabilities का उपयोग करके मूल JSON document को उसी रूप में store रखते हुए, ज़रूरी fields को virtual generated columns के रूप में extract करके index करने का तरीका पेश किया गया है
• json_extract function की मदद से JSON के अंदर के data को column की तरह handle किया जा सकता है, और B-tree index speed पर query चलाई जा सकती है
• जब भी नया query pattern चाहिए, data migration के बिना column और index जोड़कर इसे expand किया जा सकता है
• यह तरीका schemaless data की flexibility और relational database की performance दोनों एक साथ देता है
• SQLite इस्तेमाल करने वाले developers के लिए इसे सरल संरचना और उच्च performance देने वाले practical pattern के रूप में रेखांकित किया गया है

---

SQLite और JSON features का संयोजन

• SQLite JSON functions और operators को support करता है, जिससे JSON data को सीधे store और manipulate किया जा सकता है
  • JSON document को एक column में ज्यों-का-त्यों store करके, केवल ज़रूरी जानकारी को virtual columns के रूप में extract किया जा सकता है
  • यह तरीका schema define किए बिना data को flexibly handle करने की सुविधा देता है
• DB Pro टीम ने पिछले कुछ महीनों में SQLite का गहराई से उपयोग करते हुए इन features को practical work में validate किया है
  • सही configuration के साथ SQLite को production environment में भी स्थिर रूप से इस्तेमाल किया जा सकता है

Virtual generated columns

• json_extract का उपयोग करके JSON के अंदर के specific values को virtual generated columns के रूप में define किया जा सकता है
  • यह column actual data को store नहीं करता, बल्कि query के समय calculate होकर तुरंत उपयोग में आता है
  • अलग से backfill process या data duplication की ज़रूरत नहीं होती
• उदाहरण के लिए, JSON data से किसी specific field को extract करके उसे column की तरह handle करने वाली structure बनाई जा सकती है

Index जोड़ना और performance सुधार

• virtual columns पर index जोड़ने से JSON data को भी सामान्य columns की तरह B-tree index speed पर search किया जा सकता है
  • index जोड़े गए virtual columns relational database के columns जैसी ही performance देते हैं
• यह approach JSON data का आकार बड़ा होने पर भी तेज़ search संभव बनाता है

नए query patterns जोड़ना

• बाद में अगर किसी नए field से search की ज़रूरत हो, तो बस नया virtual column और index जोड़ना काफ़ी है
  • उदाहरण: user_id field को extract करके index बनाना
  • existing data rows को modify या migrate करने की ज़रूरत नहीं होती
• इससे data structure बदले बिना तुरंत query scalability हासिल होती है

इस pattern के फ़ायदे और महत्व

• यह pattern schemaless JSON storage की flexibility और relational DB की index performance को जोड़ता है
  • शुरुआती design stage में indexing strategy पहले से तय करने की ज़रूरत नहीं होती
  • ज़रूरत पड़ने पर column और index जोड़कर optimization किया जा सकता है
• SQLite का उपयोग करने वाले developers के लिए इसे सरल लेकिन शक्तिशाली data handling approach के रूप में पेश किया गया है
• DB Pro ने आगे भी SQLite की अलग-अलग capabilities पर और लेख लाने का संकेत दिया है

[GN⁺]

Hacker News की राय

• JSON दस्तावेज़ों ko सीधे serialized B-tree के रूप में encode किया जा सकता है
  इससे indexed speed पर अंदरूनी fields ko सीधे query किया जा सकता है, और दस्तावेज़ खुद पहले से indexed होता है, इसलिए parsing की ज़रूरत नहीं पड़ती
  इस format ko Lite³ कहा जाता है। यह एक प्रोजेक्ट है जिस पर मैं खुद काम कर रहा हूँ
  GitHub link

  • यह सच में शानदार है! मुझे Rkyv पसंद है, लेकिन उसके लिए Rust चाहिए, इसलिए छोटे projects में वह थोड़ा भारी पड़ता था
    Lite³ में lite3_val_bytes के जरिए binary data support होना मुझे खास तौर पर पसंद आया
  • मैं जानना चाहता था कि Lite³, PostgreSQL के JSONB से कैसे अलग है
    JSONB array length और offsets ko साथ में encode करता है, ताकि compression efficiency और speed के बीच संतुलन रखा जा सके
    Lite³ में in-place updates संभव हैं, लेकिन sensitive data के निशान न रहें, इसके लिए समय-समय पर “vacuum” करना पड़ता है
    JSONB में re-encoding के बिना updates करना मुश्किल है, लेकिन Lite³ में सिर्फ structure traversal से इसे आसानी से साफ किया जा सकता है
    compressibility शायद JSONB की बेहतर होगी, लेकिन Lite³ का design मुझे बहुत चतुर approach लगता है
    मैं भी एक ASN.1 compiler maintain कर रहा हूँ, इसलिए ऐसे serialization formats में मेरी काफी दिलचस्पी है। Lite³ से मुझे नए ideas मिले
  • अगर इसका Rust implementation हो तो बहुत अच्छा होगा

• मुझे SQLite बहुत पसंद है, लेकिन analytics के लिए मैं ज़्यादातर DuckDB इस्तेमाल करता हूँ
  DuckDB, SQLite की तरह single file इस्तेमाल करता है, लेकिन बड़े datasets ko बेहद तेज़ी से process करता है
  M2 MacBook पर 2 करोड़ records संभालने पर भी यह बहुत तेज़ है
  उदाहरण के लिए, आप इस query से सीधे JSON files पढ़ सकते हैं

  SELECT avg(sale_price), count(DISTINCT customer_id)
  FROM '/my-data-lake/sales/2024/*.json';
  

  साथ ही JSON type column load करके Postgres-style col->>'$.key' syntax भी इस्तेमाल किया जा सकता है

  • क्या पहली query file system की JSON files ko on-the-fly index कर रही है?
  • DuckDB ko visualization tool pygwalker के साथ इस्तेमाल करें तो कुछ ही सेकंड में लाखों records का analysis किया जा सकता है
    लेकिन SQLite से इसकी तुलना करना थोड़ा unfair है। SQLite system building के लिए है, और DuckDB analytics के लिए ज़्यादा उपयुक्त है
    कई platforms पर deploy करना हो तो DuckDB थोड़ा मुश्किल हो सकता है
  • dataset ko बिना compression के store करना कभी नहीं करना चाहिए। DuckDB कई तरह के compression formats support करता है

• JSON performance के लिए Generated Column इस्तेमाल करना मुझे हमेशा से एक आम तरीका लगा
  Postgres में भी JSON column के अंदर की keys ko foreign key की तरह बनाए रखने के लिए मैंने यह तरीका इस्तेमाल किया है। थोड़ा ‘cursed’ लगता है, लेकिन काम अच्छा करता है

  • Postgres में क्या JSONB के अंदरूनी field पर सीधे index नहीं लगाया जा सकता?
    उदाहरण के लिए

    CREATE INDEX idx_status_gin
    ON my_table
    USING gin ((data->'status'));
    

    संदर्भ ब्लॉग
  • लेकिन कई बार अंत में इसे key/value table में अलग करना बेहतर होता है
    JSON schema बदल जाए तो parsing या migration fail हो सकती है
  • सच कहें तो यह इतना ‘cursed’ तरीका भी नहीं है। जहाँ ज़रूरत हो वहाँ normalized relational structure रखें, और बाकी ko jsonb में संभालें
  • मैं सोच रहा था कि STORED की जगह VIRTUAL column भी इस्तेमाल किया जा सकता है या नहीं, और यह उदाहरण VIRTUAL का ही इस्तेमाल करता है

• हाल ही में Claude Code ने जो optimization सुझाया, उससे मुझे इस technique का पता चला
  SQLite की नई features से अनजान होने की वजह से यह बात मुझसे छूट गई थी, लेकिन performance improvement काफ़ी बड़ा था
  सीख यह है कि जिन tools से आप परिचित हैं, उनकी documentation भी समय-समय पर फिर से पढ़नी चाहिए

  • manual ko दोबारा पढ़ना उम्मीद से ज़्यादा insightful हो सकता है

• 2023 में HN पर bambax की comment देखने के बाद मैंने यह blog post लिखी
  मूल comment link

• JSON ko सीधे project किए बिना भी index बनाया जा सकता है, लेकिन computed column query ko आसान बना देता है
  MS-SQL 2025(v17) से पहले JSON support सीमित था, इसलिए यह तरीका ज़रूरी था

  • अगर JSON ko सीधे query किए बिना सिर्फ computed columns का इस्तेमाल करें, तो गलती से non-indexed query लिखने की संभावना नहीं रहती
  • मैंने local DBA conference में इस feature के बारे में सुना था, लेकिन तब यह कोई बड़ा बदलाव नहीं लगा था

• मैंने HN पर लेख खोला, और उसमें मेरी comment उद्धृत थी, और वही comment लेख का विषय भी थी — यह काफ़ी अजीब अनुभव था
  “धन्यवाद, bambax!” पढ़कर मुस्कान आ गई। SQLite वाकई शानदार tool है

  • जिसने वास्तव में प्रेरणा दी, वह comment इस link पर है

• दिलचस्प है, लेकिन SQLite का "Index On Expression" इस्तेमाल नहीं किया जा सकता क्या?
  उदाहरण के लिए CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
  लेकिन अगर JSON path syntax में थोड़ा भी फर्क हो, तो index इस्तेमाल नहीं हो सकता। इसके विपरीत Virtual Generated Column हमेशा index usage सुनिश्चित करता है

  • अगर index expression ko view के साथ इस्तेमाल करें, तो expression match सुनिश्चित किया जा सकता है
    उदाहरण: recordlite प्रोजेक्ट
  • JSON path syntax में थोड़ा सा फर्क भी index ko बेकार कर सकता है
    उदाहरण के लिए json_extract(data, "$.type") और data -> '$.type' ko अलग तरह से पहचाना जाता है
    यानी WHERE clause की expression बदलते ही index बेअसर हो सकता है
  • यह एक सरल और तेज़ समाधान है। query और index का match होना हमेशा से ज़रूरी रहा है
  • expression index feature, SQLite 3.9.0(2015-10-14) से जोड़ी गई एक अपेक्षाकृत नई feature है

• काश developers, एक consistent schema होने के बावजूद, सारा data JSON(B) columns में ठूँसना थोड़ा कम करें
  इससे indexing, constraints management मुश्किल हो जाते हैं, और असली इस्तेमाल में overhead भी बढ़ता है

  • JSON columns तब सबसे ज़्यादा काम आते हैं जब data ko table के रूप में दिखाना मुश्किल हो, जैसे tree structures
    उदाहरण के लिए Haskell+TypeScript environment में complex nested structures ko JSON में serialize करना कहीं ज़्यादा सुविधाजनक है
  • JSON(B) तब उपयोगी है जब अलग-अलग types के data ko एक ही collection में रखना हो
    उदाहरण: अलग-अलग payment processors के results ko एक table में store करना, या classified site में item category के हिसाब से अलग properties संभालना
    C# या JS/TS में type validation tools (Zod, OpenAPI आदि) के साथ इसे manage करना आसान हो जाता है
  • साधारण JSON के लिए normalization बेहतर हो सकती है, लेकिन जटिल API responses ko tables में तोड़ें तो JOIN hell बन जाता है
    अंत में बात maintainability और performance के बीच संतुलन की है। इस लेख का मुख्य बिंदु यही है कि JSON पर भी आसानी से index लगाया जा सकता है
  • sensor data जैसे मामलों में, जहाँ पूरी tree structure ko एक साथ संभालना हो, JSON columns काफ़ी सरल पड़ते हैं
    read performance ko index से काफ़ी हद तक सुधारा जा सकता है
  • पूरी normalization कई बार अक्षम होती है
    उदाहरण के लिए, product pricing system में customer-specific unusual discount rules ko JSON में व्यक्त करना कहीं ज़्यादा flexible हो सकता है

• अगर JSON की जगह XML इस्तेमाल करें, तो यह 90s~00s के document store जैसा ही मॉडल होगा
  insert/update के समय parsing होती थी, और query के समय सिर्फ index access किया जाता था
  SQLite का यह capability built-in देना वाकई बहुत दिलचस्प है

[iolothebard]

20वीं सदी के आखिर में… एक universal database जैसी चीज़ हुआ करती थी… (अब सही है, तब ग़लत था.)