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

Совет по продуктивной работе с Postgres: шаблонные базы данных

Многие разработчики используют шаблонные базы данных для настройки дев-окружений. Суть подхода в следующем: вы запускаете один экземпляр Postgres для разработки и при необходимости создаёте/удаляете базы данных. При этом база данных для разработки создаётся на основе шаблона, который синхронизирован со схемой продакшена.

Для распределённых команд, использующих облачные решения, шаблонная база — это экономичный и безопасный способ работы.

Преимущества использования шаблонной базы данных для разработки:

● Упрощает работу с расширениями, конфигурацией Postgres, управлением версиями и установкой зависимостей — всё, что обычно замедляет локальную разработку
● Обеспечивает соответствие схемы продакшену, так как шаблон можно быстро создать из полной копии базы без данных
● Сокращает время на развёртывание и удаление дев-машин
● Позволяет использовать анонимизированные тестовые данные, подходящие для разработки

Подробнее о шаблонных базах данных

➡️ Создайте шаблон на основе продакшена, выгрузив только схему и расширения:

pg_dump -U my_user -h my_host -p 5432 -d my_database --exclude-table-data --no-owner --no-privileges > template.sql

Флаг --exclude-table-data исключает данные, но сохраняет полную схему и все расширения.

➡️ Создайте базу и восстановите в неё шаблон:

-- создаём базу
CREATE DATABASE my_template_db OWNER postgres;

-- восстанавливаем из дампа
psql -U my_user -h my_host -p 5432 -d my_template_db -f template.sql

Добавьте сид-данные на этом этапе. Это позволит быстро клонировать шаблон без необходимости повторного восстановления.

➡️ Разрешите использовать базу как шаблон:

ALTER DATABASE my_template_db IS_TEMPLATE TRUE;

Важно: использовать шаблонные базы можно только от имени суперпользователя Postgres. На платформе Crunchy Bridge это поддерживается, но не все облачные провайдеры предоставляют доступ к суперюзеру.

➡️ Создайте новую базу на основе шаблона:

CREATE DATABASE new_db WITH TEMPLATE my_template_db OWNER my_user;

Это создаст копию my_template_db под именем new_db

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

👉 @SQLPortal

pgCompare — это утилита на Java, предназначенная для сравнения данных. Подходит для следующих сценариев:

🔸репликация — проверка того, что данные реплицируются корректно и находятся в синхронизации;

🔸кроссплатформенная репликация — валидация данных между разными СУБД;

🔸миграции между базами данных — убедиться, что данные корректно синхронизированы в рамках миграционного проекта;

🔸проверка целостности данных при active-active репликации.

На данный момент pgCompare поддерживает следующие СУБД: Oracle, PostgreSQL, DB2, MariaDB, MySQL и MSSQL.

https://github.com/CrunchyData/pgCompare

👉 @SQLPortal