Как происходит удаление строк в PostgreSQL и почему место не освобождается сразу?

В PostgreSQL удаление строк реализовано в рамках архитектуры MVCC (Multi-Version Concurrency Control), поэтому строки не удаляются физически сразу после выполнения команды DELETE.

Процесс удаления:

1. Пометка строки: Вместо физического удаления, DELETE помечает строку как "мертвую" (dead tuple). Это делается путем записи ID транзакции, которая выполнила удаление, в системное поле строки xmax.
2. Сохранение видимости: Старая версия строки остается видимой для транзакций, которые начались до момента фиксации удаления. Это обеспечивает изоляцию транзакций без блокировок на чтение.
3. Очистка: Физическое удаление "мертвых" строк и освобождение места на диске происходит позже, во время процесса VACUUM.

Способы очистки:

• VACUUM: Стандартная команда, которая находит "мертвые" строки и делает занимаемое ими место доступным для новых записей. Не блокирует таблицу для чтения и записи.
• AUTOVACUUM: Фоновый процесс, который автоматически выполняет VACUUM для таблиц при достижении определенного порога измененных или удаленных строк.
• VACUUM FULL: Перезаписывает всю таблицу в новый файл на диске, удаляя все "мертвые" строки и пустое пространство. Требует эксклюзивной блокировки таблицы на все время своей работы.
• TRUNCATE TABLE: Быстро удаляет все строки из таблицы, не сканируя ее. Это DDL-операция, она немедленно освобождает место, но ее нельзя отменить, и она не запускает триггеры ON DELETE.

-- Помечает строку как удаленную, но место не освобождает
DELETE FROM users WHERE id = 42;

-- Физически очищает место от ранее удаленных строк
VACUUM users;

-- Быстро удаляет все строки и освобождает место
TRUNCATE TABLE users RESTART IDENTITY;