Postgres'ta bir sütunu sildiğinizde gerçekte ne olur

• PostgreSQL'de bir sütunu DROP etseniz bile veri gerçekte silinmez — yalnızca metaveride "gizlenmiş" olarak işaretlenir
• DROP COLUMN sonrasında da sütun dahili olarak varlığını sürdürdüğü için 1600 sütun sınırına ulaşabilirsiniz
• Veriyi tamamen kaldırmak için VACUUM FULL veya manuel tablo yeniden yazımı gerekir
• Bu, performans optimizasyonu için yapılmış bir tasarımdır; ancak GDPR gibi uyumluluk perspektiflerinde dikkat gerektirir
• "Gerçekte ne olur"u anlamak, sorun çözme, performans optimizasyonu ve veri yönetimi açısından avantaj sağlar

---

PostgreSQL'de DROP COLUMN gerçekte nasıl çalışır

Sorun durumu: Sütunları tekrar tekrar ekleyip silerseniz ne olur?

• Aşağıdaki gibi bir kodla sütun 2000 kez eklenip sonra silinir:

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

• Sonuçta tabloda yalnızca 2 sütun kalmış olsa bile PostgreSQL, 1600 sütun sınırı hatası üretir
• Neden? Çünkü silinen sütunlar da dahili olarak hâlâ vardır

PostgreSQL içinde neler olur?

Sütun silme, "gerçek silme" değildir

• PostgreSQL verileri 8KB sayfalar halinde depolar
• Bir sütunu fiziksel olarak silmek için tüm tablonun yeniden yazılması gerekir; bu da verimsizdir
• Bunun yerine sütunu metaveride 'dropped' durumunda işaretler ve yok sayar

pg_attribute sistem tablosundan doğrulanabilir

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

• Örnek çıktı:

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

• attisdropped = t olan sütunlar sorgularda yok sayılır, ancak dahili olarak kalmaya devam eder

Veri dosyasında doğrulama (pg_filedump kullanarak)

• PostgreSQL veri dosyaları incelendiğinde, silinen sütunun değerlerinin gerçekte hâlâ durduğu görülebilir
• Eski veride (Item 1) 3 sütun değeri bulunur
• Silme işleminden sonra eklenen veride (Item 3) ilgili sütun değeri yoktur ve NULL olarak işlenir

Silinen sütunu gerçekten kaldırma yöntemleri

1. VACUUM FULL

• Tüm tabloyu yeniden yazar ve silinen sütunun verisini de kaldırır
• Dezavantajı: Sütunun kendisi hâlâ pg_attribute içinde 'dropped' durumunda kalır

2. Manuel tablo yeniden yazımı

• Yeni bir tablo oluşturup yalnızca gerekli sütunları SELECT ile kopyalarsınız

  CREATE TABLE new_table AS SELECT a, c FROM old_table;  
  

• Constraint'ler, index'ler, trigger'lar vb. öğelerin manuel olarak yeniden oluşturulması gerekir
• pg_dump ile yedek alıp → dump dosyasında düzenleme yapıp → geri yükleme yöntemi de mümkündür

Sütun DROP ve GDPR "unutulma hakkı" meselesi

• Bazıları, "Sütun gerçekte silinmiyorsa bu GDPR ihlali değil mi?" diye endişe eder
• Ancak kişisel verilerin silinmesi genellikle satır (row) düzeyinde yapılır

  DELETE FROM users WHERE id = <user_id>; -- veya ilişkili tablolar da dahil edilerek silinir  
  

• Sütun DROP, GDPR ile doğrudan ilgili değildir; asıl önemli olan kişisel verileri doğru modellemek ve doğru şekilde silmektir

Dikkat edilmesi gerekenler

• PostgreSQL MVCC yaklaşımını kullandığı için, satır silindikten sonra da VACUUM tamamlanana kadar veri kalmaya devam eder
• İşletim sistemi seviyesinde de fiziksel silme yerine "silme bayrağı" işaretleme yapılmış olabilir
• Hukuki açıdan önemli olan genellikle “makul silme çabası”dır; fiziksel diski tamamen sıfırlama düzeyi çoğu durumda gerekmez

Sonuç: DROP COLUMN bir “gizleme” işlemidir, “silme” değil

• Bu tasarım performans içindir; ancak sütunlar birikirse 1600 sınırına takılabilirsiniz
• Gerekirse VACUUM FULL veya tablo yeniden yazımıyla veri temizliği yapılmalıdır
• Sistem tasarımı ve uyumluluk açısından PostgreSQL'in iç işleyişini anlamak çok faydalıdır

Referanslar

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

[ohyecloudy]

Performans optimizasyonu için yapılan uygulama tercihinin GDPR kapsamındaki unutulma hakkı meselesiyle de ilişkilendirilebilmesi bakış açısı oldukça içgörülü. Kişisel verileri doğru modelleyip silmenin asıl mesele olduğu, dolayısıyla aslında bununla doğrudan ilgili olmadığı sonucuna varması da yerinde. Temiz bir yaklaşım.

[click]

PostgreSQL bu aralar popüler olsa da, MVCC uygulamasında redo/undo alanlarının ayrı bulunduğu yaklaşımı daha çok seviyorum.
Redo/undo alanlarında gerçek zamanlılıktan bir ölçüde feragat edilebildiği için daha düşük seviye depolama kullanarak maliyet optimizasyonu yapma alanı da var
Bir gün mutlaka tüm DB'ye kilit koyup VACUUM FULL yapmak zorunda kalınması da hoşuma gitmeyen bir unsur.

[salsa]

Bir noktada mutlaka VACUUM FULL çalıştırmak gerekiyor mu? Gördüğüm belgelerin çoğu genelde bunu yapmamayı öneriyor gibi.

Baktığım kaynaklardan biri:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/