Почему ctid нельзя использовать как постоянный идентификатор!
В PostgreSQL каждая строка имеет системную колонку ctid. Она содержит физический адрес текущей версии строки в таблице: номер страницы и позицию строки внутри этой страницы.
Иногда ctid используют для поиска, удаления или диагностики отдельных записей, но важно понимать, что это не постоянный идентификатор строки. Значение ctid может измениться в процессе обычной работы базы данных, поэтому использовать его в прикладной логике нельзя.
Для начала создадим простую таблицу с первичным ключом и несколькими тестовыми записями, чтобы наглядно проследить, как изменяется значение ctid. CREATE TABLE users ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT );
Теперь добавим несколько строк, с которыми будем работать в дальнейших примерах. INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
Посмотрим, какое значение ctid PostgreSQL присвоил каждой записи после вставки. SELECT ctid, id, name FROM users;
Результат может выглядеть так: ctid | id | name ------+----+-------- (0,1) | 1 | Alice (0,2) | 2 | Bob (0,3) | 3 | Charlie
Теперь изменим одну из строк. На первый взгляд кажется, что PostgreSQL просто обновит существующую запись, однако механизм хранения данных работает иначе. UPDATE users SET name = 'Robert' WHERE id = 2;
После выполнения UPDATE снова посмотрим значения ctid и сравним их с предыдущим результатом. SELECT ctid, id, name FROM users;
Теперь можно увидеть, что значение ctid изменилось. Например: ctid | id | name ------+----+--------- (0,1) | 1 | Alice (0,4) | 2 | Robert (0,3) | 3 | Charlie
Это происходит из-за механизма MVCC. При выполнении UPDATE PostgreSQL не изменяет строку на месте, а создаёт её новую версию, которая получает новый физический адрес (ctid). Старая версия строки некоторое время остаётся в таблице и может быть видима другим транзакциям в зависимости от их снимка данных.
Изменение ctid происходит не только при UPDATE. Любые операции, которые физически переписывают таблицу, также приводят к изменению физических адресов строк. Например: VACUUM FULL users;
или CLUSTER users USING users_pkey;
Поэтому использовать ctid в качестве внешнего ключа, хранить его в приложении или считать постоянным идентификатором записи нельзя. При этом ctid остаётся полезным инструментом для служебных задач. Один из самых распространённых случаев — удалить одну запись среди полностью одинаковых дубликатов, когда значения всех пользовательских столбцов совпадают и отличить строки обычными средствами невозможно.
Например, создадим отдельную таблицу без ограничений уникальности: CREATE TABLE duplicate_users ( name TEXT );
Добавим одинаковые строки: INSERT INTO duplicate_users (name) VALUES ('Alice'), ('Alice'), ('Bob');
Удалим только одну из двух одинаковых строк: DELETE FROM duplicate_users WHERE ctid = ( SELECT ctid FROM duplicate_users WHERE name = 'Alice' LIMIT 1 );
В результате останется одна строка с именем Alice. В этом примере ctid определяется и используется в рамках одного SQL-запроса, поэтому используется актуальный физический адрес версии строки и не возникает проблемы с использованием устаревшего значения.
🔥 ctid — это физический адрес текущей версии строки, а не её постоянный идентификатор. Для связи данных всегда используйте первичный ключ, а ctid оставьте для диагностических и служебных операций.
➡️ SQL Ready | #практика