eBPF-трейсинг спинлоков PostgreSQL

PostgreSQL использует процессную архитектуру: каждое подключение обслуживается отдельным процессом. Часть структур данных шарится между этими процессами, например shared buffer cache или write-ahead log (WAL). Чтобы координировать доступ к этим общим ресурсам, PostgreSQL использует несколько механизмов блокировок, в том числе спинлоки.

Спинлоки рассчитаны на очень короткую защиту общих структур: вместо того чтобы сразу усыплять процесс, который ждёт, он крутится в busy-wait и снова и снова проверяет, освободился ли лок. При контеншене PostgreSQL ещё применяет adaptive backoff, который может включать короткие sleep.

В статье объясняется, что такое спинлоки и как они реализованы в PostgreSQL. Также описывается, как их мониторить, и показывается, как использовать мой новый инструмент pg_spinlock_tracer, чтобы трейсить внутренности спинлоков через eBPF.

👉 @SQLPortal

50 практических вопросов по SQL для отработки навыков запросов, часто появляющихся на собеседованиях

> SQL‑скрипты для создания тестовых данных (таблицы, вставка строк)

> Вопросы типа: «Напишите запрос, чтобы вывести уникальные значения», «Найдите N‑ю максимальную зарплату» и т.д.

> Готовые решения и объяснения

читать

👉 @SQLPortal

Если у тебя получается 3 и больше вложенных циклов, остановись и переделай.

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

Цель всегда одна: чтобы код было легко понять и безопасно менять.

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

👉 @SQLPortal

Мониторинг планов запросов с pgwatch и pg_stat_plans: читать

В экосистеме PostgreSQL появилась новая extension pg_stat_plans. Она похожа на pg_stat_statements, но собирает агрегированную статистику не по SQL-выражениям, а по планам выполнения запросов.

Она предоставляет SQL-интерфейс для доступа к этим метрикам через представление (view) pg_stat_plans. В этом посте покажут, насколько просто интегрировать такие расширения с pgwatch для тех, кто хочет улучшить или кастомизировать набор метрик, которые собираются с мониторируемой базы.

👉 @SQLPortal

Сравнение базовых операций в MySQL и MongoDB

👉 @SQLPortal

Что выбрать?

Оба варианта означают не равно.

✅<> это стандарт SQL

Оператор <> определен стандартом ANSI SQL.
Он работает везде, во всех СУБД.

⚠️!= это поддержка от вендоров

Большинство современных СУБД понимают !=, но технически это не “родной” стандартный оператор. Его добавили в основном ради удобства разработчиков (ну потому что во многих языках используется !=).

Признаюсь, я сам чаще пишу != из-за привычек после Python. 😂

Чаще используй <>.

Почему?
- это стандарт SQL
- максимально переносимо между СУБД

А ты как пишешь? Что чаще используешь, <> или !=?

👉 @SQLPortal

В современных версиях Postgres есть классная фича: можно скопировать на standby все детали репликации. Тогда, если primary упадет и произойдет failover на новый primary, вся logical replication продолжит работать дальше.

👉 @SQLPortal

Row-level и Column-level Security: Oracle vs PostgreSQL

Защита чувствительных данных это не только фаервол. Нужен точный контроль доступа прямо внутри базы. Понимать технические отличия Row-level и Column-level Security в Oracle и PostgreSQL важно, особенно при миграции между СУБД и построении безопасной среды. В Oracle для тонкой настройки обычно используют Virtual Private Database (VPD), а в PostgreSQL есть нативные механизмы Row- и Column-level Security, которые дают более простой и декларативный подход. Используя эти возможности PostgreSQL, админы могут выстроить модель “защита в глубину”, которая закрывает доступ к конкретным строкам и полям без сложного проприетарного кода.

Такая детализация позволяет архитекторам зашивать правила безопасности прямо в слой данных, чтобы политики работали независимо от того, откуда идёт доступ: через REST API, BI-инструмент или из командной строки. Переход от процедурной модели Oracle (к этому мы ещё вернёмся) к нативным политикам PostgreSQL не только упрощает соответствие требованиям, но и улучшает аудит. Если смотреть шире, почему такие контролы вообще критичны для предотвращения утечек, то OWASP Top Ten стабильно включает “сломанный контроль доступа” в список самых серьёзных рисков для веб-приложений.

👉 @SQLPortal

Вышли PostgreSQL 18.2, 17.8, 16.12, 15.16 и 14.21: В этих релизах закрыто 5 уязвимостей безопасности и исправлено больше 65 багов, найденных за последние несколько месяцев.

Полный список изменений смотрите в release notes.

👉 @SQLPortal

😰😰😰

👉 @SQLPortal

Забавный SQL-вопрос

Какой будет результат у этого запроса и почему?

SELECT 'Found' AS result
WHERE 1 = 1 AND (0 OR FALSE);

A) 'Found'
B) Ничего (пустой результат)
C) Ошибка
D) 'Found' два раза

👉 @SQLPortal

Если ты когда-нибудь пытался учить PostgreSQL (переезжая с SQL Server, MySQL или просто с нуля), то наверняка упирался в ту же проблему, что и я: где взять нормальную учебную базу, которая понятная и при этом с не протухшими данными?

Да, датасетов вокруг море. На Kaggle сейчас больше 600 000 публичных датасетов, но в основном это статичные CSV, которые ты один раз загрузил и дальше они лежат мертвым грузом. Для разового анализа ок, но для понимания того, как реальная база ведет себя со временем, так себе. На Postgres Wiki тоже есть несколько десятков примерных баз. И вообще, твой модный AI-напарник по кодингу может помочь сгенерить такую базу, если готов потратить время.

Но проблема большинства таких наборов данных в том, что они по сути статичные. В лучшем случае где-то раз в месяц выкатывают новый дамп, чтобы было “актуально”. Только от этого толку мало, потому что:

ты не потренируешь тюнинг запросов, если данные никогда не меняются
ты не поизучаешь поведение VACUUM, если нет апдейтов
ты не протестишь мониторинг, если в системе ничего не происходит

Поэтому и был сделан Bluebox. А теперь автор рад показать следующий шаг: Bluebox Docker. Это готовый к запуску PostgreSQL-контейнер, который дает тебе реалистичную, постоянно обновляющуюся “учебную” базу, и все это с нулевой настройкой.

Кайф же, да? Читать

👉 @SQLPortal

Представили polyglot: SQL-транспайлер на Rust, который умеет перегонять запросы между более чем 30 SQL-диалектами.

По тестам у него 100% покрытие фикстур sqlglot.

https://github.com/tobilg/polyglot

👉 @SQLPortal

Совет по Postgres: убивай сессии, которые зависли в состоянии idle in transaction.

Для этого включи таймаут:

idle_in_transaction_session_timeout = '10min'

👉 @SQLPortal

Тулза для мажорного апгрейда версии получила несколько приятных новых фич в Postgres 18. Нам особенно нравится, что теперь можно переносить статистику по таблицам, значит, сразу после апгрейда производительность будет нормальной, без долгого разогрева.

👉 @SQLPortal

SCRAM уже давно стал предпочтительным способом хеширования паролей вместо MD5 у большинства. Postgres сейчас постепенно выводит MD5 из употребления: в Postgres 18 уже появляются предупреждения, а в Postgres 19, скорее всего, депрекейтнут еще жестче. Проверьте старые пароли и обновите их как можно скорее 🙏

👉 @SQLPortal

PostgreSQL делает одну хитрую оптимизацию, из-за которой некоторые сканы становятся index-only и запрос выполняется быстрее. Вот как это работает…

Index-only scan это когда PostgreSQL забирает данные полностью из индекса, не обращаясь к реальным строкам таблицы (heap). Это сильно быстрее, потому что убирается лишний шаг чтения строк с диска.

Чтобы index-only scan реально работал, PostgreSQL должен быть уверен, что данные в индексе актуальны. Из-за реализации MVCC одна и та же строка может существовать в нескольких версиях, которые используются разными активными транзакциями.

В не оптимизированном варианте PostgreSQL пришлось бы читать реальную строку, чтобы проверить ее видимость (visibility) и при необходимости взять последнюю версию. Из-за этого index-only scans могут быть не такими быстрыми, как хотелось бы. Чтобы ускорить это, PostgreSQL использует Visibility Map.

Visibility Map это внутренняя структура данных, которую PostgreSQL ведет, чтобы отслеживать, какие страницы (блоки строк) в таблице содержат только такие tuple (строки), которые видимы всем транзакциям. Если страница помечена в visibility map как all-visible, движок понимает, что ему не нужно проверять видимость отдельных строк на этой странице.

Поэтому во время index-only scan PostgreSQL сначала вычисляет строки, которые должны попасть в результат, а потом смотрит в visibility map, помечена ли соответствующая страница как all-visible. Если да, он пропускает чтение этой страницы из heap, и выполнение становится быстрее и эффективнее.

В PostgreSQL из-за MVCC операции update и delete приводят к появлению dead tuples, и чтобы их очистить, нужен vacuum. Поэтому в процессе vacuuming:

1. удаляются dead tuples, и
2. обновляется visibility map, чтобы пометить страницы как all-visible.

Это означает, что каждая строка на этой странице видима всем транзакциям, и теперь можно пропускать heap fetch и выполнять index-only scans быстрее.

👉 @SQLPortal