Весь синтаксис SQL, который вам понадобится

👉 @SQLPortal

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

В этой статье Абдулла объясняет, как работают подзапросы и где они используются в SQL.

Разбираются некоррелированные и коррелированные подзапросы, производные столбцы и таблицы, а также операторы IN, ANY, ALL, EXISTS и многое другое.

https://freecodecamp.org/news/how-to-work-with-subqueries-in-sql/

👉 @SQLPortal

now() — это не «сейчас», а скорее «когда-то было сейчас».

На самом деле now() возвращает время начала текущей транзакции. Реальные часы продолжают идти, но каждый последующий вызов now(), CURRENT_TIMESTAMP, CURRENT_TIME и CURRENT_DATE внутри этой же транзакции всё равно возвращает одно и то же время.

В Postgres это называется transaction-frozen timestamp.

То есть INSERT на 10 000 строк с created_at DEFAULT now() получит один и тот же timestamp для всех строк, а не 10 000 слегка отличающихся значений.

Три варианта времени:

• now() — традиционный для Postgres вариант, хотя он есть и в других СУБД, но это не стандарт SQL. В стандарте SQL используется CURRENT_TIMESTAMP. В Postgres также есть transaction_timestamp(), который работает так же, как now(), но хотя бы честно говорит об этом своим названием.

• statement_timestamp() обновляется один раз на каждый SQL-запрос. Это полезно внутри длинной транзакции, когда вам нужно фиксировать время выполнения отдельных команд, но не переходить к реальному системному времени.

• clock_timestamp() — единственный timestamptz, который меняется даже во время выполнения одного SQL-запроса.

👉 @SQLPortal

По умолчанию Postgres считает столбцы, которые вместе используются в WHERE, независимыми друг от друга.
Например, для такого SQL-запроса:

SELECT *
FROM world
WHERE country = 'Japan'
  AND continent = 'Asia';

планировщик оценивает количество подходящих строк, перемножая селективность каждого условия.

Допустим, селективность условия country = 'Japan' равна 0,02, а continent = 'Asia' — 0,4. Тогда общую селективность планировщик оценит как 0,008.

В действительности эти столбцы связаны, поэтому итоговая селективность равна селективности условия country = 'Japan'.

Чтобы исправить оценку, нужно явно указать корреляцию между столбцами: выполнить CREATE STATISTICS для них вместе, а затем запустить ANALYZE.

Я проверил это на тестовом датасете, результаты видны на изображении. Изначально планировщик ожидал, что запрос вернёт 784 строки, хотя фактический результат составлял 2000 строк.

После выполнения CREATE STATISTICS планировщик спрогнозировал 1983 строки — гораздо ближе к реальному результату.

👉 @SQLPortal

Вопрос по SQL:

Что вернёт этот запрос?

👉 @SQLPortal

Приложения и базы данных, которые стоят за ними.

Instagram — PostgreSQL
Reddit — PostgreSQL
Twitch — PostgreSQL
Notion — PostgreSQL
Figma — PostgreSQL
ChatGPT — PostgreSQL
Skype — PostgreSQL

GitHub — MySQL
Shopify — MySQL
Slack — MySQL
YouTube — MySQL
Uber — MySQL
Airbnb — MySQL
Facebook — MySQL

Netflix — Cassandra

Discord — ScyllaDB

Забавно, насколько большая часть крупнейших сервисов в мире до сих пор держится на PostgreSQL и MySQL.

👉 @SQLPortal

Опытные коллеги здесь? 👀

👉 @SQLPortal

Один из самых умных трюков для защиты данных, которые я видел в продакшене?

—> Временной RLS (Temporal RLS)

Row-Level Security — это функция PostgreSQL, позволяющая управлять тем, какие строки может видеть пользователь, прямо на уровне базы данных.

Вместо того чтобы фильтровать данные в коде приложения, RLS переносит контроль доступа в саму БД

Представь себе WHERE, который всегда включён — и индивидуален для каждого пользователя или роли.

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

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

Вместо написания логики в приложении или BI-инструменте, они полностью реализовали это на уровне базы данных.

Как?

1. Включили RLS на таблице

2. Определили политику фильтрации строк

3. Включили принудительное применение RLS для всех обращений (необязательно, но рекомендуется)

Даже если кто-то подключится к базе напрямую через psql, BI-инструмент или SQL-клиент — он увидит только строки, старше 24 часов. Без исключений.

⏩Безопасность обеспечивается у источника

⏩Политики версионируются вместе со схемой

⏩Код приложения не участвует

Вывод:

RLS — это не только про фильтрацию арендаторов. С его помощью можно строить умные правила: задержка по времени, доступ по пользователям, мягкое удаление — и всё это реализуется самой БД

👉 @SQLPortal

Забираем годный репозиторий для тех, кто хочет разобраться в SQL глубже — здесь на практике показывают, как работают уровни изоляции транзакций в разных СУБД.

На конкретных SQL-примерах разбираются Lost Update, Read Skew, Write Skew и другие проблемы конкурентных транзакций. Можно сравнить поведение PostgreSQL, MySQL, SQL Server, Oracle, CockroachDB и других баз.

Отличный способ наконец понять, чем Read Committed отличается от Repeatable Read и Serializable не только в теории.

https://github.com/ept/hermitage

👉 @SQLPortal

Почему в PostgreSQL так любят numeric — большой разбор одного из самых распространённых типов для хранения денежных значений.

Автор сравнивает numeric с bigint и double precision, разбирает точность вычислений, округление и хранение денег, а заодно смотрит, какие подходы используют PostgreSQL, SQL Server, MySQL, платёжные системы и крупные ERP.

Полезный материал, чтобы разобраться, когда действительно нужен numeric, а когда можно обойтись целыми числами — и почему float для финансовых расчётов может стать плохой идеей.

https://habr.com/ru/companies/tantor/articles/1070300/

👉 @SQLPortal

Системный дизайн: База данных

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

- один для обработки веб- и мобильного трафика — веб-уровень (web tier);
- другой для базы данных — уровень данных (data tier).

Разделение серверов, обрабатывающих веб- и мобильный трафик, и серверов базы данных позволяет масштабировать эти уровни независимо друг от друга.

Какую базу данных использовать?

Можно выбрать между традиционной реляционной базой данных — RDBMS, или SQL-базой данных — и нереляционной базой данных — NoSQL.

Реляционные базы данных представляют и хранят данные в виде таблиц и строк. С помощью SQL можно выполнять операции JOIN между различными таблицами базы данных.

Нереляционная база данных может быть подходящим выбором, если:

- приложению требуется сверхнизкая задержка;
- данные неструктурированы или не имеют связей;
- требуется только сериализация и десериализация данных — JSON, YAML и т. д.;
- необходимо хранить огромные объёмы данных.

👉 @SQLPortal

Немного разобрался с тем, как работают блокировки в PostgreSQL. Главный вывод: разные SQL-команды захватывают разные режимы блокировок, и многие из них специально сделаны совместимыми друг с другом, чтобы минимизировать конкуренцию за блокировки.

Даже обычный SELECT захватывает блокировку, которая препятствует выполнению некоторых «тяжёлых» операций (например, VACUUM FULL, REINDEX и т. п.). Оказалось, существует удивительно много сочетаний SQL-команд, которые могут или не могут выполняться одновременно.

И ещё: вот отличный сайт, который наглядно показывает в виде графа, какие SQL-команды могут выполняться параллельно.

👉 @SQLPortal

Вопрос по SQL:

Что вернёт этот запрос?

👉 @SQLPortal

Избегать SQL — значит избегать настоящего бэкенда.

API меняются.
Фреймворки уходят в историю.
Базы данных переживают миграции, переписывания и смену стека.

Большинство проблем с производительностью на бэкенде возникают не из-за медленного кода приложения.

Обычно виноваты:

* плохие запросы;
* отсутствующие индексы;
* неудачная схема БД;
* слабая модель данных;
* неправильные паттерны чтения и записи.

Отсутствующий индекс не лечится микросервисами.

Дублирование данных не исправляется кэшем.

Медленные отчёты не ускоряются переписыванием API.

Если ты не понимаешь ACID, уровни изоляции, блокировки, дедлоки, индексы, партиционирование и планы выполнения запросов, рано или поздно система начнёт разваливаться под нагрузкой.

SQL — это не опция.

Хорошее знание SQL делает тебя сильнее в проектировании систем, распределённых системах, производительности и надёжности.

Изучи SQL.

Всё остальное строится поверх него.

👉 @SQLPortal

pg_cron — это расширение PostgreSQL, которое запускает задачи по расписанию прямо внутри базы, используя стандартный синтаксис cron. Если ты пишешь фоновые джобы, которые в основном сводятся к SQL-запросам, проще отдать это Postgres’у через pg_cron. Получается проще, стабильнее и без внешнего планировщика.

Настройка
В managed PostgreSQL (например, Crunchy Bridge и похожие провайдеры) pg_cron обычно уже доступен.
Если поднимаешь Postgres сам, ставишь пакет:

sudo apt install postgresql-18-cron

Дальше подключаешь расширение при старте сервера через postgresql.conf:

shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'


shared_preload_libraries обязателен — без него расширение не загрузится.

Как работает
По умолчанию задачи выполняются в той базе, где создано расширение.
Можно явно указать другую базу через cron.schedule_in_database.
Задачи выполняются от имени роли, которая их создала. Значит, права на таблицы и команды берутся из этой роли.

Создание задач
Основная функция — cron.schedule. У неё несколько перегрузок, поведение зависит от количества аргументов.

Типичный вариант из трёх частей:
имя задачи
cron-расписание
SQL-команда

Пример:

SELECT cron.schedule(
  'nightly_vacuum',
  '0 3 * * *',
  'VACUUM ANALYZE big_table'
);

Управление и мониторинг
Список задач лежит в cron.job.

Там есть:
имя
расписание
SQL
активна ли задача

История запусков — cron.job_run_details.

Там видно:
время старта
длительность
успех или ошибка

Повторов при ошибке нет. Задача просто логируется и ждёт следующего запуска.

Частые кейсы
чистка старых логов
nightly VACUUM для горячих таблиц
агрегация метрик по часам
перенос старых строк в архив
обновление materialized views

Реже используемые сценарии
Иногда pg_cron используют не только для базы:
батчинг запросов к внешним API (в связке с http)
ETL-процессы и выгрузка в data warehouse (например, через pg_lake)
прогрев кэша через pg_prewarm перед пиками нагрузки

👉 @SQLPortal

БОЛЬШОЙ SQL-ГРЕХ

Использовать операторы NOT IN или IN, когда в данных присутствует NULL.

В SQL оператор IN — это сокращённая запись нескольких условий OR, а сравнение value = NULL всегда возвращает UNKNOWN. Из-за этого запрос может вернуть неожиданный результат или вообще пустую выборку.

Если нужно учитывать NULL, обрабатывайте его явно. Добавьте условие IS NULL к IN или используйте другой подход, корректно учитывающий NULL.

Всегда тестируйте SQL-запросы с NULL, чтобы убедиться, что они работают так, как ожидается.

👉 @SQLPortal

Как импортировать большой dump mysql-dump.sql.gz размером более 10 ГБ?

gunzip < mysql-dump.sql.gz | mysql -u <user> -p <database>

Этот вариант работает медленно. Некоторые говорят, что подход на картинке быстрее. Так ли это?

Вопрос: как добавить SQL statements в начало и конец dump прямо во время выполнения импорта?

#MySQL #DBA Tips

👉 @SQLPortal