PostgreSQL 데이터베이스 작업 중 갑작스럽게 나타나는 “terminating connection due to idle-in-transaction timeout” 에러는 많은 개발자들을 당황시키는 문제입니다. 특히 중요한 작업을 진행하다가 예상치 못하게 연결이 끊어져 버리면 답답함은 이루 말할 수 없죠.
이런 경험이 있으시다면 안심하세요. 이 에러는 PostgreSQL의 안전장치 중 하나이며, 원인을 정확히 파악하고 적절한 해결방법을 적용하면 충분히 해결할 수 있는 문제입니다. 오늘은 이 에러가 발생하는 정확한 원인부터 단계별 해결방법까지 상세히 알아보겠습니다.
1. 에러 발생 원인 이해하기
idle-in-transaction timeout이란?
PostgreSQL에서 ‘idle-in-transaction timeout’은 트랜잭션이 시작된 상태에서 클라이언트가 아무런 쿼리를 보내지 않고 대기하는 시간이 지정된 제한 시간을 초과했을 때 발생하는 타임아웃입니다.
이 기능은 PostgreSQL 9.6에서 처음 도입되었으며, 기본값은 0(비활성화)으로 설정되어 있습니다.
언제 이 에러가 발생하나요?
다음과 같은 상황에서 주로 발생합니다:
- 트랜잭션 시작 후 장시간 대기: BEGIN 명령 후 다음 쿼리까지 시간이 오래 걸리는 경우
- 애플리케이션 로직 문제: 트랜잭션 중간에 HTTP 호출이나 무거운 연산을 수행하는 경우
- 개발 도구 사용: DBeaver 같은 도구에서 쿼리 실행 중 사용자가 오랫동안 응답하지 않는 경우
- 연결 풀링 문제: 애플리케이션에서 트랜잭션을 적절히 종료하지 않는 경우
2. 현재 설정 확인하기
문제를 해결하기 전에 먼저 현재 데이터베이스의 타임아웃 설정을 확인해야 합니다.
-- 현재 세션의 타임아웃 설정 확인
SHOW idle_in_transaction_session_timeout;
-- 전체 설정 확인 (관리자 권한 필요)
SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name LIKE '%idle%';
결과 예시:
idle_in_transaction_session_timeout
-------------------------------------
5min
(1 row)
만약 값이 0이 아니라면 해당 시간(밀리초 단위) 후에 타임아웃이 발생합니다.
3. 즉시 해결방법 (임시 조치)
현재 세션에서만 비활성화
가장 빠른 해결방법은 현재 세션에서 타임아웃을 비활성화하는 것입니다:
-- 현재 세션에서 타임아웃 비활성화
SET SESSION idle_in_transaction_session_timeout = 0;
-- 또는 시간을 늘리기 (30분으로 설정)
SET SESSION idle_in_transaction_session_timeout = '30min';
주의사항: 이 방법은 현재 세션에서만 적용되며, 새로운 연결 시에는 다시 기본값으로 돌아갑니다.
psql에서 자동 설정하기
psql 클라이언트를 사용할 때는 연결 옵션으로 타임아웃을 설정할 수 있습니다:
# 연결 시 타임아웃 설정
psql 'options=-cidle_in_transaction_session_timeout=1800000 host=localhost user=postgres dbname=mydb'
# 또는 환경변수 사용
export PGOPTIONS="-c idle_in_transaction_session_timeout=30min"
psql -h localhost -U postgres -d mydb
4. 근본적인 해결방법
데이터베이스 레벨에서 설정 변경
특정 데이터베이스에 대해서만 타임아웃을 조정할 수 있습니다:
-- 특정 데이터베이스의 타임아웃 설정 (30분으로 설정)
ALTER DATABASE mydatabase SET idle_in_transaction_session_timeout = '30min';
-- 타임아웃 비활성화
ALTER DATABASE mydatabase SET idle_in_transaction_session_timeout = 0;
-- 설정 제거 (기본값으로 복원)
ALTER DATABASE mydatabase RESET idle_in_transaction_session_timeout;
사용자/역할 레벨에서 설정 변경
특정 사용자나 역할에 대해서만 설정을 변경할 수도 있습니다:
-- 특정 사용자에 대한 타임아웃 설정
ALTER ROLE myuser SET idle_in_transaction_session_timeout = '1hour';
-- 개발자 역할에 대한 설정
ALTER ROLE developer SET idle_in_transaction_session_timeout = 0;
-- 운영 환경 사용자에게는 보수적인 설정
ALTER ROLE production_user SET idle_in_transaction_session_timeout = '10min';
postgresql.conf 파일 수정 (전역 설정)
주의: postgresql.conf에서 이 설정을 변경하는 것은 모든 세션에 영향을 주므로 권장되지 않습니다.
# postgresql.conf
idle_in_transaction_session_timeout = 600000 # 10분 (밀리초 단위)
설정 후 PostgreSQL 재시작:
sudo systemctl reload postgresql
5. 애플리케이션 코드 개선방법
트랜잭션 최적화
타임아웃 에러의 근본 원인은 대부분 잘못된 트랜잭션 관리에 있습니다. 다음과 같이 코드를 개선해보세요:
잘못된 예시:
import psycopg2
import time
import requests
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
cur.execute("BEGIN;")
cur.execute("INSERT INTO users (name) VALUES ('John');")
# 문제: 트랜잭션 중간에 외부 API 호출
time.sleep(10) # 또는 긴 처리 시간
response = requests.get("https://api.example.com/data")
cur.execute("UPDATE users SET status = 'active' WHERE name = 'John';")
conn.commit()
개선된 예시:
import psycopg2
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
# 외부 처리를 트랜잭션 밖에서 수행
external_data = requests.get("https://api.example.com/data").json()
# 트랜잭션을 짧게 유지
try:
cur.execute("BEGIN;")
cur.execute("INSERT INTO users (name, status) VALUES (%s, %s);",
('John', 'active'))
cur.execute("INSERT INTO user_data (user_id, data) VALUES (%s, %s);",
(user_id, external_data))
conn.commit()
except Exception as e:
conn.rollback()
raise
연결 풀 설정 최적화
연결 풀을 사용하는 경우 적절한 타임아웃 설정이 중요합니다:
# SQLAlchemy 예시
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
engine = create_engine(
'postgresql://user:password@localhost/dbname',
poolclass=QueuePool,
pool_size=10,
max_overflow=20,
pool_pre_ping=True, # 연결 상태 확인
connect_args={
"options": "-c idle_in_transaction_session_timeout=300000" # 5분
}
)
6. Idle 트랜잭션 모니터링, 오래된 idle 트랜젝션 정리하는 방법
현재 활성 트랜잭션 확인
다음 쿼리로 현재 시스템의 idle 트랜잭션을 모니터링할 수 있습니다:
-- 현재 idle in transaction 상태인 세션 확인
SELECT
pid,
usename,
application_name,
client_addr,
state,
state_change,
now() - state_change AS idle_duration,
query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;
자동 정리 스크립트
오래된 idle 트랜잭션을 자동으로 정리하는 스크립트를 설정할 수 있습니다:
-- 5분 이상 idle인 트랜잭션 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - state_change > interval '5 minutes'
AND pid <> pg_backend_pid();
7. 권장 설정값
상황별 권장 설정값을 정리해보겠습니다:
| 환경 | 권장 설정 | 설명 |
|---|---|---|
| 개발 환경 | 0 (비활성화) |
개발자가 자유롭게 디버깅할 수 있도록 |
| 테스트 환경 | 30min |
테스트 시간을 고려한 충분한 여유 |
| 운영 환경 (웹 애플리케이션) | 5-10min |
웹 요청 처리에 적합한 시간 |
| 운영 환경 (배치 작업) | 1-2hour |
대용량 처리를 고려한 시간 |
| 대화형 세션 (psql, DBeaver) | 30min-1hour |
사용자 작업 시간 고려 |
8. 추가 고려사항
다른 타임아웃 설정과의 관계
PostgreSQL에는 여러 타임아웃 설정이 있으며, 이들 간의 관계를 이해하는 것이 중요합니다:
- statement_timeout: 단일 쿼리 실행 시간 제한
- lock_timeout: 락 대기 시간 제한
- idle_session_timeout: 일반 idle 연결 시간 제한
- transaction_timeout: 전체 트랜잭션 시간 제한 (PostgreSQL 17+)
성능에 미치는 영향
idle 트랜잭션이 오래 지속되면 VACUUM 작업에 영향을 주어 테이블 블로팅을 유발할 수 있습니다. 따라서 적절한 타임아웃 설정은 데이터베이스 성능 유지에 중요합니다.
PostgreSQL의 ‘idle-in-transaction timeout’ 에러는 처음에는 당황스럽지만, 원인을 이해하고 적절한 해결책을 적용하면 충분히 해결할 수 있는 문제입니다. 핵심은 타임아웃 설정 조정과 애플리케이션 코드 개선을 함께 진행하는 것입니다. 단순히 타임아웃 값만 늘리는 것보다는 트랜잭션을 효율적으로 관리하고, 상황에 맞는 적절한 설정을 적용하는 것이 중요합니다.