Первичные ключи в PostgreSQL: sequence, int4 и bigint

Вставки падают с nextval: reached maximum value of sequence, а прямой ALTER COLUMN ... TYPE bigint (основы ALTER TABLE) переписывает таблицу под ACCESS EXCLUSIVE: на 5 млн строк запись стояла 3,8 с. Дальше: откуда дырки в нумерации, чем кончается int4 (ошибкой или отрицательными ключами) и перевод на bigint по шагам, где сильная блокировка держится 32 мс.

Опыт идёт на таблице в 5 млн строк. Исчерпание int4 показано через setval, без 2 млрд вставок. Времена относятся к этой таблице и этой машине; на другие объёмы они не пересчитываются.

DBA / 01

Перевод int на bigint: блокировка каждого шага

Янтарные шаги держат блокировку, которая мешает записи, не дольше 32 мс; зелёные идут секунды и запись пропускают. Нижняя красная строка — прямой ALTER на копии той же таблицы: блокировка на всё время перезаписи.

Опыт 1: дырки, CACHE и сбой

nextval не участвует в транзакции: выданное значение откат не возвращает. Дальше P — короткое имя команды (-Atq: вывод без таблиц и тегов):

docker run -d --name dba-primary-keys-sequences-int-exhaustion -e POSTGRES_PASSWORD=pg postgres:18
P="docker exec -i dba-primary-keys-sequences-int-exhaustion psql -U postgres -Atq"

SQL подаётся на stdin команды $P:

SELECT version();
CREATE TABLE gap (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, v text);
INSERT INTO gap (v) VALUES ('a'), ('b');
BEGIN; INSERT INTO gap (v) VALUES ('rollback'); ROLLBACK;
INSERT INTO gap (v) VALUES ('c');
SELECT string_agg(id::text, ',' ORDER BY id) FROM gap;

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
1,2,4

Значение 3 досталось откатившейся вставке. Вторая причина — CACHE. Каждая команда $P -c — отдельное соединение, и каждое забирает у sequence блок из 10 значений:

$P -c "CREATE TABLE cached (id bigint GENERATED ALWAYS AS IDENTITY (CACHE 10) PRIMARY KEY, who text)"
for v in "('A1'),('A2')" "('B1'),('B2')" "('A3')"; do $P -c "INSERT INTO cached (who) VALUES $v"; done
$P -c "SELECT string_agg(id || ':' || who, ' ' ORDER BY id) FROM cached"

1:A1 2:A2 11:B1 12:B2 21:A3

Значения 3–10, 13–20 и 22–30 остались в кэшах соединений, которые уже закрылись, и не достанутся никому. В nextval_internal (sequence.c, строки 668–676, тег REL_18_6; строки 670–674 опущены) пока кэш не исчерпан, ответ берётся из записи elm, а к самой sequence сервер не обращается:

	if (elm->last != elm->cached)	/* some numbers were cached */
	{
		...
		return elm->last;
	}

elm лежит в static HTAB *seqhashtab процесса (строка 91), поэтому кэш у соединений свой. Третья причина — сбой: чтобы не писать в WAL каждую выдачу, сервер логирует значения на SEQ_LOG_VALS (32) вперёд (sequence.c, строки 53–58: после сбоя «можно потерять столько значений, сколько записано вперёд»). Проверка: три строки, docker kill вместо остановки, запуск, ещё одна вставка (таблица legacy нужна для опыта 2):

Q="SELECT string_agg(id::text, ',' ORDER BY id) FROM crash"
$P -c "CREATE TABLE legacy (id serial PRIMARY KEY, v text)" -c "INSERT INTO legacy (v) VALUES ('x')"
$P -c "CREATE TABLE crash (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY)" -c "INSERT INTO crash SELECT FROM generate_series(1, 3)" -c "$Q"
docker kill dba-primary-keys-sequences-int-exhaustion && docker start dba-primary-keys-sequences-int-exhaustion
$P -c "INSERT INTO crash DEFAULT VALUES" -c "$Q"

1,2,3
1,2,3,34

Значения 4–33 не получил никто. Гарантии нумерации без дырок у identity нет ни при откате, ни при CACHE, ни при сбое: по разрывам нельзя искать «потерянные» заказы.

Опыт 2: 2 147 483 647 и ложное спасение

Предел int4 проверяется без 2 млрд вставок: setval ставит sequence в двух шагах от него. Запрос показывает остаток по всем sequence базы и берёт меньший из двух пределов: самой sequence и типа столбца, к которому она привязана (pg_depend: тип i у identity, a у serial; таблица legacy в выводе создана как id serial).

Запрос ниже запускается без -At (psql -U postgres -q), строка 2147483645 от setval в выводе опущена.

CREATE TABLE orders_id (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text);
SELECT setval('orders_id_id_seq', 2147483645);
SELECT s.schemaname || '.' || s.sequencename AS sequence, a.attrelid::regclass AS tbl, a.attname,
       format_type(a.atttypid, NULL) AS col_type, s.last_value,
       least(s.max_value, CASE a.atttypid WHEN 'int2'::regtype THEN 32767
            WHEN 'int4'::regtype THEN 2147483647 ELSE 9223372036854775807 END) - s.last_value AS remaining
FROM pg_sequences s
JOIN pg_class c ON c.relname = s.sequencename AND c.relnamespace = s.schemaname::regnamespace
JOIN pg_depend d ON d.objid = c.oid AND d.classid = 'pg_class'::regclass
     AND d.refclassid = 'pg_class'::regclass AND d.deptype IN ('a', 'i')
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE s.last_value IS NOT NULL
ORDER BY remaining;

        sequence         |    tbl    | attname | col_type | last_value |      remaining      
-------------------------+-----------+---------+----------+------------+---------------------
 public.orders_id_id_seq | orders_id | id      | integer  | 2147483645 |                   2
 public.crash_id_seq     | crash     | id      | integer  |         34 |          2147483613
 public.gap_id_seq       | gap       | id      | integer  |         33 |          2147483614
 public.legacy_id_seq    | legacy    | id      | integer  |         33 |          2147483614
 public.cached_id_seq    | cached    | id      | bigint   |         42 | 9223372036854775765
(5 rows)

Остаток 2. У gap, crash и legacy после сбоя last_value на десятки больше выданного (33 при максимальном id 4 у gap): запрос слегка занижает остаток, это безопасная сторона. Дальше вставки до предела и «исправление» через ALTER SEQUENCE:

INSERT INTO orders_id (note) VALUES ('1'), ('2');
INSERT INTO orders_id (note) VALUES ('3');
ALTER SEQUENCE orders_id_id_seq AS bigint;
INSERT INTO orders_id (note) VALUES ('4'), ('5');
SELECT id || '|' || note FROM orders_id ORDER BY ctid;

ERROR:  nextval: reached maximum value of sequence "orders_id_id_seq" (2147483647)
2147483646|1
2147483647|2
-2147483648|4
-2147483647|5

Вставка ‘3’ честно упала. После расширения sequence до bigint ошибок нет, а ключи ушли в минус: столбец остался int, и значение обрезается до 32 бит в ExecEvalNextValueExpr (execExprInterp.c, строки 3283–3292):

	int64		newval = nextval_internal(op->d.nextvalueexpr.seqid, false);

	switch (op->d.nextvalueexpr.seqtypid)
	{
		case INT2OID:
			*op->resvalue = Int16GetDatum((int16) newval);
			break;
		case INT4OID:
			*op->resvalue = Int32GetDatum((int32) newval);
			break;

Для identity приведение (int32) newval диапазон не проверяет. Расширять нужно столбец, а не sequence: ошибка на пределе громкая, отрицательные ключи молчат, пока не упрутся в занятые.

Опыт 3: перевод на bigint без простоя

Стенд: orders на 5 000 000 строк (таблица 444 МБ, индексы 107 МБ) и payment на 1 000 000 строк с внешним ключом на orders.id. Параллельно процедура writer вставляет строки в orders с паузой 5 мс и пишет время каждой вставки в wlog. Замеры шли при работающем писателе (в прогоне работал и опросчик pg_locks раз в ~2 мс, его вывод ниже не используется); перед началом autovacuum простаивал.

CREATE TABLE orders (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id int NOT NULL,
  amount int NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), note text);
INSERT INTO orders (user_id, amount, note) SELECT i % 100000, i % 5000, md5(i::text) FROM generate_series(1, 5000000) i;
CREATE TABLE payment (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id int NOT NULL REFERENCES orders (id), paid int NOT NULL);
INSERT INTO payment (order_id, paid) SELECT i * 5, 1 FROM generate_series(1, 1000000) i;
CREATE UNLOGGED TABLE wlog (ts timestamptz, ms float8);
CREATE TABLE stopflag (x int);
CREATE PROCEDURE writer(tbl text) LANGUAGE plpgsql AS $$
DECLARE t timestamptz;
BEGIN LOOP
  t := clock_timestamp();
  EXECUTE format('INSERT INTO %I (user_id, amount, note) VALUES (1, 1, ''w'')', tbl); COMMIT;
  INSERT INTO wlog VALUES (t, extract(epoch FROM clock_timestamp() - t) * 1000); COMMIT;
  EXIT WHEN EXISTS (SELECT 1 FROM stopflag); PERFORM pg_sleep(0.005);
END LOOP; END $$;

Писатель стартует командой CALL writer('orders') в отдельной сессии, останавливается строкой в stopflag. Базовая линия: копия orders_naive (CREATE TABLE ... (LIKE orders INCLUDING ALL), те же 5 млн строк, без внешнего ключа) и лобовая смена типа при писателе writer('orders_naive'):

\timing on
ALTER TABLE orders_naive ALTER COLUMN id TYPE bigint;
SELECT count(*) AS inserts, round(max(ms)::numeric, 1) AS max_ms, round((percentile_cont(0.5) WITHIN GROUP (ORDER BY ms))::numeric, 2) AS p50_ms FROM wlog;

Time: 3931.336 ms (00:03.931)

 inserts | max_ms | p50_ms 
---------+--------+--------
     316 | 3833.9 |   1.70

Вставка, пришедшая во время перезаписи, ждала 3833,9 мс при медиане 1,70 мс. Режимы шагов миграции снимались так: транзакция с шагом спит 1,2 с, вторая сессия читает pg_locks (holder — application_name транзакции). Шаги проверялись на отдельной базе в тысячу строк:

BEGIN; ALTER TABLE orders ADD COLUMN id_x bigint; SELECT pg_sleep(1.2); ROLLBACK;
SELECT c.relname, l.mode FROM pg_locks l JOIN pg_stat_activity a USING (pid) JOIN pg_class c ON c.oid = l.relation
WHERE a.application_name = 'holder' AND l.locktype = 'relation' AND c.relname IN ('orders', 'payment');

ADD COLUMN: orders=AccessExclusiveLock
CREATE TRIGGER: orders=ShareRowExclusiveLock
UPDATE (пачка заполнения): orders=RowExclusiveLock
ADD CHECK NOT VALID: orders=AccessExclusiveLock
VALIDATE CONSTRAINT: orders=ShareUpdateExclusiveLock
DROP FOREIGN KEY: orders=AccessExclusiveLock payment=AccessExclusiveLock
ADD FOREIGN KEY NOT VALID: orders=ShareRowExclusiveLock payment=ShareRowExclusiveLock
VALIDATE FOREIGN KEY: orders=RowShareLock payment=ShareUpdateExclusiveLock
ALTER COLUMN TYPE bigint: orders=AccessExclusiveLock payment=AccessExclusiveLock

Выведен самый сильный режим на таблице. CREATE INDEX CONCURRENTLY в транзакции не выполняется, его режим снят опросом pg_locks из второй сессии через 1,5 с после старта на таблице в 5 млн строк: ShareUpdateExclusiveLock. Записи мешают режимы от Share и выше (ShareRowExclusive, Exclusive, AccessExclusive): они конфликтуют с RowExclusive у INSERT, как в матрице блокировок. Теперь сама миграция. Перед каждым шагом SELECT clock_timestamp() AS t0 \gset, после него INSERT INTO steps VALUES ('метка', :'t0', clock_timestamp()) (в листинге опущено), на сессии SET lock_timeout = '2s':

ALTER TABLE orders ADD COLUMN id_new bigint;
CREATE FUNCTION orders_fill_id_new() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.id_new := NEW.id; RETURN NEW; END $$;
CREATE TRIGGER orders_fill_id_new BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION orders_fill_id_new();
DO $$
DECLARE lo int := 0; mx int := (SELECT max(id) FROM orders);
BEGIN WHILE lo < mx LOOP
  UPDATE orders SET id_new = id WHERE id > lo AND id <= lo + 100000 AND id_new IS NULL;
  COMMIT; lo := lo + 100000;
END LOOP; END $$;
CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_key ON orders (id_new);
ALTER TABLE orders ADD CONSTRAINT orders_id_new_nn CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_new_nn;

Триггер заполняет id_new у новых строк, пачки с COMMIT — у старых. Подмена — одна транзакция с lock_timeout 500 мс; identity пересоздаётся со значения, следующего за старой sequence:

\timing on
BEGIN;
SET LOCAL lock_timeout = '500ms';
ALTER TABLE payment DROP CONSTRAINT payment_order_id_fkey;
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
SELECT last_value + 1 AS nxt FROM orders_id_seq \gset
ALTER TABLE orders DROP COLUMN id;
ALTER TABLE orders RENAME COLUMN id_new TO id;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_key;
ALTER TABLE orders ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (START WITH :nxt);
DROP TRIGGER orders_fill_id_new ON orders;
DROP FUNCTION orders_fill_id_new();
ALTER TABLE orders DROP CONSTRAINT orders_id_new_nn;
ALTER TABLE payment ADD CONSTRAINT payment_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
COMMIT;
ALTER TABLE payment VALIDATE CONSTRAINT payment_order_id_fkey;
VACUUM orders;

Time: 0.646 ms
Time: 0.176 ms
Time: 0.217 ms
Time: 0.219 ms
Time: 29.799 ms

Из четырнадцати строк Time (по одной на команду от BEGIN до COMMIT) оставлены пять: DROP CONSTRAINT внешнего ключа, DROP COLUMN, ADD ... PRIMARY KEY USING INDEX, ADD ... FOREIGN KEY ... NOT VALID и COMMIT; у остальных девяти от 0,012 до 0,250 мс. Блокировки снимаются после COMMIT, а он занял 29,8 мс. Подмена дешёвая по трём причинам: DROP COLUMN не переписывает таблицу, PRIMARY KEY USING INDEX берёт готовый индекс, а скана на NOT NULL нет, потому что валидное CHECK доказывает отсутствие NULL (документация). Внешний ключ возвращается как NOT VALID, миллион строк проверяется после коммита. Результат по шагам:

SELECT s.label, round(extract(epoch FROM s.t1 - s.t0) * 1000) AS step_ms,
       count(w.*) AS inserts, round(max(w.ms)::numeric, 1) AS max_insert_ms
FROM steps s LEFT JOIN wlog w ON w.ts >= s.t0 AND w.ts < s.t1
GROUP BY s.label, s.t0, s.t1 ORDER BY s.t0;

        label         | step_ms | inserts | max_insert_ms 
----------------------+---------+---------+---------------
 1 add column         |       2 |       1 |           1.9
 2 trigger            |       8 |       1 |           2.0
 3 backfill           |   15665 |    1732 |         122.1
 4 index concurrently |    4263 |     337 |          56.5
 5 check not valid    |       2 |       0 |              
 6 validate check     |     244 |      36 |           2.5
 7 swap transaction   |      32 |       5 |           5.5
 8 validate fk        |    1666 |     252 |           2.9
 9 vacuum             |    3036 |     366 |          46.1
(9 rows)

Секундные шаги пропускают запись: самая медленная вставка за их время — 122,1 мс против 3833,9 мс у лобового ALTER. Весь путь без VACUUM занял около 22 с против 3,9 с у лобового ALTER: за отсутствие простоя платят временем. Результат проверен запросом:

SELECT (SELECT format_type(atttypid, NULL) FROM pg_attribute WHERE attrelid = 'orders'::regclass AND attname = 'id') AS id_type,
       (SELECT bool_and(convalidated) FROM pg_constraint WHERE conrelid IN ('orders'::regclass, 'payment'::regclass) AND contype IN ('p', 'f')) AS all_valid,
       pg_size_pretty(pg_table_size('orders')) AS orders_table, count(*) = max(id) AS dense
FROM orders;

 id_type | all_valid | orders_table | dense 
---------+-----------+--------------+-------
 bigint  | t         | 926 MB       | t

Новая identity продолжила нумерацию без разрывов (dense: число строк равно max(id)), все ключи проверены. Цена — место: 926 МБ против 444 МБ, потому что UPDATE пишет новую версию каждой из 5 млн строк (почему), а обычный VACUUM отдаёт место таблице, но не файловой системе; вернуть его можно перепаковкой из статьи про bloat. Столбец id оказался последним, и INSERT без списка столбцов увидит другой порядок.

Подмена должна отказываться, а не ждать. Читатель держит orders (BEGIN; SELECT count(*) FROM orders; SELECT pg_sleep(3); в соседней сессии), подмена идёт с lock_timeout 500 мс (отдельная база в тысячу строк; Time двух первых команд, BEGIN и SET, опущены, как и хвост после ошибки):

ERROR:  canceling statement due to lock timeout
Time: 500.296 ms

Первая команда подмены (DROP CONSTRAINT у payment берёт AccessExclusive и на orders) не дождалась читателя и упала через 500,3 мс, не успев собрать за собой очередь; транзакция откатывается целиком, подмену повторяют, когда читателя нет. В MySQL 8.4 смена типа столбца идёт не INPLACE, а COPY, и тот блокирует запись (опыт 2 про online DDL InnoDB), поэтому и там остаётся путь «новый столбец, заполнение, подмена».

Что из этого следует

Остаток считай по столбцу. Запрос из опыта 2 берёт меньший из пределов sequence и типа; ALTER SEQUENCE ... AS bigint без смены столбца превращает ошибку в отрицательные ключи.
Дырки — норма. Откат, CACHE и сбой дали пропуски 3, 3–10 и 4–33. Нумерация без разрывов (счета, акты) требует отдельного счётчика под блокировкой строки, не sequence.
В миграции мешают записи четыре шага, и каждый короткий. ADD COLUMN, CREATE TRIGGER, CHECK NOT VALID и подмена заняли 2, 8, 2 и 32 мс; секундные шаги идут под RowExclusive и ShareUpdateExclusive. Каждому нужен lock_timeout и повтор. Заполнение удваивает таблицу: 926 МБ вместо 444 МБ.

Чек-лист

Запусти запрос остатка по всем sequence и добавь в мониторинг порог с запасом на время миграции.
До миграции найди зависимости от id: внешние ключи других таблиц, представления, индексы с id. Подмена в опыте переносила один внешний ключ вручную.
Подмену и мгновенные шаги запускай с SET LOCAL lock_timeout в сотни миллисекунд и в цикле с ограничением числа попыток; долгие транзакции перед ней ищи в pg_stat_activity.
Перед подменой убедись, что id_new заполнен у всех строк (валидное CHECK это и доказывает), а приложение не зависит от порядка столбцов.
Убери стенд вместе с данными: docker rm -f -v dba-primary-keys-sequences-int-exhaustion.

Первичные ключи в PostgreSQL: дырки в sequence, исчерпание int4 и перевод на bigint без простоя

Опыт 1: дырки, CACHE и сбой

Опыт 2: 2 147 483 647 и ложное спасение

Опыт 3: перевод на bigint без простоя

Что из этого следует

Чек-лист

Источники