Soft delete की मुश्किलें

• डेटा recovery और regulatory compliance के लिए archived_at कॉलम-आधारित soft delete अक्सर इस्तेमाल किया जाता है, लेकिन समय के साथ इसकी जटिलता और अक्षमता बढ़ती जाती है
• यह तरीका query, index, migration, और restore logic को जटिल बनाता है, और क्योंकि ज़्यादातर archived data फिर कभी पढ़ा नहीं जाता, यह database पर अनावश्यक load डालता है
• विकल्प के तौर पर application event-आधारित archiving, trigger-आधारित archiving, और WAL(Change Data Capture)-आधारित archiving सुझाए गए हैं
• हर तरीके में operational complexity, infrastructure requirements, और restore की आसानी के मामले में अंतर है, और खास तौर पर WAL-आधारित तरीके में Kafka जैसे external systems के साथ integration की ज़रूरत होती है
• नए प्रोजेक्ट के लिए trigger-आधारित approach को सरलता और maintainability के लिहाज़ से सबसे संतुलित विकल्प माना गया है

---

Soft delete की समस्याएँ

• आम तौर पर डेटा को logical रूप से delete करने के लिए deleted boolean या archived_at timestamp कॉलम का इस्तेमाल किया जाता है
  • अगर ग्राहक ने गलती से डेटा delete कर दिया हो तो recovery संभव रहती है
  • कुछ मामलों में regulatory या audit purposes के लिए data retention भी ज़रूरी होता है
• लेकिन archived_at कॉलम query, operations, और application code में व्यापक जटिलता पैदा करता है
  • archived data का बड़ा हिस्सा दोबारा पढ़ा ही नहीं जाता
  • API behavior की समस्याओं या automation tools (Terraform आदि) की वजह से लाखों अनावश्यक rows जमा हो सकती हैं
• अगर archived data cleanup का काम configure न किया जाए तो database backup और restore के समय performance गिरती है
• query और index में archived data को filter करना पड़ता है, और data leak का जोखिम रहता है
• migration के दौरान पुराने data को handle करना या default values बदलना मुश्किल हो जाता है
• restore logic जटिल हो जाता है, और जहाँ external system calls की ज़रूरत हो वहाँ bug आने की संभावना रहती है
• नतीजतन archived_at तरीका ऊपर से सरल दिखता है, लेकिन लंबी अवधि में इसकी maintenance cost ज़्यादा होती है

Application level archiving

• delete के समय event publish किया जाता है, उसे SQS में भेजा जाता है, और दूसरी service उसे S3 में archive करती है
• फायदे
  • primary database और application code दोनों सरल हो जाते हैं
  • external resource cleanup को asynchronous processing के ज़रिए संभालकर performance और reliability बेहतर की जा सकती है
  • JSON के रूप में serialize करके application-friendly structure में archive किया जा सकता है
• नुकसान
  • application code में bug होने पर archived data loss हो सकता है
  • message queue जैसी operational infrastructure complexity बढ़ती है
  • S3 में रखा archived data इस्तेमाल करने के लिए search और restore tools की ज़रूरत होती है

Trigger-आधारित archiving

• delete से पहले trigger row को अलग archive table में JSON रूप में copy कर देता है
  • उदाहरण table: archive(id, table_name, record_id, data, archived_at, caused_by_table, caused_by_id)
• foreign key delete(cascade) के समय delete के कारण को track करने के लिए session variables(archive.cause_table, archive.cause_id) का इस्तेमाल होता है
  • इससे यह पता लगाया जा सकता है कि किस parent record ने child data को delete किया
• फायदे
  • live tables साफ-सुथरे रहते हैं, archived_at कॉलम की ज़रूरत नहीं रहती
  • archive table cleanup (WHERE archived_at < NOW() - INTERVAL '90 days') सरल होता है
  • query और index की efficiency बनी रहती है, और migration सरल हो जाती है
  • backup का आकार कम होता है
• archive tables को अलग tablespace या time partitioning के साथ manage किया जा सकता है

WAL(Change Data Capture)-आधारित archiving

• PostgreSQL के WAL logs को पढ़कर delete events को external systems तक stream किया जाता है
  • प्रमुख tool: Debezium (Kafka के साथ integration)
  • उदाहरण path: PostgreSQL → Debezium → Kafka → Consumer → Archive Storage
• हल्के विकल्प
  • pgstream: WAL को सीधे webhook या message queue तक भेजता है
  • wal2json: WAL को JSON में output करता है
  • pg_recvlogical: PostgreSQL का built-in logical replication tool
• operational complexity
  • Kafka-आधारित setup में monitoring, incident response, और tuning की ज़रूरत होती है
  • अगर consumer धीमा हो जाए तो WAL files जमा हो सकती हैं → disk space खत्म होने का जोखिम
  • PostgreSQL 13+ में max_slot_wal_keep_size setting से इसे सीमित किया जा सकता है
  • replication slot lag की monitoring और alerts अनिवार्य हैं
• फायदे
  • application code बदले बिना सभी changes capture किए जा सकते हैं
  • कई destinations(S3, data warehouse, search index) तक stream किया जा सकता है
  • primary database पर अतिरिक्त load नहीं पड़ता
• नुकसान
  • operational complexity और infrastructure cost अधिक होती है
  • consumer lag होने पर data loss या re-synchronization की ज़रूरत पड़ सकती है
  • schema changes के समय source और consumer के बीच coordination ज़रूरी होता है

Deletes को handle न करने वाले replica का विचार

• DELETE queries को ignore करने वाले PostgreSQL replica को बनाए रखने का विचार रखा गया है
  • delete न हुआ सारा data लगातार जमा करके archive किया जा सकता है
  • archived data पर सीधे query चलाई जा सकती है
• संभावित समस्याएँ
  • delete information को अलग से पहचानना मुश्किल हो सकता है
  • migrations लागू करते समय conflict का जोखिम
  • storage space और operational cost बढ़ना

निष्कर्ष

• नए प्रोजेक्ट्स में trigger-आधारित archiving सबसे व्यावहारिक विकल्प है
  • इसे configure करना आसान है, और live tables साफ रहते हैं
  • अलग infrastructure के बिना भी archived data को देखना और manage करना आसान है
• अगर जटिल infrastructure पहले से मौजूद हो या multiple destinations पर streaming चाहिए, तो WAL-आधारित approach अधिक उपयुक्त है

[love7peace]

अगर यह trigger-based है, तो मैंने सीखा था कि इससे DB पर लोड पड़ता है...? फिर भी trigger की सिफारिश कर रहे हैं।

[nemorize]

अगर उस स्तर के trigger से पैदा होने वाला लोड समस्या बन रहा है, तो trigger न होने पर भी स्थिति पहले से ही समस्याओं से भरी हुई है।

[cherrycoder]

आख़िरकार, नियम-कानून हमेशा लागत ही बढ़ाते हैं। खैर, वैसे भी इसकी कीमत तो आखिर उपभोक्ताओं को ही चुकानी है।

[GN⁺]

Hacker News की राय

• जिस banking domain में मैं काम करता हूँ, वहाँ मुझे उल्टा soft delete फायदेमंद लगा
  अगर deleted_at कॉलम हो, तो query लिखना साफ़ रहता है, और analytics queries या admin page में भी वही dataset इस्तेमाल किया जा सकता है
  ज़्यादातर मामलों में deletion कम ही होता है, और soft delete की गई rows से performance issue भी लगभग कभी नहीं हुआ
  साथ ही relations वैसे ही बने रहते हैं, इसलिए recovery (undo) भी आसान होती है
  मैं इससे आगे बढ़कर rows को पूरी तरह immutable बनाना पसंद करता हूँ, ताकि update के समय नई row जोड़ी जाए
  अगर log रखना हो, तो DB trigger का उपयोग करके INSERT/UPDATE/DELETE पर replica table में record छोड़ने वाला approach अच्छा लगता है

  • तुम्हारी बात सही है, लेकिन यह केवल तब लागू होती है जब deletion कम हो
    जिन tables को मैंने देखा, उनमें जब 50~70% rows soft delete थीं, तब performance गिरावट साफ़ दिखी
    आखिरकार soft delete situation पर depend करता है, और पहले से analysis ज़रूरी है
  • Postgres में सिर्फ non-soft-deleted data को index करने के लिए भी configure किया जा सकता है
    ज़्यादातर मामलों में इसकी ज़रूरत नहीं पड़ती, लेकिन RAM बचाने में मदद मिल सकती है
  • बैंक में soft delete, auditability की कमी को अस्थायी रूप से ढकने वाला उपाय भर है
    असली समाधान Event Sourcing है, जिसमें हर बदलाव को event के रूप में record करना चाहिए
    performance कम हो सकती है, लेकिन snapshot और sync से इसकी भरपाई की जा सकती है
  • अगर DB को immutable structure में चलाना हो, तो Datomic जैसे system पर विचार किया जा सकता है
    इसकी time travel feature से पुरानी state को पूरी तरह देखा जा सकता है
  • जब मैं पहले एक insurance company में काम करता था, तब भी हर table को append-only log की तरह चलाया जाता था
    latest state सबसे बड़े timestamp वाली row में होती थी, और पुरानी states को filter से देखा जा सकता था
    यह तरीका मज़बूत history management संभव बनाता है

• soft delete का सबसे बड़ा जाल query complexity है
  शुरुआत में लगता है कि बस WHERE deleted_at IS NULL जोड़ना है, लेकिन कुछ महीनों बाद filter छूट जाने से reports में ghost data दिखने लगती है
  इसे View से हल किया जा सकता है, लेकिन अंत में parallel access pattern बनाए रखना पड़ता है, और deleted data देखना हो तो abstraction को bypass करना पड़ता है
  Event sourcing ज़्यादा साफ़ है, लेकिन operational burden बड़ा होने के कारण ज़्यादातर लोग hybrid approach चुनते हैं

  • View काफ़ी powerful tool है
    समस्या यह है कि कई SWE और BI engineers SQL और schema design में सहज नहीं होते
    soft delete से भी ज़्यादा आम समस्या Type 2 Slowly Changing Dimension handling है
    ज़्यादातर लोग बेवजह audit table बना लेते हैं और inefficient UPDATE/INSERT दोहराते रहते हैं
    सच तो यह है कि DB एक बहुत सुंदर system है, लेकिन उतना सम्मान नहीं मिलता, यह अफ़सोस की बात है

• मुझे लगता है कि soft delete अगर DB built-in feature के रूप में मिले तो अच्छा होगा
  अगर इसे table level पर enable किया जा सके और deletion strategy चुनी जा सके, तो आदर्श होगा

  • वास्तव में Iceberg, Delta Lake, BigQuery जैसे systems time travel feature देते हैं
    लेकिन कई teams custom requirements की वजह से आखिरकार SCD(Slowly Changing Dimension) तरीके से ही implement करती हैं

• मेरे अनुभव में trigger-based approach सबसे स्थिर रही है
  archive table को append-only रखा जाना चाहिए, और recovery application layer में handle होनी चाहिए
  updates को soft delete माना जाता है, और trigger को पुरानी state capture करनी चाहिए
  trigger को ज़रूर BEFORE timing पर चलना चाहिए, और logic सरल होना चाहिए
  partition आमतौर पर महीने के हिसाब से होते हैं, और अगर write load ज़्यादा हो तो दिन के हिसाब से बाँटना बेहतर है

• मैं चाहता हूँ कि DB stateful → stateless दिशा में evolve करे
  मैं ऐसी structure पसंद करता हूँ जिसमें हर बदलाव append-only event के रूप में record हो, और ज़रूरी data को view से दिखाया जाए
  अगर DB अपने-आप materialized index manage करे, तो आदर्श होगा
  कुछ modern DB यह feature देते हैं, लेकिन अभी भी OLTP-केंद्रित विकास की कमी है

  • यह आखिरकार Event Sourcing की ही concept है
    Martin Fowler की व्याख्या देखना उपयोगी होगा

• जिस company में मैं पहले था, वहाँ हर system में soft delete लगाया गया था
  मुझे प्रोफ़ेसर की यह बात भी याद है: “business world में data कभी सचमुच delete नहीं होता”

  • पूरी तरह delete करना, भविष्य की data analysis capability को खुद सीमित करना है
    storage सस्ता है, इसलिए data कभी नहीं मिटाना चाहिए
  • लेकिन updates के बारे में प्रोफ़ेसर ने कुछ नहीं कहा था, यह दिलचस्प है

• database वह जगह है जहाँ facts store किए जाते हैं
  record बनना एक नया fact है, deletion भी एक और fact है
  लेकिन अगर row को physically मिटा दिया जाए, तो fact ही गायब हो जाता है
  ज़्यादातर मामलों में ऐसी deletion वांछनीय नहीं है

  • लेकिन अगर data ऐसा asset हो जिसमें leak risk हो, तो उल्टा बड़े पैमाने पर deletion की ज़रूरत पड़ सकती है
    retention cost और security risk दोनों पर विचार करना चाहिए
  • अगर DB immutable नहीं है, तो update अपने-आप में ही गायब हो चुके facts पैदा करता है
    data को हमेशा के लिए preserve करने का फ़ैसला सावधानी से लेना चाहिए
  • व्यक्तिगत रूप से, मुझे लगता है कि data store को सिर्फ read और insert ये दो operations support करने चाहिए
    इसके लिए data के lifecycle को समझना महत्वपूर्ण है

• Firezone में शुरू में soft delete को audit log के लिए इस्तेमाल किया गया था, लेकिन migration समस्याओं के कारण इसे छोड़ दिया गया
  इसके बजाय Postgres CDC(Change Data Capture) का उपयोग करके events को एक अलग write-optimized table में भेजने वाला तरीका अपनाया गया
  soft delete user recovery feature के लिए उपयोगी है, लेकिन audit या compliance उद्देश्यों के लिए उपयुक्त नहीं है

  • simple projects में DB changes की बजाय API calls का ही audit करना ज़्यादा efficient हो सकता है

• soft delete field वाले tables के ऊपर View बनाकर deleted rows छिपाना काफ़ी साफ़ तरीका है
  इससे application को deletion status की चिंता नहीं करनी पड़ती

  • Postgres की RLS(Row Level Security) का उपयोग करके soft delete की गई rows को अपने-आप छिपाया जा सकता है
    application फिर भी उसी table पर read/write/delete करती रहती है

• एक सवाल यह भी है कि schema drift को कैसे handle किया जाए
  अगर deletion के समय के schema में serialized data को बाद में restore करना हो, तो schema change समस्या बन जाता है

  • मेरे अनुभव में archived objects तक लगभग कभी पहुँचा नहीं जाता
    ज़्यादातर restore deletion के कुछ दिनों के भीतर ही होते हैं, इसलिए schema changes का असर कम पड़ता है
    पुराने archives को नए model में migrate करना जटिल और error-prone काम था
    अंत में, system का उपयोग कैसे होता है, उसी के अनुसार approach strategy बदलती है