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

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