PgBouncer 트랜잭션 풀링 뒤에서 깨지는 것들과 각각의 해결 방법

PgBouncer를 트랜잭션 모드로 Postgres 앞에 두면 수천 개의 짧게 사는 클라이언트가 수십 개의 서버 연결을 나눠 쓸 수 있습니다. 그 대가로 트랜잭션과 트랜잭션 사이에서 "내 연결"이라는 말은 더 이상 의미가 없습니다. prepared statement "s0" already exists 같은 오류, 다른 사람의 요청에 나타나는 SET, 두 워커가 동시에 쥐고 있는 어드바이저리 락이 모두 여기서 생깁니다. 이 글은 Relyt One의 풀링 연결 문자열과 같은 방식(트랜잭션 모드, Postgres 17)으로 구성한 로컬 PgBouncer 1.24.1과 PostgreSQL 17.11에서 이 문제들을 하나씩 재현하고, 각각의 해결 방법을 보여 준 뒤, 언제 풀러를 건너뛰어야 하는지에 대한 규칙으로 마무리합니다. 아래의 모든 명령과 출력은 그 로컬 환경에서 실행했습니다.

트랜잭션 모드에서 PgBouncer는 트랜잭션 하나 동안 클라이언트에게 서버 연결을 빌려주고 COMMIT 또는 ROLLBACK 시점에 회수합니다. 명시적 트랜잭션 밖의 문장은 그 자체로 하나의 트랜잭션입니다. 트랜잭션 하나보다 오래 서버 연결에 남는 것은 무엇이든 다음 클라이언트에게 새어 나가거나, 다음 트랜잭션에서는 사라져 있습니다. PgBouncer의 기능 표, 설정 레퍼런스, 그리고 저희 실습에서 확인한 피해 목록은 다음과 같습니다.

이 글의 나머지 부분은 각 행이 실제로 일어나는 모습을 보여 줍니다.

PgBouncer의 설정 레퍼런스에 있는 두 가지 사항이 이어지는 내용 대부분을 설명합니다.

• 클라이언트 사이에서 아무것도 초기화되지 않습니다. server_reset_query(기본값 DISCARD ALL)는 트랜잭션 풀링에서 "사용되지 않습니다". 트랜잭션이 서버 연결에 무엇을 남기든 다음 클라이언트가 물려받습니다.
• 일부 설정만 추적되고 나머지는 추적되지 않습니다. PgBouncer는 Postgres가 클라이언트에게 다시 보고하는 짧은 파라미터 목록(1.24 기준: client_encoding, DateStyle, IntervalStyle, TimeZone, standard_conforming_strings, application_name)에 대해 클라이언트별 값을 기억하고, 그 클라이언트에게 서버 연결을 넘겨줄 때 복원합니다. 기능 표는 그 밖의 모든 SET을 트랜잭션 풀링과 호환되지 않는다고 표시합니다. 변경 사항이 서버 연결에 남아 다음 클라이언트에게 넘어가기 때문입니다.

아래의 모든 내용은 Docker 위의 로컬 Postgres 17.11과 PgBouncer 1.24.1에서 실행합니다. PgBouncer 이미지는 커뮤니티가 관리하는 edoburu/pgbouncer입니다. 풀러 설정은 Relyt One의 풀러처럼 트랜잭션 모드를 쓰고 prepared statement 추적을 꺼 두지만, 풀 크기는 서버 연결 하나로 설정합니다. 서버 연결이 하나뿐이면 모든 클라이언트가 같은 연결에 붙으므로, 각 장애가 가끔이 아니라 매번 일어납니다. 풀에 서버 연결이 수십 개 있는 운영 환경에서는 같은 장애가 간헐적으로 일어나며, 바로 그 때문에 디버깅하기 어렵습니다.

; pgbouncer.ini
[databases]
* = host=pg port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 1
max_prepared_statements = 0
ignore_startup_parameters = extra_float_digits

echo '"app" "secret"' > userlist.txt
docker network create pgblab
docker run -d --name pg --network pgblab \
  -e POSTGRES_USER=app -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=app \
  postgres:17.11-alpine
docker run -d --name pgbouncer --network pgblab -p 6432:6432 \
  -v "$PWD/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro" \
  -v "$PWD/userlist.txt:/etc/pgbouncer/userlist.txt:ro" \
  edoburu/pgbouncer:v1.24.1-p1
export PGPASSWORD=secret

아래의 각 psql -c는 PgBouncer에 대한 별도의 클라이언트 연결입니다. 웹 앱의 서로 다른 요청이나 서로 다른 서버리스 호출과 같은 방식입니다. 먼저 이 헬퍼를 셸에 붙여 넣으세요.

pooled() { psql -X -h localhost -p 6432 -U app app "$@"; }

클라이언트 A는 앱이 요청을 시작할 때 하듯이 1초짜리 문장 타임아웃을 설정합니다. 클라이언트 B는 다른 사람입니다.

pooled -c "SET statement_timeout = '1s';"
pooled -c "SHOW statement_timeout;" -c "SELECT pg_sleep(2);"

SET
 statement_timeout
-------------------
 1s
(1 row)

ERROR:  canceling statement due to statement timeout

클라이언트 B는 타임아웃을 설정한 적이 없는데도 2초짜리 쿼리가 취소되었습니다. SET은 그 자체로 하나의 트랜잭션에서 실행되었고, 서버 연결은 statement_timeout = 1s를 그대로 지닌 채 풀로 돌아갔으며, B가 그 연결을 받았습니다. 풀이 더 크면 반대 상황도 일어납니다. 클라이언트 A의 다음 문장이 타임아웃이 설정된 적 없는 다른 서버 연결에 붙는 것입니다. 운영 환경에서는 두 가지를 모두 보게 됩니다. 가끔 적용되지 않는 설정, 그리고 요청한 적 없는 요청에서 발생하는 타임아웃이나 잘못된 search_path입니다.

추적되는 파라미터는 다르게 동작합니다. 같은 실험을 SET TimeZone = 'Asia/Tokyo'로 하고 두 번째 클라이언트에서 SHOW TimeZone을 실행하면 UTC가 출력됩니다. PgBouncer가 각 클라이언트 자신의 값을 복원하기 때문입니다. search_path는 그 목록에 없으며, statement_timeout과 같은 방식으로 새어 나갑니다.

pooled -c "SET search_path = app_private, public;"
pooled -Atc "SHOW search_path;"

SET
app_private, public

해결 방법: 설정의 범위를 트랜잭션으로 한정합니다. SET LOCAL은 현재 트랜잭션이 끝날 때까지 유지되므로, 그 트랜잭션의 모든 문장에 적용되고 서버 연결이 풀로 돌아가기 전에 사라집니다.

pooled -c "BEGIN; SET LOCAL statement_timeout = '1s'; SHOW statement_timeout; COMMIT;"
pooled -Atc "SHOW statement_timeout;"

BEGIN
SET
 statement_timeout
-------------------
 1s
(1 row)

COMMIT
0

(따라 하고 있다면 위에서 샌 설정을 정리하기 위해 먼저 pooled -c "RESET statement_timeout;" -c "RESET search_path;"를 실행하세요.) 애플리케이션 코드에서는 set_config(name, value, true)가 SET LOCAL과 같은 일을 하면서 파라미터를 받을 수 있으므로, 행 수준 보안 정책에 사용자 ID를 전달하는 일반적인 방법입니다. 관리형 인증 가이드가 바로 그 패턴을 사용하며, 같은 이유로 풀러 뒤에서도 그대로 동작합니다.

모든 연결에 있어야 하는 설정이라면 대신 역할에 붙이세요.

ALTER ROLE app SET statement_timeout = '5s';

Postgres는 서버 연결이 시작될 때 역할 설정을 적용합니다. PgBouncer가 이미 열어 둔 서버 연결은 이전 값을 유지합니다. 저희 실습에서는 ALTER ROLE 직후 풀러를 통해 실행한 SHOW statement_timeout이 여전히 0을 출력했고, PgBouncer가 새 서버 연결을 연 뒤에야 5s를 출력했습니다.

SQL 수준 prepared statement는 서버 연결에 속합니다. 같은 이름을 준비하는 두 클라이언트는 조만간 같은 서버 연결에 붙게 됩니다.

pooled -c "PREPARE get_one AS SELECT 1;"
pooled -c "PREPARE get_one AS SELECT 1;"

PREPARE
ERROR:  prepared statement "get_one" already exists

PgBouncer는 SQL 수준 PREPARE를 절대 추적하지 않습니다. 설정 레퍼런스에 따르면 설정과 관계없이 PREPARE, EXECUTE, DEALLOCATE를 Postgres로 그대로 전달합니다. 트랜잭션 풀러를 통해서는 이것들을 쓰지 마세요. (pooled -c "DEALLOCATE ALL;"로 실습 환경을 정리할 수 있습니다.)

대부분의 사람은 이 오류를 드라이버를 통해 만납니다. 많은 드라이버가 기본적으로, 또는 쿼리가 반복되면 프로토콜 수준 prepared statement를 사용합니다. 쿼리를 이름과 함께 한 번 보낸 뒤 이름으로 실행하는 방식입니다. 다음은 node-postgres(pg 8.23)가 두 클라이언트에서 이름 있는 쿼리를 실행하는 예입니다.

// named.js
const { Client } = require('pg');

async function main() {
  const url = process.env.DATABASE_URL;
  const a = new Client({ connectionString: url });
  const b = new Client({ connectionString: url });
  await a.connect();
  await b.connect();
  const query = { name: 'get-account', text: 'SELECT $1::int AS id' };
  try {
    console.log('a:', (await a.query({ ...query, values: [1] })).rows);
    console.log('b:', (await b.query({ ...query, values: [2] })).rows);
  } catch (err) {
    console.log('error:', err.message);
  } finally {
    await a.end();
    await b.end();
  }
}

main();

npm install pg@8.23.1
DATABASE_URL=postgres://app:secret@localhost:6432/app node named.js

a: [ { id: 1 } ]
error: prepared statement "get-account" already exists

그 반대 상황을 보려면 서버 연결 두 개짜리 풀이 필요합니다. 클라이언트 A가 이름 있는 쿼리를 실행하고, 다른 클라이언트가 트랜잭션을 열어 A가 방금 사용한 서버 연결을 가져가고, A가 쿼리를 다시 실행합니다. 드라이버는 이미 get-account를 준비했다고 기억하므로 "실행" 메시지만 보내고, 다른 서버 연결에 붙습니다. 결과는 prepared statement "get-account" does not exist입니다. 저희는 실습에서 두 오류를 모두 재현했습니다.

PgBouncer 1.21은 프로토콜 수준의 경우에 대한 해결책을 추가했습니다. max_prepared_statements를 0보다 크게 설정하면 각 클라이언트가 준비하는 문장을 추적하고, 클라이언트가 받는 서버 연결이 어느 것이든 그 연결에서 문장을 준비합니다. max_prepared_statements = 200으로 두 실험을 다시 실행하자 두 오류가 모두 사라졌지만, 위의 SQL 수준 PREPARE는 여전히 실패했습니다.

Relyt One의 풀링 연결 문자열에서는 이 추적이 꺼져 있으므로(max_prepared_statements = 0), 풀러를 통해 연결할 때는 드라이버에서 이름 있는 prepared statement를 끄세요.

문장을 한 번 준비해서 아끼던 계획 수립 시간은 포기하게 됩니다. 웹 요청이 실행하는 짧은 쿼리에서는 그 비용이 네트워크 왕복에 비해 보통 작습니다. prepared statement에 의존한다면 직접 연결을 사용하세요.

세션 수준 어드바이저리 락은 하나의 워커만 작업을 실행하도록 보장하는 흔한 방법입니다. 이 락은 서버 연결에 속하며 재진입이 가능합니다. 즉 락을 보유한 세션은 같은 락을 다시 얻을 수 있습니다. 트랜잭션 풀러 뒤에서는 이것이 다음과 같이 됩니다.

pooled -c "SELECT pg_advisory_lock(42);"
pooled -c "SELECT pg_try_advisory_lock(42) AS got_it;"

 pg_advisory_lock
------------------

(1 row)

 got_it
--------
 t
(1 row)

워커 A가 락 42를 얻었습니다. 워커 B가 같은 락을 요청했고, Postgres는 허락했습니다. 두 요청이 같은 서버 연결을 통해 들어왔기 때문입니다. 이제 두 워커 모두 작업을 독점하고 있다고 믿습니다. 풀이 더 크면 다른 방식으로, 하지만 똑같이 나쁘게 실패합니다. 워커 A의 잠금 해제가 다른 서버 연결에 붙고, Postgres는 그 락을 소유하지 않았다는 경고로 응답하며, A가 끝난 뒤에도 락은 원래 서버 연결에 계속 잡혀 있습니다. (pooled -c "SELECT pg_advisory_unlock_all();"로 실습 환경의 락을 해제할 수 있습니다.)

해결 방법: 락을 트랜잭션 단위로 잡습니다. pg_advisory_xact_lock()과 pg_try_advisory_xact_lock()은 COMMIT 또는 ROLLBACK 시점에 락을 해제하므로, 락과 작업이 같은 트랜잭션과 같은 서버 연결을 공유합니다. 두 번째 워커를 막는 모습을 보려면 실습 환경에서 default_pool_size = 2로 설정하고 PgBouncer를 재시작해 두 워커가 동시에 실행될 수 있게 하세요.

docker restart pgbouncer
pooled -c "BEGIN; SELECT pg_advisory_xact_lock(42); SELECT pg_sleep(3); COMMIT;" > /dev/null &
sleep 1
pooled -c "SELECT pg_try_advisory_xact_lock(42) AS got_it;"
wait

 got_it
--------
 f
(1 row)

작업은 트랜잭션 하나 안에 들어가야 합니다. 그럴 수 없다면 직접 연결을 통해 세션 락을 잡으세요.

연결별 기본값을 설정하는 흔한 방법은 연결 문자열의 options 파라미터입니다. PgBouncer는 이를 거부합니다.

psql -X "host=localhost port=6432 user=app dbname=app options=-cstatement_timeout=5s" -c "SELECT 1"

psql: error: connection to server at "localhost" (::1), port 6432 failed: FATAL:  unsupported startup parameter in options: statement_timeout

PgBouncer는 추적할 수 있는 시작 파라미터만 받아들입니다. 알 수 없는 파라미터를 무시하게 할 수도 있지만(ignore_startup_parameters), 그러면 설정이 조용히 버려지므로 더 나쁩니다. Relyt One의 풀링 엔드포인트도 options를 전달하지 않습니다. 기본값에는 1절의 ALTER ROLE … SET을, 트랜잭션 하나에는 SET LOCAL을 사용하세요.

이 세 가지는 트랜잭션보다 오래 사는 세션이 필요하며, PgBouncer의 기능 표는 트랜잭션 풀링에서 이들을 "Never"로 표시합니다.

• **LISTEN**은 클라이언트가 아니라 서버 연결을 등록하므로, 알림은 그때 그 서버 연결을 쥐고 있는 클라이언트에게 가거나 아무에게도 가지 않습니다. NOTIFY로 보내는 것은 동작합니다.
• WITH HOLD 커서는 COMMIT 후에도 서버 연결에 열려 있습니다. 다음 FETCH는 다른 곳에 붙을 수 있습니다.
• 트랜잭션보다 오래 사는 임시 테이블은 다른 클라이언트에게 새어 나갑니다. 여기서는 클라이언트 A가 임시 테이블을 만들고, 그것을 만든 적 없는 클라이언트 B가 조회할 수 있습니다.

pooled -c "CREATE TEMP TABLE scratch (id int);"
pooled -c "SELECT count(*) FROM scratch;"

CREATE TABLE
 count
-------
     0
(1 row)

ON COMMIT DROP으로 만든 임시 테이블은 트랜잭션이 끝날 때 사라지므로 안전합니다. 나머지 경우에는 직접 연결을 사용하세요.

Relyt One은 컴퓨트마다 두 개의 연결 문자열을 제공합니다. Connect(연결) 대화 상자에서 Connection pooling(연결 풀링) 스위치로 둘 중 하나를 고릅니다. 풀링 연결은 트랜잭션 수준 풀링을 사용하는 PgBouncer를 거치며 최대 10,000개의 동시 클라이언트 연결을 지원합니다. 직접 연결은 위의 모든 기능을 갖춘 일반 Postgres 세션입니다.

두 연결 문자열은 같은 데이터베이스를 가리키므로 한 앱에서 둘 다 사용할 수 있습니다. 요청 트래픽에는 풀링 연결을, 마이그레이션 단계와 알림을 수신하는 백그라운드 워커에는 직접 연결을 사용하면 됩니다.

1. 코드에서 모든 일반 SET을 찾아(BEGIN … COMMIT 안의 SET도 커밋 후까지 남습니다) 트랜잭션 안의 SET LOCAL 또는 set_config(…, true)로 바꾸세요.
2. 연결별 기본값을 options=에서 ALTER ROLE … SET으로 옮기세요.
3. 드라이버에서 이름 있는 prepared statement를 끄세요(2절의 표).
4. pg_advisory_lock()을 pg_advisory_xact_lock()으로 바꾸거나, 그 작업을 직접 연결로 옮기세요.
5. LISTEN, WITH HOLD 커서, 세션 임시 테이블을 직접 연결로 옮기세요.
6. 마이그레이션은 직접 연결에서 계속 실행하세요.
7. 작은 풀로 테스트하세요. 실습에서 보았듯이 크기가 1인 풀은 운영 환경의 간헐적인 버그를 매번 실행할 때마다 일어나는 오류로 바꿔 줍니다.

무료로 시작하기