# PgBouncer 트랜잭션 풀링 뒤에서 깨지는 것들과 각각의 해결 방법 트랜잭션 풀러 뒤에서 'prepared statement already exists' 오류가 나고, 설정이 요청 사이에 새고, 어드바이저리 락이 아무것도 잠그지 못하는 이유를 PgBouncer 1.24와 PostgreSQL 17에서 재현하고, 각각의 해결 방법과 언제 직접 연결을 써야 하는지에 대한 규칙을 설명합니다. **PgBouncer를 트랜잭션 모드로 Postgres 앞에 두면 수천 개의 짧게 사는 클라이언트가 수십 개의 서버 연결을 나눠 쓸 수 있습니다. 그 대가로 트랜잭션과 트랜잭션 사이에서 "내 연결"이라는 말은 더 이상 의미가 없습니다. `prepared statement "s0" already exists` 같은 오류, 다른 사람의 요청에 나타나는 `SET`, 두 워커가 동시에 쥐고 있는 어드바이저리 락이 모두 여기서 생깁니다. 이 글은 Relyt One의 풀링 연결 문자열과 같은 방식(트랜잭션 모드, Postgres 17)으로 구성한 로컬 PgBouncer 1.24.1과 PostgreSQL 17.11에서 이 문제들을 하나씩 재현하고, 각각의 해결 방법을 보여 준 뒤, 언제 풀러를 건너뛰어야 하는지에 대한 규칙으로 마무리합니다. 아래의 모든 명령과 출력은 그 로컬 환경에서 실행했습니다.** ## 요약 트랜잭션 모드에서 PgBouncer는 트랜잭션 하나 동안 클라이언트에게 서버 연결을 빌려주고 `COMMIT` 또는 `ROLLBACK` 시점에 회수합니다. 명시적 트랜잭션 밖의 문장은 그 자체로 하나의 트랜잭션입니다. 트랜잭션 하나보다 오래 서버 연결에 남는 것은 무엇이든 다음 클라이언트에게 새어 나가거나, 다음 트랜잭션에서는 사라져 있습니다. [PgBouncer의 기능 표](https://www.pgbouncer.org/features.html), 설정 레퍼런스, 그리고 저희 실습에서 확인한 피해 목록은 다음과 같습니다. | 사용하는 것 | 무엇이 잘못되는가 | 해결 방법 | | --------------------------------------------- | -------------------------------------- | ---------------------------------------------------------------------- | | `SET statement_timeout`, `SET search_path`, … | 설정이 서버 연결에 남아 다음에 그 연결을 받는 클라이언트에게 적용됨 | 트랜잭션 안에서 `SET LOCAL` 또는 `set_config(…, true)`; 기본값은 `ALTER ROLE … SET` | | SQL의 `PREPARE` / `EXECUTE` | `already exists` / `does not exist` 오류 | 풀러를 통해서는 SQL 수준 prepared statement를 쓰지 않음 | | 드라이버의 이름 있는 prepared statement | 같은 오류가 드라이버에서 발생 | 드라이버에서 끄거나, `max_prepared_statements`가 설정된 풀러를 사용 | | `pg_advisory_lock()` | 두 클라이언트가 같은 락을 동시에 "보유"할 수 있음 | 트랜잭션 안에서 `pg_advisory_xact_lock()` | | 연결 문자열의 `options=-c …` | 연결 거부 | `ALTER ROLE … SET`, 또는 트랜잭션마다 `SET LOCAL` | | `LISTEN`, `WITH HOLD` 커서, 트랜잭션보다 오래 사는 임시 테이블 | 신뢰할 수 없거나 잘못된 결과 | 직접 연결을 사용 | 이 글의 나머지 부분은 각 행이 실제로 일어나는 모습을 보여 줍니다. ## 트랜잭션 풀링이 연결을 나눠 주는 방식 ![세 클라이언트가 PgBouncer의 서버 연결 하나를 나눠 씁니다. 각 클라이언트는 트랜잭션 하나 동안만 연결을 받고, 연결에 남은 세션 상태는 다음 클라이언트에게 보입니다](/blog/assets/ef3926d7cf5391f6.svg) [PgBouncer의 설정 레퍼런스](https://www.pgbouncer.org/config.html)에 있는 두 가지 사항이 이어지는 내용 대부분을 설명합니다. - **클라이언트 사이에서 아무것도 초기화되지 않습니다.** `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 추적을 꺼 두지만, 풀 크기는 서버 연결 **하나**로 설정합니다. 서버 연결이 하나뿐이면 모든 클라이언트가 같은 연결에 붙으므로, 각 장애가 가끔이 아니라 매번 일어납니다. 풀에 서버 연결이 수십 개 있는 운영 환경에서는 같은 장애가 간헐적으로 일어나며, 바로 그 때문에 디버깅하기 어렵습니다. ```ini ; 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 ``` ```bash 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에 대한 별도의 클라이언트 연결입니다. 웹 앱의 서로 다른 요청이나 서로 다른 서버리스 호출과 같은 방식입니다. 먼저 이 헬퍼를 셸에 붙여 넣으세요. ```bash pooled() { psql -X -h localhost -p 6432 -U app app "$@"; } ``` ## 1. 한 요청의 SET이 다음 요청에 적용된다 클라이언트 A는 앱이 요청을 시작할 때 하듯이 1초짜리 문장 타임아웃을 설정합니다. 클라이언트 B는 다른 사람입니다. ```bash pooled -c "SET statement_timeout = '1s';" pooled -c "SHOW statement_timeout;" -c "SELECT pg_sleep(2);" ``` ```text 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`과 같은 방식으로 새어 나갑니다. ```bash pooled -c "SET search_path = app_private, public;" pooled -Atc "SHOW search_path;" ``` ```text SET app_private, public ``` **해결 방법: 설정의 범위를 트랜잭션으로 한정합니다.** `SET LOCAL`은 현재 트랜잭션이 끝날 때까지 유지되므로, 그 트랜잭션의 모든 문장에 적용되고 서버 연결이 풀로 돌아가기 전에 사라집니다. ```bash pooled -c "BEGIN; SET LOCAL statement_timeout = '1s'; SHOW statement_timeout; COMMIT;" pooled -Atc "SHOW statement_timeout;" ``` ```text 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를 전달하는 일반적인 방법입니다. [관리형 인증 가이드](/blog/ko/managed-auth-getting-started)가 바로 그 패턴을 사용하며, 같은 이유로 풀러 뒤에서도 그대로 동작합니다. 모든 연결에 있어야 하는 설정이라면 대신 역할에 붙이세요. ```sql ALTER ROLE app SET statement_timeout = '5s'; ``` Postgres는 서버 연결이 시작될 때 역할 설정을 적용합니다. PgBouncer가 이미 열어 둔 서버 연결은 이전 값을 유지합니다. 저희 실습에서는 `ALTER ROLE` 직후 풀러를 통해 실행한 `SHOW statement_timeout`이 여전히 `0`을 출력했고, PgBouncer가 새 서버 연결을 연 뒤에야 `5s`를 출력했습니다. ## 2. "prepared statement already exists" SQL 수준 prepared statement는 서버 연결에 속합니다. 같은 이름을 준비하는 두 클라이언트는 조만간 같은 서버 연결에 붙게 됩니다. ```bash pooled -c "PREPARE get_one AS SELECT 1;" pooled -c "PREPARE get_one AS SELECT 1;" ``` ```text 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)가 두 클라이언트에서 이름 있는 쿼리를 실행하는 예입니다. ```js // 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(); ``` ```bash npm install pg@8.23.1 DATABASE_URL=postgres://app:secret@localhost:6432/app node named.js ``` ```text 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를 끄세요. | 드라이버 | 풀러를 통해 연결할 때 | | ------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | node-postgres(`pg`) | 쿼리 설정에 `name`을 넘기지 마세요. 일반 파라미터화 쿼리(`client.query(text, values)`)는 이름 없는 문장을 사용하므로 문제없이 동작합니다. 실습에서 확인했습니다 | | psycopg 3 | `prepare_threshold=None`으로 연결하면 [prepared statement가 비활성화됩니다](https://www.psycopg.org/psycopg3/docs/advanced/prepare.html) | | asyncpg | [FAQ](https://magicstack.github.io/asyncpg/current/faq.html)에서 PgBouncer용으로 권장하는 대로 `connect()` 또는 `create_pool()`에 `statement_cache_size=0`을 넘기세요 | | Prisma | 풀링 URL에 `pgbouncer=true`를 추가하고, 마이그레이션은 직접 연결 URL로 실행하세요. Prisma 가이드는 PgBouncer 1.21 이상에서는 이 플래그를 빼라고 하지만, 이는 `max_prepared_statements`가 0보다 크다는 전제이며 여기서는 그렇지 않습니다([Prisma의 PgBouncer 가이드](https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/databases-connections/pgbouncer) 참고) | 문장을 한 번 준비해서 아끼던 계획 수립 시간은 포기하게 됩니다. 웹 요청이 실행하는 짧은 쿼리에서는 그 비용이 네트워크 왕복에 비해 보통 작습니다. prepared statement에 의존한다면 직접 연결을 사용하세요. ## 3. 아무것도 잠그지 않는 어드바이저리 락 세션 수준 어드바이저리 락은 하나의 워커만 작업을 실행하도록 보장하는 흔한 방법입니다. 이 락은 서버 연결에 속하며 재진입이 가능합니다. 즉 락을 보유한 세션은 같은 락을 다시 얻을 수 있습니다. 트랜잭션 풀러 뒤에서는 이것이 다음과 같이 됩니다. ```bash pooled -c "SELECT pg_advisory_lock(42);" pooled -c "SELECT pg_try_advisory_lock(42) AS got_it;" ``` ```text 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를 재시작해 두 워커가 동시에 실행될 수 있게 하세요. ```bash 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 ``` ```text got_it -------- f (1 row) ``` 작업은 트랜잭션 하나 안에 들어가야 합니다. 그럴 수 없다면 직접 연결을 통해 세션 락을 잡으세요. ## 4. "unsupported startup parameter in options" 연결별 기본값을 설정하는 흔한 방법은 연결 문자열의 `options` 파라미터입니다. PgBouncer는 이를 거부합니다. ```bash psql -X "host=localhost port=6432 user=app dbname=app options=-cstatement_timeout=5s" -c "SELECT 1" ``` ```text 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`을 사용하세요. ## 5. LISTEN, 커서, 임시 테이블 이 세 가지는 트랜잭션보다 오래 사는 세션이 필요하며, PgBouncer의 기능 표는 트랜잭션 풀링에서 이들을 "Never"로 표시합니다. - \*\*`LISTEN`\*\*은 클라이언트가 아니라 서버 연결을 등록하므로, 알림은 그때 그 서버 연결을 쥐고 있는 클라이언트에게 가거나 아무에게도 가지 않습니다. `NOTIFY`로 보내는 것은 동작합니다. - **`WITH HOLD` 커서**는 `COMMIT` 후에도 서버 연결에 열려 있습니다. 다음 `FETCH`는 다른 곳에 붙을 수 있습니다. - 트랜잭션보다 오래 사는 **임시 테이블**은 다른 클라이언트에게 새어 나갑니다. 여기서는 클라이언트 A가 임시 테이블을 만들고, 그것을 만든 적 없는 클라이언트 B가 조회할 수 있습니다. ```bash pooled -c "CREATE TEMP TABLE scratch (id int);" pooled -c "SELECT count(*) FROM scratch;" ``` ```text CREATE TABLE count ------- 0 (1 row) ``` `ON COMMIT DROP`으로 만든 임시 테이블은 트랜잭션이 끝날 때 사라지므로 안전합니다. 나머지 경우에는 직접 연결을 사용하세요. ## Relyt One에서 풀링 연결과 직접 연결 Relyt One은 컴퓨트마다 두 개의 연결 문자열을 제공합니다. **Connect**(연결) 대화 상자에서 **Connection pooling**(연결 풀링) 스위치로 둘 중 하나를 고릅니다. 풀링 연결은 트랜잭션 수준 풀링을 사용하는 PgBouncer를 거치며 최대 10,000개의 동시 클라이언트 연결을 지원합니다. 직접 연결은 위의 모든 기능을 갖춘 일반 Postgres 세션입니다. | 풀링 연결을 사용할 곳 | 직접 연결을 사용할 곳 | | ------------------------------------------------------- | --------------------------------------------------------------- | | 호출마다 연결을 여는 서버리스 함수와 엣지 함수 | 스키마 마이그레이션([어차피 주의가 필요한 이유](/blog/ko/postgres-migration-locks)) | | 짧은 트랜잭션 몇 개를 실행하는 웹 요청 | `LISTEN`, `WITH HOLD` 커서, 세션 임시 테이블 | | 그렇지 않으면 각자 연결을 보유할 많은 앱 인스턴스 | 세션 어드바이저리 락과 오래 실행되는 작업 | | 상태를 트랜잭션 안에 유지하는 코드(`SET LOCAL`, `set_config(…, true)`) | prepared statement에 의존하는 드라이버나 도구 | | | `psql`, GUI 클라이언트와 관리 작업 | 두 연결 문자열은 같은 데이터베이스를 가리키므로 한 앱에서 둘 다 사용할 수 있습니다. 요청 트래픽에는 풀링 연결을, 마이그레이션 단계와 알림을 수신하는 백그라운드 워커에는 직접 연결을 사용하면 됩니다. ## 풀링 연결로 전환하기 전 체크리스트 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인 풀은 운영 환경의 간헐적인 버그를 매번 실행할 때마다 일어나는 오류로 바꿔 줍니다. [무료로 시작하기](/auth/signin?ref=blog-pgbouncer-transaction-pooling)