Postgres में अक्सर होने वाली DB schema change की गलतियां

 (postgres.ai)
5 पॉइंट द्वारा GN⁺ 2024-04-29 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • Postgres schema migration में lock, table rewrite और replication lag ऑपरेशन संबंधी बाधा में बदल सकते हैं, इसलिए बड़े OLTP environment में इसका जोखिम खास तौर पर अधिक होता है
  • जोखिम खासकर उन बदलावों में केंद्रित होता है जो DEFAULT और NOT NULL को साथ जोड़ते हैं, CONCURRENTLY के बिना index बनाते हैं, column को तुरंत हटाते हैं, असुरक्षित type change करते हैं, या बिना validation के foreign key जोड़ते हैं — क्योंकि ये full scan और लंबे lock पैदा कर सकते हैं
  • PostgreSQL 11 से कुछ column add operations की लागत कम हुई है, लेकिन index के लिए CREATE INDEX CONCURRENTLY और foreign key के लिए NOT VALID के बाद VALIDATE CONSTRAINT जैसे steps अब भी ऑपरेशन पर प्रभाव कम करने के लिए जरूरी हैं
  • बड़े बदलावों को छोटे batches में बांटना चाहिए, और साथ ही read replicas, replication lag, dependent objects, और यह भी जांचना चाहिए कि क्या पुराने application instances अब भी उन columns को refer कर रहे हैं
  • production-scale data पर पहले से testing करनी चाहिए, और destructive operations केवल multi-step deployment तथा verified rollback plan के साथ ही करनी चाहिए

schema migration की बुनियादी समझ

  • यहां DB migration का मतलब DBMS migration नहीं, बल्कि DB schema change है
  • लक्षित बदलावों की तीन प्रमुख विशेषताएं हैं
    • version-controlled changes, जिनमें हर बदलाव का अपना unique identifier और automated apply process होता है
    • immutable changes, जिन्हें production में apply करने के बाद बदला नहीं जाता, बल्कि केवल नए changes जोड़े जाते हैं
    • incremental changes, जिनमें database schema चरणबद्ध तरीके से evolve होता है
  • फोकस मोबाइल और web applications जैसे OLTP use cases पर है, जहां 1 सेकंड से अधिक का query execution आम तौर पर बहुत धीमा माना जाता है
  • छोटे database और कम activity में कुछ समस्याएं आसानी से सामने नहीं आतीं, लेकिन लगभग 10TiB आकार और प्रति सेकंड 10⁴~10⁵ transactions के load पर ज्यादातर समस्याएं दिख सकती हैं
  • Database Lab Engine development और testing के लिए thin clones का उपयोग करता है, और 10TiB database को 10 सेकंड के भीतर clone करके deployment से पहले schema changes के जोखिम की जांच की जा सकती है
  • GitLab Migration Style Guide कई Postgres schema changes को automated deployment के साथ लागू करने के अनुभव पर आधारित एक उपयोगी reference है

column जोड़ना और table rewrite

  • DEFAULT और NOT NULL दोनों के साथ column जोड़ना, खासकर पुराने PostgreSQL versions में, बहुत जोखिमभरा है
    • PostgreSQL 11 से पहले पूरे table को rewrite करना पड़ता है
    • बड़े tables में इसमें घंटों या दिनों तक लग सकते हैं, और इस दौरान write lock लग सकता है
  • जोखिमभरा उदाहरण:
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • ज्यादा सुरक्षित तरीका यह है कि column add, data update और constraint add को अलग-अलग steps में किया जाए
    • पहले NOT NULL के बिना column जोड़ें
    • जरूरत हो तो existing rows को update करें
    • उसके बाद NOT NULL constraint जोड़ें
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • PostgreSQL 11 और उसके बाद, non-volatile DEFAULT value वाले column को जोड़ने पर अब table rewrite की जरूरत नहीं रहती

index बनाना और foreign key जोड़ना

  • CONCURRENTLY के बिना index बनाने पर standard index creation table पर exclusive lock ले लेता है
    • index बनने तक सभी writes और कुछ reads रुक सकते हैं
  • जोखिमभरा उदाहरण:
CREATE INDEX idx_users_email ON users(email);
  • live operation के दौरान CREATE INDEX CONCURRENTLY का उपयोग ज्यादा सुरक्षित है
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY की कुछ सीमाएं हैं
    • इसमें अधिक समय लग सकता है, लेकिन यह table access को block नहीं करता
    • इसे transaction block के अंदर उपयोग नहीं किया जा सकता
    • failure होने पर यह invalid index छोड़ सकता है, जिसे बाद में हटाना पड़ सकता है
  • बड़े table पर foreign key constraint को सीधे जोड़ने से existing data validation के लिए full table scan करना पड़ता है और लंबे lock लग सकते हैं
  • ज्यादा सुरक्षित तरीका है पहले NOT VALID के साथ constraint जोड़ना और फिर कम traffic वाले समय में validation चलाना
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

column हटाना और type बदलना

  • production में column को तुरंत delete करने से application errors हो सकते हैं, अगर application code अब भी उस column को refer कर रहा हो
  • column deletion को multi-step तरीके से करना चाहिए
    • पहले वह application code deploy करें जो उस column का उपयोग नहीं करता
    • तब तक प्रतीक्षा करें जब तक सभी पुराने application instances replace न हो जाएं
    • फिर अलग migration में column हटाएं
  • column type बदलना table rewrite या compatibility issues पैदा कर सकता है
    • इससे downtime, data loss, या application errors हो सकते हैं
  • समस्या पैदा करने वाले उदाहरण:
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • integer से bigint में बदलने के लिए नए column का उपयोग करते हुए multi-step process की जरूरत होती है
  • varchar की लंबाई कम करने से पहले data को जांचना चाहिए और यह भी देखना चाहिए कि बदलाव सच में जरूरी है या नहीं

बड़े बदलाव, replication, और dependent objects

  • ऐसे migrations से बचना चाहिए जो एक ही transaction में बहुत अधिक data modify करते हैं
    • lock contention और memory usage बढ़ता है
    • समस्या आने पर recovery time लंबा हो जाता है
    • replication lag बढ़ सकता है
  • बड़े data migrations को छोटे batches में बांटना ज्यादा सुरक्षित है
  • migration का read replicas और replication lag पर असर भी देखना चाहिए
    • बड़े migrations काफी replication lag पैदा कर सकते हैं
    • read replica performance पर असर पड़ सकता है
  • उन objects की भी जांच करनी चाहिए जो बदले जा रहे column या table पर depend करते हैं
    • views, functions, triggers जैसे dependent objects छूट जाएं तो chain failures हो सकते हैं या अतिरिक्त manual intervention की जरूरत पड़ सकती है

testing और rollback plan

  • यदि migration को केवल छोटे development datasets पर test किया जाए, तो बड़े datasets की performance characteristics समझना मुश्किल होता है
  • testing production-scale data clones पर करनी चाहिए, और Database Lab Engine जैसे tools उपयोगी हो सकते हैं
  • अगर समस्या आने पर migration को वापस लेने का तरीका न हो, तो production issue लंबे downtime में बदल सकता है
  • खासकर destructive operations के लिए verified rollback plan जरूरी है
  • सुरक्षित schema changes के लिए बुनियादी सिद्धांत ये हैं
    • production-scale data पर testing करें
    • जोखिमभरे operations के लिए multi-step approach अपनाएं
    • CONCURRENTLY और NOT VALID जैसी PostgreSQL features का उपयोग करें
    • performance और replication impact को monitor करें
    • हमेशा rollback plan तैयार रखें

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

1 टिप्पणियां

 
GN⁺ 2024-04-29
Hacker News की राय

  • मुझे Postgres वाकई पसंद है, लेकिन इस लेख की ज़्यादातर बातें ऐसी हैं जिनसे बचा जा सकता है और जिन पर ध्यान देना चाहिए। हालांकि मेरे हिसाब से Postgres की सबसे खराब चीज़ role management है
    इसकी capabilities शक्तिशाली हैं, इसलिए सही तरह इस्तेमाल करें तो शानदार होंगी, लेकिन इसे सच में काम करवाने की प्रक्रिया काली विद्या जैसी लगती है। interface के कई हिस्से ऐसे कठिन मंत्रों जैसे हैं जिनके बारे में पता नहीं होता कि वे उम्मीद के मुताबिक काम करेंगे या नहीं, और इतनी महत्वपूर्ण चीज़ को manage करने का यह भयानक तरीका है
    इस हिस्से का manual भी पतला है, बस इतना बताता है कि संकीर्ण use cases में इसे मोटे तौर पर कैसे काम करना चाहिए। अगर यह उम्मीद के मुताबिक न चले, तो trial and error से ढूंढना पड़ता है कि आपने क्या गलत किया, और सही तरीका फिर भी समझ नहीं आता। जटिल user permissions वाले DB को migrate करना सच में बहुत मुश्किल है
    लगा कि करीब एक महीना लगाकर एक cookbook लिखनी चाहिए। अगर उसे देखकर एक भी व्यक्ति रोते-रोते सोने से बच जाए, तो उसका मूल्य होगा

    • मैं मानता/मानती हूँ कि PostgreSQL का IAM जटिल है। इसकी जटिलता की वजह यह है कि object hierarchy Database, Schema, Tables के 3 स्तरों की है, और DB object owner को implicitly दिए जाने वाले privileges भी होते हैं
      किसी table से SELECT करने के लिए Database का CONNECT, Schema का USAGE चाहिए, और Schema owner को यह implicitly मिल जाता है। Table का SELECT भी चाहिए, और table owner को यह implicitly दिया जाता है
      permissions देखने के लिए grantee=privilege-abbreviation[]/grantor: format वाले ACL entries को समझना पड़ता है। Database permissions \l+, Schema permissions \dn+, और Table permissions \dp+ से देख सकते हैं
      permissions की सूची here पर है। उदाहरण के लिए user=arwdDxt/postgres का मतलब है कि postgres role ने user को सभी permissions दी हुई हैं
      अगर किसी object का grantee column खाली है, तो इसका मतलब default owner permissions, यानी सभी permissions हो सकता है, या फिर सभी मौजूद roles वाले PUBLIC role के लिए permissions हो सकता है। उदाहरण =r/postgres है
      public Schema इस्तेमाल करने पर बात और उलझती है। Schema में CREATE permission होती है, इसलिए अगर वही user जो data query करता है table बनाता है, तो owner permissions default रूप से लग जाती हैं और वह तुरंत query हो जाती है
    • authentication के लिए roles पर निर्भर postgREST docs भी बहुत detailed नहीं लगते: https://postgrest.org/en/v12/explanations/db_authz.html
      अगर आप Postgres roles पर cookbook लिखने को लेकर गंभीर हों और Kickstarter जैसी कोई चीज़ खोलें, तो शायद मैं सबसे पहले support करने वालों में से एक होऊँगा/होऊँगी
    • “इसे काम करवाना काली विद्या जैसा लगता है” वाली बात से सहमत हूँ। पिछले साल मैंने row-level security वाला एक simple postgREST server implement किया था, और वहाँ तक पहुँचना काफी कठिन था
      फिर भी जब यह चलने लगा तो सच में जादू जैसा लगा, और संबंधित mechanisms खुद आश्चर्यजनक रूप से काफी simple थे
    • ऐसा article हो तो मैं पढ़ूँगा/पढ़ूँगी। role management में बहुत guesswork होता है, और नतीजतन roles को excessive permissions मिल जाना बहुत आम है
    • कृपया ज़रूर लिखें। उस स्तर की सामग्री के लिए मैं लगभग 20 dollars खुशी से दे सकता/सकती हूँ

  • production environment में Schema migration चलाते हैं तो lock_timeout इस्तेमाल करना चाहिए
    foreign key वाली table को drop करना या foreign key हटाना जैसे changes, जो दिखने में harmless हैं और tests में लगभग तुरंत खत्म हो जाते हैं, high-traffic production DB में मौजूदा transactions या autovacuum की वजह से lock conflicts से टकरा सकते हैं
    वह ALTER पहले transaction के lock का इंतज़ार करते हुए ACCESS EXCLUSIVE lock पकड़ लेता है, और तब locked table पर सारी queries रुक जाती हैं
    बड़े scale पर Postgres चलाते हैं तो ऐसे conflicts होना बस समय की बात है। lock_timeout set करने पर migration बाकी सभी queries को रोककर इंतज़ार करने के बजाय, time limit पार होने पर fail हो जाती है

    • statement_timeout lock wait time को भी शामिल करता है, इसलिए busy table पर पड़ने वाले impact का बेहतर अनुमान लगाया जा सकता है
      अगर time limit 5 seconds रखी जाए, तो पता होता है कि total interruption अधिकतम 5 seconds होगा, और उसके बाद transactions चलते रहेंगे। सिर्फ lock_timeout इस्तेमाल करने पर lock मिलने के बाद काम में कितना समय लगेगा, इसे control नहीं कर सकते, और concurrent traffic की वजह से यह तेज़ भी हो सकता है या धीमा भी
    • Postgres version के हिसाब से यह काफी बदलता है कि कोई खास DML query exclusive lock लेती है या नहीं
      सोचता/सोचती हूँ कि query analyze करके यह बताने का कोई अच्छा तरीका है या नहीं कि वह किस तरह का lock लेगी। जब भरोसा नहीं होता, तो मैं हमेशा docs फिर से पढ़ता/पढ़ती आया/आई हूँ
    • अच्छी सलाह है। हालांकि तकनीकी रूप से मेरी समझ यह थी कि यह पहले ही ACCESS EXCLUSIVE lock हासिल करके इंतज़ार नहीं करता, बल्कि lock queue की वजह से इंतज़ार करता है
      ALTER ऐसी state में होता है जहाँ वह ACCESS EXCLUSIVE से lower lock के release होने का इंतज़ार कर रहा होता है
    • ऐसा करने पर ALTER शायद कभी execute ही न हो। अगर उस table पर पर्याप्त traffic हो तो ऐसा हो सकता है
      ऐसे मामले में, अगर app recover कर सकता है, तो मेरे हिसाब से सबसे अच्छा है कि ALTER को block कर रही दूसरी चल रही queries को kill कर दिया जाए

  • मैं Fly.io की Safe Migrations in Ecto guide को हफ्ते में कई बार refer करता/करती हूँ। Ecto, Elixir का DB adapter है
    यह जल्दी check करने के लिए बहुत उपयोगी reference है कि default migration काफी है या फिर ज़्यादा complex procedure की जरूरत है
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • शुरुआती दिनों में Postgres इंडेक्स के बारे में सबसे चौंकाने वाली बात यह थी कि UNIQUE इंडेक्स अतिरिक्त locking की वजह से concurrent queries के परिणामों को प्रभावित कर सकता है
    INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo); जैसी query को default mode में साथ-साथ चलाने पर duplicate bar values insert हो सकती हैं। क्योंकि एक transaction दूसरे transaction द्वारा बनाई गई नई maximum value नहीं देख पाता
    UNIQUE इंडेक्स जोड़ने पर लगता है कि “हारने वाले” transaction को constraint error मिलेगा, लेकिन असल में दोनों transactions सफल हो जाते हैं और race condition भी खत्म हो जाती है

    • यह सच नहीं है। इंडेक्स competition में हारने वाला subtransaction abort हो जाता है
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • अगर कहने का मतलब यह है कि UNIQUE इंडेक्स होने पर भी दोनों inserts सफल होते हैं और आखिर में duplicate values आ जाती हैं, तो अगर ऐसा है तो यह bug है
    • अगर मैं गलत नहीं समझ रहा, तो normal इंडेक्स को CONCURRENTLY से बनाकर और एक unchecked UNIQUE constraint बनाकर यह बिना downtime के किया जा सकता है
      वह constraint सिर्फ नए INSERT/UPDATE पर लागू होता है। बाद में constraint पर VALIDATE चलाने से वह पूरा UNIQUE constraint बन जाता है
    • अगर यह हैरान करने वाला लगता है, तो मेरे हिसाब से वजह यह है कि आप imperative languages के बहुत ज्यादा संपर्क में रहे हैं
      मैं मानता हूँ कि यह आम बात है, लेकिन समस्या Postgres से ज्यादा पूरे software development में है
    • यह किस isolation level पर होता है?

  • ऐसी pitfalls की वजह से zero-downtime Schema migration को automate करने के लक्ष्य से Reshape [0] बनाया
    यह नहीं कह सकता कि यह सभी समस्याओं से बचाता है, लेकिन हम इसी लक्ष्य वाला नया product बना रहे हैं। अगर इस क्षेत्र में, खासकर Postgres में, आपकी रुचि है तो आपसे संपर्क पाकर खुशी होगी: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • क्या इसके crdb पर भी काम करने की संभावना है?

  • एक और गलती जो अक्सर दिखती है, वह है table copy करते समय indexes छोड़ देना
    CREATE TABLE SELECT * FROM WHERE <> इस तरह काम नहीं करता। backup table बनाने या bulk delete करने की कोशिश में लोग अक्सर ऐसा करते हैं

    • अगर backup table बनाने की बात है, यानी आप कोई complex और ambiguous operation करने जा रहे हैं जो तुरंत unpredictable तरीके से खराब हो सकता है, तो मुझे indexes या constraints की बिल्कुल परवाह नहीं होती
      DB backup और WAL से restore न करना पड़े, इसलिए मुझे डेटा की एक copy तुरंत चाहिए होती है, भले ही शायद उसका इस्तेमाल न हो। indexes बनाना server time और disk space की बर्बादी है
      अगर काम बिगड़ जाए या सच में जरूरत पड़े, तो बाद में वह index बनाया जा सकता है
    • तो क्या आप यह भी बता सकते हैं कि सही तरीका क्या है?

  • “Case 2. IF [NOT] EXISTS का गलत इस्तेमाल” वाला हिस्सा misuse का अच्छा example नहीं देता
    और वास्तव में इसे इसी तरह इस्तेमाल करना सही है। यह clean और simple है, और इसमें कोई छिपा हुआ trap नहीं है। अगर tables बस कुछ ही हैं तो Schema migration tool जरूरत से ज्यादा बोझ है

    • trap सरल है। “logic से समस्या को ढककर abnormal state का risk बढ़ाना”
      खराब डेटा पर band-aid लगाने से समस्या solve नहीं होती, बस छिप जाती है। समस्या के प्रकार के हिसाब से बाद में यह unexpected तरीके से, सबसे खराब समय पर फट सकती है
      इस case में “खराब डेटा” वे tables, columns, views हैं जिन्हें होना चाहिए या नहीं होना चाहिए, लेकिन उल्टा मौजूद हैं। कोई table जो अभी मौजूद नहीं होनी चाहिए, वह क्यों मौजूद है? delete fail हुआ था? मौजूदा table का Schema सही है? क्या वही migration गलती से दो बार चल गई?
      हर migration के बाद Schema बिल्कुल सही state में होना चाहिए। अगर migration में IF [NOT] EXISTS है, तो इसका मतलब है कि पिछली migration के बाद Schema सही state में नहीं बचा। Schema state पर भरोसा न होना अच्छी बात नहीं है
    • मुझे लगता है लेख ने misuse को काफी अच्छी तरह समझाया। मुख्य बात यह है कि अलग रास्ते से Schema changes करना process और workflow की समस्या है, इसलिए उसे सीधे हल करना चाहिए
      अगर पहले से मौजूद table का column उस column से अलग हो जिसे migration बनाना चाहती है, तो क्या करेंगे? IF EXISTS migration को सफल करा देगा, लेकिन Schema को खराब state में छोड़ देगा। ऐसे मामले में migration का जल्दी fail होना बेहतर है

  • int4 को surrogate primary key के रूप में इस्तेमाल करने वाले हिस्से पर एक छोटी टिप्पणी
    अहम चीज table size नहीं बल्कि index size नहीं है क्या? table size में पहले से 23-byte header और alignment padding होता है, इसलिए 4-byte का फर्क ज्यादा असर नहीं डालता। लेकिन अगर ज्यादा indexes memory में रखे जा सकें, तो फायदा हो सकता है। index entry में 8-byte header होता है
    साथ ही example में 1 billion rows int4 की maximum value के बहुत करीब हैं, इसलिए थोड़ा असहज लगता है
    फिर भी लेख शानदार है

    • सही है। index size भी है और disk size भी। Postgres disk पर table rows को compactly pack करता है, लेकिन RAM में ऐसा नहीं करता
      तो क्या इसका मतलब है कि disk का 8KB page RAM में 8KB से बड़ा हो सकता है?
      लगता है यह सिर्फ table row data की working memory को प्रभावित करता है। फिर भी यह महत्वपूर्ण है। खासकर क्योंकि Postgres में rows random order में होती हैं, इसलिए range queries की locality बेहद खराब होती है। हालांकि मुझे नहीं लगता कि यह कोई निर्णायक insight है

  • मैं एक developer हूँ जो DB से जुड़ी समस्याओं से काफी हद तक बचा रहा है। Django में migrations बनाना, model tables बनाना, ORM से query करना जानता हूँ, लेकिन अंदर होने वाली बहुत-सी चीजें काला जादू जैसी लगती हैं
    अब कंपनी शुरू करते हुए डर है कि ऐसी समस्याओं का सामना करना पड़ेगा और उन्हें अकेले हल करना पड़ेगा। development environment में क्या करना चाहिए, यह सीखने के लिए कैसे approach करूँ?

    • fail हों और गलतियों से सीखें। या फिर developers hire करें ताकि साथ में fail हों और साथ में सीखें

  • मुझे Postgres पसंद है, लेकिन built-in batch update/delete तरीका न होना मुझे सच में नापसंद है
    यह सबसे annoying हिस्सा है, और जब भी इससे टकराता हूँ तो लगभग हर महीने batcher दोबारा लिखना पड़ता है