SQL Portal | Базы Данных

JSON и JSONB удобны, когда нужно просто складывать схемно-нефиксированные данные, но иногда требуется добавить контроль структуры.

Можно использовать расширение pg_jsonschema, чтобы задать схему и валидировать содержимое. Это даёт больше контроля над форматом данных в таких колонках.

👉 @SQLPortal

🚫 Никогда не используй COUNT для проверки существования

Если нужно просто проверить, существует ли запись, не используй COUNT() с фильтром (WHERE)

SELECT COUNT(*)
FROM users
WHERE email = 'http://thisemaildontexist.com';

Проблема в том, что COUNT(*) отвечает на другой вопрос — считает количество строк, подходящих под условие, хотя нужна только проверка наличия хотя бы одной.

При использовании COUNT(*) база может просканировать все подходящие строки, чтобы посчитать итог. Даже если совпадение найдено сразу, выполнение может продолжиться, потому что запрошен полный подсчёт. На больших таблицах это лишняя работа.

Вместо COUNT() используй EXISTS

SELECT EXISTS (
    SELECT 1
    FROM users
    WHERE email = 'http://thisemaildontexist.com'
);

👉 @SQLPortal

SQL Joins cheatsheet

👉 @SQLPortal

pgstattuple - Расширения Postgres, которые у тебя уже есть

Что делает: проверяет раздувание таблиц и индексов

Как включить:

CREATE EXTENSION pgstattuple;

Как использовать:
select * from pgstattuple или писать свои запросы, чтобы смотреть таблицы, количество «живых» и «мертвых» строк и процент.

Процент «мертвых» и свободных строк в таблицах Postgres

SELECT
  relname AS table_name,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  (pgstattuple(relid)).dead_tuple_percent AS dead_pct,
  (pgstattuple(relid)).free_percent AS free_pct
FROM pg_catalog.pg_stat_user_tables
ORDER BY dead_pct DESC;

👉 @SQLPortal

Современный редактор SQL и баз данных PostgreSQL

https://github.com/cin12211/orca-q

👉 @SQLPortal

В SQL есть разные уровни

📍Стиль 1

SELECT
 COUNT(CASE 
 WHEN days_lost = 1 THEN NULL 
 ELSE days_lost 
 END
 ) AS count_of_days_lost
FROM safety;

Преимущества
— Явно видно, что происходит
— Очень гибкий, подходит для сложной логики

Недостатки
— Многословный, добавляет шум
— Часто используется там, где можно проще

📍Стиль 2

SELECT
 COUNT(NULLIF(days_lost, 1)) AS count_of_days_lost
FROM safety;

Преимущества
— Коротко и аккуратно для простых условий
— Меньше шаблонного кода по сравнению с CASE

Недостатки
— Хуже читается для новичков
— Ограничен простыми проверками на равенство

📍Стиль 3

SELECT
 COUNT(*) FILTER (WHERE days_lost != 1) AS count_of_days_lost
FROM safety;

Преимущества
— Читаемо, намерение выражено напрямую
— Логика отделена от агрегации
— Удобно при нескольких условиях

Недостатки
— Поддерживается не во всех диалектах SQL
— Менее распространен среди разработчиков

👉 @SQLPortal

Расширение Postgres, которое у вас уже есть: pg_visibility.

Оно отслеживает, какие страницы таблиц полностью видимы для транзакций, а какие содержат строки, которые ещё не были обработаны VACUUM.

Используйте его, чтобы диагностировать, почему медленно работают сканирования только по индексу, проверить, успевает ли VACUUM, и мониторить заморозку строк и циклическое переполнение xID.

Какой процент страниц видим и заморожен

SELECT
  c.relpages AS total_pages,
  s.all_visible,
  s.all_frozen,
  round(100.0 * s.all_visible / NULLIF(c.relpages, 0), 1) AS pct_visible,
  round(100.0 * s.all_frozen / NULLIF(c.relpages, 0), 1) AS pct_frozen
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) s
WHERE c.relname = 'employees';

👉 @SQLPortal

Термины в Data engineering, которые нужно знать

ETL — извлечение, трансформация, загрузка. Очистка до сохранения.
ELT — извлечение, загрузка, трансформация. Очистка внутри хранилища.
CDC — отслеживание изменений данных. Перенос только новых изменений.
DAG — граф воркфлоу. Задачи + зависимости.
OLAP — системы, ориентированные на аналитику. Оптимизированы под инсайты.
SLA — соглашение об уровне сервиса. Доступность и производительность.
Data Lake — хранилище сырых данных. Любой формат, любой объем.
Medallion Architecture — Bronze → Silver → Gold. Слои качества данных.
Reverse ETL — хранилище → бизнес-инструменты. Данные обратно в приложения.

👉 @SQLPortal

Интегрированное рабочее окружение для инженеров баз данных с поддержкой ИИ: написание SQL, отладка и сопровождение.

Dory объединяет SQL-редактор, ИИ-ассистента и инструменты для эксплуатации и сопровождения баз данных в одном десктопном приложении. Есть глубокая интеграция с ClickHouse (мониторинг + управление правами), также поддерживаются популярные базы вроде PostgreSQL и MySQL.

Поддерживает локальную установку и развёртывание через Docker, данные хранятся локально — в облако через Cloudflare уходят только запросы к ИИ.

https://github.com/dorylab/dory

👉 @SQLPortal

Оператор CASE в SQL

Оператор CASE в SQL позволяет выполнять условные логические операции непосредственно в запросах. Это аналог конструкции if-else в языках программирования и полезен для создания вычисляемых столбцов и реализации сложной логики выборки данных.

Два варианта синтаксиса:

🍩Простой CASE
Используется для сравнения выражения с набором значений.

CASE выражение
  WHEN значение1 THEN результат1
  WHEN значение2 THEN результат2
  ELSE результат_по_умолчанию
END

🍩CASE с условиями
Используется для проверки условий.

CASE
  WHEN условие1 THEN результат1
  WHEN условие2 THEN результат2
  ELSE результат_по_умолчанию
END

Примеры использования: 👇

SELECT 
  product_name,
  CASE category_id
    WHEN 1 THEN 'Электроника'
    WHEN 2 THEN 'Одежда'
    WHEN 3 THEN 'Книги'
    ELSE 'Другое'
  END AS category_name
FROM products;

SELECT 
  employee_name,
  salary,
  CASE
    WHEN salary >= 100000 THEN ' Высокий доход'
    WHEN salary >= 50000 THEN 'Средний доход'
    ELSE 'Низкий доход'
  END AS income_level
FROM employees;

Важные замечания:

🟡Первое совпадение: Оператор CASE возвращает результат при первом совпавшем условии и игнорирует остальные.

🟡ELSE и NULL: Если ни одно из условий не выполнено и не указан блок ELSE, то возвращается NULL.

🟡Типы данных: Все возвращаемые значения в THEN и ELSE должны быть совместимых типов данных.

👉 @SQLPortal

pg_buffercache позволяет читать таблицы и отношения, находящиеся в кеше общей памяти (shared buffers).

Как включить:
CREATE EXTENSION pg_buffercache;

Как использовать:
select * from pg_buffercache или писать свои запросы, чтобы смотреть таблицы или отношения в shared buffers и сколько буферов они занимают

Таблицы Postgres в shared buffers

SELECT
  c.relname AS table_name,
  pg_size_pretty(count(*) * 8192) AS buffered,
  count(*) AS buffers,
  ROUND(100.0 * count(*) / (SELECT count(*) FROM pg_buffercache
  WHERE relfilenode IS NOT NULL), 1) AS pct_of_cache
FROM pg_buffercache b
JOIN pg_class c ON c.relfilenode = b.relfilenode
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE b.relfilenode IS NOT NULL
  AND c.relkind = 'r'      -- только обычные таблицы (не индексы, toast, последовательности)
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') -- исключаем системные схемы
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

Также полезно для тюнинга настроек чекпоинтов — можно увидеть, какие буферы «грязные» и ещё не сброшены на диск.

👉 @SQLPortal

Некоторые cron-задачи не должны покидать базу данных.
И Postgres даёт механизм, чтобы это реализовать.

Background Workers в Postgres — это процессы, которые работают внутри самого сервера базы данных. Не рядом, не «поблизости», а внутри него.

Типичный паттерн, который используют команды:
scheduler → приложение → база → логика в приложении → запись в базу

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

Инструменты вроде pg_cron построены на Background Workers. Задача выполняется как фоновый процесс внутри Postgres: читает состояние, выполняет логику, записывает результат — без участия приложения.

Это не просто «ближе к данным».
Это когда данные сами обрабатывают себя.

Где такой подход выигрывает:
→ истечение просроченных резерваций
→ очистка старых строк
→ обновление производных данных
→ смена состояния записей
→ небольшие задачи обслуживания, завязанные только на локальные данные

Это не универсальный паттерн. Внешние API-вызовы, координация между сервисами, долгие воркфлоу, бизнес-оркестрация — остаются в приложении.

Но для небольших задач, завязанных на состояние и локальные данные — фоновый воркер внутри Postgres даёт более чистую архитектуру.

Проблема не в использовании cron.

Проблема в том, что задачи, связанные только с данными, выполняются далеко от этих данных.

Background Workers в Postgres позволяют это исправить.

Используешь pg_cron или фоновые воркеры в проде?

👉 @SQLPortal

На этой неделе разбираем расширения Postgres, которые у тебя уже есть — они идут в составе библиотеки contrib.

Их нужно просто включить, но из коробки всё уже готово к работе. Начнём с pg_stat_statements — это одно из самых полезных расширений для отслеживания метрик запросов.

Что делает: после включения отслеживает статистику запросов

Как включить:
CREATE EXTENSION pg_stat_statements;
добавить в shared_preload_libraries

Как использовать:
SELECT * FROM pg_stat_statements
или писать кастомные запросы для просмотра самых долгих и самых часто выполняемых запросов

Самые медленные запросы по суммарному времени

SELECT
  LEFT(query, 80) AS query,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

👉 @SQLPortal

Когда-нибудь случайно удаляли таблицу? 👍

Oracle Database помещает её в корзину (recyclebin), так что вы можете восстановить её с помощью

FLASHBACK TABLE ... TO BEFORE DROP

и база данных восстановит её, включая индексы и (не внешние) ограничения.

Если хотите удалить её окончательно, используйте

DROP TABLE ... PURGE

👉 @SQLPortal

Совет для аналитиков данных;

Если проект в твоём портфолио почти не показывает, как он сэкономил компании деньги или время, с вероятностью 99% он никого не впечатлит.

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

👉 @SQLPortal

Не пиши SELECT DISTINCT, если проблему создаёт плохой JOIN

Частая ошибка - сначала размножают строки джойном, потом сверху лечат это DISTINCT. Это дорого и часто убивает план.

Плохо:

SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

Лучше так:

SELECT u.id, u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
);

DISTINCT часто добавляет sort или hash aggregation на огромном объёме.
EXISTS превращает задачу в semijoin:
• оптимизатору проще остановиться на первом совпадении
• меньше памяти, меньше лишних строк, стабильнее план

Это один из тех рефакторингов, который на больших таблицах реально даёт заметный буст.

👉 @SQLPortal

В PostgreSQL есть специальный параметр для логирования долго выполняющихся запросов — log_min_duration_statement.

После установки этого параметра PostgreSQL будет записывать в лог длительность и текст запроса, если он выполняется дольше указанного времени.

ALTER DATABASE postgres SET log_min_duration_statement = '500ms';

Это полезно для того, чтобы:

> определить, какие запросы работают медленно;
> понять, где можно добавить индексы для повышения производительности.

Параметр можно задать и на уровне сессии, если нужно логировать (или, наоборот, исключить из логирования) только конкретные запросы:

SET log_min_duration_statement = '20000ms';

log_min_duration_statement — действительно мощный инструмент, но мы обычно не рекомендуем устанавливать его на очень низкие значения (0, 1 или 5 мс), так как это приведёт к логированию практически всех операций базы данных. Это создаст шум в логах и может занять много места на диске.

👉 @SQLPortal

Образовательный сайт для обучения и практики SQL-запросов, предлагающий задачи и проверки в реальном времени.

⛓ Ссылка: тык

👉 @SQLPortal

Держите потрясающе полезную шпаргалку по SQL ⚡️

👉 @SQLPortal

Здесь вы найдете практические примеры и задания по SQL, предназначенные для обучения и отработки навыков работы с базами данных.

⛓ Ссылка: тык

👉 @SQLPortal