SQL Ready | Базы Данных on Telegram: “Почему ctid нельзя использовать как постоянный и…”

Почему 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 | #практика