warda bibi: Раскрываем высокую производительность PostgreSQL: ключевые оптимизации памяти

👉 @SQLPortal

БОЛЬШОЙ SQL-грех

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

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

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

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

👉 @SQLPortal

Postgres 18 теперь поддерживает OAUTH 2.0 для аутентификации пользователей.

Также в Postgres 18 log_line_prefix теперь может включать %L для вывода IP-адреса клиента.

👉 @SQLPortal

Строки в PostgreSQL неизменяемые и хранятся в heap. У каждой строки есть скрытый идентификатор CTID, который указывает на ее физическое местоположение на диске.

CTID это идентификатор-кортеж из двух частей: номер страницы и индекс (позиция) внутри этой страницы. Посмотреть его можно простым запросом:

SELECT ctid, * FROM your_table;

Вывод будет выглядеть примерно так: (0,1), (0,2), (1,1), где первое число это страница, а второе это позиция.

Что в CTID интересного: когда ты создаешь индекс по первичному ключу, индекс не хранит всю строку целиком. Он хранит значение ключа и CTID, который указывает на реальное место строки в heap.

Из-за этого выборки быстрые. Индекс дает CTID, и PostgreSQL прыгает напрямую в нужную физическую точку в heap, чтобы достать строку.

Но есть нюанс: CTID меняется при обновлении строки. PostgreSQL использует MVCC (Multi-Version Concurrency Control), поэтому update создает новую версию строки в другом физическом месте. Старый CTID становится невалидным, а у строки появляется новый.

Из-за этого индексы нужно обновлять при каждом обновлении строки. Значение ключа может остаться тем же, но CTID, на который оно указывает, меняется.

Интересные архитектурные решения. PostgreSQL сэкономил одно лишнее разыменование, сохраняя CTID, но теперь индекс нужно переписывать даже если меняются неиндексируемые колонки.

Вот почему я обожаю копаться во внутренностях СУБД :) Там сплошь интересные решения и компромиссы.

👉 @SQLPortal

Как получить топ-5 продавцов в SQL (с учётом ничьих)

❌Первый вариант выглядит корректно, но он не учитывает ничьи. Если несколько продавцов делят 5-е место, SQL просто молча отбросит лишних.

✅Если база данных поддерживает FETCH FIRST … WITH TIES (например, PostgreSQL), второй вариант будет работать правильно и вернёт всех, кто делит место.

✅Для остальных СУБД выручит оконная функция — через RANK() или DENSE_RANK(). Она корректно обработает равные значения и ничего не потеряет.

👉 @SQLPortal

Гайд: материализованные представления в PostgreSQL — когда кэширование результатов запросов имеет смысл (и когда нет…)

👉 @SQLPortal

В Postgres есть несколько способов делать ранжирование с ORDER BY. С выбором нужно быть аккуратным, особенно если есть дубликаты значений.

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

👉 @SQLPortal

Позволь Postgres владеть Iceberg-каталогом, а аналитику делегировать DuckDB. В итоге получаешь транзакционные обновления lakehouse плюс быстрые аналитические запросы.

Это не концепт. Именно так pg_lake работает уже сегодня.

pg_lake объединяет набор расширений и компонентов, которые позволяют напрямую из Postgres читать и изменять Iceberg-таблицы (и другие lakehouse-форматы). Для ускорения аналитических запросов используется DuckDB, запущенный в сайдкар-процессе pgduck_server, который общается с Postgres во время выполнения запроса.

Как это работает:

1. Приложение отправляет запрос в Postgres, чтобы посчитать нереализованный PnL (profit and loss) по тикеру Disney.

2. Postgres парсит запрос и определяет часть, где считается средняя цена на основе исторических данных из lakehouse.

3. Эта часть запроса проксируется в pgduck_server для ускоренного выполнения.

4. pgduck_server передает выполнение в DuckDB, который читает данные из lakehouse (переиспользуя кэш, если он есть).

5. DuckDB считает среднюю цену и возвращает результат в Postgres.

6. Postgres джойнит этот результат с локальными данными портфеля и считает нереализованный PnL.

7. Финальный результат возвращается приложению.

👉 @SQLPortal

Снижение ошибок оценки количества строк в PostgreSQL: читать

Планировщик запросов PostgreSQL опирается на статистику таблиц, чтобы оценить, сколько строк (estimated rows) будет обработано на каждом этапе выполнения, и на основе этих оценок выбрать оптимальный план выполнения. Когда оценка сильно расходится с реальным количеством строк, планировщик может выбрать неудачный план, что приводит к серьёзному падению производительности запросов.

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

👉 @SQLPortal

Рекурсивные CTE в Postgres позволяют проходить по вложенным структурам или иерархиям и находить связи шаг за шагом. Ты задаёшь рекурсию, добавляешь anchor-запрос для старта, и дальше CTE будет итерироваться по данным, пока не дойдёт до конца.

👉 @SQLPortal

У индексов есть встроенный порядок сортировки. По умолчанию B-tree индекс идет по возрастанию с NULLS LAST.
Postgres умеет сканировать индекс как вперед, так и назад, поэтому во многих случаях это не имеет значения.
Но если порядок NULLS FIRST или NULLS LAST не совпадает с тем, что требуется в запросе, Postgres уже не сможет использовать этот индекс.

👉 @SQLPortal

Elasticsearch отлично работает в разработке. В продакшене все совсем иначе.

За годы эксплуатации ES-кластеров команды снова и снова натыкаются на одни и те же проблемы. Они всплывают в инцидент-репортах и вопросах на Stack Overflow. В статье собраны 10 самых частых:

» Паузы сборщика мусора JVM, которые запускают каскадные отказы
» Взрыв маппингов из-за полуструктурированных данных
» Математика с шардами, которую нужно угадать заранее (и потом уже не поменять)
» Глубокая пагинация, упирающаяся в жесткий лимит в 10 000 результатов
» Split-brain сценарии при сетевых разделениях
» Сюрпризы eventual consistency («я только что сохранил, но не могу найти»)
» Ошибки в настройке безопасности, которые приводили к массовым утечкам
» Сотни метрик, где понять, какие реально важны, можно только с глубокой экспертизой
» Пайплайны синхронизации данных между Postgres и ES, которые ломаются тихо
» Инфраструктурные затраты, растущие с каждым новым узлом

У всех этих проблем один корень: Elasticsearch — это отдельная система.

Отдельная инфраструктура, отдельная экспертиза, отдельные режимы отказа, отдельные пайплайны.

Теперь, когда BM25 доступен в Postgres через pg_textsearch, поиск можно оставить в основной базе. Без JVM, без шардов, без джобов синхронизации. Одна база, один источник истины.

В блоге разбирается каждая из проблем со ссылками на реальные инциденты и объясняется, как Postgres их избегает

👉 @SQLPortal

SQL-хак, о котором мало кто знает

Знал, что можно юзать FILTER как альтернативу CASE в агрегатах?

Самое приятное, что условия отделяются от самой агрегации, и это проще дебажить.

Если твой SQL-движок поддерживает FILTER (PostgreSQL, SQLite, DuckDB), попробуй.

CASE или FILTER? Что тебе больше нравится? 😆

👉 @SQLPortal

Table bloat это одна из самых больших слабых сторон Postgres.

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

В базах с большой нагрузкой по UPDATE и DELETE в таблицах накапливаются тонны мертвых строк (tombstones), разбросанных по файлу таблицы. В итоге размер таблицы на диске выглядит гораздо больше, чем реально нужно.

Это и есть table bloat.

Мертвые строки могут переиспользоваться новыми строками, если те по размеру влезают, но иногда, чтобы реально вернуть все доступное мертвое место, нужен VACUUM FULL или похожая пересборка таблицы через расширения.

И это не случайный косяк дизайна: хранение старых версий строк полезно для MVCC и производительности. Просто за это платишь усложнением управления хранилищем :/

👉 @SQLPortal

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