DBA: когда почти закончился serial

[RedQuark]

Здесь есть существенное допущение, что первичный ключ некластерный, что позволило заранее подготовить индекс с новым типом. Не рассматривался вариант с новой таблицей, куда все скопировано и потом переключение? Все равно после манипуляций с колонками таблицу наверное надо "выравнивать"(появились пустые байты, новая колонка где то сбоку записалась). Подошло бы и под кластерный ключ.

[Kilor]

Вариант с полным переписыванием таблицы тоже возможен (ровно так поступает pg_repack, если не ошибаюсь), но накладывает и дополнительные ограничения — как минимум, необходимость иметь 2x дискового пространства «в моменте».

В случае же сегментного UPDATE, autovacuum может успевать оперативно высвобождать место, если процесс сильно растянут во времени.

[Tangeman]

Один маааленький нюанс не учтён в этом сценарии — всё это не будет работать пока есть хотя бы один VIEW который ссылается на одно из модифицируемых полей (прямо или косвенно), то есть эти VIEW нужно сначала удалить (перед модификацией) а потом пересоздать.

Нужно также не забыть про ограничения доступа и прочие мелочи, но если всё что нам нужно это изменить INT на BIGINT, то достаточно будет этого набора и банального ALTER TABLE tab ALTER COLUMN id TYPE BIGINT в промежутке между сохранением зависимостей и восстановлением (всё онлайн и в транзакции) — никаких триггеров, дропов, промежуточных полей, переименований и прочих бубнов (кроме, разумеется, пересоздания VIEWs которые и делает упомянутая тулза).

[Kilor]

Да, VIEW, RULE и разнообразные GRANT я не рассматривал в силу редкости использования, как и рекурсивные зависимости — для демонстрации модели это было бы избыточным, а для тулзы — как раз тема.

[Tangeman]

Может GRANT и RULE и редко используются, но вот VIEWs это очень распостранённая вещь, редкая база (кроме разве что вордпрессовских и подобных) без них обходится, уж зело удобно это для массы вещей.

Но если у вас нет VIEW — то зачем вообще городить огород? Почему просто не сделать ALTER COLUMN id BIGINT? Ничего больше не нужно — всё автоматом сконвертируется, индексы менять не надо, вообще ничего делать не надо.

[Kilor]

VIEW крайне неудобны в поддержке именно необходимостью их пересоздавать при изменениях структуры БД.

Просто ALTER COLUMN SET TYPE повесит блокировку на таблицу и начнет ее грустно переписывать. От версии к версии необходимость full rewrite все снижается, но если varchar(n) / text поддержали еще в 9.1, то timestamp / timestamptz — только в v12.

[Antohin]

Цель VIEW как раз обратная — оградить приложения от изменения структуры базы. Именно этим они и удобны :)
Пример из практики, как раз в тему статьи. У нас в одной из версий системы (речь о oracle, но суть та же) pk/fk были number. В следующей версии сделали их number(9,0). Все приложения со статической типизацией полей стали валиться с ошибками несоответствия типов. Поправили типы полей. Однако хочется и обратной совместимости — чтобы приложения могли работать и с предыдущей версией БД. Если бы не было view пришлось бы поддерживать 2 версии, а так просто во VIEW поправили select pk… на select cast(pk as number(9,0)) as pk…
И да, самое сложное не в самой базе, а в приложения ее использующих — далеко не все из приложений стерпят молча замену int на bigint

[KivApple]

А мораль простая — пытаться выбирать тип первичного ключа придывая сколько данных будет в таблице — плохая затея (потому что может оказаться, что либо мы плохо знаем предметную область, либо модель данных эволюционирует так, что наши допущения больше не будут верны).

BIGINT/BIGSERIAL наше всё. Маленькие числовые типы в primary key — антипаттерн. Потому что на него слишком много всего завязано.

[Tangeman]

Вообще-то нередко бывают случаи когда число строк в таблице гарантированно вписывается не только в 32 бита а даже в 16 или 8 — если это таблица определений а-ля список типов/enum/etc и ещё чего-то заведомо малочисленного. Плюс, если на них ссылаются где-то из реально больших таблиц то это может сэкономить кучу места и повысить производительность (потому что по FK строят индексы).

[edo1h]

BIGINT/BIGSERIAL наше всё

а почему не uuid?

[Imposeren]

Когда очень много таблиц, то примерная «последовательность во времени» integer-ов очень часто оказывается полезной. uuid тоже можно наверное «сортируемо» гененрировать, но зачем его тогда вообще использовать?

[akhkmed]

Автоматизация по смене типа данных есть в скрипте https://github.com/comagic/transparent_alter_type

Работает в фоне и незаметно для приложения, как и pg_repack.