Совет по логированию в Postgres для повышения производительности: включите логирование временных файлов (log_temp_files).

Зачем?

Идеально — установить log_temp_files в значение, равное текущему work_mem.
work_mem — это лимит оперативной памяти на одну операцию. Если операция укладывается в этот лимит, временные файлы не создаются и логирование не требуется.
Если лимит превышен — происходит сброс на диск (spill), и создаётся временный файл не меньше, чем work_mem.

Пример настройки: логировать временные файлы больше 4 МБ (в килобайтах), соответствует текущему значению work_mem:

log_temp_files = '4096'

Пример лога временного файла:

2024-05-16 14:23:05.123 UTC [12345] user@database LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 245760
2024-05-16 14:23:05.123 UTC [12345] user@database DETAIL:  Sort operation used temporary file because work_mem was exceeded

То есть операция сортировки превысила work_mem, и была выгружена во временный файл. 🥰

👉 @SQLPortal

Постройте надёжную основу для звёздных схем (star schemas) в Oracle Database, следуя этим рекомендациям от Дани Шнайдера:

Измерения (Dimension)

> Первичный ключ на суррогатном ключе
> Уникальное ограничение на колонке(ах) бизнес-ключа

Факты (Facts)

> Внешний ключ и битмаповый индекс на каждой колонке измерения

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

👉 @SQLPortal | #cтатья

Как исполняется SQL-запрос в базе данных?

Разберём на примере:

SELECT name, age FROM users WHERE city = 'San Francisco'

1. Transport Subsystem

Запрос прилетает по сети и попадает в транспортный уровень базы.

2. Query Processor

SQL парсится → строится Query Parse Tree — дерево, где разбираются:

> SELECT → какие колонки
> FROM → из какой таблицы
> WHERE → какое условие

3. Execution Engine

На основе дерева создаётся Execution Plan:

> индексный поиск по city_index
> выборка колонок name, age
> возврат подходящих строк

4. Storage Engine

Работает с физическими данными через:

> Transaction Manager
> Lock Manager
> Buffer Manager
> Recovery Manager

Это вся цепочка — от строки запроса до результата в твоей IDE. 📝

👉 @SQLPortal

Общие рекомендации по распределению памяти в Postgres:

🔸25% — на shared_buffers (кэш буферов). Чаще всего запрашиваемые данные должны помещаться в кэш. Проверьте коэффициент попаданий в кэш (cache hit ratio) — 98–99% запросов должны обслуживаться из кэша (хотя это зависит от нагрузки).

🔸25% — на work_mem, распределённый между соединениями. Значение work_mem, умноженное на max_connections, должно составлять примерно 25% от общего объёма памяти.

🔸50% — резерв для файлового кэша ОС, системных процессов и служебных операций (например, VACUUM), использующих maintenance_work_mem

👉 @SQLPortal

Таймауты запросов находятся в самом верху нашего списка рекомендаций для любой базы данных — будь то маленький pet-проект или крупная продакшен-БД. Мы часто говорим об этом, но если вы вдруг пропустили — выставляйте таймаут запроса от одной до двух минут:

ALTER DATABASE dbname SET statement_timeout = '60s';

Это предотвратит выполнение запросов, которые «убегают» и начинают потреблять ресурсы сверх меры.

Вы можете установить statement_timeout на уровне базы данных, пользователя или сессии. Мы рекомендуем установить глобальный таймаут в Postgres, а затем переопределять его для отдельных пользователей или сессий, которым требуется больше времени на выполнение.

👉 @SQLPortal

Ограничения (constraints) в базе данных это хорошая практика: они задают правила на уровне данных, обеспечивая корректность, согласованность и надёжность информации в приложении.

Ограничения предотвращают запись некорректных или противоречивых данных в таблицы.

– NOT NULL гарантирует, что обязательные поля не останутся пустыми.
– CHECK проверяет, что значения соответствуют заданным условиям (например, age > 0).

Ограничения также поддерживают связи между полями.

– FOREIGN KEY обеспечивает ссылочную целостность между таблицами. Например, нельзя создать заказ для несуществующего клиента.

Многие ORM и приложения тоже реализуют валидации, но дублирование проверки на уровне кода и на уровне базы — это хорошая практика.

Есть туториал по работе с constraints, с примерами кода:

👉https://crunchydata.com/developers/playground/postgres-constraints-tutorial

👉 @SQLPortal

Supabase работает над Multigres — решением для шардинга PostgreSQL, которое позволит масштабировать базу данных до петабайтного уровня

👉 @SQLPortal

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