DDL (Data Definition Language) - это команды, которые описывают структуру базы: создают таблицы, меняют столбцы, раскладывают объекты по схемам. В этом уроке разберём, как в postgresql завести таблицу с правильными типами данных и значениями по умолчанию, как потом безопасно её переделать через ALTER TABLE (и почему один ALTER может положить нагрузку, а другой - нет), как организовать объекты по схемам и search_path, что такое tablespaces и зачем нужны временные и нелогируемые таблицы. Цель проста: чтобы ваша схема не превратилась через год в свалку, а изменения на проде не вызывали долгих блокировок.
Как это работает
Таблица в PostgreSQL - это не просто сетка строк, а набор столбцов, каждый со своим типом. Тип определяет, что туда влезет и сколько места займёт. Когда вы пишете CREATE TABLE, планировщик и движок начинают полагаться на эти типы при проверках, индексах и оценках. Поэтому выбор типа - это не косметика, а основа корректности.
DEFAULT задаёт значение, которое подставится, если столбец не указан в INSERT. Важная деталь: DEFAULT вычисляется в момент вставки строки, а не в момент создания таблицы. Поэтому now() в DEFAULT даст время каждой вставки, а не время CREATE TABLE.
Схема - это пространство имён внутри одной базы. Объекты с одинаковым именем могут жить в разных схемах и не конфликтовать. Какую схему PostgreSQL подставит, если вы написали просто имя таблицы без префикса, решает параметр search_path - это упорядоченный список схем, который сервер просматривает слева направо. По умолчанию там public, но в нормальном проекте схемы разделяют по доменам или модулям.
ALTER TABLE меняет уже существующую таблицу. Ключевой вопрос здесь - блокировки. Многие формы ALTER берут блокировку ACCESS EXCLUSIVE на таблицу, то есть на это время к ней нельзя ни читать, ни писать. Хорошая новость: добавление столбца с обычным DEFAULT с версии 11 происходит мгновенно, без переписывания всех строк (метаданные хранят default отдельно). А вот смена типа столбца обычно требует полной перезаписи таблицы и долгой блокировки.
Tablespace - это привязка объекта к каталогу на диске. Через tablespaces можно разнести тяжёлые таблицы на отдельный (например, быстрый) накопитель, не трогая логику. Временные (TEMP) таблицы видны только внутри сессии и исчезают по её завершении. Нелогируемые (UNLOGGED) таблицы не пишутся в WAL: вставки в них быстрее, но при сбое сервера их содержимое обнуляется и они не реплицируются. Это инструмент для промежуточных данных, не для боевых.
SQL и примеры
Создадим рабочую схему и простую таблицу заметок об аэропортах поверх демобазы bookings (демобаза Авиаперевозки от Postgres Professional):
Код: Выделить всё
CREATE SCHEMA ops;
CREATE TABLE ops.airport_notes (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
airport char(3) NOT NULL,
note text NOT NULL,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now()
);
Свяжем заметки с реальными аэропортами и посмотрим активные:
Код: Выделить всё
INSERT INTO ops.airport_notes (airport, note)
SELECT airport_code, 'нужна проверка стоек'
FROM bookings.airports
WHERE airport_code IN ('SVO', 'LED', 'KJA');
SELECT n.airport, a.airport_name, n.created_at
FROM ops.airport_notes n
JOIN bookings.airports a ON a.airport_code = n.airport
WHERE n.is_active;
Теперь ALTER TABLE. Добавим столбец с дефолтом (быстро, без перезаписи) и аккуратно поменяем тип:
Код: Выделить всё
ALTER TABLE ops.airport_notes
ADD COLUMN priority integer NOT NULL DEFAULT 0;
ALTER TABLE ops.airport_notes
ALTER COLUMN note TYPE varchar(500) USING left(note, 500);
Управление search_path, чтобы не писать ops. перед каждым именем:
Код: Выделить всё
SHOW search_path;
SET search_path TO ops, bookings, public;
SELECT count(*) FROM airport_notes;
Код: Выделить всё
CREATE TEMP TABLE tmp_top_airports AS
SELECT departure_airport, count(*) AS cnt
FROM bookings.flights
GROUP BY departure_airport;
CREATE UNLOGGED TABLE ops.import_staging (
raw_line text
);
Частые грабли
- Думают, что DEFAULT now() запоминает момент создания таблицы. Нет, оно вычисляется при каждой вставке.
- Меняют тип столбца на большой таблице в рабочее время и ловят долгую блокировку ACCESS EXCLUSIVE - все запросы к таблице висят.
- Кладут всё в public и потом не могут разделить права. Схемы - это и про порядок, и про доступ.
- Забывают, что UNLOGGED-таблица обнуляется при крахе сервера и не попадает на реплику. Для важных данных это потеря.
- Ставят search_path так, что одноимённый объект из чужой схемы перехватывает запрос. Порядок схем в списке важен.
- Используют serial в новых проектах. Лучше GENERATED ... AS IDENTITY: чище права и поведение, это рекомендуемый способ в актуальных версиях.
- Льют char(N) на тексты переменной длины. Для большинства задач берите text или varchar, char дополняет пробелами.
- Создайте схему lab: CREATE SCHEMA lab;
- Создайте таблицу lab.flight_audit с id (GENERATED ALWAYS AS IDENTITY), flight_id integer, status text DEFAULT 'new', changed_at timestamptz DEFAULT now().
- Вставьте 3 строки, указав только flight_id, и проверьте, что status и changed_at заполнились сами.
- Через ALTER TABLE добавьте столбец comment text DEFAULT '' и убедитесь по \d lab.flight_audit, что он появился.
- Сделайте SET search_path TO lab; и выполните запрос к flight_audit без префикса схемы.
- Создайте CREATE UNLOGGED TABLE lab.scratch (x int), вставьте данные, сравните скорость с обычной таблицей на большом INSERT.
- Удалите учебные объекты: DROP SCHEMA lab CASCADE;
- В какой момент вычисляется выражение в DEFAULT и почему это важно для now()?
- Почему ADD COLUMN с DEFAULT теперь быстрый, а ALTER COLUMN TYPE - нет?
- Как search_path влияет на то, к какой именно таблице обратится запрос без указания схемы?
- Чем UNLOGGED-таблица отличается от обычной и от TEMP по времени жизни и надёжности?
- Зачем нужны tablespaces и какую задачу они решают на уровне физического хранения?
- Почему GENERATED AS IDENTITY предпочитают serial в новых схемах?
