Представление 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

Power BI смотрит на тебя, когда ты начинаешь путь дата-аналитика с ноутбуком на 4ГБ ОЗУ и Intel Celeron:

👉 @SQLPortal

Распространённый вопрос на собеседовании по SQL: Как оптимизировать медленный SQL-запрос?

Варианты ответов:

• Избегать использования SELECT DISTINCT
• Использовать INNER JOIN вместо фильтрации через WHERE
• По возможности избегать JOIN вообще (рассмотреть денормализацию)
• Добавить индексы в базу данных
• Проанализировать план выполнения запроса (EXPLAIN / QUERY PLAN)

👉 @SQLPortal

7 сайтов для практики логики программирования — бесплатно, интерактивно и интересно:

① adventjs.dev — задачи на JavaScript с новогодней атмосферой
② exercism.org — практика с менторством и большим выбором языков
③ leetcode.com — топовая платформа для подготовки к интервью
④ codewars.com — "ката"-задачи и система рангов
⑤ coderbyte.com — челленджи и оценки навыков
⑥ codesignal.com — тренировки и эмуляция интервью
⑦ hackerrank.com — задачи по алгоритмам, SQL, DevOps и др.

👉 @SQLPortal

SQL-вопрос

Что из перечисленного обязательно присутствует в каждой оконной функции SQL❓

a. DENSE_RANK
b. PARTITION
c. OVER
d. RANK
e. Всё вышеперечисленное

👉 @SQLPortal