К сожалению, гарантировать идентичные планы можно только если приближать тестовый стенд к проду. Но если отходить от частных случаев того что лежит в базе на 1Тб, где, например, одна только табличка может быть полтерабайта, то в целом, алгоритм можно предложить следующий:
Сделать дамп схемы продакшена и снять статистику.
Собрать планы запросов, выделить 20% таблиц, которые дают 80% нагрузки и это перенести на тестовую базу.
При переносе важно учитывать индексы метаданные.
Перенести схемы больших таблиц и загрузить часть данных например каждую 100 запись, загрузить записи содержащие MIN и MAX значения для всех колонок больших таблиц
Перенести настройки postgresql с прода и привести к характеристикам тестовой среды (shared_buffers, work_mem, maintenance_work_mem, effective_cache_size и т.д.) Важно выделить то, что зачастую планы могут зависеть от окружения и подкладывать их нужно исходя из статистики. Проведение нагрузочного тестирования в большей степени относится к работе приложений, мы со своей стороны ведём внутри обширную работу по тестированию именно производительности БД.
Согласен с тем, что хранить большие файлы в БД — плохая идея. Но некоторые пользователи так делают, это факт. Достаточно часто спрашивают об этом при переходе на Postgres с Oracle, где есть возможность хранить файлы в БД.
2. СУБД работает с файлами на сервере, как загрузить файл в базу данных с клиентской машины?
Для типа BFILE файл загружается на сервер средствами пользователя. СУБД "узнаёт" об этом файле только в момент создания значения BFILE (т.е. ссылки на внешний файл).
Для типа SFILE файлы можно загрузить либо с помощью серверных, либо с помощью клиентских функций. Подробности лучше посмотреть в документации:
1.Как обеспечивается репликация, когда хранение файлов осуществляется в базе данных. Как это влияет на производительность?
Для типа BFILE (внешний файл) реплицируются только значения BFILE, т.е., по сути дела, это путь к файлу и имя файла. Решение проблемы репликации самих внешних файлов, а также того, чтобы на реплике пути к внешним файлам соответствовал путям к внешним файлам на основном сервере, возложена на пользователя.
Значения типа SFILE целиком хранятся в БД и реплицируются обычным образом, как и другие значения БД. Естественно, при больших объемах файлов это плохо влияет на производительность. Если для пользователя критична производительность при репликации, то следует использовать BFILE (т.е. ссылки на файлы) и обеспечивать репликацию самих файлов самостоятельно.
Так можно, но это не устраняет транзакционный разрыв между БД и брокером. Последовательность «сначала фиксация БД, затем публикация» с идемпотентностью снижает риск потери, но допускает дубли и требует дополнительной инфраструктуры (outbox/CDC, мониторинг, повторная доставka). В pgpro_queue сообщение и данные живут в одной транзакции: при откате оно автоматически возвращается в очередь, без внешних механизмов согласования.
P.S.: ваш пример в ачале статьи с транзакионным разрывом между брокером и бд некорректен: все зависит от последовательности "коммитов", при правильной последовательности мы таску\сообщение не теряем, но должны быть готовы к обработке дубля. Что может быть дешевле 2pc или просто остается единственным вариантом, когда 2pc не возможен (kafka+db).
Суть примера — показать фундаментальный разрыв атомарности между брокером и БД без 2PC: outbox/CDC и «сначала коммит БД, затем публикация» снимают риск потери, но оплачиваются сложностью идемпотентности, дедупликации и возможными повторными доставками. pgpro_queue решает это иначе: сообщение и бизнес‑данные находятся в одной транзакции СУБД, где откат гарантированно возвращает сообщение в очередь, то есть нет разрыва и нет необходимости отдельно проектировать анти‑дубль логику на уровне интеграции.
Эта проблема решается стандартными, но целенаправленно настроенными средствами самого PostgreSQL, так как очередь представляет собой обычную таблицу. Предполагается, что для высоконагруженных таблиц-очередей администратор базы данных настроит более агрессивный autovacuum, чтобы минимизировать раздувание таблиц и поддерживать высокую производительность чтения.
Вы правы, Kafka незаменима для больших потоков событий, но pgpro_queue решает другую ключевую задачу — обеспечение 100% транзакционной надёжности. В отличие от внешних брокеров, оно гарантирует атомарность операции "задача + данные", полностью исключая риск рассогласования при сбоях. Поэтому для критически важных систем, где цена ошибки высока, это не компромиссное, а стратегически верное решение для упрощения архитектуры и повышения её предсказуемости.
Расширение pgpro_queue является частью Postgres Pro Enterprise. То есть для использования именно этого решения нужна подписка на коммерческий дистрибутив Postgres Pro Enterprise. Оно тесно интегрировано с их механизмами, включает управляемые ретраи на откате и работу с планировщиком задач.
Да, всё верно, имеются в виду таблицы, хранящиеся на одном сервере.
Для использования шардирования требуется, чтобы глобальный индекс также был распределён по разным серверам (вместе с соответствующими им данными в секциях).
Но текущая реализация глобального индекса изначально проектировалась без поддержки секционирования, поэтому нет и поддержки шардирования.
При операциях удаления и DETACH PARTITION глобальный индекс не перестраивается. То есть, для глобального индекса является нормальным содержать некоторое количество недействительных данных (которые могут быть очищены с помощью команды VACUUM).
>Или же записи остаются в индексе условно "живыми", а удалёнными отмечаются только при попытке доступа к ним?
В глобальном индексе записи не помечаются как удалённые, так как в глобальном индексе хранятся не ссылки на версии записей в heap-таблицах (как в btree), а значения ключевых полей. Поэтому помечать как удалёнными записи не получится, так как при повторном добавлении записи с теми же значениями индексную запись придётся включать.
PPEM не является инструментом IaC, он предназначен для управления существующим ландшафтом СУБД, однако, в будущем мы планируем документировать REST API и разработать консольную утилиту для выполнения команд PPEM в командной строке, что позволит встраивать PPEM в пайплайны
К сожалению, гарантировать идентичные планы можно только если приближать тестовый стенд к проду. Но если отходить от частных случаев того что лежит в базе на 1Тб, где, например, одна только табличка может быть полтерабайта, то в целом, алгоритм можно предложить следующий:
Сделать дамп схемы продакшена и снять статистику.
Собрать планы запросов, выделить 20% таблиц, которые дают 80% нагрузки и это перенести на тестовую базу.
При переносе важно учитывать индексы метаданные.
Перенести схемы больших таблиц и загрузить часть данных например каждую 100 запись, загрузить записи содержащие MIN и MAX значения для всех колонок больших таблиц
Перенести настройки postgresql с прода и привести к характеристикам тестовой среды (shared_buffers, work_mem, maintenance_work_mem, effective_cache_size и т.д.) Важно выделить то, что зачастую планы могут зависеть от окружения и подкладывать их нужно исходя из статистики. Проведение нагрузочного тестирования в большей степени относится к работе приложений, мы со своей стороны ведём внутри обширную работу по тестированию именно производительности БД.
Согласен с тем, что хранить большие файлы в БД — плохая идея. Но некоторые пользователи так делают, это факт. Достаточно часто спрашивают об этом при переходе на Postgres с Oracle, где есть возможность хранить файлы в БД.
Для типа BFILE файл загружается на сервер средствами пользователя. СУБД "узнаёт" об этом файле только в момент создания значения BFILE (т.е. ссылки на внешний файл).
Для типа SFILE файлы можно загрузить либо с помощью серверных, либо с помощью клиентских функций. Подробности лучше посмотреть в документации:
https://postgrespro.ru/docs/enterprise/current/pgpro-sfile
Для типа BFILE (внешний файл) реплицируются только значения BFILE, т.е., по сути дела, это путь к файлу и имя файла. Решение проблемы репликации самих внешних файлов, а также того, чтобы на реплике пути к внешним файлам соответствовал путям к внешним файлам на основном сервере, возложена на пользователя.
Значения типа SFILE целиком хранятся в БД и реплицируются обычным образом, как и другие значения БД. Естественно, при больших объемах файлов это плохо влияет на производительность. Если для пользователя критична производительность при репликации, то следует использовать BFILE (т.е. ссылки на файлы) и обеспечивать репликацию самих файлов самостоятельно.
Разнообразный опыт это хорошо, но в первую очередь интересны те, кто имеет более менее опыт c PostgreSQL
Так можно, но это не устраняет транзакционный разрыв между БД и брокером. Последовательность «сначала фиксация БД, затем публикация» с идемпотентностью снижает риск потери, но допускает дубли и требует дополнительной инфраструктуры (outbox/CDC, мониторинг, повторная доставka). В pgpro_queue сообщение и данные живут в одной транзакции: при откате оно автоматически возвращается в очередь, без внешних механизмов согласования.
Суть примера — показать фундаментальный разрыв атомарности между брокером и БД без 2PC: outbox/CDC и «сначала коммит БД, затем публикация» снимают риск потери, но оплачиваются сложностью идемпотентности, дедупликации и возможными повторными доставками. pgpro_queue решает это иначе: сообщение и бизнес‑данные находятся в одной транзакции СУБД, где откат гарантированно возвращает сообщение в очередь, то есть нет разрыва и нет необходимости отдельно проектировать анти‑дубль логику на уровне интеграции.
По расширению функциональности на shardman, всё будет зависеть от потребностей клиентов.
Эта проблема решается стандартными, но целенаправленно настроенными средствами самого PostgreSQL, так как очередь представляет собой обычную таблицу. Предполагается, что для высоконагруженных таблиц-очередей администратор базы данных настроит более агрессивный autovacuum, чтобы минимизировать раздувание таблиц и поддерживать высокую производительность чтения.
Вы правы, Kafka незаменима для больших потоков событий, но pgpro_queue решает другую ключевую задачу — обеспечение 100% транзакционной надёжности. В отличие от внешних брокеров, оно гарантирует атомарность операции "задача + данные", полностью исключая риск рассогласования при сбоях. Поэтому для критически важных систем, где цена ошибки высока, это не компромиссное, а стратегически верное решение для упрощения архитектуры и повышения её предсказуемости.
Расширение pgpro_queue является частью Postgres Pro Enterprise. То есть для использования именно этого решения нужна подписка на коммерческий дистрибутив Postgres Pro Enterprise. Оно тесно интегрировано с их механизмами, включает управляемые ретраи на откате и работу с планировщиком задач.
Да, всё верно, имеются в виду таблицы, хранящиеся на одном сервере.
Для использования шардирования требуется, чтобы глобальный индекс также был распределён по разным серверам (вместе с соответствующими им данными в секциях).
Но текущая реализация глобального индекса изначально проектировалась без поддержки секционирования, поэтому нет и поддержки шардирования.
>как работает DETACH | DELETE PARTITION?
При операциях удаления и DETACH PARTITION глобальный индекс не перестраивается. То есть, для глобального индекса является нормальным содержать некоторое количество недействительных данных (которые могут быть очищены с помощью команды VACUUM).
>Или же записи остаются в индексе условно "живыми", а удалёнными отмечаются только при попытке доступа к ним?
В глобальном индексе записи не помечаются как удалённые, так как в глобальном индексе хранятся не ссылки на версии записей в heap-таблицах (как в btree), а значения ключевых полей. Поэтому помечать как удалёнными записи не получится, так как при повторном добавлении записи с теми же значениями индексную запись придётся включать.
Насчёт «почему есть только в коммерческих форках» не согласны, мы активно продвигаем его в ванильную PostgreSQL. И даже написали про это статью:
https://habr.com/ru/companies/postgrespro/articles/864142/
https://www.tadviser.ru/index.php/Продукт:Moc.OC_Серверная
Вот здесь можно подробнее почитать
PPEM не является инструментом IaC, он предназначен для управления существующим ландшафтом СУБД, однако, в будущем мы планируем документировать REST API и разработать консольную утилиту для выполнения команд PPEM в командной строке, что позволит встраивать PPEM в пайплайны