21.10.2025 07:05
alexgreendev 24 12000 Источник

Лайфхаки для миграций, оптимизации и избегания граблей

Работа с PostgreSQL — критически важный навык для бэкенд-разработчиков. Но именно здесь многие спотыкаются на прод-окружении. Эта шпаргалка соберет ключевые практики, которые помогут избежать частых ошибок.

"Знание PostgreSQL — это не запоминание синтаксиса, а понимание того, как СУБД обрабатывает данные на уровне страниц, транзакций и WAL".

— Грег Смит, автор "PostgreSQL 9.0 High Performance"

Содержание

  1. Миграции: как не сломать прод

  2. Оптимизация запросов: выжимаем скорость

  3. Соглашение по именованию: код как документация

  4. Где чаще всего ошибаются

  5. Чеклист перед запуском в прод

1. Миграции: как не сломать прод

Миграции — это код, который нельзя откатить Ctrl+Z. Ошибки здесь стоят дорого.

Правила безопасных миграций:

  • Идемпотентность
    Каждая миграция должна работать при повторном применении:

    CREATE TABLE IF NOT EXISTS users ( ... ); -- Хорошо
CREATE TABLE users ( ... ); -- Плохо (упадет при повторном запуске)

  • DDL в транзакциях
    В PostgreSQL DDL можно обернуть в транзакцию (в отличие от MySQL!):

    BEGIN;
ALTER TABLE orders ADD COLUMN status TEXT;
-- CONCURRENTLY нельзя в транзакции!
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
COMMIT;

  • Долгие операции — только с CONCURRENTLY
    Создание/удаление индексов на больших таблицах:

    -- Не блокирует запись
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);

    То же самое относится к любым долгим операциям. Например, частая ошибка — это добавить новое поле в большую таблицу с дефолтным значением. Это будет долгая блокирующая операция всегда для PostgreSQL <= 11 версии, если дефолтное значение не является константной или новое поле NOT NULL (для любой версии), так как PostgreSQL будет копировать дефолтное значение во все записи в таблице.

  • Изменение колонок через новую колонку
    Прямой ALTER TYPE блокирует таблицу! Рецепт:

    1. Добавить новую колонку new_column с нужным типом

    2. Добавить триггер на UPDATE для сохранения значений и в старое, и в новое поле.

    3. Перенести констрейнты и индексы

    4. Написать фоновый скрипт для копирования исторических данных.

    5. Переименовать старую колонку, переименовать новую

    6. Если все ОК, то удалить старую колонку и индексы.

Это лишь один из вариантов, который может не подойти для вашего конкретного случая. Тут я опустил порядок выполнения миграций и деплоев.

Релизы и откаты:

  • Пишите down-миграции. Тестируйте их!

  • Для данных используйте обратимые преобразования:

    -- up
UPDATE users SET status = 'active' WHERE status = 'new';

-- down
UPDATE users SET status = 'new' WHERE status = 'active';

Чаще всего это пременимо только для маленьких таблиц и возможность отката существует лишь в течение короткого промежутка времени, и она особенно необходима во время релиза. Заниматься этим стоит, когда в крупном релизе присутствуют высокие риски ошибок, и важно продумывать стратегию быстрого отката. Иногда для этого приходится временно сохранять какие-то данные, чтобы иметь возможность откатиться. Но это очень выручает, когда что-то идет не так в проекте, где каждая минута простоя стоит миллионы. Без подобной стратегии восстановление работы может занять непредсказуемо много времени. Если таблица большая, то миграция применяется только в локальных и тестовых средах, а на проде выполняется силами DBA (касетные обновления и тд) так как хорошей практикой считается, что миграция не должна выполняться более 3-5 секунд.

2. Оптимизация запросов: выжимаем скорость

Анализ проблемных запросов:

  • Включите логирование медленных запросов в postgresql.conf:

    log_min_duration_statement = 100  # Логировать запросы >100ms

  • Если план выполнения запроса неочевиден, то сгенерируйте в локальной БД побольше данных для тестирования индексов. Планировщик строит план запроса в том числе в зависимости от количества данных. Данные должны быть разнообразными и максимально похожими на реальные исторические данные — это тоже влияет на план запроса.

Практики оптимизации:

  • EXPLAIN — ваш лучший друг
    Всегда смотрите план перед запуском на проде:

    EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE user_id = 123 AND status = 'shipped';

  • Индексы: не просто добавить, а правильно выбрать

    • Составные индексы: (user_id, order_id) вместо двух отдельных

    • Частичные индексы для фильтрации:

      CREATE INDEX idx_orders_active ON orders(user_id) 
WHERE status = 'active'; -- Индекс в 10-100 раз меньше!

    • Используйте покрывающие индексы (Index-Only Scan):

      CREATE INDEX idx_orders_covering ON orders (created_at, user_id)
INCLUDE (total);
-- Запрос использует только индекс:
SELECT user_id, total FROM orders WHERE created_at > '2023-01-01';

Использование INCLUDE особенно полезно для аггрегирующих запросов

  • Бойтесь N+1 в ORM
    Типичная ошибка в ORM:

    # Плохо: 100+ запросов для 100 пользователей
users = User.objects.all()
for user in users:
    print(user.orders.count())

# Хорошо
users = User.objects.prefetch_related('orders').all()

3. Соглашение по именованию: код как документация

Правила именования:

  • Таблицы: snake_case (users, order_items). Часто предпочтение отдается именованию в единственном числе.

  • Колонки: created_at, user_id (не UserId или creationDate)

  • Первичные ключи: id (не user_id в таблице users)

  • Внешние ключи: user_id (не userId или owner)

  • Индексы: idx_table_column (idx_orders_user_id)

  • Ограничения: pk_table (pk_users), fk_table_reference (fk_orders_users)

Я не хочу сказать, что это единственно верное именование. Главное, чтобы вся команда придерживалась одного стиля.

Избегайте:

  • Зарезервированных слов

  • Префиксов (tbl_users) — это не SQL Server 2000

4. Где чаще всего ошибаются

  1. Транзакции на все запросы BEGIN/COMMIT
    ORM делает неявные транзакции? Проверьте настройки! Явный контроль — надежнее.

  2. SELECT * в коде приложения
    Тащите только нужные поля:

    SELECT id, email FROM users; -- Вместо SELECT *

  3. Игнорирование блокировок
    Долгая транзакция = блокировка записей. Используйте:

    SET lock_timeout = '5s'; -- Прервать запрос при долгом ожидании

  4. Миграции без тестирования
    Всегда тестируйте миграции UP и DOWN. Если в одном релизе несколько миграций, то тестируйте возможность отката к исходному состоянию после каждой миграции и учитывайте необходимость совместимости кода с схемой БД после каждой миграции, чтобы правильно выбрать порядок миграций и деплоя новой версии кода.

  5. Касетные обновления/удаления
    Всегда добавляйте LIMIT в цикле:

    WHILE EXISTS (SELECT 1 FROM orders WHERE status = 'old') 
LOOP
  DELETE FROM orders WHERE status = 'old' LIMIT 1000;
  COMMIT; -- Освобождаем блокировки
END LOOP;

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

  6. Миграция содержит операции, которые блокируют таблицу с большим количеством данных и нагрузкой. Например, изменение типа столбца, добавление столбца с дефолтным значением, добавление индекса. По возможности нужно использовать CONCURRENTLY и делать это не в миграциях, а на прод-БД. Например, в случае с Django миграция выполняется с флагом --fake, а сами изменения делаются вручную с CONCURRENTLY, если это возможно. Главное — не блокировать таблицу надолго, иначе наше приложение не будет работать, пока не завершится блокирующая операция. Всегда до релиза убедитесь, что вы не делаете блокирующих операций на больших таблицах.

  7. Старая версия кода не работает после применения миграций. Допустим, после применения миграции приложение не сможет работать на старой версии кода. В этом случае необходимо сначала выполнить деплой новой версии, где приложение будет иметь возможность работать до и после миграции, поддерживая и старую, и новую схему БД. Например, если мы удаляем столбец, то нам нужно сначала выполнить деплой новой версии кода, в которой не используется этот столбец, и только потом применять миграцию, которая удаляет столбец. Если мы добавляем столбец, то необходимо сначала выполнить миграцию, а потом деплой новой версии кода. Могут быть разные ситуации в том числе, когда мы выполняем миграции и до, и после деплоя. Основной посыл здесь в том, что мы всегда должны думать о совместимости кода с схемой БД, особенно когда у нас несколько ДЦ.

  8. Медленные запросы.
    Часто разработчики забывают, что если все работает быстро у них локально или на тестовом сервере, это не означает, что так же будет и на проде, где сотни миллионов записей в таблицах. Если добавили SQL-запрос или внесли изменения в существующий, проверяйте план запроса с помощью EXPLAIN и убедитесь, что в запросе используются эффективные индексы. Если есть сомнения, обратитесь за ревью к опытным коллегам или сгенерируйте побольше данных в локальной БД для проверки индексов.

Чеклист перед запуском в прод

  • Миграция идемпотентна и имеет down-скрипт.

  • Индексы для новых запросов протестированы через EXPLAIN на большом объёме данных. На небольшом объёме индексы могут не использоваться или планировщик может выбрать другой индекс.

  • ORM-запросы не генерируют N+1.

  • Имена сущностей соответствуют конвенции команды.

  • Длительные операции разбиты на батчи.

  • Миграция не блокирует таблицы длительно. Если есть долгая операция, то она выполняется не в миграции, а вручную на prod-БД и использует CONCURRENTLY, если возможно. Например, хорошей практикой является не допускать блокировок (более тяжелых, чем AccessExclusiveLock) длительностью более 1–5 секунд на высоконагруженных таблицах. На практике обычно предпочитают вообще не выполнять в миграциях операции дольше 3–5 секунд, даже если они не блокирующие.

  • Миграции должны быть обратно совместимы — применённая миграция не должна влиять на работу текущей версии приложения. Расширять до, изменять во время, очищать после.

  • Миграция схемы БД и миграция данных разделены (рекомендации DSF).

  • Откат миграции планируется и проверяется также, как и её применение.

  • Разработчик обязан убедиться, что после каждого шага миграции приложение будет работать корректно.

  • Все изменения SQL-запросов протестированы на предмет наличия необходимых эффективных индексов.

"В проду нет 'я не знал'. Есть 'я не проверил'".

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

Дополнительные ресурсы:

А какие ваши любимые лайфхаки по PostgreSQL? Делитесь в комментариях!

Комментарии (24)


  1. Sleuthhound
    21.10.2025 07:59
    #28990692

    >>Миграции — это код, который нельзя откатить

    На этих словах статью можно закрыть.

    Нет, это не верное утверждение, даже я бы сказал опасное.

    Миграции должны иметь механизм отката и он есть, для этого и делают up и down миграции. Up это накат изменений, Down это откат этих изменений. Если у вас нет механизма отката миграции, то грош цена вашему проекту (сервису).


    1. kamaz1
      21.10.2025 07:59
      #28990834

      Может если у вас постоянно косячные миграции которые надо откатывать, то грош цена таким разработчикам? Непомню даже, приходилось ли когда откатывать миграции. Но откат всегда можно выполнить новой миграцией


      1. qngdjas
        21.10.2025 07:59
        #28997420

        Временами приходится таким образом конфликты решать, когда успел собственные миграции поверх необновлённой ветки применить


    1. Q3_Results
      21.10.2025 07:59
      #29001554

      Автор имел в виду, что нельзя откатить просто так, без дополнительных приседаний и седых волос релиз-менеджера. А если бы прочитали дальше, то автор так же пишет, что нужны up и down миграции.


  1. erogov
    21.10.2025 07:59
    #28990980

    Например, частая ошибка — это добавить новое поле в большую таблицу с дефолтным значением. Это будет долгая блокирующая операция, так как PostgreSQL будет копировать дефолтное значение во все записи в таблице.

    Да уж семь лет как не блокирует. Кому нужен очередной сборник мифов из интернета?

    А вот это так просто просто шедевр:

    Сгенерируйте побольше данных в локальной БД для проверки индексов.


    1. Sleuthhound
      21.10.2025 07:59
      #28991076

      Мне кажется кто-то опытный (возможно из постгреспро) должен уже написать аналогичную статью, но уже "по делу" и развеять все мифы раз и навсегда.


    1. AZverg
      21.10.2025 07:59
      #28992320

      >> Кому нужен очередной сборник мифов из интернета?

      Тем кто идет на собеседования и будет общаться с людьми продолжающими жить в мифах (ну или все знания которых получены из таких статей)


    1. alexgreendev Автор
      21.10.2025 07:59
      #28993366

      Спасибо за комментарий) Смотря что в дефолтном значении. Добавил уточнение


  1. minamoto
    21.10.2025 07:59
    #28991668

    -- up
    UPDATE users SET status = 'active' WHERE status = 'new';
    -- down
    UPDATE users SET status = 'new' WHERE status = 'active';

    Мимо. Второй скрипт не откатит состояние таблицы к тому, которое было до наката первого скрипта. Подсказать, почему, или и так понятно? )


    1. yarkov
      21.10.2025 07:59
      #28993160

      Подсказать, почему, или и так понятно? )

      Мастер интриги )


      1. minamoto
        21.10.2025 07:59
        #28993174

        Так вроде довольно очевидно и без подсказки )

        Достаточно представить таблицу, в которой 1000 записей со статусом active, 1000 записей со статусом new, мысленно применить к ней два скрипта и подумать, что будет в итоге.


        1. yarkov
          21.10.2025 07:59
          #28997374

          Да вы ещё и адмирал ))


    1. alexgreendev Автор
      21.10.2025 07:59
      #28993356

      Спасибо за комментарий) Это просто "наивный" пример, чтобы было понятно о чем речь.


      1. minamoto
        21.10.2025 07:59
        #28995358

        У вас очень много по статье таких "наивных" неправильных примеров. Проблема как раз в этом - материал вроде бы расчитан на новичков, которые могут взять и скопировать такой пример, не подумав достаточно, и огрести себе проблем с этим в дальнейшем. Из-за чего окажется, что полезность вашей статьи по общему объему причиненных последствий скорее отрицательная.
        Мое мнение, что лучше никаких примеров не давать, чем давать такие.


        1. boldape
          21.10.2025 07:59
          #28995972

          Мне вот стало интересно, вот люди пишут пишет даун миграции дают пример который не верный, другие подсвечивают ошибки, но никто толком не объясняет что в общем случае ап миграции невозможно ревертнуть.

          Возьмём этот же пример, кажется что можно сохранить айдишники всех обновленных строк и тогда можно откатить. Но это только кажется, т.к. между накатом и откатом строка может поменяться по любой причине и тогда откатывать применявшуюся строку нельзя. Ок давайте смотреть на айди и статус, все равно нельзя потому что статус может тоже поменяться. Ок берём айди + апдэйтед_эт + статус, но тогда некоторые строки могут не откатиться. Это наверное самый безопасный откат, но где же хранить эти данные? Да и при таком раскладе все равно 100% откат невозможен в принципе.

          Вопрос, а если нельзя в принципе откатиться гарантировано то зачем вообще заниматься этой хернёй?


          1. alexgreendev Автор
            21.10.2025 07:59
            #28996444

            Спасибо за комментарий) Да чаще всего возможность отката есть только через короткий промежуток времени и это может быть нужно во время релиза. Заниматься этим стоит, когда есть высокие риски ошибок в большом релизе и продумывать стратегию быстрого отката. Иногда приходится для отката временно сохранять какие-то данные чтобы откатиться. Но это очень сильно спасает, когда что-то пошло нет так в проекте, где каждая минута простоя стоит миллионы. Без этого восстановление работы может занять непредсказуемо много времени. Добавлю уточнение спасибо


            1. boldape
              21.10.2025 07:59
              #29001460

              Я, вам, честно не верю. Если у вас в реальности, а не в теории, был случай когда в проде успешно сработала даун миграция и пофиксила реализовавшийся заранее предвиденных риск, то это событие со всеми деталями, куда больше достойно статьи чем та под которой мы общаемся.

              У всех все по-разному конечно организовано, но мне вот не очень понятно как вообще в теории даже люди организуют запуск даун миграций на проде? Ап миграции обычно идут в комплекте с новым релизом тут все понятно, а как даун миграция доставляется до прода? Нельзя просто так взять и после ап миграции запустить даун, т.к. уже развернутая версия прода может тупо крашиться из за даун миграции. Раскатать предыдущую версию? Но она уже может и не запуститься, никто никогда не тестирует форвард совместимость. В общем вопрос о том как даже на бумаге/чисто в теории организовать более менее безопасную даун миграцию вообще не тривиален, а на практике если у вас нет строго бюрократического процесса релиза ваша даун миграция скорее ещё больше усугубит проблему из за которой ее решили запустить.

              В общем, я вам, про даун миграции вообще не верю.


              1. alexgreendev Автор
                21.10.2025 07:59
                #29001802

                Почему вы мне не верите? Я вас никогда не обманывал)

                А если серьезно, то конечно откат миграций я видел не раз. Многое зависит от процессов, а они в свою очередь от требований к проекту.

                Например, если проект имеет низкую нагрузку или стоимость простоя не велика, то часто принебрегают стратегиями отката, а в случае каких-то проблем, просто готовят новый релиз с исправлением ошибки.

                А если проект с высокой нагрузкой и высокой стоимость простоя, то и процессы налаживают с учетом этого. В финтех проекте, где я сейчас работаю, требование по доступности %99.99, а минута простоя стоит миллионы.

                Для каждого релиза обязательно подробно расписываем план релиза и план отката. Если после релиза что-то пошло не так, а откат невозможен, то к автору этого релиза будут большие вопросы. Автоматизация в пайплайнах gitlab и доставка down миграции выглядит так же, как и up. В большинстве релизов сначала выполняется up миграция, а потом деплой, а значит в случае отката версии кода нужно откатить и миграции. Иногда миграции не откатывают если проблема очевидна и можно собрать новый релиз в ближайшее время. Во время релиза катим не master ветку а релизный tag, новый код в mater попадает только после успешного релиза это упрощает процесс отката и не блокирует другие релизы в случае отката первого.

                Форвард совместимость мы тестируем всегда. Это обязательно нужно делать при наличии высоких рисков + у нас несколько ДЦ и планировать релиз и откат нужно с учетом этого.

                Откаты релизов происходят не часто и если такое случается и есть убытки, то пишем подробный post mortem и делаем выводы, как этого можно было избежать.

                По поводу отдельной статьи на эту тему подумаю, возможно напишу)


          1. AZverg
            21.10.2025 07:59
            #29000194

            ...в общем случае ап миграции невозможно вернуть/развернуть...

            ...зачем вообще заниматься этой..

            В общем случае нельзя статическими, заранее подготовленными скриптами вернуться к логически первоначальному результату :)

            В частных случаях, если мы говорим про DML, через миграции практически всегда обновляются справочники, то есть части которые изменяются только через миграции и можно прогнозировать что и когда может измениться. Если говорить про DDL там проблем ещё меньше.

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

            Из практики, если успели допустить значительные изменения данных, нужно будет готовить новые скрипты, предусмотреть всё заранее не удастся, да и не требуется.


    1. DrDir
      21.10.2025 07:59
      #28995098

      Откатит, если до первого скрипта не было ни одного status = 'active' :) Но так как этого мы не знаем, то делать так конечно категорически нельзя. Интересно, в Бегете реально так делают?


  1. surly
    21.10.2025 07:59
    #28995058

    Это вредный совет:

    • Первичные ключи: id (не user_id в таблице users)

    • Внешние ключи: user_id (не userId или owner)

    Я всегда придерживаюсь другого правила. Для каждой таблицы придумываю 2--4-буквенное сокращение и использую его как префикс для всех колонок этой таблицы. А если в таблице есть внешний ключ, то эта колонка называется точно так же, как в родительской таблице.

    Т.е. если таблица users будет ссылаться на таблицу accounts, то в таблице users будут колонки usr_id, acc_id.

    При таком наименовании у JOIN в запросах не придётся перечислять условия равенства, а достаточно написать NATURAL JOIN (в более сложных случаях иногда требуется JOIN USING). И если состав внешнего ключа поменяется, то текст запроса менять не придётся.


    1. KoIIIeY
      21.10.2025 07:59
      #28995232

      Да это тупо читать проще, сложнее закосячить. Не понимаю смысла ходить и всем рассказывать что поле должно называться id, а не user_id


    1. alexgreendev Автор
      21.10.2025 07:59
      #28995434

      Спасибо за комментарий) Подход с унифицированными именами столбцов действительно имеет преимущества для NATURAL JOIN.

      Однако в своей практике я предпочитаю избегать NATURAL JOIN из-за его неявного поведения. Чтобы он гарантированно работал правильно, необходимо, чтобы все таблицы в проекте строго следовали единым правилам именования. В больших проектах с множеством разработчиков всегда есть риск, что новый член команды создаст таблицу по другим правилам, или что при изменении схемы добавятся столбцы с совпадающими именами. За этим сложно следить, особенно если это монолит, в котором работает несколько команд, и их состав периодически меняется.

      Я предпочитаю явно указывать условия в ON - это делает запросы более предсказуемыми и устойчивыми к изменениям. А если не использовать NATURAL JOIN, то префиксы к каждому столбцу в таблице могут быть избыточны.


  1. Nur47
    21.10.2025 07:59
    #29003298

    С начало думал, что увидел знакомые слова(спасибо автору), почитал комментарии, такое впечатление, что мне "глаза выдавили".