Что это решает
Таблица ломается предсказуемо. Файл на несколько сотен тысяч строк открывается минуту и падает при пересчёте. Формула ссылается на диапазон, который съехал после вставки строки, и цифры тихо врут. Существуют «отчёт_финал» и «отчёт_финал_2», расходящиеся на сорок строк, и никто не помнит, какой из них правильный. Кто-то удалил столбец, а узнали об этом через месяц. PostgreSQL снимает ровно эти четыре боли: объём перестаёт упираться в память одной машины, несколько человек пишут одновременно без копий файла, данные защищены правилами на уровне хранилища, а любая цифра воспроизводится запросом, а не последовательностью кликов.
Как устроено
Проще всего заходить через соответствия привычным вещам.
| В таблице | В базе |
|---|---|
| Лист | Таблица |
| Строка | Запись |
| Столбец | Поле с фиксированным типом |
| Номер строки | Первичный ключ |
| ВПР / VLOOKUP | Внешний ключ и join |
| Сводная таблица | group by с агрегатами |
| Проверка данных | Ограничения check, unique, not null |
| Второй лист с формулами | Представление (view) |
| Сортировка перед поиском | Индекс |
| Сохранить копию перед правкой | Транзакция и резервная копия |
Главное отличие в поведении: в базе нет формул внутри ячеек. Данные хранятся, вычисления делаются в момент запроса. Это и убирает съехавшие диапазоны — запрос описывает, что нужно получить, а не по каким координатам ползти.
Тип столбца и ограничение — это и есть проверка данных; всё, что не запрещено схемой, рано или поздно окажется в таблице.
Типы стоит выбрать один раз и осознанно. text для строк любой длины — ограничение длины в Postgres не даёт выигрыша в скорости, только лишний повод для ошибки при импорте. integer и bigint для счётчиков и идентификаторов. numeric для денег: этот тип считает десятичные дроби точно. double precision для денег не подходит совсем — сумма трёхсот строк по 0.1 даст хвост в пятнадцатом знаке, а потом расхождение с бухгалтерией на копейку, которую будут искать неделю. date для дат без времени, timestamptz для моментов времени: он хранит значение в UTC и переводит в часовой пояс сессии при чтении. Обычный timestamp хранит то, что записали, без всякой зоны — при работе из разных городов это источник тихих ошибок. boolean вместо «да»/«нет» строкой. jsonb для нерегулярных атрибутов, у которых нет общего набора полей.
Отдельная тема — NULL. Он означает «значения нет», и он не равен ни пустой строке, ни нулю, ни другому NULL. Сравнение where comment = null не найдёт ничего, нужен is null. Агрегаты sum и avg пропускают NULL, а count(*) считает строки целиком, тогда как count(comment) — только заполненные. Половина расхождений при переезде из таблиц объясняется этой парой.
Связи вместо ВПР строятся через внешний ключ. В таблице orders лежит client_id, который ссылается на clients.id. База не даст записать заказ на несуществующего клиента и не даст удалить клиента, у которого есть заказы, пока не описано желаемое поведение. Соединение делается явно:
select c.name, count(*) as orders, sum(o.total) as revenue
from orders o
join clients c on c.id = o.client_id
where o.created_at >= date_trunc('month', now())
group by c.name
order by revenue desc;Это и есть сводная таблица, только записанная текстом и повторяемая хоть каждый час.
Индексы заменяют ручную сортировку. По умолчанию создаётся B-tree — он ускоряет равенство, диапазоны и сортировку. Для поиска внутри jsonb и полнотекстового поиска нужен GIN. Индекс не бесплатен: он занимает место и замедляет запись, поэтому вешать его на каждый столбец бессмысленно. Уникальный индекс попутно работает ограничением и не даёт завести двух клиентов с одним ИНН.
Транзакция — это «сохранить копию перед правкой», только автоматическое. Между begin и commit изменения либо применяются целиком, либо не применяются вовсе. Перевод остатка со склада на склад — две операции внутри одной транзакции, и промежуточного состояния, где товар исчез с обоих складов, не существует.
Изменение структуры делается миграциями — SQL-файлами, которые лежат в репозитории рядом с кодом. alter table orders add column comment text; — это запись в истории, а не молчаливая правка чужого файла.
Работа из терминала идёт через psql: \dt показывает таблицы, \d orders — структуру, типы и ограничения конкретной таблицы, \copy orders from 'data.csv' csv header заливает CSV. Для тех, кому нужен привычный вид сетки, есть графические клиенты и BI-инструменты, читающие ту же базу.
Полезные сценарии
- Учёт, переросший книгу Excel: клиенты, заказы, платежи в связанных таблицах вместо трёх листов с ВПР друг на друга.
- Витрина для BI: база как единый источник, поверх которого дашборд строит графики без ручных выгрузок.
- Склейка выгрузок из разных систем: CRM, банк, реклама заливаются в отдельные таблицы и соединяются по ключу вместо ручного сопоставления.
- Журнал событий и аудит: кто и когда изменил запись, с историей вместо перезаписанной ячейки.
- Справочник для сайта или приложения: одна таблица цен и наличия, из которой читают все каналы сразу.
Ограничения
База не хранит оформление. Цветовая заливка, заметки на полях, объединённые ячейки, ручные пометки «уточнить у Сергея» — всё это придётся либо превратить в отдельные столбцы, либо потерять. Для многих таблиц это половина смысла файла.
Без клиента базу не посмотреть. Открыть двойным щелчком и проглядеть глазами не выйдет: нужен psql, графический клиент или BI-надстройка. Порог входа у коллег, которые никогда не писали select, реальный, и его надо закладывать в план.
Изменение структуры на большой таблице берёт блокировку. Добавление столбца с постоянным значением по умолчанию проходит быстро начиная с PostgreSQL 11, а вот смена типа столбца переписывает таблицу целиком и на время делает её недоступной для записи. Индексы на живой базе создаются через create index concurrently, иначе запись встанет.
Импорт CSV не прощает грязных данных. Пробел в числовом поле, дата в формате 31.02.2024, разделитель тысяч внутри суммы — всё это остановит загрузку. Чистка происходит один раз и болезненно, обычно через промежуточную таблицу, где все столбцы объявлены как text.
База сама себя не бэкапит и не мониторит. Управляемый хостинг снимает часть заботы, свой сервер — нет. Резервная копия, которую ни разу не восстанавливали, копией не считается.
Разовый расчёт на две сотни строк по-прежнему быстрее сделать в таблице. Переезд оправдан, когда данные живут дольше одной задачи и к ним обращается больше одного человека.
Как проверить результат
После переноса — сверка контрольных сумм. По каждому источнику считается число строк и сумма ключевых денежных полей, то же самое считается в базе, цифры должны совпасть до копейки. Расхождение почти всегда объясняется двумя вещами: NULL вместо нуля или потерянные строки на импорте из-за ошибки типа.
\d имя_таблицы показывает, доехали ли ограничения. Если not null, unique и внешние ключи отсутствуют, схема существует только на бумаге. Работоспособность ограничений проверяется попыткой их нарушить: вставка дубля по уникальному полю обязана вернуть ошибку, а не записаться.
explain (analyze, buffers)
select * from orders where client_id = 42;В плане должен появиться индексный доступ. Seq Scan на большой таблице означает, что индекс отсутствует или не подходит под условие. Хронически медленные запросы ловятся расширением pg_stat_statements — оно копит статистику по всем выполненным запросам и показывает, что именно съедает время.
Часовые пояса проверяются заранее: show timezone; и select now(); из того же подключения, откуда работает приложение. Если приложение и база разошлись в поясах, суточные отчёты будут врать на границах суток.
Последняя проверка — восстановление. Резервная копия разворачивается на пустую базу, на ней прогоняются те же контрольные суммы. Пока эта процедура не пройдена руками хотя бы раз, считать данные защищёнными нельзя.