Điều gì thực sự xảy ra khi xóa một cột trong Postgres

 (thenile.dev)
22 điểm bởi GN⁺ 2025-04-25 | 3 bình luận | Chia sẻ qua WhatsApp
  • Trong PostgreSQL, ngay cả khi DROP một cột thì dữ liệu cũng không thực sự bị xóa — nó chỉ bị "đánh dấu ẩn" trong metadata
  • Vì cột vẫn tồn tại ở tầng nội bộ ngay cả sau khi DROP COLUMN, bạn vẫn có thể chạm tới giới hạn 1600 cột
  • Để loại bỏ dữ liệu hoàn toàn, cần VACUUM FULL hoặc ghi lại bảng thủ công
  • Đây là một thiết kế nhằm tối ưu hiệu năng, nhưng cần thận trọng từ góc độ tuân thủ như GDPR
  • Hiểu được "điều gì thực sự xảy ra" sẽ có lợi cho xử lý sự cố, tối ưu hiệu năng và quản lý dữ liệu

Cách DROP COLUMN thực sự hoạt động trong PostgreSQL

Tình huống vấn đề: nếu liên tục thêm/xóa cột thì sao?

  • Thêm rồi xóa một cột 2000 lần bằng đoạn mã như sau: 
    ALTER TABLE t ADD COLUMN c1 int;  
ALTER TABLE t DROP COLUMN c1;  
...
  • Cuối cùng bảng chỉ còn lại 2 cột, nhưng PostgreSQL vẫn phát sinh lỗi chạm giới hạn 1600 cột
  • Lý do? Ngay cả các cột đã xóa cũng vẫn còn tồn tại ở bên trong

Bên trong PostgreSQL đang diễn ra điều gì?

Xóa cột không phải là "xóa thật"

  • PostgreSQL lưu trữ dữ liệu theo đơn vị trang 8KB
  • Nếu muốn xóa cột ở mức vật lý thì phải ghi lại toàn bộ bảng, nên rất kém hiệu quả
  • Thay vào đó, nó đánh dấu cột là trạng thái 'dropped' trong metadata và bỏ qua cột đó

Có thể kiểm tra bằng bảng hệ thống pg_attribute

SELECT attnum, attname, attisdropped FROM pg_attribute WHERE attrelid = 'test2'::regclass AND attnum > 0;
  • Ví dụ đầu ra: 
    attnum | attname                  | attisdropped  
--------+--------------------------+--------------  
      1 | a                        | f  
      2 | ........pg.dropped.2.... | t  
      3 | c                        | f
  • Các cột có attisdropped = t sẽ bị bỏ qua trong truy vấn nhưng vẫn còn tồn tại nội bộ

Kiểm tra trong tệp dữ liệu (pg_filedump)

  • Khi phân tích tệp dữ liệu PostgreSQL, có thể xác nhận rằng giá trị của cột đã xóa vẫn thực sự còn lại
  • Dữ liệu cũ (Item 1) vẫn có giá trị của 3 cột
  • Dữ liệu được chèn sau khi xóa (Item 3) thì không có giá trị cho cột đó nữa và được xử lý là NULL

Cách thực sự loại bỏ cột đã xóa

1. VACUUM FULL

  • Ghi lại toàn bộ bảng và xóa luôn dữ liệu của cột đã bị xóa
  • Nhược điểm: bản thân cột vẫn còn tồn tại trong pg_attribute ở trạng thái 'dropped'

2. Ghi lại bảng thủ công

  • Tạo bảng mới rồi chỉ SELECT và sao chép các cột cần thiết 
    CREATE TABLE new_table AS SELECT a, c FROM old_table;
  • Các ràng buộc, chỉ mục, trigger... cần được tạo lại thủ công
  • Cũng có thể sao lưu bằng pg_dump → sửa trong tệp dump → khôi phục lại

DROP cột và vấn đề GDPR về 'quyền được lãng quên'

  • Một số người lo ngại rằng "nếu cột không thực sự bị xóa thì có phải vi phạm GDPR không?"
  • Nhưng việc xóa dữ liệu cá nhân thường được thực hiện ở cấp độ hàng (row) 
    DELETE FROM users WHERE id = <user_id>; -- hoặc xóa cả trong các bảng liên quan
  • DROP cột không liên quan trực tiếp đến GDPR; điều cốt lõi là mô hình hóa và xóa dữ liệu cá nhân đúng cách

Lưu ý

  • Vì PostgreSQL dùng cơ chế MVCC, nên ngay cả sau khi xóa hàng thì dữ liệu vẫn còn cho đến khi VACUUM hoàn tất
  • Ở cấp hệ điều hành, dữ liệu cũng có thể chỉ được đánh dấu là đã xóa thay vì bị xóa vật lý
  • Về mặt pháp lý, điều quan trọng là “nỗ lực xóa hợp lý”; đa số trường hợp không yêu cầu phải xóa sạch hoàn toàn ở mức đĩa vật lý

Kết luận: DROP COLUMN chỉ là “ẩn đi”, không phải “xóa”

  • Đây là thiết kế phục vụ hiệu năng, nhưng nếu cột tích tụ lại thì có thể vướng giới hạn 1600 cột
  • Khi cần, phải dùng VACUUM FULL hoặc ghi lại bảng để dọn dữ liệu
  • Từ góc độ thiết kế hệ thống hay tuân thủ, hiểu rõ cơ chế nội bộ của PostgreSQL là rất hữu ích

Tài liệu tham khảo

Bài viết liên quan

3 bình luận

 
ohyecloudy 2025-04-30

Góc nhìn cho rằng lựa chọn triển khai để tối ưu hiệu năng cũng có thể được xem xét trong liên hệ với vấn đề quyền được lãng quên theo GDPR thật sự rất sắc sảo. Rốt cuộc đi đến kết luận rằng cốt lõi là phải mô hình hóa và xóa dữ liệu cá nhân một cách đúng đắn, nên thực ra đây không phải vấn đề có liên quan. Rất gọn gàng.
 
click 2025-04-25

PostgreSQL dạo này rất được ưa chuộng, nhưng về cách triển khai MVCC thì tôi vẫn thích kiểu có vùng redo/undo tách biệt hơn.
Vùng redo/undo có thể chấp nhận hy sinh phần nào tính thời gian thực, nên cũng có dư địa tối ưu chi phí bằng cách dùng lưu trữ cấp thấp hơn.
Việc đến một lúc nào đó vẫn phải khóa toàn bộ DB và chạy VACUUM FULL cũng là một điểm tôi không thích.
 
salsa 2025-04-26

Có đúng là sớm hay muộn cũng nhất định phải chạy VACUUM FULL không? Trong hầu hết tài liệu tôi từng đọc thì họ đều nói là không nên làm vậy.

Một tài liệu tôi đã xem:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/