PostgreSQL *

CTE в PostgreSQL упрощают код, но могут снижать производительность. Разбираем 4 антипаттерна, примеры EXPLAIN ANALYZE и практические способы оптимизации.

В прошлой статье мы упоминали основные SQL‑антипаттерны, способные замедлять работу базы данных. Продолжаем тему — на этот раз про CTE. 

Common Table Expressions (CTE), или конструкции WITH, — привычный инструмент SQL-разработчика. Чем сложнее запрос, тем выше шанс встретить в нём WITH: код становится чище, а запутанная логика разбивается на понятные блоки. CTE используют как альтернативу вложенным запросам и временным таблицам. Однако за внешней простотой и читаемостью скрываются риски снижения производительности, которые не всегда удаётся предвидеть.

Такие запросы на первый взгляд выглядят правильными, но работают неэффективно, и проблема вылезает только в EXPLAIN ANALYZE (инструмент разбирали в прошлом гайде). Разберём четыре антипаттерна CTE, посмотрим планы выполнения и покажем, как переписать запрос. В конце — короткий чек-лист диагностики.

Эта статья может быть полезна начинающим разработчикам и аналитикам, которые уже полюбили синтаксис CTE, но хотят понять, что на самом деле происходит «под капотом» в PostgreSQL.

Мы (ну ладно, я) ждали этого больше десяти лет: в PostgreSQL 19 наконец-то завезли штатную онлайн-перепаковку таблиц. Команда REPACK в ядре и теперь больше никаких сторонних расширений и бесконечных согласований с ИБ. Да? Или нет? Эпоха pg_repack подошла к концу? Спойлер: не спешите удалять старые скрипты и утилиты. На моих тестах новая встроенная команда под нагрузкой заблокировала таблицу на три с лишним минуты, в то время как "старичок" pg_repack уложился в 0.6 секунды.

Выяснил: как устроен новый REPACK под капотом, почему он ломает привычный MVCC и в каких сценариях попытка использовать штатный инструмент на проде станет фатальной ошибкой.

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

Разберём, откуда PostgreSQL берёт статистику, как ANALYZE её собирает и по каким признакам понять, что проблема действительно в оценках планировщика.

Объектное хранилище с типами, деревьями и настоящим SQL прямо в вашей PostgreSQL/MS SQL/SQLite. FK на объекты, EF и Dapper рядом. redb против Mongo и Raven.

Чтобы хранить объекты, графы и деревья с типами, индексами и полноценными запросами, вам не нужна ещё одна база данных. Нужна та, что у вас уже есть.

redb превращает PostgreSQL, MS SQL или SQLite в типизированное объектное хранилище, не отнимая ни SQL, ни EF Core, ни Dapper. Это принципиально другой разговор, чем «MongoDB против RavenDB»: там вы выбираете отдельный движок и живёте с ним отдельно; здесь объекты ложатся в базу, которая у вас уже крутится в проде. Ниже чем это выигрывает у документных баз, с кодом, и где у redb честные границы.

Чтобы не спорить с чучелами: MongoDB  зрелая серверная документная база с горизонтальным масштабированием и огромной экосистемой, и мультидокументные ACID-транзакции у неё есть с версии 4.0 (2018). RavenDB  .NET-native документная база, полностью ACID, с типизированным LINQ и автоиндексами. Обе хорошие продукты. redb просто играет на другом поле и на этом поле у него сильные карты.

И учить, по сути ...

NVMe показывает сотни тысяч IOPS, а база упирается в пару сотен коммитов в секунду — знакомая ситуация, если ориентироваться на обычные тесты записи.

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

Большинство материалов о компрессии в PostgreSQL отвечают на вопрос «во сколько раз удалось уменьшить базу». Мы предлагаем посмотреть на проблему с другой стороны: какой ценой достигается эта экономия? В статье разбираем архитектурные компромиссы различных подходов к компрессии страниц, объясняем, почему при разработке CSM в Tantor Postgres отказались от погони за максимальным коэффициентом сжатия, и показываем результаты нагрузочных испытаний на реальных базах 1С.

Привет, Хабр!

В этой статье я хочу подробно разобрать практический пример инкрементальной синхронизации данных между двумя базами PostgreSQL с использованием FESB.

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

Статья в первую очередь будет полезна разработчикам, интеграторам и архитекторам, которые работают с ESB, ETL и реляционными базами данных. Даже если вы не используете FESB, описанный подход с контрольной точкой и инкрементальной загрузкой можно адаптировать для других интеграционных платформ.

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

Агрегаты в PostgreSQL не очень-то эффективны. Это особенно заметно в сравнении с SQL Server в сценарии, где частичная агрегация не помогает: когда агрегация только подготавливает данные для запроса, обрабатывая большой поток строк и на выходе получая ненамного меньший набор групп и посчитанных по ним агрегатов. Хуже всего приходится типам переменной длины. И здесь характерный пример — SUM(numeric). Встроенные агрегаты обязаны обрабатывать значения в самом общем виде, тогда как на практике данные часто ограничены: например, в БД 1С все numeric имеют фиксированный масштаб.

Отсюда возникает идея оптимизировать агрегаты, подстроив их под конкретные условия эксплуатации. Раньше это было возможно только в форке PostgreSQL. Однако недавно David Rowley добавил в ядро любопытный инструмент расширения SupportRequestSimplifyAggref (коммит 42473b3b31, PostgreSQL 19): теперь можно предоставить планнеру кастомную логику трансформации агрегата через механизм функций поддержки планнера (prosupport). Сам механизм существует ещё с PostgreSQL 12, но до агрегатов добрался только сейчас. В ядре новый запрос применяется скромно: заменяет COUNT(1) и COUNT(col) по NOT NULL-колонке на COUNT(*). А вот расширению он позволяет сделать с агрегатом во время планирования практически что угодно. Это открывает пространство для интересных технических решений.

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

Про асинхронный ввод-вывод в PostgreSQL за последний год написали многие:

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

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

Так получилось, что пару десятилетий занимался в тои числе и базами данных. Ставил. Тюнил. Выносил логику В базу. Вынос иллогику ИЗ базы. Поднимал когда падали... Когда перешёл в большой SRE‑спорт, почти октаду лет поддерживал экзабайтные аналитические query engine. В общем, развлекался, как мог.

Главное открытие заключалось в том, что почти всё, что считается невозможным, случается. Люди креативны. Ты им дашь регексп — и они начнут майнить биткойны. Ты им дашь data lake — и они начнут хранить данные в именах таблиц. Ты им дашь GEO... Сам виноват.

Но самое интересное в том, что есть пласт проблем, который лежит совсем не в области современных сверхсложных методов параллелизации обработки, а в наследии 80ых годов прошлого века. То, что никого раньше не волновало, внезапно стало вызывать сбои. Причём в самом лучшем их проявлении — всё работает, клиент доволен, система растёт, цветёт и пахнет. А в один внезапный день запросы не работают, сервер падает, и что делать непонятно, так как никто ничего не менял.

И это ещё хороший сценарий, так как никто ничего плохого и не хотел: можно поймать запрос, заблокировать, и спокойно разбираться.

Часть 1. Как я делаю backend для браузерной игры три в ряд: server-driven персонажи и способности, общий контракт клиента и сервера, версионирование контента, транзакции, идемпотентность и админка.

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

Построение HA-кластера в PostgreSQL традиционно требует внешнего стека: Patroni, распределенного хранилища конфигураций и набора вспомогательных утилит. В СУБД Postgres Pro Standard и Enterprise отказоустойчивость реализована на уровне ядра: механизм BiHA берет на себя консенсус Raft, защиту от split-brain и перестроение топологии.

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

Документация PostgreSQL по numeric содержит два плохо согласующихся утверждения:

«especially recommended for storing monetary amounts and other quantities where exactness is required» — и сразу же: «calculations on numeric values are very slow compared to the integer types, or to the floating-point types». То есть рекомендуют для хранения денежных величин и тут же признают, что это весьма дорого.

Для меня, как разработчика СУБД это сигнал к действию. Если операции с типом заметно медленнее bigint, возникает соблазн: а нельзя ли хранить денежные величины целым числом копеек и округлять по стандартному правилу? Это бы прилично сэкономило вычислительные ресурсы наших серверов баз данных, разве нет? А что, если вообще использовать double precision?

Недавно я написал свою систему распределённой обработки задач. Сначала это был учебный проект. Мне хотелось лучше разобраться в очередях задач, координации воркеров, блокировках строк в PostgreSQL, retry‑механизмах и планировании фоновых задач.

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

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

Увеличение количества воркеров с 16 до 32 практически не дало прироста производительности, а при 64 воркерах ситуация стала ещё интереснее: задачи, которые должны были выполняться около 0.5 секунды, начали занимать больше секунды.

Первой моей мыслью было, что я упёрся в PostgreSQL.
Оказалось, что нет.

Привет, Хабр!

Меня зовут Жуйков Андрей, занимаюсь развитием и продвижением СУБД Digital Q.DataBase.

Сегодня для многих организаций импортозамещения СУБД - уже практическая задача, которую необходимо решать без остановки бизнес-процессов и без многомесячной переработки прикладных систем. Одним из ключевых требований при выборе новой платформы становится возможность сохранить существующую прикладную логику и минимизировать объем изменений в коде приложений.

В этой статье я покажу, как установить Digital Q.DataBase 18.2 на РЕД ОС 8.0.3, познакомлю с новой архитектурой СУБД и продемонстрирую подключение к каждому из поддерживаемых диалектов.

Если вы пишете SQL‑запросы, то наверняка сталкивались с ситуацией, когда данные исчезают, отчеты не сходятся, а бизнес теряет деньги. И виновник этого — маленькое, но очень коварное слово NULL. В 1974 году Эдгар Кодд, создатель реляционной модели данных, ввел это понятие, чтобы обозначить отсутствие информации. Он хотел, как лучше, но спустя пятьдесят лет NULL продолжает «терроризировать» разработчиков по всему миру. Важно усвоить раз и навсегда: NULL — это не значение. Это состояние неизвестности.

Поэтому:

· NULL ≠ 0 (ноль — это число);
· NULL ≠ '' (пустая строка — это строка);
· NULL ≠ ' ' (пробел — это символ).

Но всегда есть нюансы и исключения. Например, в Oracle INSERT INTO table (col) VALUES ('') запишет NULL. Это поведение отличается от других СУБД и часто становится сюрпризом при миграции.

Когда речь заходит об оптимизации запросов в PostgreSQL, разработчики, как правило, сосредотачиваются на времени выполнения: индексы, планы запросов, настройки памяти и так далее. Время планирования остаётся в тени. А зря! Планировщик работает перед каждым выполнением запроса. Для OLTP‑нагрузки с короткими транзакциями накладные расходы на планирование могут составлять значительную долю от общего времени ответа. Для запросов с большими IN‑списками и высоким statistics_target планирование может занимать сотни миллисекунд, тогда как само выполнение укладывается в миллисекунды. Поэтому ускорение планировщика не менее важно, чем ускорение выполнения.

В статье рассматривается исполнительная часть утилиты ggrebalance: механизм физического перемещения primary- и mirror-сегментов между хостами кластера Greengage DB (open-source форк Greenplum), устройство конечного автомата ребаланса, отслеживание статусов операций, обработка сбоев и реентерабельность, откат перемещений, а также практические рекомендации по эксплуатации и итоговое сравнение с альтернативными инструментами.

CPU и диск свободны, а сервис уже задыхается: транзакции ждут блокировок, соединения заканчиваются, база перестаёт отвечать.

Разберём, как найти корневой процесс в PostgreSQL, отличить длинную транзакцию от конкуренции за строки и остановить каскадный сбой.