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

Как убрать тормоза ORM на 80% с помощью грамотных индексов в PostgreSQL

Современные приложения часто плотно завязаны на ORM (Object-Relational Mapper) ради быстрой разработки. ORM реально ускоряют разработку, но при этом нередко генерируют запросы, которые не до конца оптимальны с точки зрения производительности базы. В такой ситуации у инженеров по БД мало контроля над тем, как именно выглядят запросы, поэтому индексация и тюнинг базы остаются главными инструментами для ускорения.

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

👉 @SQLPortal

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

SET lock_timeout = '10s';

👉 @SQLPortal

Как использовать функцию «Text to Columns» в Excel.

👉 @SQLPortal

Миф, что реляционные БД не масштабируются!!!

Zerodha, один из крупнейших брокеров Индии, придумал любопытный способ решать проблему с отчетами.

В этом видео CTO Zerodha, Kailash Nadh, рассказывает про софтверный хак под названием Dung Beetle, который помогает вытягивать огромные объемы отчетности по сотням миллиардов финансовых записей.

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

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

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

👉 @SQLPortal

Удалять огромную пачку строк (скажем, миллион) одним DELETE кажется эффективным. На деле нет. Так можно легко положить базу.

Когда ты делаешь большой DELETE одним запросом, база держит блокировки на всех затронутых строках до конца операции. Если запрос выполняется 30 секунд, блокировки висят 30 секунд. Любые другие запросы, которым нужны эти строки, встают и ждут.

На нагруженных системах (много чтений и апдейтов) это разъезжается волной. Чтения начинают копиться, записи блокируются, пул соединений забивается. Такой “клининг” очень легко превращается в продовый инцидент :)

Есть еще WAL (transaction log). Массовый DELETE генерит огромный объем логов разом, что может резко поднять disk I/O и замедлить репликацию. Реплики начинают отставать, иногда очень заметно.

Фикс простой: удаляй батчами.

DELETE ... WHERE ... LIMIT 1000, потом небольшая пауза, потом повтор. По общему времени это может быть дольше, зато блокировки короткие, записи в WAL размазаны по времени, и база остается отзывчивой.

Забавный факт: базы не спасут тебя от тебя самого :) Это как раз из тех вещей, которые ты усваиваешь один раз, обычно на факапах. Не спрашивай, откуда знаю :) 🎉

👉 @SQLPortal

Если ты читал предыдущий пост про буферы, ты уже знаешь: PostgreSQL вообще не обязан “думать” про твои строки. Ты можешь вставлять профиль пользователя или доставать платежные данные, но для Postgres все это просто блоки данных. Конкретно блоки по 8KB. Хочешь получить одну маленькую строку? PostgreSQL тащит с диска целую страницу на 8 192 байта, чтобы отдать тебе ее. Обновляешь один булевый флаг? Та же история. 8KB-страница это атомарная единица I/O.

Но просто знать, что эти страницы существуют, мало. Чтобы понять, почему база ведет себя так, как ведет, надо понимать, как она устроена. Каждый раз, когда ты выполняешь INSERT, PostgreSQL должен решить, как упаковать запись в одну из этих страниц по 8 192 байта.

Буферный пул кеширует эти страницы, Write-Ahead Log (WAL) защищает их, а VACUUM вычищает. Глубокое погружение во внутренности хранения PostgreSQL начинается с понимания того, что вообще происходит внутри этих 8KB-страниц. Страниц, которые PostgreSQL использует, чтобы организовать все данные: таблицы, индексы, последовательности (sequences), TOAST-отношения (relations).

Читать гайд

👉 @SQLPortal

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

Если тебе нужно просто понять, есть ли конкретная запись в данных, не используй COUNT() с фильтром (WHERE).

SELECT COUNT(*)
FROM users
WHERE email = 'johnfake@analytica.com';

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

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

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

SELECT EXISTS (
  SELECT 1
  FROM users
  WHERE email = 'johnfake@analytica.com'
);

С EXISTS база остановится, как только найдет первое совпадение. Никакого подсчета, никакого лишнего сканирования.

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

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

👉 @SQLPortal

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

👉 @SQLPortal

Вышел pg_ash.

У Postgres нет session history. Если час назад что-то тормозило, смотреть уже некуда.

pg_ash это чинит: чистый SQL, установка одним файлом, работает на RDS / Cloud SQL / Supabase / self-hosted, везде где есть pg_stat_statements и pg_cron.

Никаких extensions, никаких рестартов, никаких “одобрений провайдера”. Просто \i и один файл.

Вот репо: [https://github.com/NikolayS/pg_ash]

Сэмплинг раз в 1 секунду, примерно 100–200 байт/сек, около 20–30 MiB сырых данных в день, плюс ротация партиций без раздувания (отсылка к Skype PGQ).

И еще: 32 функции, заточенные под RCA (поиск первопричины), удобно и людям, и AI.

Пример расследования с помощью LLM тут

// self-driving Postgres это будущее Postgres

👉 @SQLPortal

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

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

idle_in_transaction_session_timeout = '10min'

👉 @SQLPortal

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

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

https://github.com/tobilg/polyglot

👉 @SQLPortal

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

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

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

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

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

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

👉 @SQLPortal

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

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

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

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

👉 @SQLPortal

😰😰😰

👉 @SQLPortal

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

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

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

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

👉 @SQLPortal