Шпаргалка для изучения SQL и NoSQL баз данных

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

⏩Базы данных SQL предлагают структурированное хранилище данных, поддержку SQL и реляционной модели
⏩Базы данных NoSQL обеспечивают гибкость, масштабируемость и распределенную архитектуру
⏩Специализированные базы данных, такие как столбчатые, графовые, пространственные и базы данных временных рядов, удовлетворяют конкретные потребности

👉 @SQLPortal

NocoDB — превращает любой MySQL, PostgreSQL, SQL Server, SQLite и MariaDB в интеллектуальную электронную таблицу.

⛓ Ссылка: тык

👉 @SQLPortal

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

⛓ Ссылка: тык

👉 @SQLPortal

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

👉 @SQLPortal

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

⛓ Ссылка: тык

👉 @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

Не пиши 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 вручную?

Сэкономьте силы для задач разработки.

21 апреля в 16:00 (мск) пройдёт вебинар от MWS Cloud Platform, где эксперты компании расскажут, как получить готовую базу для бэкенда за несколько минут.

Что будет в эфире:

⚫️️️ облачный PostgreSQL: плюсы/минусы решения;
⚫️️️ как устроен управляемый сервис в новом облаке от MWS Cloud;
⚫️️️ машинерия под капотом бэкапов, автообновлений, switch и failover;
⚫️️️ создадим кластер за несколько минут и настроим подключение.

Вебинар будет интересен администраторам баз данных (DBA), бэкенд-разработчикам, DevOps- и SRE-инженерам, техническим лидам и архитекторам, владельцам продуктов и стартапам.

Зарегистрироваться

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

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

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

👉 @SQLPortal

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

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

FLASHBACK TABLE ... TO BEFORE DROP

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

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

DROP TABLE ... PURGE

👉 @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

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

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

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

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

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

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

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

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

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

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

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

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

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

👉 @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

Оператор 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

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

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

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

https://github.com/dorylab/dory

👉 @SQLPortal

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

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

👉 @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

В 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

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

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

👉 @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 Joins cheatsheet

👉 @SQLPortal