CREATE STATISTICS позволяет взять взаимосвязанные данные в ваших таблицах и объяснить Postgres, как именно они связаны.

Например, можно создать статистику для связки country и city следующим образом:

CREATE STATISTICS (dependencies) ON country, city FROM facilities;
ANALYZE facilities;

В нашей тестовой базе данных это сделало один запрос с фильтрацией по WHERE city быстрее в 97 раз

👉 @SQLPortal

Объединение таблиц в схемы

Таблица представляет собой набор строк и столбцов и описывает определённый объект, например список клиентов, товаров или счетов-фактур. Чтобы работать с несколькими связанными объектами, используют группы таблиц, объединённые в логическую схему. Логическая схема — это структура, связывающая таблицы между собой.

Кроме логической, у базы данных есть и физическая схема. Она определяет, как данные и связанные с ними компоненты (например, индексы) размещаются на диске. Когда речь идёт о «схеме базы данных», обычно имеется в виду логическая схема, а не физическая.

Если в одной системе работают несколько проектов, для каждого проекта можно создать свою схему. Таблицы, относящиеся к одному проекту, размещаются в одной схеме, таблицы других проектов — в других. Это помогает избежать путаницы между одноимёнными таблицами.

Чтобы таблицы из одного проекта случайно не использовались в другом, схемам дают имена. Так, таблицы CUSTOMER или PRODUCT могут встречаться в разных проектах. Чтобы отличать их, при обращении к таблице желательно указывать имя схемы, например: SCHEMA_NAME.TABLE_NAME. Если схема не указана, SQL использует схему по умолчанию

👉 @SQLPortal

Совет по Postgres: установите таймаут блокировки.

Часто бывает полезно задать lock_timeout в Postgres, чтобы блокировки в рамках сессии автоматически освобождались через определённое время. Это помогает улучшить производительность запросов и предотвращает удержание блокировок на неопределённый срок. Параметр можно задать через ALTER SYSTEM или на уровне отдельной сессии.

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

Дополнительный совет — логировать ожидания блокировок немного ниже порога таймаута. Например, если таймаут установлен на 5 секунд, логируйте ожидания от 4.9 секунд. Это поможет фиксировать запросы, которые отменяются, для последующего анализа.

lock_timeout часто можно задать при инициализации ORM и легко подключить к соединению вашей сессии в приложении.

👉 @SQLPortal

Подсчеты и общие итоги в Postgres с помощью функций ROLLUP и CUBE:

📖 Читать: ссылка

👉 @SQLPortal | #cтатья

Представление pg_stats в Postgres — это действительно удобное место для получения информации о распределении данных по таблицам.

Для каждой схемы, таблицы и колонки можно увидеть:

> наличие NULL-значений
> n_distinct / кардинальность

→ отрицательные значения — высокая кардинальность
→ положительные значения — низкая кардинальность

> наиболее часто встречающиеся значения
> частоты этих значений
> информацию о гистограмме распределения данных

В представлении pg_stats названия колонок указываются как attname (attribute name), поэтому можно легко просматривать статистику по отдельным колонкам таблицы.

Эти данные полезны для ряда оптимизаций, главное применение — проектирование индексов:

1. Знание кардинальности

Если колонка с низкой кардинальностью — индекс может не помочь, последовательное сканирование может быть быстрее.
Если колонка с высокой кардинальностью и по ней часто выполняются запросы — индекс, скорее всего, будет полезен.

2. Поиск смещенного распределения

Если колонка имеет кардинальность, но распределение сильно смещено — могут быть полезны частичные индексы.

3. Знание частоты NULL-значений

Это может помочь в проектировании запросов и очистке данных.

👉 @SQLPortal

Это визуальная шпаргалка по SQL-командам, оформленная в виде рисунков с фруктами и человечками. Она охватывает основные конструкции SQL на примере таблицы fruits

По моему одна из лучших, что есть 🐸

👉 @SQLPortal

Postgres предоставляет широкий и надёжный набор функций для отчетности и аналитики. Доступно множество опций для группировки по датам и агрегации (rollup).

Смотри практическое руководство по работе с датами. Примеры можно запускать прямо в браузере, чтобы попробовать разные инструменты.

▶️playground/postgres-date-functions

👉 @SQLPorta

Ты обожаешь Postgres и SQL, но начальству нужны отчёты в Excel? К счастью, в Postgres есть простой способ выгрузить данные из SQL-запроса сразу в CSV.

Экспортировать CSV-файл для любого SQL-запроса можно так:

psql --csv -c 'select * from orders'

psql уже содержит встроенную утилиту для работы с CSV. Просто укажи параметры подключения, запрос в кавычках — и готово.

– флаг --csv выводит данные в формате CSV прямо в stdout
– добавь >, чтобы направить вывод в файл на локальной системе

Этот способ не такой гибкий, как \copy, но для простого экспорта результатов запроса — это быстрое и удобное решение.

Прямой вывод в CSV избавляет от лишней постобработки. Нам нравится, как psql позволяет быстро и эффективно работать со структурированными данными — для отчётов, аудита и загрузки в таблицы.

👉 @SQLPortal

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

Для тех, кто только начинает работать с Postgres или только осваивает работу с базой данных, важно познакомиться со следующими ключевыми представлениями каталогов — они помогут увидеть, что происходит внутри, выявить проблемы и провести отладку:

pg_stat_activity → текущие выполняющиеся запросы и сессии

pg_stat_statements → журнал запросов и их длительность

pg_stat_user_indexes → пользовательские индексы (не системные), включая первичные ключи

pg_roles → системные роли и роли для входа в систему

pg_locks → процессы, удерживающие блокировки

pg_stats → статистика по таблицам: распределение данных, кардинальность столбцов

pg_settings → параметры конфигурации, включая те, что задаются через ALTER SYSTEM

pg_stat_database → информация о базе данных, например, объем транзакций

👉 @SQLPortal

Индексация в Postgres для запросов с подстановочными символами 🃏

Это немного отличается от стандартной индексации. Если запросы твоего приложения делают точное сравнение — стандартные B-tree индексы обычно работают хорошо.

Но если ты часто используешь LIKE или ILIKE с шаблоном вида %строка%, такие запросы обрабатываются иначе.

Индексы типа B-tree зависят от сортировки, а наличие подстановочного символа % и в начале, и в конце строки делает такую сортировку невозможной обычными методами.

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

pg_trgm работает вместе с GIN-индексами, создавая индекс по триграммам для ускоренного поиска по схожести:

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

Оператор gin_trgm_ops — это специальный оператор индексации, который связывает GIN-индекс с триграммами.

Если посмотреть план выполнения (EXPLAIN) для такого индекса, ты увидишь, что используются планы Index Bitmap и Heap Bitmap.

Обычно это работает так: поиск по индексу создает bitmap (битовую карту), ссылающуюся на строки, подходящие под условие, а затем heap bitmap используется для получения этих строк из основной таблицы

👉 @SQLPortal

5 нормальных форм баз данных, которые должен знать каждый разработчик

Первая нормальная форма (1NF)

Обеспечивает, чтобы каждое значение в столбце было атомарным (неделимым).

Вторая нормальная форма (2NF)

Обеспечивает, чтобы каждый неключевой столбец зависел от всего составного первичного ключа.

Третья нормальная форма (3NF)

Удаляет транзитивные зависимости: неключевые столбцы должны зависеть только от первичного ключа.

Нормальная форма Бойса-Кодда (BCNF)

Усиление 3NF: все детерминирующие атрибуты должны быть кандидатными ключами.

Четвёртая нормальная форма (4NF)

Обеспечивает, чтобы таблица не смешивала независимые связи «один-ко-многим» для одной и той же сущности.

👉 @SQLPortal

Совет по SQL:

5 причин использовать Common Table Expressions (CTE):

🔸CTE позволяют разбивать логику на именованные и читаемые шаги.
🔸Если что-то ломается, каждое выражение CTE можно протестировать изолированно.
🔸С CTE вы определяете подзапрос один раз и потом чисто переиспользуете его.
🔸CTE масштабируются лучше, чем одна длинная монолитная выборка.
🔸Так как каждое CTE — это как отдельный мыслительный шаг, код читается так, как вы бы объясняли решение вслух. Это помогает и при написании, и при ревью SQL-запросов.

👉 @SQLPortal

Пояснение концепций баз данных на примере банковской терминологии

➣ База данных = Банк
➣ Таблица = Счёт
➣ Данные = Деньги
➣ DDL = Открытие/обновление/закрытие счёта
➣ DML = Списание и зачисление средств
➣ ACID = Безопасная транзакция
➣ Индекс = Кредитная/дебетовая карта
➣ Ограничения = Политики банка
➣ Первичный ключ = Номер счёта
➣ Внешний ключ = Совместный счёт
➣ Триггеры = SMS-уведомления
➣ Хранимая процедура = Служба поддержки клиентов

👉 @SQLPortal

SQL-запрос в Postgres для просмотра объема транзакций через представление pg_stat_database:

SELECT datname, xact_commit, xact_rollback
FROM pg_stat_database;

dattname | xact_commit. | xact_rollback
-------------------------+-------------+---------------
metabase | 48304399 | 3377
postgres | 4448208 | 20

➣ xact_commit — количество успешно зафиксированных транзакций
➣ xact_rollback — количество откатанных транзакций

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

👉 @SQLPortal