Apa yang Sebenarnya Terjadi Saat Menghapus Kolom di Postgres

• Di PostgreSQL, meski DROP kolom dilakukan, data sebenarnya tidak dihapus — hanya "disembunyikan" di metadata
• Karena secara internal kolom tetap ada setelah DROP COLUMN, Anda bisa mencapai batas 1600 kolom
• Untuk benar-benar menghapus data, diperlukan VACUUM FULL atau penulisan ulang tabel secara manual
• Ini adalah desain untuk optimasi performa, tetapi perlu kehati-hatian dari sudut pandang kepatuhan seperti GDPR
• Memahami "apa yang sebenarnya terjadi" menguntungkan untuk pemecahan masalah, optimasi performa, dan pengelolaan data

---

Cara Kerja Sebenarnya DROP COLUMN di PostgreSQL

Situasi masalah: bagaimana jika kolom ditambah/dihapus berulang kali?

• Menambahkan lalu menghapus kolom sebanyak 2000 kali dengan kode seperti berikut:

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

• Pada akhirnya hanya 2 kolom yang tersisa di tabel, tetapi PostgreSQL tetap memunculkan error batas 1600 kolom
• Alasannya? Kolom yang dihapus pun secara internal masih tetap ada

Apa yang terjadi di dalam PostgreSQL?

Menghapus kolom bukan "penghapusan sungguhan"

• PostgreSQL menyimpan data dalam unit halaman 8KB
• Untuk menghapus kolom secara fisik, seluruh tabel harus ditulis ulang sehingga tidak efisien
• Sebagai gantinya, kolom hanya ditandai sebagai status 'dropped' di metadata lalu diabaikan

Dapat diperiksa lewat tabel sistem pg_attribute

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

• Contoh output:

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

• Kolom dengan attisdropped = t diabaikan dalam query, tetapi secara internal masih tersisa

Memeriksanya di file data (pg_filedump)

• Jika menganalisis file data PostgreSQL, dapat dipastikan bahwa nilai kolom yang dihapus benar-benar masih tersisa
• Pada data lama (Item 1), ada nilai untuk 3 kolom
• Pada data yang dimasukkan setelah penghapusan (Item 3), nilai kolom tersebut tidak ada dan diperlakukan sebagai NULL

Cara benar-benar menghapus kolom yang sudah di-drop

1. VACUUM FULL

• Menulis ulang seluruh tabel dan menghapus juga data dari kolom yang sudah dihapus
• Kekurangannya: kolom itu sendiri masih tetap ada di pg_attribute dengan status 'dropped'

2. Penulisan ulang tabel secara manual

• Buat tabel baru lalu salin hanya kolom yang diperlukan dengan SELECT

  CREATE TABLE new_table AS SELECT a, c FROM old_table;  
  

• Constraint, index, trigger, dan sebagainya perlu dibuat ulang secara manual
• Metode backup dengan pg_dump → modifikasi file dump → restore juga memungkinkan

DROP kolom dan persoalan hak untuk dilupakan dalam GDPR

• Sebagian orang mengkhawatirkan, "kalau kolom tidak benar-benar dihapus, bukankah ini pelanggaran GDPR?"
• Namun penghapusan data pribadi biasanya dilakukan pada level baris (row)

  DELETE FROM users WHERE id = <user_id>; -- atau hapus beserta tabel terkait  
  

• DROP kolom tidak berhubungan langsung dengan GDPR, dan yang penting adalah memodelkan serta menghapus data pribadi dengan benar

Hal yang perlu diperhatikan

• Karena PostgreSQL memakai mekanisme MVCC, bahkan setelah baris dihapus, data tetap ada sampai VACUUM selesai
• Di level sistem operasi pun, bisa jadi yang dilakukan hanyalah penandaan "terhapus" alih-alih penghapusan fisik
• Secara hukum, yang penting adalah “upaya penghapusan yang wajar”; tingkat menghapus disk fisik secara total biasanya tidak diwajibkan

Kesimpulan: DROP COLUMN hanyalah “menyembunyikan”, bukan “menghapus”

• Ini adalah desain demi performa, tetapi jika kolom menumpuk, Anda bisa terkena batas 1600 kolom
• Jika diperlukan, lakukan VACUUM FULL atau penulisan ulang tabel untuk membersihkan data
• Dari sudut pandang desain sistem maupun kepatuhan, memahami cara kerja internal PostgreSQL sangat berguna

Referensi

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

[ohyecloudy]

Perspektif bahwa pilihan implementasi untuk optimasi performa juga bisa dipikirkan dalam kaitannya dengan persoalan hak untuk dilupakan dalam GDPR terasa sangat tajam. Intinya, yang penting adalah memodelkan dan menghapus data pribadi dengan benar, sehingga sampai pada kesimpulan bahwa keduanya sebenarnya tidak berkaitan. Rapi.

[click]

postgresql belakangan memang populer, tetapi untuk implementasi MVCC saya lebih suka pendekatan yang memiliki area redo/undo terpisah.
Karena area redo/undo masih oke jika sedikit mengorbankan real-time, ada juga ruang untuk mengoptimalkan biaya dengan memakai storage kelas lebih rendah.
Saya juga kurang suka fakta bahwa pada akhirnya harus mengunci seluruh DB dan menjalankan VACUUM FULL.

[salsa]

Apakah VACUUM FULL memang pada akhirnya harus dilakukan? Dari sebagian besar dokumentasi yang saya lihat, mereka justru menyarankan untuk tidak melakukannya.

Salah satu referensi yang saya lihat:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/