Типы данных: от integer до jsonb

Разработчик создаёт таблицу: `price FLOAT, created_at TIMESTAMP, id SERIAL`. Три колонки - три ошибки. Float теряет центы на миллионах транзакций - финтех-стартап потерял 12 000 долларов за месяц на одном такой баге. Timestamp без timezone показывает разное время в Москве и Нью-Йорке. SERIAL - устаревший синтаксис с неожиданными подводными камнями. Правильные типы данных - это не педантизм, это защита от багов, которые проявятся через полгода в production.

• **Финтех-стартап** потерял 12 000 долларов за месяц из-за округления float при расчёте комиссий - переход на numeric решил проблему
• **SaaS с пользователями в 20 странах** - timestamptz автоматически показывает правильное время каждому пользователю без единой строки кода конвертации
• **API-платформа** использует UUID для публичных идентификаторов - невозможно угадать ID другого пользователя или узнать количество записей

Предварительные знания

• Installation and Configuration

Майкл Стоунбрейкер и расширяемость Postgres

В 1986 году Майкл Стоунбрейкер из UC Berkeley заложил в Postgres (Post-Ingres) принцип расширяемости типов - разработчик может добавить новый тип данных в систему так же, как разработчик языка добавляет новый класс. Это позволило PostgreSQL позднее добавить jsonb, hstore, PostGIS, векторные типы. В 2014 году Стоунбрейкер получил премию Тьюринга. Спустя 38 лет его архитектурное решение об extensible types остаётся основой для pgvector и qdrant-клиентов.

Числовые типы: от smallint до numeric

Финтех-стартап потерял 12 000 долларов за месяц. Причина: `price FLOAT`. Округление при комиссиях накапливалось транзакция за транзакцией. PostgreSQL предлагает целые числа трёх размеров, числа с плавающей точкой и тип numeric для точных вычислений. Неправильный выбор стоит либо потерянного места на диске, либо - что хуже - потерянных денег.

**Никогда не храните деньги в real/double precision!** Пример: `SELECT 0.1::real + 0.2::real` → `0.30000001`. В финансовых операциях на миллионах транзакций эти ошибки накапливаются. Используйте `numeric(p,s)` или храните суммы в центах как `bigint`.

**Правило выбора:** если значение помещается в integer (до 2 млрд) - используйте integer. Для ID в high-load системах - bigint. Для денег - numeric(p,s). Для научных расчётов, где допустима погрешность - double precision.

Строковые типы: char, varchar, text

**В PostgreSQL есть три строковых типа, но на практике разницы между ними почти нет.** Это одна из особенностей, которая удивляет людей, привыкших к MySQL, где varchar(255) и text - принципиально разные типы с разной производительностью.

**В PostgreSQL text, varchar и varchar(n) используют один и тот же внутренний механизм хранения - varlena.** Все три типа хранятся одинаково и работают с одинаковой скоростью. varchar(n) лишь добавляет проверку длины при записи. Это NOT как в MySQL, где varchar(255) и text хранятся по-разному.

**LIKE '%something%'** (с % в начале) не может использовать обычный B-tree индекс - PostgreSQL сканирует всю таблицу. Для поиска подстроки используйте pg_trgm extension с GIN-индексом или full-text search.

**Рекомендация от PostgreSQL core team:** используйте `text` по умолчанию. Добавляйте `varchar(n)` только если ограничение длины - бизнес-требование (email, phone). Не ставьте varchar(255) по привычке из MySQL - это не даёт преимуществ.

Дата и время: timestamptz навсегда

SaaS с пользователями в 20 странах. Одна таблица events с колонкой `created_at TIMESTAMP`. Сервер в UTC, пользователи в Токио, Москве, Нью-Йорке. Баги с временными зонами копились три релиза. Запомните одно правило: **всегда используйте timestamptz** (timestamp with time zone).

**timestamp (без tz) - источник неуловимых багов.** Сервер в UTC, пользователь в Москве записывает '2024-03-15 15:00'. PostgreSQL сохраняет 15:00 без пометки о timezone. Другой сервис, думая что это UTC, показывает 15:00 как 18:00 МСК. Данные испорчены, и никто не знает когда произошло событие.

**timestamptz НЕ хранит timezone!** Внутри это просто microseconds since 2000-01-01 UTC (8 байт, как и timestamp). При записи значение конвертируется в UTC, при чтении - обратно в timezone сессии. Название «with time zone» означает «учитывает timezone при конвертации».

**Правило для NestJS/TypeORM:** в entity ставьте `@Column({ type: 'timestamptz' })`. В миграциях - `timestamp with time zone`. Сервер PostgreSQL настройте на `timezone = 'UTC'`. Конвертацию делайте на фронтенде.

Идентификаторы: SERIAL, IDENTITY, UUID

**Каждой строке нужен уникальный идентификатор - Primary Key.** PostgreSQL предлагает три подхода: старый SERIAL, современный GENERATED ALWAYS AS IDENTITY и UUID. Выбор влияет на безопасность, производительность репликации и удобство работы.

**UUID - альтернатива числовым ID** для распределённых систем, публичных API и случаев, когда нельзя раскрывать порядок создания записей.

**UUID v7** (RFC 9562) включает timestamp в первые 48 бит, что обеспечивает естественную сортировку по времени. Это решает главную проблему UUID v4 - рандомные значения плохо ложатся в B-tree индекс, вызывая splits и фрагментацию. В PostgreSQL 17+ есть `uuidv7()` через расширение.

**Не используйте числовые ID в публичных API!** URL вида `/users/42` раскрывает что в системе 42 пользователя. Конкурент может перебрать `/users/1` .. `/users/10000` и собрать все профили. UUID решает эту проблему: `/users/f47ac10b-58cc-4372-a567-0e02b2c3d479` невозможно угадать.

**Практический совет:** используйте integer/bigint IDENTITY для внутренних ID (FK, JOIN) и UUID для внешних (API, публичные ссылки). Можно иметь оба: `id integer IDENTITY` + `public_id uuid DEFAULT gen_random_uuid()`.

JSONB и массивы: полуструктурированные данные

**JSONB - одна из killer-фич PostgreSQL.** MongoDB внутри PostgreSQL - но с ACID и JOIN-ами. Хранение полуструктурированных данных (настройки, метаданные, вложенные объекты) прямо в реляционной таблице, с индексами и запросами по содержимому.

**Массивы PostgreSQL** - нативный тип для хранения списков однородных значений. Не путайте с JSON-массивами - это отдельный тип с собственными операторами.

**json vs jsonb:** PostgreSQL имеет два JSON-типа. `json` хранит текст как есть (с дубликатами ключей и пробелами). `jsonb` - бинарный формат: парсится при записи, дедуплицирует ключи, поддерживает индексы. Всегда используйте `jsonb`.

**Когда использовать JSONB, а когда отдельные колонки?** Если часто фильтруете и JOIN-ите по полю - вынесите в отдельную колонку. JSONB идеален для метаданных, настроек, логов - данных с переменной структурой, по которым редко делают JOIN.

Пользовательские типы: enum, composite, domain

**PostgreSQL позволяет создавать собственные типы данных.** Это та самая расширяемость, которую Стоунбрейкер заложил в 1986 году. Три наиболее полезных вида: enum (перечисления), composite (составные типы) и domain (типы с ограничениями).

**Добавить значение в ENUM можно, удалить - нельзя (до PostgreSQL 16).** `ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled'` - работает. Удаление и переименование значений невозможно без пересоздания типа. Подумайте дважды перед использованием ENUM для часто меняющихся списков.

**Domain vs CHECK constraint:** оба валидируют данные, но domain - переиспользуемый. Создайте domain `email` один раз и используйте в 10 таблицах. Изменение валидации `ALTER DOMAIN email ADD CHECK(...)` применится ко всем таблицам.

**Альтернатива ENUM для часто меняющихся списков:** справочная таблица с FK. `CREATE TABLE statuses (id serial, name text unique)` + `REFERENCES statuses(id)`. Легко добавлять, удалять и переименовывать значения.

Ключевые идеи

• **Числа:** integer для большинства случаев, bigint для больших значений, numeric(p,s) для денег. Никогда float для финансов!
• **Строки:** text по умолчанию, varchar(n) если нужно ограничение длины. В PostgreSQL разницы в производительности нет
• **Время:** всегда timestamptz. Никогда timestamp. Это спасёт от неуловимых timezone-багов
• **Идентификаторы:** GENERATED ALWAYS AS IDENTITY вместо SERIAL. UUID для публичных API
• **JSONB:** для полуструктурированных данных с GIN-индексом. Но основная модель - реляционная
• Три ошибки из hook: `price numeric(12,2)`, `created_at timestamptz`, `id integer GENERATED ALWAYS AS IDENTITY`

Связанные темы

Типы данных влияют на индексирование, производительность и дизайн схемы:

• DDL: создание таблиц — Как применять типы данных при CREATE TABLE, ALTER TABLE, constraints
• JSONB глубже — Расширенные операции с JSONB: jsonpath, обновление вложенных полей, производительность
• B-tree индексы — Как выбор типа данных влияет на размер индекса и скорость поиска

Вопросы для размышления

• Почему PostgreSQL не сделал numeric типом по умолчанию для всех чисел, раз он точнее float? Подумайте о производительности и размере хранения.
• В каких случаях стоит выбрать JSONB-колонку вместо отдельных колонок? А когда наоборот - вынести данные из JSONB в реляционную структуру?
• Коллега предлагает хранить все ID как UUID для единообразия. Какие аргументы «за» и «против» можно привести?

Связанные уроки

• pg-04-ddl — DDL - практическое применение типов при CREATE TABLE и ALTER TABLE
• pg-12-btree — Выбор типа прямо влияет на размер B-tree индекса и скорость поиска
• pg-32-jsonb — Расширенные операции с JSONB: jsonpath, обновление вложенных полей
• db-03-acid — Правильные типы (numeric для денег) - основа ACID Consistency
• pg-24-mvcc — MVCC версионирует строки - тип влияет на размер tuple и overhead MVCC
• db-02-relational-model