Postgres में कॉलम हटाने पर वास्तव में क्या होता है

• PostgreSQL में कॉलम को DROP करने पर भी डेटा वास्तव में डिलीट नहीं होता — उसे सिर्फ metadata में "छिपा हुआ" चिह्नित कर दिया जाता है
• DROP COLUMN के बाद भी कॉलम अंदरूनी तौर पर मौजूद रहता है, इसलिए 1600 कॉलम की सीमा तक पहुँचा जा सकता है
• डेटा को पूरी तरह हटाने के लिए VACUUM FULL या manual table rewrite की ज़रूरत होती है
• यह performance optimization के लिए किया गया design है, लेकिन GDPR जैसे compliance के नज़रिए से सावधानी ज़रूरी है
• "वास्तव में क्या होता है" यह समझना troubleshooting, performance optimization, और data management के लिए फ़ायदेमंद है

---

PostgreSQL में DROP COLUMN वास्तव में कैसे काम करता है

समस्या: अगर कॉलम को बार-बार जोड़ें और हटाएँ तो?

• नीचे जैसे code से कॉलम को 2000 बार जोड़कर फिर हटाया गया:

  ALTER TABLE t ADD COLUMN c1 int;  
  ALTER TABLE t DROP COLUMN c1;  
  ...  
  

• अंत में table में सिर्फ 2 कॉलम बचे होने के बावजूद PostgreSQL 1600-कॉलम सीमा की error देता है
• वजह? हटाए गए कॉलम भी अंदरूनी तौर पर अब भी मौजूद रहते हैं

PostgreSQL के अंदर क्या होता है?

कॉलम हटाना "असल deletion" नहीं है

• PostgreSQL डेटा को 8KB page units में store करता है
• कॉलम को physical रूप से हटाने के लिए पूरी table को फिर से लिखना पड़ता है, जो अक्षम है
• इसके बजाय, PostgreSQL कॉलम को metadata में 'dropped' स्थिति के रूप में चिह्नित करके ignore करता है

pg_attribute system table से इसकी पुष्टि की जा सकती है

SELECT attnum, attname, attisdropped FROM pg_attribute WHERE attrelid = 'test2'::regclass AND attnum > 0;  

• output उदाहरण:

  attnum | attname                  | attisdropped  
  --------+--------------------------+--------------  
        1 | a                        | f  
        2 | ........pg.dropped.2.... | t  
        3 | c                        | f  
  

• attisdropped = t वाले कॉलम query में ignore हो जाते हैं, लेकिन अंदरूनी तौर पर बने रहते हैं

डेटा फ़ाइल में जाँच (pg_filedump का उपयोग)

• PostgreSQL data file का विश्लेषण करने पर यह देखा जा सकता है कि हटाए गए कॉलम के values वास्तव में अब भी मौजूद रहते हैं
• पुराने डेटा (Item 1) में 3 कॉलम के values मौजूद हैं
• deletion के बाद insert किए गए डेटा (Item 3) में उस कॉलम का value नहीं है और उसे NULL माना जाता है

हटाए गए कॉलम को वास्तव में हटाने के तरीके

1. VACUUM FULL

• यह पूरी table को rewrite करता है और हटाए गए कॉलम का डेटा भी हटा देता है
• कमी: कॉलम खुद अभी भी pg_attribute में 'dropped' स्थिति में मौजूद रहता है

2. manual table rewrite

• एक नई table बनाकर सिर्फ ज़रूरी कॉलमों को SELECT करके कॉपी करें

  CREATE TABLE new_table AS SELECT a, c FROM old_table;  
  

• constraints, indexes, triggers आदि को manually फिर से बनाना होगा
• pg_dump से backup → dump file में बदलाव → restore करने का तरीका भी संभव है

कॉलम DROP और GDPR का 'भूल जाने का अधिकार' मुद्दा

• कुछ लोग चिंता जताते हैं: "अगर कॉलम वास्तव में डिलीट नहीं होता, तो क्या यह GDPR उल्लंघन है?"
• लेकिन व्यक्तिगत डेटा deletion आम तौर पर row स्तर पर किया जाता है

  DELETE FROM users WHERE id = <user_id>; -- या संबंधित tables सहित deletion  
  

• कॉलम DROP का GDPR से सीधा संबंध नहीं है; मुख्य बात है personal data को सही ढंग से model करना और delete करना

सावधानियाँ

• PostgreSQL MVCC का उपयोग करता है, इसलिए row delete होने के बाद भी VACUUM पूरा होने तक डेटा बना रह सकता है
• operating system स्तर पर भी physical deletion के बजाय "delete flag" जैसा व्यवहार हो सकता है
• कानूनी रूप से "उचित deletion प्रयास" ज़्यादा महत्वपूर्ण है; physical disk को पूरी तरह मिटाना आम तौर पर आवश्यक नहीं होता

निष्कर्ष: DROP COLUMN सिर्फ “छिपाना” है, “डिलीट” नहीं

• यह performance के लिए बनाया गया design है, लेकिन कॉलम जमा होते जाएँ तो 1600 की सीमा तक पहुँचा जा सकता है
• ज़रूरत पड़ने पर VACUUM FULL या table rewrite के ज़रिए डेटा cleanup करना होगा
• system design या compliance के नज़रिए से PostgreSQL की अंदरूनी कार्यप्रणाली को समझना बहुत उपयोगी है

संदर्भ सामग्री

• Postgres File Layout
• Postgres Page Layout
• Internal Layout of Heap Tables
• VACUUM documentation
• pg_filedump

[ohyecloudy]

परफॉर्मेंस ऑप्टिमाइज़ेशन के लिए किए गए implementation choices को GDPR के right to be forgotten मुद्दे से जोड़कर भी देखा जा सकता है—यह नज़रिया काफ़ी insightful है। निष्कर्ष यह है कि असली बात personal data को सही तरह से model करना और delete करना है, इसलिए इसका सीधा संबंध नहीं बनता। बहुत साफ़-सुथरा है।

[click]

हालांकि PostgreSQL इन दिनों काफ़ी लोकप्रिय है, लेकिन MVCC implementation के मामले में मुझे वह तरीका ज़्यादा पसंद है जिसमें redo/undo area अलग से मौजूद होता है.
redo/undo area में real-time performance को कुछ हद तक sacrifice किया जा सकता है, इसलिए lower-tier storage का उपयोग करके cost optimize करने की गुंजाइश भी होती है.
और यह बात भी मुझे पसंद नहीं है कि कभी-न-कभी पूरे DB पर lock लगाकर VACUUM FULL करना ही पड़ता है.

[salsa]

क्या किसी समय VACUUM FULL करना वास्तव में ज़रूरी होता है? मैंने जो ज़्यादातर दस्तावेज़ देखे हैं, उनमें तो आमतौर पर इसे न करने की सलाह दी गई है।

मैंने जो सामग्री देखी, उनमें से एक:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/