GitLab के Postgres स्कीमा डिज़ाइन पर मेरे नोट्स (2022)

 (shekhargulati.com)
1 पॉइंट द्वारा GN⁺ 2024-02-18 | 1 टिप्पणियां | WhatsApp पर शेयर करें

GitLab Postgres स्कीमा डिज़ाइन पर मेरे नोट्स

  • GitLab के Postgres स्कीमा को देखकर, अपनी डिज़ाइन की तुलना करना और GitLab की schema definitions से best practices सीखना।
  • GitLab एक ओपन-सोर्स DevOps प्लेटफ़ॉर्म है, GitHub का विकल्प और self-hosting के लिए उपलब्ध है।

सही Primary Key प्रकार का उपयोग

  • जब डेटाबेस छोटा होता है तब यह बड़ा मुद्दा नहीं लगता, लेकिन स्केल बढ़ने पर primary key storage space, write speed और read speed को प्रभावित कर सकता है।
  • GitLab के पास 573 tables हैं: इनमें से 380 tables में bigserial primary key type है, 170 में serial4, और बाकी 23 में composite primary key है।

Internal और External ID का उपयोग

  • अपनी internal primary key को बाहर expose न करना अच्छा practice है।
  • GitLab issues, ci_pipelines, deployments, epics जैसी tables में internal ID (id) और external ID (iid) दोनों का उपयोग करता है।

text data type और CHECK constraint का उपयोग

  • GitLab schema में character varying(n) और text दोनों हैं, लेकिन text का उपयोग ज्यादा है।
  • text में length limit नहीं होती, इसलिए लंबाई की सीमाएँ CHECK constraints से define की जाती हैं।

Naming conventions

  • सभी tables plural form में हैं और namespace देने के लिए module prefix use किया गया है।
  • table और column नाम snake_case format का पालन करते हैं।

Timestamp में time zone का उपयोग

  • GitLab दोनों उपयोग करता है: timestamp with timezone और timestamp without timezone।
  • सिस्टम operations के लिए timestamp without timezone और user actions के लिए timestamp with timezone use होते हैं।

Foreign key constraints

  • GitLab अधिकांश tables में foreign key constraints use करता है, लेकिन audit_events, abuse_reports, web_hooks_logs, spam_logs जैसी कुछ tables में नहीं।

बड़े tables की partitioning

  • GitLab query performance सुधारने के लिए बड़े हो सकने वाले tables को partition करता है।

Trigrams और gin_trgm_ops के साथ LIKE search use cases को support करना

  • GitLab efficient खोज के लिए GIN (Generalized Inverted Index) index का उपयोग करता है।

jsonb का उपयोग

  • GitLab schema कई tables में jsonb डेटा type का उपयोग करता है।

अन्य tips

  • बदलने योग्य tables में updated_at जैसी audit fields रखी जाती हैं, जबकि immutable log tables में नहीं।
  • Enums को character varying के बजाय smallint में store करके space बचाया जाता है।

GN⁺ की राय:

  • GitLab का स्कीमा डिज़ाइन DB design के लिए अच्छे insights देता है और बड़े पैमाने पर चलने वाले systems के लिए schema optimization पर महत्वपूर्ण lessons देता है।
  • GitLab ओपन-सोर्स होने की वजह से, ये schema design निर्णय अन्य developers के लिए practical examples बनते हैं जिन्हें अपने projects में apply किया जा सकता है।
  • GitLab schema से सीख मिलती है कि datatype चुनना, indexing strategy, partitioning और foreign key constraints का सही उपयोग जैसे decisions सीधे DB performance और maintainability को प्रभावित करते हैं।

संबंधित पढ़ाई

1 टिप्पणियां

 
GN⁺ 2024-02-18
Hacker News की राय

  • मुझे समझ नहीं आता कि primary key को बाहर expose न करने की प्रथा क्यों ज़रूरी है। अगर request को वैसे भी authenticate होना है, तो ID guess करने से रोकने की क्या वैल्यू है, यह भी समझ नहीं आता
    अगर सिर्फ guessed ID से authentication/authorization के बिना कोई उपयोगी काम किया जा सकता है, तो सिस्टम में कहीं और पहले से गंभीर गड़बड़ है, और schema में बेकार complexity जोड़ने के बजाय उसी हिस्से पर ध्यान देना चाहिए। competitors ग्राहक संख्या का अनुमान न लगा सकें, ऐसी competitive intelligence छिपाना जैसी बात कुछ वैल्यू रख सकती है, लेकिन लगता नहीं कि GitLab ने इसे बहुत बड़ा मुद्दा माना होगा। GitLab का id + iid वाला फैसला internal ID guessing रोकने से ज़्यादा query performance की ज़रूरतों से प्रेरित रहा होगा

    • सही है, लेकिन ID guess कर पाना किसी security vulnerability को भयानक बना सकता है या उसे कहीं ज़्यादा गंभीर कर सकता है
      अगर आपने users को UUID expose किया है, तो वही vulnerability होने पर भी attacker को UUID सही guess करना होगा, इसलिए यह बहुत कठिन हो जाता है और शायद secondary source की ज़रूरत पड़े। data leak हो भी जाए तो response करने का समय मिलता है और leaked volume का हिसाब भी लगाया जा सकता है। इसके उलट sequential ID हो तो समस्या तुरंत full-scale leak में बदल सकती है, और privacy regulator को mandatory report करना पड़ने वाला बड़ा incident बन सकती है। यह ऐसी defense in depth है जिसकी ज़रूरत नहीं पड़नी चाहिए, लेकिन असल में ऐसे भयानक software मौजूद हैं जो इसी तरीके से breach हुए हैं
    • लेख में भी जैसा आता है, यह security से ज़्यादा competitive intelligence के करीब है। simple auto-increment ID table के total record count या growth rate को उजागर कर देता है
      अगर issues table की primary key id expose कर दी जाए, तो किसी project में issue बनाते समय वह 1 से शुरू नहीं होती, इसलिए GitLab में कुल कितने issues हैं इसका अनुमान आसानी से लगाया जा सकता है
    • security theater वाला expression बहुत ज़्यादा इस्तेमाल होता है। security कई layers से बनी हो सकती है और होनी भी चाहिए; authentication जैसी एक layer टूट जाए तो बाकी तक आसानी से पहुंच नहीं होनी चाहिए
      बेशक, अगर guessed ID मात्र से authentication/authorization के बिना कुछ हो सके तो यह बड़ा issue है। लेकिन उस point पर अगर कोई दूसरी layer नहीं है, तो game पहले ही खत्म है। bugs पहले से सूचना देकर नहीं आते, खासकर subtle bugs तो और भी नहीं। जब ऐसा bug सामने आए और आपने IDs को कम से कम unguessable बना रखा हो, तो आप शुक्र मनाएंगे कि सिस्टम के सभी user accounts तक आसानी से access होने वाली स्थिति से बच गए
    • access control में भी bugs आते हैं। unguessable IDs ऐसे कुछ bugs को exploit करना बहुत कठिन बना देती हैं
      जाहिर है, पहले सही access control सुनिश्चित करने पर focus करना चाहिए, लेकिन unguessable IDs किसी भयानक आपदा और बाल-बाल बचे incident के बीच फर्क बना सकती हैं। अगर UUID सही fit न हो, तो auto-increment database ID इस्तेमाल करके उसे encrypt करने का तरीका भी संभव है, और उचित software layer हो तो encrypted IDs भी लगभग automatically काम करती हैं
    • शब्दों का मामूली फर्क है, लेकिन यह आम तौर पर company के internal data use को कहा जाने वाला “business intelligence” नहीं, बल्कि competitive intelligence कहना ज़्यादा सही लगता है। https://en.wikipedia.org/wiki/Competitive_intelligence

  • 12.8 करोड़ public repositories में से अधिकांश दूसरी repositories के forks हैं, और सिर्फ main repository में pull request बनाने के लिए मौजूद हैं, इसलिए जब तक गलती न हो, उनमें issues शायद नहीं होंगे
    छोटे toy projects या जल्दी abandon हो गए projects में भी issues न होने या बहुत कम होने की संभावना है। सैकड़ों-हज़ारों issues वाले projects निश्चित रूप से होंगे, लेकिन 12.8 करोड़ repositories का overall average काफी कम होगा, इसलिए 2 अरब limit के नीचे रहने की संभावना है। फिर भी, उस table में 4-byte type, सटीक कहें तो 31 bits, इस्तेमाल करना github.com समेत कुछ organizations के लिए time bomb है—इससे सहमत हूं

    • अभी भी repositories 362,107,148 और unique issues/pull requests 818,516,506 हैं, इसलिए अभी limit के नीचे हैं
      https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...
    • मुझे लगता है GitHub के Rails से दूर जाने के फैसले पर ActiveRecord की एक बड़ी कमी, यानी composite primary keys support न होना, भी कुछ असर डालता है
      PRIMARY KEY(repo_id, issue_id) जैसी basic requirement ActiveRecord के अंदर बेवजह complicated हो जाती है, और single primary key column मांगने वाले ActiveRecord के हिसाब से unique key और अलग primary key साथ में इस्तेमाल करने वाला workaround चाहिए होता है। UUID primary key भी workaround जैसी लगती है, लेकिन (repo_id, issue_id) pair की unique constraint फिर भी चाहिए, जिससे database size और overhead बढ़ता है। व्यापक रूप से देखें तो Ruby on Rails की single model/controller/view layer पर आधारित monolithic MVC architecture भी application बड़े होने पर scalability और maintainability problems पैदा करती है, और मेरे हिसाब से MVC modular या component-based architecture में बेहतर fit बैठता है
    • जानना चाहूंगा कि क्या किसी को पक्का पता है कि GitLab Cloud multi-tenant database इस्तेमाल करता है, या user/customer/organization-wise databases
      self-hosting और cloud दोनों देने वाले products अक्सर per-customer databases पसंद करते थे। वजह यह कि hosting type चाहे जो हो, समान queries इस्तेमाल करने के लिए codebase का shared हिस्सा बहुत सरल हो जाता है। per-customer database हो तो ऐसे usage limits के पास पहुंचना लगभग नहीं होगा, और अगर पहुंचता है तो self-hosting ज़्यादा उपयुक्त होगा
    • वह time bomb 11-second migration से defuse हो जाने वाला bomb है
    • primary key को int से bigint में migrate करना संभव है। थोड़ी तैयारी और custom code चाहिए, लेकिन इसे zero-downtime के साथ किया जा सकता है
      हम बड़े migrations manage करने के लिए मोटे तौर पर इसी procedure को follow कर रहे हैं, और थोड़ा modify करके इस्तेमाल कर रहे हैं: http://zemanta.github.io/2021/08/25/column-migration-from-in...
      foreign keys, indexes और constraints पूरी process को कठिन बनाते हैं, लेकिन असंभव नहीं। मेरे case में data migration में कुछ घंटे लगे, लेकिन उसे तेज़ होना ज़रूरी नहीं था। मेरी जानकारी में GitLab के पास post-upgrade tasks चलाने वाला tool है, ताकि version upgrade के दौरान कहीं भी यह काम कर सके

  • UUID कॉलम के storage size वाली बात बहुत मजबूत नहीं लगती। अगर table में 5 और columns हैं, तो 128-bit बनाम 64-bit का फर्क बहुत बड़ा नहीं है
    ज्यादा अहम चिंता performance है। UUIDv4 व्यापक रूप से supported है, लेकिन पूरी तरह random होने के कारण index performance के लिए ideal नहीं है। UUIDv7[0] Snowflake[1] के करीब है, इसलिए उसमें temporal locality होती है, लेकिन implementations अभी उतनी व्यापक नहीं हैं। एक अलग approach यह भी है कि bigserial इस्तेमाल करें और key को encrypt करें: https://github.com/abevoelker/gfc64
    लेकिन इस तरीके में 1) secret value rotate नहीं की जा सकती और 2) एक बार leak हो जाए तो कोई भी table size का Fermi estimate लगा सकता है। public ID और internal ID को अलग करना झंझट भरा है, और अगर public ID UUIDv4 है तो performance भी sacrifice होती है। मेरे हिसाब से UUIDv7 सबसे ज्यादा शर्तें पूरी करने वाला समाधान है
    [0]: https://uuid7.com/
    [1]: https://en.wikipedia.org/wiki/Snowflake_ID

    • समस्या सिर्फ उस एक column के size की नहीं है; जहाँ-जहाँ वह id foreign key के रूप में इस्तेमाल होता है और उन foreign key columns के लिए जो index size चाहिए, वह भी मुद्दा है
      user ID जैसी value के बारे में सोचें, जिसे पूरी database में दर्जनों या सैकड़ों foreign keys reference कर सकती हैं
    • समस्या यह है कि बाकी 5 columns indexed नहीं हैं
      database performance के तीन चरण होते हैं। 1) index और data दोनों memory में fit हो जाते हैं। 2) index memory में fit हो जाता है, लेकिन data नहीं। 3) index और data दोनों memory में fit नहीं होते। नंबर 1 अच्छा है, लेकिन अगर ऐसा नहीं है तो किसी भी कीमत पर नंबर 2 को बचाए रखना चाहिए। index size दोगुना होना इसे और मुश्किल बना देता है
    • database की primary key को typedef void* जैसे basic pointer की तरह समझा जा सकता है। उसका size memory और disk usage, throughput bottlenecks, और joins व lookups के सबसे अंदरूनी loops में keys compare करने में लगने वाले CPU time तक, पूरी performance को प्रभावित करता है
      जब x86-64 CPU नए आए थे, 64-bit pointers पर shift का performance impact इतना बड़ा था कि x32/ilp32 बनाया गया, और .NET में आज भी “prefer 32-bit” default रखने की वजह भी यही है। database primary key के रूप में 128-bit UUID इस्तेमाल करना भयानक गलती है
    • UUIDv7 भी कोई silver bullet नहीं है। कई मामलों में resource का creation time leak नहीं करना चाहते
      उदाहरण के लिए, हो सकता है आप कोई video public release से एक महीने पहले upload करें, लेकिन viewers को यह पता न चलने देना चाहें
    • इस approach के दूसरे variants भी हैं: https://pgxn.org/dist/permuteseq/
      URLs, emails आदि में दिखाते समय value को encrypt करना भी संभव है: https://wiki.postgresql.org/wiki/Pseudo_encrypt
      इससे sequential index के कई फायदे बने रहते हैं और key बदलना भी संभव होता है। हालांकि key बदलने से bookmarks टूट जाते हैं, पुराने emails में भेजे गए links invalid हो जाते हैं, और असर व्यावहारिक रूप से हर नाम बदल देने जैसा ही रहता है

  • छोटी-सी nitpick है, लेकिन text बनाम varchar section पर बात करना चाहूँगा
    लेखक पहले ऐसी performance difference साबित करने के लिए बहुत कुछ लिखता है जो मौजूद ही नहीं है, और फिर निष्कर्ष निकालता है कि “दोनों types के बीच बड़ा performance difference नहीं है।” यह विषय बहुत पहले settle हो चुका है, और बात “बड़ा नहीं” की नहीं, बल्कि “कोई difference नहीं” की है। PostgreSQL wiki[1] साफ कहता है कि कोई बहुत अच्छी वजह न हो तो text इस्तेमाल करें, और docs[2] भी कहते हैं कि “कई उद्देश्यों के लिए character varying, text के ऊपर domain की तरह behave करता है,” और हरे Tip box में कहते हैं कि “इन तीनों types के बीच कोई performance difference नहीं है।” इसलिए GitLab का ज्यादातर text इस्तेमाल करना इस बात का संकेत लगता है कि उन्होंने docs पढ़े और किसी अधकचरे “portable” schema के बजाय PostgreSQL के हिसाब से schema design किया
    [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...
    [2] https://www.postgresql.org/docs/current/datatype-character.h...

    • असल में जब stored string length में बदलाव के हिसाब से schema migrate करना पड़े, तब काफी performance difference आता है
      varchar(300) को varchar(200) में बदलने के लिए हर row को फिर से लिखना पड़ता है, लेकिन text column की constraint update करना मूल रूप से लगभग free है और सिर्फ यह check करने के लिए full table scan चाहिए कि मौजूदा values नई constraint satisfy करती हैं या नहीं। लेख में भी कहा गया है कि text type और CHECK constraint इस्तेमाल करने से, length check होने पर character varying या varchar(n) की तुलना में schema evolution आसान होता है

  • foreign keys महंगी होती हैं—यह बात अक्सर दोहराई जाती है, लेकिन benchmark कम ही मिलते हैं
    गलत implementation के कई तरीके हैं, लेकिन integrity तो stack में कहीं न कहीं enforce हो ही रही होती है। इसे फिर से implement करने के बजाय database का फायदा उठाने के लिए knowledge और experimentation चाहिए, और आम तौर पर यह बड़े हादसों से बचाता है

  • सोच रहा हूँ कि क्या किसी ने GitLab और GitHub के performance difference को summarize किया है या इस पर ध्यान दिया है
    दोनों Rails-based applications हैं, लेकिन overall page load time में GitLab, GitHub के मुकाबले बेहद खराब लगता है

    • कुछ साल पहले जब मैंने GitLab इस्तेमाल किया था, बड़े pull requests में client-side performance issues बहुत ज्यादा थे। GitHub भी ideal नहीं है, लेकिन फिर भी acceptable level पर handle कर लेता है
    • GitHub से तुलना करना Chrome और दूसरे browsers, यहाँ तक कि Chromium-based browsers, की तुलना करने जैसा है
      Chrome और GitHub users को नुकसान पहुँचाना पड़े तब भी हर तरह की tricks अपनाएँगे। उदाहरण के लिए, एक बार company GitHub में merge diff खोलकर Ctrl F से search किया, result न मिलने पर next पर जाता रहा और Git history manually खंगालता रहा; 100वें diff पर जाकर पता चला कि सबसे important file गहराई में छिपी हुई थी। शायद इसलिए कि किसी के लिए page load metrics meet करना और promotion पाना आसान था
    • पिछले साल की दो outages को छोड़ दें तो GitHub generally stable और आम तौर पर काफी fast है। ऐसा न होता तो मैं keyboard shortcuts इस्तेमाल नहीं करता
      GitLab की culture और performance को हल्के में लेने को समझने में मदद कर सकने वाली एक former developer की post यहाँ है: https://news.ycombinator.com/item?id=39303323
      मैं GitLab इतना इस्तेमाल नहीं करता कि performance issues खुद महसूस कर सकूँ, लेकिन मुझे लगता है यह post मददगार हो सकती है

  • CI variables CI_PIPELINE_IID और CI_MERGE_REQUEST_IID में जो extra I है, उसका मतलब क्या है, यह हमेशा सोचता था
    अंदाजा था कि यह database-related choice होगी, और यह लेख इसकी पुष्टि करता है

  • “1 quintillion, 1,000,000,000 billion के बराबर है” देखकर यह काफी अजीब लगता है कि हम आम तौर पर सिर्फ int32 और int64 के बीच ही चुनते हैं। लगभग 1 trillion cardinality support करने वाला 5-byte integer type होना चाहिए

    • अगर values को tightly pack नहीं कर रहे हैं, तो power of 2 न होने वाला size चुनने का कोई मतलब नहीं है

  • auto-increment ID न इस्तेमाल करना reasonable हो सकता है, लेकिन internal और external use के लिए 2 IDs रखने का फायदा मुझे ठीक से समझ नहीं आता
    columns और indexes की संख्या बढ़ती है, हमेशा पहले lookup करना पड़ता है, और ऐसा कोई security scenario भी दिमाग में नहीं आता जहाँ internal key बदलें लेकिन external key न बदलें। क्या मैं कुछ miss कर रहा हूँ?

    • project-level पर कुछ करते समय आपके पास वैसे भी जरूरी information पहले से होती है। साथ ही, हर project के issues का 2,700,300,571,325 जैसे number से शुरू होने की बजाय 1 से शुरू होना users के लिए ज्यादा friendly है

  • कहा जाता है कि PostgreSQL native UUID v4 type को bigserial की जगह इस्तेमाल करने से table size 25% बढ़ जाता है और insertion rate bigserial के 25% तक गिर जाता है; सोच रहा हूँ UUIDv4 इतना खराब क्यों है
    UUID तो बस 128-bit number नहीं है? क्या generation cost बहुत expensive है, या असल में क्या हो रहा है?

    • UUIDv4 पूरी तरह random होता है, और B-tree index “right-skewed” values की उम्मीद करता है जिनका reasonable order हो
      इसलिए UUIDv4 column की indexing धीमी हो जाती है, और यही UUIDv6 और UUIDv7 के development की motivation बना
    • size में 25% increase सही है, लेकिन यह per row 8 bytes की छोटी और predictable linear increase है। row के बाकी data की तुलना में यह बहुत चिंता की बात नहीं है
      बड़ा issue insertion rate है। UUID के मामले में insertion rate available RAM की मात्रा से limited होता है। auto-increment integers में ऐसा नहीं है। integers time के साथ correlated होते हैं, लेकिन UUID4 random होता है, इसलिए scale बढ़ने पर performance characteristics fundamentally बदल जाते हैं। छोटी tables में insertion penalty लगभग negligible होती है, लेकिन जब B-tree index size memory limit तक पहुँचता है, तो PostgreSQL पूरे UUID B-tree को memory में नहीं रख पाता और disk page replacement पर निर्भर करता है। auto-increment integers में समय के लिहाज से पास-पास वाली rows वही index pages इस्तेमाल करती हैं, इसलिए समान load में disk hit करने की जरूरत नहीं पड़ती। इस scale पर पहुँचने पर फर्क कोई constant 25% slowdown नहीं, बल्कि 25x performance cliff होता है, और schema migration को छोड़ दें तो RAM ज्यादा खरीदने के अलावा कोई उपाय नहीं बचता
    • मुझे लगता है वजह B-tree है। B-tree और pages तब बेहतर काम करते हैं जब ज्यादातर writes सिर्फ last page पर हों
      UUID बहुत सारी unordered writes पैदा करता है, जिससे page bloat होता है
    • अगर sort order randomly distributed हो, तो B-tree की cache locality खराब हो जाती है। insertions last page पर नहीं जाते, बल्कि इधर-उधर फैल जाते हैं
      batch insertion की locality भी बाद में lookup करते समय खराब हो जाती है, जिससे related records को randomly ढूँढना पड़ता है। आखिरकार cost insert time और बाद के select time, दोनों पर चुकानी पड़ती है