Как мне настроить PGbouncer для работы с SET search_path по запросам?

Question

У меня довольно большая база данных PostgreSQL с 300 подключенными клиентами из веб-приложения и некоторой фоновой обработкой.

Я подумываю добавить PGbouncer, поскольку, насколько я понимаю, postgresql не будет хорошо масштабироваться с таким количеством соединений из-за издержек соединения.

Фон

Мое веб-приложение является многопользовательским. Например, у меня есть немецкая версия моего сайта на www.my-app.de и американская версия на www.my-app.com. Данные по каждой стране делятся на отдельные SCHEMAS в postgresql. Таким образом, для каждого HTTP-запроса веб-приложение переключается между наборами данных в PostgreSQL, запуская запрос, устанавливая search_path вот так: если www.my-app.com то SET search_path = 'us', Это действительно удобно с точки зрения приложения, однако я думаю, что это противоречит возможностям создания пула соединений?

Эта проблема

Таким образом, в каждом HTTP-запросе search_path устанавливается для всего запроса, и в запросе может быть много запросов к базе данных. Насколько я понимаю, если бы у меня PGbouncer выполнял пул соединений, я мог бы рискнуть первым запросом в запросе на соединение, для которого search_path установлен в us и следующий запрос к соединению, для которого search_path установлен в de,

Вопрос

Есть ли способ избежать такого поведения с PGbouncer? Или, может быть, альтернативный шаблон, который я мог бы использовать для уменьшения накладных расходов на соединение?

Answer 1

Есть хороший список ограничений PgBouncer в PostgreSQL Wiki. SET в этом списке.

Предполагая, что вы хотите pool_mode = transaction в PgBouncer - который является наиболее разумным режимом, и у вас есть некоторый код приложения, зависящий от настроек сеанса (например, search_path), единственный способ сохранить состояние сеанса - использовать транзакции.

Если ваше приложение просто создает соединение с PgBouncer, запускается SET search_path TO us на нем, а затем запускает несколько SELECT - он не будет работать.

Все это легко доказать - просто подключитесь с помощью psql к PgBouncer и используйте команду SET:

(postgres.example.com:6432) prod=# SET client_min_messages TO debug;
SET
(postgres.example.com:6432) prod=# SHOW client_min_messages ;
 client_min_messages 
---------------------
 notice
(1 row)

Как мы видим, состояние сеанса не сохраняется. Вам нужна транзакция:

(postgres.example.com:6432) prod=# begin;
BEGIN
(postgres.example.com:6432) prod=# SET client_min_messages TO debug;
SET
(postgres.example.com:6432) prod=# SHOW client_min_messages ;
 client_min_messages 
---------------------
 debug
(1 row)

(postgres.example.com:6432) prod=# COMMIT;
COMMIT

Другими словами - чтобы включить пул транзакций, транзакции должны быть без сохранения состояния - точно так же, как и HTTP-запросы, которые содержат всю информацию о состоянии внутри (в виде файлов cookie, идентификаторов сеансов и т. Д.).

Очевидно, в вашем случае это означает внесение изменений в код приложения. Добавление "обертки" вокруг каждого звонка. Это довольно уродливо, и если вам нужно пойти по этому пути, подумайте о том, чтобы переписать приложение с использованием явного кода страны в качестве столбца параметра или базы данных.