Как на самом деле выполняется оператор UPDATE в PostgreSQL?

Question

Оператор UPDATE в PostgreSQL на самом деле не изменяет существующие строки, а создает новые версии строк (tuple) и помечает старые как удаленные. Это реализация механизма Multi-Version Concurrency Control (MVCC). Старая версия строки сохраняется до тех пор, пока не будет удалена процессом vacuum. Каждая новая версия получает новый xmin (идентификатор транзакции создания). Такой подход позволяет обеспечить изоляцию транзакций и возможность чтения неподтвержденных данных.

YeaHub · Accepted Answer

PostgreSQL использует механизм MVCC для управления конкурентным доступом, что определяет способ выполнения операций UPDATE.1. Процесс выполнения UPDATEСоздание новой версии: Создается копия строки с новыми значениямиОбновление системных полей:xmin — ID транзакции, создавшей версиюxmax — ID транзакции, удалившей версию (0 для активных)ctid — физическое расположение версии в таблицеПометка старой версии: Старая версия помечается как удаленная текущей транзакцией2. Внутреннее представление данных-- Создаем тестовую таблицу
CREATE TABLE test_update (id SERIAL PRIMARY KEY, value TEXT);
INSERT INTO test_update (value) VALUES ('initial');

-- Смотрим системные поля до UPDATE
SELECT xmin, xmax, ctid, * FROM test_update;

-- Выполняем UPDATE
UPDATE test_update SET value = 'updated' WHERE id = 1;

-- Смотрим системные поля после UPDATE
SELECT xmin, xmax, ctid, * FROM test_update;3. Влияние на производительность и обслуживаниеРаздувание таблицы (bloat): Накопление старых версий строкVacuum процесс: Автоматическое удаление устаревших версийAutovacuum: Фоновый процесс, который управляет очисткойHOT (Heap-Only Tuples) updates: Оптимизация для UPDATE без изменения индексируемых полей4. Пример наблюдения за процессом-- Мониторинг активности autovacuum
SELECT schemaname, tablename, 
       n_tup_upd, n_tup_hot_upd,
       (n_tup_hot_upd::float / NULLIF(n_tup_upd, 0)) * 100 as hot_update_ratio
FROM pg_stat_user_tables 
WHERE n_tup_upd > 0;

-- Проверка раздувания таблицы
SELECT schemaname, tablename,
       n_dead_tup, n_live_tup,
       (n_dead_tup::float / NULLIF(n_live_tup, 0)) * 100 as dead_tup_ratio
FROM pg_stat_user_tables;Вывод: Понимание механизма UPDATE в PostgreSQL помогает писать более эффективные приложения и правильно настраивать обслуживание базы данных. Используйте HOT-оптимизации где возможно, следите за раздуванием таблиц и настраивайте autovacuum для поддержания производительности.