Шпаргалка по работе с PostgreSQL для бэкенд-разработчиков

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

Работа с 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 — мощный инструмент, но с большой силой приходит и большая ответственность. Следуя этим практикам, вы не только избежите ночных инцидентов, но и станете тем разработчиком, на которого равняется команда.

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

• Официальная документация

• Use The Index, Luke

• PG Mustard (визуализация EXPLAIN)

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