कंप्यूटरों के बीच SQLite डेटाबेस को तेज़ी से कॉपी करने का तरीका

 (alexwlchan.net)
1 पॉइंट द्वारा GN⁺ 2025-05-02 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • बड़े SQLite डेटाबेस को remote server से local में ले जाते समय मूल .db फ़ाइल की जगह gzip-compressed SQL dump कॉपी करने से ट्रांसफ़र डेटा काफी कम हो जाता है
  • फ़ाइल के बड़े होने की मुख्य वजह index होते हैं, जो query को तेज़ करने के लिए table data को दोहराकर store करते हैं और disk usage बढ़ाते हैं
  • SQLite का .dump डेटाबेस को SQL statements के text में बदल देता है, और index को वास्तविक data की जगह CREATE INDEX की एक लाइन के रूप में दिखाता है, जिससे duplicate transfer से बचा जा सकता है
  • उदाहरण डेटाबेस में मूल फ़ाइल 3.4GB, dump text 1.3GB, और gzip-compressed फ़ाइल 240MB थी; compressed dump मूल फ़ाइल से 14 गुना छोटा था
  • कॉपी करने से पहले dump फ़ाइल को स्थिर रूप में बना लेने पर ट्रांसफ़र के दौरान मूल फ़ाइल बदलने से होने वाली database disk image is malformed त्रुटि की संभावना कम हो जाती है

SQLite dump को compress करके कॉपी करना

  • छोटे SQLite डेटाबेस को rsync से सीधे कॉपी करना पर्याप्त हो सकता है
    • उदाहरण: rsync --progress username@server:my_remote_database.db my_local_database.db
  • डेटाबेस बड़ा होने पर direct copy धीमी और अस्थिर हो सकती है
    • home internet पर 250MB डेटाबेस को web server से डाउनलोड करने में लगभग 1 मिनट लगता है
    • कई GB आकार के डेटाबेस में इंतज़ार और बढ़ जाता है
  • आकार बढ़ने की सबसे बड़ी वजह index हैं
    • index query speed को बहुत बढ़ाते हैं, लेकिन डेटाबेस फ़ाइल का आकार भी बढ़ाते हैं
    • एक डेटाबेस में एक खास index अकेले disk size का आधा हिस्सा ले रहा था
    • index अनोखा data रखने से ज़्यादा दूसरे tables के data को दोहराकर lookup को तेज़ बनाते हैं
  • SQLite पूरे डेटाबेस को text file के रूप में dump कर सकता है
    • कमांड: sqlite3 my_database.db .dump > my_database.db.txt
    • बनी हुई फ़ाइल CREATE TABLE, INSERT INTO, CREATE INDEX जैसी SQL statements से बनी होती है
    • index को वास्तविक index data की जगह CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]); जैसे creation command के रूप में store किया जाता है
  • dump फ़ाइल को फिर से SQLite डेटाबेस में restore किया जा सकता है
    • कमांड: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
  • SQL dump में दोहराव ज़्यादा होता है, इसलिए gzip compression बहुत प्रभावी रहता है
    • उदाहरण: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
    • आकार तुलना:
      • मूल SQLite डेटाबेस: 3.4GB
      • text dump: 1.3GB
      • gzip-compressed text: 240MB
    • gzip-compressed फ़ाइल मूल SQLite डेटाबेस से 14 गुना छोटी थी

वास्तविक प्रक्रिया और reliability का लाभ

  • नया तरीका यह है कि remote server पर compressed dump बनाया जाए, सिर्फ वही फ़ाइल डाउनलोड की जाए, और फिर local पर डेटाबेस को दोबारा बनाया जाए
  • कॉपी करने की प्रक्रिया इस प्रकार है
    • server पर gzip-compressed text फ़ाइल बनाना: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
    • local में कॉपी करना: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
    • server की अस्थायी compressed फ़ाइल हटाना: ssh username@server "rm my_remote_database.db.txt.gz"
    • local पर decompress करना: gunzip my_local_database.db.txt.gz
    • local डेटाबेस को फिर से बनाना: cat my_local_database.db.txt | sqlite3 my_local_database.db
    • local text फ़ाइल हटाना: rm my_local_database.db.txt
  • compressed dump एक consistent copy source की तरह काम करता है
    • बड़े डेटाबेस को लंबे समय तक कॉपी करते हुए अगर मूल फ़ाइल अपडेट हो जाए, तो नतीजे वाली फ़ाइल का शुरुआती हिस्सा update से पहले का और बाद का हिस्सा update के बाद का हो सकता है
    • ऐसी फ़ाइल local में खोलने पर database disk image is malformed त्रुटि आ सकती है
  • कॉपी से पहले text dump बना लेने पर rsync जिस फ़ाइल को भेज रहा है, वह बीच में नहीं बदलती
    • इससे पूरी और consistent text फ़ाइल कॉपी होने की संभावना बढ़ जाती है
  • अगर आप बड़े SQLite डेटाबेस को बार-बार स्थानांतरित करते हैं, तो इस तरीके से डाउनलोड समय घटाया जा सकता है और कॉपी की reliability बढ़ाई जा सकती है

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

1 टिप्पणियां

 
GN⁺ 2025-05-02
Hacker News की राय

  • चल रही और अपडेट्स पा रही database file को वैसे ही copy करेंगे तो जाहिर है वह corrupt हो सकती है
    SQLite database को सुरक्षित रूप से replicate करने के लिए Litestream है: https://github.com/benbjohnson/litestream

    • Litestream साधारण SFTP पर भी काम करता है, इसलिए SSH से access किए जा सकने वाले लगभग किसी भी UNIX endpoint पर database replication stream की जा सकती है
      पसंदीदा service तो है, लेकिन कोई भी SFTP server चलेगा
      [1] https://github.com/benbjohnson/litestream/issues/140
      [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
    • “लाज़मी तौर पर” corrupt होगा, इस बात के अपवाद हैं। Btrfs या ZFS पर अगर file या subvolume को atomically copy किया जाए, तो ACID database या LSM tree के मामले में सबसे खराब स्थिति में केवल rollback होगा
      बेशक अगर कई files हों, तो उन्हें subvolume में बांधना होगा ताकि सब एक ही transaction में copy हों, और सिर्फ cp --reflink=always काफी नहीं है
      SIGSTOP से process रोकना भी मिलता-जुलता परिणाम दे सकता है, लेकिन मैं उस पर निर्भर नहीं रहना चाहूंगा
    • built-in .backup command भी एक official tool है, जिसका मकसद running database का snapshot version बनाकर उसे copy करने देना है
    • अपने system पर मैं Litestream अच्छे से इस्तेमाल कर रहा हूं, लेकिन built-in tools से मिलते-जुलते काम को manually करने का तरीका काफी विस्तार से बताने वाला document भी अच्छा है: https://litestream.io/alternatives/cron/
    • Litestream सच में शानदार है। मैं इसे container level पर SQLite backup और recovery के लिए आजमाने की योजना बना रहा हूं, जैसा उस ex-Google व्यक्ति ने किया था जिसने एक छोटा KVM startup बनाया था और जिसकी छुट्टी के दौरान warehouse में पानी भर गया था
      अगर मुझे ठीक याद है तो उसने एक बेहतरीन guide लिखी थी, लेकिन उसे ढूंढ पाने की संभावना लगभग 0 है। अगर किसी को यह reference पता हो तो link डाल दे, अच्छा होगा

  • computers के बीच database copy करने की बात है, लेकिन ऐसा लगता है जैसे सिर्फ circle बना दिया और बाकी उल्लू छोड़ दिया
    जैसा दूसरों ने कहा है, incremental rsync कहीं तेज होगा, लेकिन ज्यादा खटकने वाली बात यह है कि SQL statements भेजना database भेजने से तेज है, ऐसा कहते हुए उस SQL statements को execute करना पड़ेगा वाली बात पूरी तरह छोड़ दी गई है। फिर /optimize/ भी चलाना होगा और /vacuum/ भी
    अभी मेरे पास एक ऐसी स्थिति है जहां CSV files से database को “incrementally rebuild” करना पड़ता है, और मेरे मामले में शुरू से फिर बनाना ज्यादा optimal है। फिर भी काफी optimize करने के बावजूद खाली in-memory database में batch insert करने और indexes बनाने में ही 30 मिनट लगते हैं

    • उम्मीद है आपने https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite देखा होगा
      SQLite3 में तेजी से insert करने के तरीकों का बहुत अच्छा सारांश है
    • कोई भी optimization हो, यहां अहम बात यह है कि bottleneck कहां है। मूल उदाहरण में bandwidth bottleneck लगती है और CPU/disk I/O पर्याप्त लगता है
      कहा गया था कि 250MB database download करने में 1 मिनट लगता है, जबकि मैंने 1Gbps fiber connection की वजह से company server से 2GB का SQLite test database 15 सेकंड में खींच लिया
    • 30 मिनट लंबा लगता है। data बहुत ज्यादा है क्या? मैं बहुत सारे JSON data से SQLite database bootstrap करने पर काम कर रहा हूं, और values को list में इकट्ठा करके 10,000-10,000 के batch में insert करने पर ऐसा अच्छा performance sweet spot मिला कि लाखों rows भी कुछ ही मिनटों में डाल सकते हैं
      Bloom filter और LRU cache जैसी तरकीबें लगानी पड़ीं, लेकिन अब 6GB database करीब 20 मिनट में बना सकता हूं

  • SQLite में इस काम के लिए official tool है: https://www.sqlite.org/rsync.html
    यह page level पर काम करता है
    “protocol में replica हर page का cryptographic hash original की तरफ भेजता है, और original उन pages की पूरी content वापस भेजता है जिनके hash match नहीं करते”

    • सही है, लेकिन अफसोस कि SQLite team उस tool को उस autotools tarball में शामिल नहीं करती जिसे ज्यादातर distros और brew SQLite packaging के लिए इस्तेमाल करते हैं
      यह tool इस्तेमाल करना है तो खुद compile करने के अलावा कोई रास्ता नहीं है

  • हाल में release हुई sqlite_rsync utility SQLite database की internal structure के हिसाब से optimized rsync algorithm version इस्तेमाल करती है। यह internal data pages को efficiently compare करती है और फिर सिर्फ बदले हुए या missing pages को sync करती है
    लेख की tips भी अच्छी हैं, लेकिन अब built-in utility इस्तेमाल करना ज्यादा आसान है :)
    मैंने इसके काम करने का तरीका blog में विस्तार से लिखा है: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

    • sqlite3_rsync अब rsync.net platform में built-in है
      ssh user@rsync.net sqlite3_rsync … blah blah …
      इसे पिछले हफ्ते ही add किया गया है, इसलिए अभी सभी regions में deploy नहीं हुआ है, लेकिन शुरुआती users ने कहा कि यह ठीक वैसा ही चला जैसा उम्मीद थी
    • sqlite_rsync सिर्फ WAL mode में इस्तेमाल किया जा सकता है। WAL mode की एक अतिरिक्त constraint यह है कि database file local disk पर होनी चाहिए
      ज्यादातर मामलों में यह स्वाभाविक रूप से ऐसा ही होगा, लेकिन जहां यह संभव नहीं है, वहां यह utility इस्तेमाल नहीं कर सकते
    • अगर बार-बार sync करते हैं तो bandwidth की वजह से page size बढ़ाना चाहिए

  • यह थोड़ा आश्चर्यजनक था कि rsync में मिलने वाली ट्रांसफर के दौरान compression की कोशिश नहीं की गई
    -z, --compress ट्रांसफर के दौरान file data को compress करता है, और --compress-level=NUM compression level को स्पष्ट रूप से तय करता है
    शायद gzip से compress करके फिर भेजना तेज हो सकता है, लेकिन सिर्फ एक flag से transfer बेहतर किया जा सकता है, यह अच्छी बात है

    • अगर corruption की समस्या का ज़िक्र किया गया था, तो -z के साथ sqlite3_rsync(https://sqlite.org/rsync.html) इस्तेमाल करना बेहतर है
      यानी SQLite transactions और WAL को समझने वाले rsync में transfer-time compression भी जुड़ जाता है
    • मुख्य बात indexes को skip करना है, और यह compression से पहले होना चाहिए
      ऐसे काम करते समय dump को सीधे gzip में stream करता हूं। आम तौर पर intermediate file के बिना सीधे destination तक stream करने का तरीका भी मिल जाता है
      ऊपर से ऐसा करने पर destination पर compressed form में save होता है। अगर मकसद गरीब आदमी की replication नहीं, बल्कि backup है, तो खासकर यही सही है
    • मेरे हिसाब से compression सिर्फ slow network पर ही फायदेमंद है
    • यह जरूर करना चाहिए। compressed file पर rsync इस्तेमाल करने से rsync का मूल फायदा, यानी rolling checksum based delta transfer, पूरी तरह bypass हो जाता है
    • --remove-source-files इस्तेमाल किया होता तो दोबारा SSH में जाकर rm करने की जरूरत नहीं पड़ती

  • text file के रूप में save करना inefficient है। मैं SQLite database को VACUUM INTO से save करता हूं
    sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
    https://sqlite.org/lang_vacuum.html के अनुसार, INTO clause वाला VACUUM command running database की backup copy बनाने के लिए backup API का alternative है। फायदा यह है कि result backup database का size minimum हो जाता है, जिससे filesystem I/O कम हो सकता है

    • अच्छा तरीका है, लेकिन original में कही गई index problem हल नहीं करता। मुख्य idea slow link पर index data न भेजने का था, जबकि VACUUM INTO approach indexes को बनाए रखता है
      text file अपने-आप में inefficient हो सकती है, लेकिन gzip जैसे basic tools से भी अच्छी तरह compress हो जाती है। SQLite binary format भी उतना ही अच्छी तरह compress होता है या नहीं, यह पक्का नहीं है, पर संभव है
    • क्या वह तरीका indexes को preserve करता है? original में कहा गया था कि SQLite file बड़ी होने की वजह indexes हैं

  • DuckDB में यही काम करते हुए Parquet में export किया जा सकता है, जिससे data size text-based SQL statements की तुलना में single-digit multiplier तक छोटा हो जाता है। transfer भी तेज होता है और load भी तेज होता है
    https://duckdb.org/docs/stable/sql/statements/export.html

    • command line से यह ऐसे किया जा सकता है
      duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
      मेरे test database में यह gzip से compressed text SQL statements की तुलना में लगभग 20% छोटा था

  • SQLite में एक session extension है जो tables के set में changes को track करता है और पुराने version के SQLite database पर apply किए जा सकने वाले changeset/patchset बनाता है
    https://www.sqlite.org/sessionintro.html

    • मैंने अभी तक कोई SQLite binding नहीं देखी जो इसे support करती हो। इसलिए अगर आप application C में लिखने या language binding patch करने का इरादा नहीं रखते, तो इसकी उपयोगिता काफी कम हो जाती है
      अपने एक project में मैंने गरीब आदमी का session खुद implement किया था: सभी SQL statements और parameters को अलग database में record किया, फिर उसे sync करके replay किया। करीब 30GB के database के लिए, जिसमें रोज लगभग 0.1% ही बदलता है, यह काफी अच्छी तरह काम करता है
    • क्या इसे कभी इस्तेमाल किया है? documentation पढ़ी है, लेकिन ऐसा कोई नहीं सुना जो इस extension को सच में इस्तेमाल करता हो
    • SQLite में session या मिलते-जुलते तरीके से change data capture करने का built-in तरीका हो तो वाकई अच्छा होगा

  • अगर पुराने version से नए version में नियमित रूप से sync करते हैं, तो gzip के --rsyncable option से और optimize किया जा सकता है
    compression ratio लगभग 1% कम हो जाता है, लेकिन एक version से अगले version तक का difference compressed output के पूरे हिस्से में chain नहीं होता, बल्कि localized रहता है
    एक और तरीका यह है कि dump output को compress न करें, rsync को पुराने uncompressed dump और current dump का diff calculate करने दें, और फिर network पर भेजे जाने वाले changeset को ही compress करे। यानी rsync -z इस्तेमाल करें

  • लगता है इसे एक pipeline के रूप में handle किया जा सकता है
    ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

    • अगर SSH call में -oCompression=on या -C से SSH compression on करते हैं, तो gzip/gunzip शायद duplicate हो सकता है
    • मेरा भी पहला खयाल यही था। और यह भी काफी obvious लगता है कि इसमें pv भी होना चाहिए