SQLite में UUID primary key के खतरे

• SQLite में primary key का implementation सामान्य rowid tables और WITHOUT ROWID tables में physical storage order को अलग बनाता है, और अगर random UUID4 को clustered index के रूप में इस्तेमाल किया जाए तो B-tree rebalancing और अतिरिक्त paging cost पैदा होती है
• integer rowid baseline में 10 लाख rows के insertion पर लगभग 10 लाख inserts प्रति सेकंड का स्तर मिलता है, जबकि UUID4 WITHOUT ROWID में insertion time 14–16 गुना धीमा दर्ज हुआ
• UUID4 की unordered प्रकृति rows को B-tree में random तरीके से insert कराती है, और profiling के नतीजों में tree balancing तथा read/write पर अधिक समय खर्च होता दिखा
• UUID7 WITHOUT ROWID ने time-ordered UUID होने के कारण UUID4 की sorting समस्या को कम किया और अधिक व्यावहारिक insertion time दिखाया, लेकिन 16-byte BLOB key होने से यह 8-byte integer key से फिर भी धीमा रहा
• UUID4 WITH ROWID को hidden rowid की sequentiality का लाभ मिलता है, लेकिन दो indexes की वजह से write amplification और random index insertion cost बनी रहती है, इसलिए इसका performance UUID7 WITHOUT ROWID से कम रहा

---

Clustered index क्या है?

• Clustered index वह index है जो table rows के physical storage order को तय करता है
• rows को physical रूप से केवल एक ही तरीके से sort किया जा सकता है, इसलिए हर table में केवल एक clustered index हो सकता है
• clustered index वास्तव में table itself होता है, जबकि non-clustered index केवल indexed columns और actual row data के location की ओर pointer store करता है

Rowid

• हर सामान्य SQLite table में rowid नाम की एक implicit 64-bit integer primary key होती है
• table data एक B-tree structure में store होता है जिसमें हर row के लिए एक entry होती है, और rowid value को key के रूप में इस्तेमाल किया जाता है
• rowid व्यवहार में SQLite का clustered index है, और rows का physical storage order rowid order के अनुसार होता है

WITHOUT ROWID

• SQLite WITHOUT ROWID tables को support करता है, और इन tables में implicit rowid नहीं होता
• WITHOUT ROWID table में declared primary key clustered index की भूमिका निभाती है
• SQLite rowid table को B*-Tree के रूप में implement करता है जहाँ सारा content leaves में store होता है, जबकि WITHOUT ROWID table एक सामान्य B-Tree का उपयोग करता है जो leaves और intermediate nodes दोनों में content store करता है

Baseline: rowid integer primary key

• baseline में id INTEGER PRIMARY KEY, data BLOB structure वाले सामान्य rowid table पर 10 लाख rows की insertion time मापी गई
• result table में total row count 1 करोड़ rows से 10 करोड़ rows तक है, और measured time 692ms से 838ms के बीच रहा
• baseline performance लगभग 10 लाख inserts प्रति सेकंड के स्तर पर रही

UUID4 WITHOUT ROWID

• UUID4 test में id BLOB PRIMARY KEY, data BLOB structure वाले WITHOUT ROWID table में random-uuid4-bytes value को primary key के रूप में insert किया गया
• result table में measured time 1 करोड़ rows पर 2649ms और 10 करोड़ rows पर 12586ms रहा
• insertion performance integer rowid baseline की तुलना में 14–16 गुना धीमी रही

Profile

• normalized diffgraph INTEGER और UUID4 profiling snapshots की तुलना करता है और flamegraph structure में अंतर दिखाता है
• normalized view दोनों profiles के कुल sample count को समान करके relative अंतर को percentage में देखने देता है
• नीले frames दिखाते हैं कि दूसरे profile, यानी UUID4, में संबंधित function time INTEGER की तुलना में कम हुआ, जबकि लाल frames दिखाते हैं कि UUID4 में वह अधिक बढ़ा
• color intensity उस frame के अपने sample count change, यानी self time delta, के relative change को दर्शाती है
• diffgraph में tree balancing और read/write पर अधिक समय खर्च होता दिखा
• UUID4 की unordered प्रकृति के कारण keys random order में sort होती हैं, और SQLite को B-tree को लगातार rebalance करना पड़ता है

UUID7 WITHOUT ROWID

• UUID7 एक time-ordered UUID है, जो UUID4 की sorting समस्या को हटाने का तरीका हो सकता है
• UUID7 test भी id BLOB PRIMARY KEY, data BLOB structure वाले WITHOUT ROWID table में चलाया गया
• result table में measured time 1 करोड़ rows पर 1372ms और 10 करोड़ rows पर 1258ms रहा
• UUID7 WITHOUT ROWID, UUID4 WITHOUT ROWID की तुलना में अधिक व्यावहारिक स्तर पर लौट आया, लेकिन baseline से फिर भी धीमा रहा
• UUID BLOB primary key 16-byte की होती है, जबकि integer primary key 8-byte की होती है

UUID4 WITH ROWID

• UUID4 WITH ROWID test में WITHOUT ROWID के बिना id BLOB PRIMARY KEY, data BLOB table का उपयोग किया गया
• इस configuration में hidden rowid clustered index होता है, और rowid का लाभ उसकी sequentiality है
• इसकी कमी यह है कि table में दो indexes बन जाते हैं, जिससे write amplification होती है
• result table में measured time 1 करोड़ rows पर 2003ms और 10 करोड़ rows पर 7119ms रहा
• UUID4 WITH ROWID का performance UUID7 WITHOUT ROWID जितना अच्छा नहीं था, क्योंकि clustered index न होने पर भी random insertion की वजह से index को लगातार बनाना पड़ता है

निष्कर्ष

• SQLite में UUID primary key ऐसा विकल्प है जिसमें clustered index और key ordering के अनुसार insertion performance काफी बदल सकती है
• random UUID की समस्या केवल SQLite तक सीमित नहीं है, बल्कि clustered index इस्तेमाल करने वाले दूसरे databases पर भी लागू होती है
• पूरा benchmark code GitHub repository में उपलब्ध है

[GN⁺]

Lobste.rs की राय

• अच्छा है। rowid table में जब integer key monotonic increase न होकर random हो, तब के आँकड़े भी देखना दिलचस्प होता
  लेख में छूटा एक अहम बिंदु यह है कि rowid table का primary index B+ tree होता है, और without rowid table B-tree होता है
  इसलिए आम तौर पर अगर average record size किसी threshold से ऊपर चला जाए, तो बाद वाला आदर्श नहीं रहता। वजह यह है कि index internal nodes पूरे record को store करते हैं, और अगर ठीक याद है तो SQLite manual page size के 1/20 को thumb rule के रूप में सुझाता है

• UUID performance को measure करने में इतनी मेहनत की गई, लेकिन natural key पर विचार नहीं किया गया
  integer हो, UUID हो या कोई और रूप, surrogate key complexity बढ़ाती है, कोई नई information नहीं जोड़ती, और normalization errors को छिपाती है
  लेखक UUID इस्तेमाल करने की वजह “duplicate prevention” बताता है, लेकिन वह वजह नहीं है। हर database की हर table में हर row के पास कम-से-कम एक key होनी चाहिए, यानी columns का ऐसा set जो उस row को uniquely identify करे
  जिस database में ऐसी key नहीं है, उसमें duplicate information होती है और वह logical inconsistency के प्रति संवेदनशील होता है। जिस database में ऐसी key पहले से है, वहाँ surrogate key की ज़रूरत नहीं होती। अगर natural key पहले से मौजूद है और enforce भी की जा रही है, तो “performance के लिए ज़रूरी है” वाला दावा extra cost और अनावश्यक complexity का मतलब रखता है, इसलिए उसका प्रमाण होना चाहिए

  • हो सकता है मेरी कल्पना सीमित हो, लेकिन real-world से आए data को संभालते समय असली natural key ढूँढना ज़्यादातर मुश्किल होता है
    जो value ऊपर से unique लगती है, वह अक्सर उम्मीद जितनी unique नहीं निकलती, या जिसे immutable माना गया था, उसे आखिरकार बदलना पड़ता है
    दूसरी तरफ surrogate key इस्तेमाल करने पर मैं अपने system के भीतर identity को define कर सकता हूँ, बिना इस पर निर्भर हुए कि किसी और ने identifiability को कैसे define किया है, या अक्सर ठीक से define भी नहीं किया है
    अपवाद हैं। अगर पूरा model मैंने define किया है, और data तथाकथित real world से नहीं आता, तो natural key ज़्यादा समझ में आती है। लेकिन जब ऐसे schema design करने हों जो ऐसे real-world data को रखते हैं जो शायद कभी पूरी तरह normalized न रहा हो, तब surrogate key अक्सर उपयोगी होती है
  • monotonic increasing integer surrogate key की तुरंत दिखने वाली practical utility काफ़ी होती है
    किसी भी row को integer से refer किया जा सकता है, जिससे access बहुत सरल हो जाता है, और इंसानों के लिए उसे याद रखना और query में लिखना भी आसान होता है
    अगर वह monotonic increasing है, तो वह अपने-आप में information भी रखती है। वह redundant information है, लेकिन यह सच है
    lookup speed भी optimize हो जाती है। B-tree, bitmap वगैरह से indexing के लिए यह लगभग सबसे अनुकूल स्थिति है
    मुझे लगता है लोग UUID ज़्यादातर भ्रम की वजह से इस्तेमाल करते हैं। आम तौर पर तर्क यह होता है कि key को obfuscate और unguessable बनाया जाए, लेकिन इस उद्देश्य के लिए अलग identifier रखने के बजाय primary key पर ही इसे क्यों थोपना चाहिए, यह समझने की कोशिश मैंने छोड़ दी है
  • “duplicate prevention” वाली अभिव्यक्ति ब्लॉग पोस्ट में आती ही नहीं। दरअसल लेख में यह बताया ही नहीं गया कि UUID क्यों इस्तेमाल किया जाता है

• UUID version 7 में आगे की तरफ 48-bit timestamp होता है, इसलिए यह इस तरह random distribution नहीं बनाता। इसलिए excessive paging और rebalancing भी कम होंगे

  • सही है। लेख में UUID7 ही लिया गया है

• क्या लोग सच में UUID को primary key की तरह इस्तेमाल कर रहे हैं? जब UUID की ज़रूरत हो, तो उसे secondary key की तरह रखने के बजाय ऐसा करने का फायदा क्या है, यह जानना चाहूँगा

  • scale-out की वजह से। अगर बहुत-से computers और दुनिया भर के कई data centers में high speed पर unique ID distributed तरीके से generate करनी हो, जैसे S3 upload में, तो आप single increasing integer पर lock नहीं लगाना चाहेंगे। lock को globally synchronize करना धीमा होता है
    GUID और UUID संरचनात्मक रूप से इसी समस्या का समाधान करते हैं
    v1 और v6 machine ID और timestamp को encode करते हैं, इसलिए वे per-machine namespace वाले auto-increment integer के क़रीब होते हैं
    बहुत-से लोग यह मान लेते हैं कि UUID random होता है। भ्रम यहीं से पैदा होता है। यह सिर्फ v4 पर लागू होता है, और दुर्भाग्य से v4 चुनने की एक लागत भी होती है
    data को व्यवस्थित रखने या performance/collision guarantees के लिए v3, v5, v7 जैसी कुछ हद तक determinism वाली चीज़ अक्सर चाहिए होती है
  • अगर random UUID या uniformly distributed random value को secondary key में भी रखें, तब भी insert धीमे होंगे। भले वह clustered index न हो, उस index के B-tree में फिर भी random inserts होते रहेंगे