애플리케이션 개발자를 위한 PostgreSQL 아키텍처 고려사항: 파트 1

이 글은 AWS Database 블로그의 PostgreSQL architecture considerations for application developers: Part 1 by Peter Celentano and Tracy Jenkins의 한국어 번역입니다.

클라우드 아키텍처에서 애플리케이션 계층이 외부와 소통하는 핵심 역할을 함에도 불구하고, 우리는 종종 사용 중인 데이터베이스에 맞춰 애플리케이션을 최적화하는 방법을 간과하고는 합니다. 관계형 데이터베이스를 사용할 때는 단순히 스키마 설계에만 집중할 것이 아니라, 데이터베이스가 저장 시스템과 어떻게 상호작용하는지 이해하는 것이 중요합니다. 이를 통해 애플리케이션의 관리성, 확장성, 그리고 성능을 개선할 수 있습니다. 이번 블로그는 시리즈의 첫 번째 파트로, 먼저 PostgreSQL의 주요 용어들을 소개한 후, Amazon Aurora PostgreSQL 호환 버전및 PostgreSQL 용 Amazon RDS 사용 시 중요한 개념인 autocommit, autovacuum, 그리고 트랜잭션 유휴 상태에 대해 자세히 살펴보겠습니다.

PostgreSQL 파라미터 변경: 무엇을, 언제, 그리고 왜 해야하는가?

데이터베이스와 PostgreSQL에서 파라미터는 기본 속성을 정의하는 데 사용됩니다. PostgreSQL은 새로운 데이터베이스를 생성할 때 기본 파라미터가 설정되어 있으며, 많은 시스템에서는 기본 파라미터로도 좋은 성능을 제공하여 튜닝이 필요하지 않을 수 있습니다. 그러나 시스템이 성장하고 확장되며 더 많은 부하가 걸리게 되면 최적의 성능을 위해 일부 파라미터를 조정해야 할 수도 있습니다.

자체적으로 관리하는 데이터베이스를 사용하는지 또는 AWS 관리형 데이터베이스를 사용하는지에 따라 각각 다른 방법으로 파라미터 값을 변경해야 합니다. 자체적으로 관리하는 데이터베이스를 사용하는 경우, 파라미터 변경은 postgresql.conf 파일에서 이루어집니다. AWS 관리형 데이터베이스에서는 postgresql.conf 파일에 대한 접근이 제한되므로, AWS Management Console, AWS Command Line Interface (AWS CLI), SDK 또는 AWS CloudFormation을 통해 데이터베이스나 클러스터 파라미터 그룹을 변경해야 합니다.

postgresql.conf (자체 관리형)

자체적으로 관리하는 데이터베이스의 경우, PostgreSQL 클러스터 전반에 걸쳐 파라미터를 전역적으로 설정하려면 PostgreSQL 데이터 디렉토리 내의 postgresql.conf 파일에서 변경을 수행합니다. 자세한 내용은 PostgreSQL 커뮤니티 문서의 파라미터 설정 섹션을 참조하세요.

RDS DB 파라미터 그룹 (AWS 관리형)

Amazon RDS 클러스터 수준 및 데이터베이스 수준의 파라미터 그룹은 인스턴스 클래스와 크기에 따라 기본 설정이 다릅니다. 더 나은 성능을 위해 다른 값이 필요하다면, 콘솔, AWS CLI, SDK 또는 AWS CloudFormation을 통해 새로운 파라미터 그룹을 생성할 수 있습니다. 자세한 내용은 DB 파라미터 그룹 생성에 관한 문서를 참조하세요.

세션 수준 (자체 관리형 또는 AWS 관리형)

많은 PostgreSQL 매개변수는 세션 수준에서 변경할 수 있습니다. 이는 이러한 매개변수가 전체적으로가 아닌 특정 작업 부하 내의 특정 쿼리에만 필요하기 때문입니다. 이러한 세션 수준의 매개변수는 SET 명령어를 사용하여 변경할 수 있습니다. 이 명령어는 현재 세션에만 영향을 미치며 다른 세션에는 영향을 주지 않습니다. 자세한 내용은 PostgreSQL 커뮤니티 문서의 SET 섹션을 참조하세요.

PostgreSQL 의 핵심 개념

이 섹션에서는 PostgreSQL 데이터베이스 운영에 필수적인 핵심 PostgreSQL 개념을 논의합니다.

Transaction

PostgreSQL에서 트랜잭션은 하나의 작업으로 실행되는 일련의 SQL 문입니다. 이 트랜잭션 모델은 트랜잭션 내 모든 문이 성공적으로 데이터베이스에 커밋되거나, 문이 실패하거나 오류가 발생할 경우 롤백되도록 보장합니다. PostgreSQL은 ACID 특성을 준수하며, 이는 데이터베이스 작업에서 원자성, 일관성, 고립성, 지속성을 항상 유지하도록 하는 특성입니다:

• 원자성 (Atomicity) – 데이터베이스 원자성은 트랜잭션이 완료될 때까지 다른 트랜잭션에 보이지 않게하며, 완료되면 모든 변경 사항이 동시에 하나의 단위로 보이게 합니다.
• 일관성 (Consistency) – 일관성은 데이터에 커밋된 변경 사항이 있으면 새로운 트랜잭션이 그 변경 사항을 볼 수 있도록 보장하며, 서버 충돌 후에도 데이터가 오류 없이 복구될 수 있도록 합니다.
• 고립성 (Isolation) – PostgreSQL은 다양한 고립 수준을 제공하여 다른 동시 트랜잭션에 대한 데이터 변경의 가시성을 제어합니다. 기본적으로 ‘Read Committed’ 고립 수준을 사용하여, 다른 트랜잭션에 의해 커밋된 후에만 변경 사항을 볼 수 있게 합니다.
• 지속성 (Durability) – 지속성은 데이터베이스가 모든 커밋된 변경 사항을 추적하여 비정상적인 취소가 발생하더라도 데이터베이스가 원래 상태로 롤백하거나 트랜잭션 로그를 재생하여 계속할 수 있도록 보장합니다.

트랜잭션에 대한 더 자세한 정보는 Transactions를 참조하세요. ACID 준수에 대한 더 많은 정보는 PostgreSQL 커뮤니티 문서의 용어집을 참조하세요.

Locking

PostgreSQL는 여러 트랜잭션이 동시에 데이터에 접근할 때 충돌을 방지하기 위해 locking 메커니즘을 사용합니다. PostgreSQL은 두 가지 종류의 locking을 제공합니다:

• 공유 Locks – 여러 트랜잭션이 동시에 특정 데이터 객체를 읽을 수 있도록 허용합니다.
• 배타적 Locks – Lock이 해제될 때까지 다른 트랜잭션이 데이터 객체에 접근하지 못하도록 합니다.

PostgreSQL은 특정 순서로 락을 획득하고 해제하는 locking 프로토콜을 사용하여 데드락(두 개 이상의 트랜잭션이 서로의 락 해제를 기다리며 교착 상태에 빠지는 상황)을 방지합니다. 또한, PostgreSQL은 개별 행에 대한 락을 지원하여 전체 테이블 대신 개별 행에 대한 세밀한 동시 접근 제어를 제공합니다. 마지막으로, PostgreSQL은 많은 수의 락을 하나의 상위 레벨 락으로 대체하여 전체 락 오버헤드를 줄이는 락 에스컬레이션을 구현합니다.

Locking 에 대한 더 자세한 정보는 Explicit Locking을 참조하세요.

VACUUM

PostgreSQL은 데이터를 튜플이라는 구조로 저장합니다. 튜플이 논리적으로 업데이트되거나 삭제되면, 보이지 않는 버전이 여전히 데이터베이스에 남아 있습니다. 이는 삭제나 업데이트 명령과 동시에 실행되는 트랜잭션이 시작 시점의 데이터베이스 상태를 유지할 수 있도록 하기 위함입니다. PostgreSQL은 VACUUM 프로세스를 통해 오래된 보이지 않는 튜플 버전이 차지하는 공간을 해제하고 저장소를 회수합니다. 업데이트되거나 삭제된 행은 나중에 VACUUM 프로세스에 의해 정리될 데드 튜플로 표시됩니다. 이러한 튜플들은 즉시 정리되지 않으며, 여러 트랜잭션이 동일한 튜플을 동시에 작업할 수 있도록 하여 다중 버전 동시성 제어 (Multi-Version Concurrenty Control, MVCC)를 통해 정확성을 보장합니다. 예를 들어, 원본 버전을 즉시 제거하면 동시에 실행 중인 트랜잭션이 정확하게 롤백되지 않을 수 있습니다. 단순히 VACUUM을 실행하는 것만으로도 튜플을 제거할 수 있지만, VACUUM 명령어에는 이해해야 할 여러가지 변형이 있습니다. 이 섹션에서는 이러한 변형에 대해 논의합니다.

VACUUM ANALYZE

이 명령은 데드 튜플을 제거하고 해당 테이블의 내용에 대한 데이터베이스 통계를 수집하여 pg_statistic 시스템 카탈로그에 저장합니다. 이러한 데이터는 PostgreSQL 쿼리 최적화기가 쿼리 실행 시 가장 효율적인 실행 계획을 결정하는 데 사용됩니다.

VACUUM FREEZE

이 옵션은 데드 튜플을 정리할 뿐만 아니라, 튜플을 적극적으로 고정(freeze)하여 VACUUM이 오래된 행을 고정할지 삭제할지를 결정하는 기준 연령(Transaction ID, XID)을 설정합니다. 이를 통해 VACUUM 과정에서 오래된 활성 튜플의 데이터 손실과 트랜잭션 랩어라운드로 인한 손상을 방지할 수 있습니다. 트랜잭션은 고유한 XID로 추적되며, 이 숫자는 한정되어 있기 때문에 소진될 수 있습니다. 제대로 관리하지 않으면 운영 중인 데이터베이스의 XID 번호가 등록 가능한 최대치에 도달할 수 있고, 이 경우 랩어라운드가 발생하여 과거의 XID 번호가 현재로 돌아와 재사용될 수 있습니다. 이러한 랩어라운드 손상은 데이터 무결성을 보호하기 위해 데이터베이스를 종료시킬 수 있습니다.

VACUUM FULL

이 옵션은 테이블 (또는 데이터베이스)의 내용을 완전히 다시 작성하여 데드 튜플을 제거합니다. 삭제되거나 업데이트된 튜플로부터 디스크 공간을 회수하고 데이터를 물리적으로 재배열하여 더 압축된 저장소와 향상된 쿼리 성능을 제공합니다. 이 옵션은 테이블에 대한 독점적인 락을 생성하여 작업이 완료될 때까지 모든 다른 접근을 차단하므로 일반적인 운영 환경에서는 사용하지 않는 것이 좋습니다.

PostgreSQL 의 타임아웃 관련 파라미터

타임아웃 설정은 필수적입니다. 벤치마크 테스트 환경에서는 워크로드가 어떻게 작동할지 예측할 수 있지만, 실제 운영 환경에서는 예상치 못하게 작동할 수 있습니다. 적절한 타임아웃 설정은 워크로드 실행 중 발생할 수 있는 이상 현상으로부터 클러스터를 보호하는 안전장치 역할을 합니다. 이러한 설정은 데이터베이스의 수명 주기 동안 계속해서 조정될 수 있습니다. 연결 및 요청 타임아웃 설정을 갖추는 것이 좋은 방법입니다. PostgreSQL용 RDS는 유용한 기본 설정을 제공하지만, 다른 설정이 성능을 향상시킬 수 있다고 판단되면, 실제 운영에 적용하기 전에 하나씩 변경하고 테스트하는 것이 좋습니다.

PostgreSQL 데이터베이스의 타임아웃 설정은 쿼리, 사용자, 또는 데이터베이스 수준에서 가능합니다. 애플리케이션 개발자는 이러한 타임아웃 파라미터와 그 작동 방식을 이해하여 타임아웃 오류를 방지하는 것이 중요합니다. 다음은 애플리케이션 성능과 사용자 경험이 타임아웃 오류로 인해 부정적인 영향을 받지 않도록 조정할 수 있는 몇 가지 타임아웃 관련 파라미터입니다:

• statement_timeout – 쿼리 내 명령문이 타임아웃되기 전까지의 시간을 밀리초 단위로 설정합니다. 기본값은 타임아웃이 없는 상태입니다.
• idle_in_transaction_session_timeout – 지정된 시간 이상 유휴 상태인 열린 트랜잭션 세션을 종료합니다. 이렇게 하면 해당 세션이 보유한 락을 해제하여 연결 슬롯을 재사용할 수 있게 하고, 해당 트랜잭션에만 보이는 튜플을 정리하여 데이터 비대화를 줄일 수 있습니다. 기본값은 비활성 (0) 입니다.
• idle_session_timeout – PostgreSQL 14 버전 이상에서 사용할 수 있는 파라미터로, 열린 트랜잭션 외부에서 지정된 시간 이상 유휴 상태인 세션을 종료합니다. 기본값은 비활성(0)입니다. 13 버전 이하에서는 idle_in_transaction_session_timeout 파라미터를 사용했지만, 이는 열린 세션 내 모든 트랜잭션을 중지시켰습니다.
• client_connection_check_interval – PostgreSQL 14 버전 이상에서 사용할 수 있는 파라미터로, 쿼리를 실행할 때 클라이언트 연결에 대한 선택적 점검 간격을 설정합니다. 이 설정을 통해 커널이 연결이 끊어졌다고 보고하면 장시간 실행되는 쿼리를 더 빨리 종료할 수 있습니다. 기본값은 비활성(0)입니다. 13 버전 이하에서는 쿼리가 완료될 때까지 서버가 끊어진 연결을 감지하지 못해, 연결이 예기치 않게 끊어진 경우 결과를 클라이언트에 보낼 수 없었습니다.

PostgreSQL의 데이터베이스 동작과 관련된 기능 및 모범 사례

PostgreSQL는 강력한 객체-관계형 데이터베이스 시스템으로, 데이터 무결성, 신뢰성, 확장성이 뛰어나며, 고성능의 혁신적인 데이터베이스 솔루션을 제공할 수 있는 견고한 아키텍처를 가지고 있습니다. PostgreSQL는 애플리케이션 개발자가 무료 오픈 소스 확장 환경에서 운영상 내결함성 (fault tolerant) 애플리케이션을 구축할 수 있도록 다양한 기능을 제공합니다. 개발자는 데이터베이스를 다시 컴파일하지 않고도 사용자 정의 함수를 만들고 다양한 프로그래밍 언어의 코드를 사용할 수 있습니다. 이 섹션에서는 데이터베이스 동작과 관련된 몇 가지 기능 및 모범 사례에 대해 논의하고자 합니다.

AUTOCOMMIT

PostgreSQL는 ACID 준수를 위해 트랜잭션을 명시적으로 커밋해야 합니다. 이를 돕기 위한 기능 중 하나가 트랜잭션을 자동으로 데이터베이스 저장하도록 하는 AUTOCOMMIT입니다. AUTOCOMMIT이 활성화되면 각 SQL 문이 개별 트랜잭션으로 실행되며 자동으로 커밋됩니다. 기본 설정은 ON으로, BEGIN이나 COMMIT 명령을 별도로 입력할 필요가 없습니다. 일반적으로 트랜잭션은 BEGIN으로 시작하고 COMMIT 명령으로 끝나며, COMMIT은 사용자 변경 사항을 저장합니다. AUTOCOMMIT을 OFF로 설정하면 BEGIN 명령은 필요 없지만, 변경 사항을 데이터베이스에 반영하려면 COMMIT 명령은 명시적으로 작성해야 합니다.

AUTOCOMMIT의 기본 설정은 대부분의 환경에서 유용하며, 변경할 필요가 없습니다. 예를 들어, \COPY 명령을 사용하여 대량의 행을 로드할 때 AUTOCOMMIT을 비활성화할 필요가 없습니다. AUTOCOMMIT상에서 100개의 행을 한 번에 삽입하는 BULK INSERT 방식(예: INSERT … VALUES (...), (...), (...), (...)) 은 100개의 별도 INSERT 문에 대해 단일 COMMIT을 사용하는 방식(예: BEGIN; INSERT … INSERT … INSERT …)보다 성능이 더 좋습니다. 이는 개별 BEGIN 및 COMMIT 명령이 상당한 디스크 활동과 CPU를 사용하기 때문입니다. 그러나 특정 상황에서는 AUTOCOMMIT 설정을 끄는 것이 더 나은 작업 환경을 제공할 수 있습니다. 예를 들어, 하나의 삽입이 실패하면, 비즈니스 요구에 따라 문제가 될 수 있는 원치 않는 부분 데이터 로드를 피하기 위해 모든 행이 롤백됩니다.

이 기능은 실수로 WHERE 절 없이 DELETE 문을 실행했을 때 빠르게 복구할 수 있도록 해주기 때문에 애플리케이션 개발자에게 유용할 수 있습니다. AUTOCOMMIT을 끈 상태로 유지하려면 특정 워크로드에 대해 세션 수준에서 이 설정을 조정하는 것이 좋습니다. AUTOCOMMIT이 꺼진 상태에서 COMMIT 명령 없이 명령문이 실행되면, PostgreSQL는 COMMIT이 주어질 때까지 락을 유지하므로, 이 글의 앞쪽에서 설명하였듯 다음 명령문에서 락 문제가 발생할 수 있습니다.

PostgreSQL에서 AUTOCOMMIT을 사용하는 모범 사례는 다음과 같습니다:

• AUTOCOMMIT을 전역적으로 켜 두고, 비즈니스적인 이유가 있을 때만 비활성화하시고, 다음 사항을 유의하세요:
  • AUTOCOMMIT이 켜져 있으면 쿼리가 그룹화되지 않습니다.
  • AUTOCOMMIT이 암시적으로 수행되므로, 어떤 쿼리가 커밋되거나 롤백되는지에 대한 불확실성이 없습니다.
  • PostgreSQL에서 AUTOCOMMIT은 암시적인 BEGIN과 COMMIT으로 둘러싸여 있으며, 이는 트랜잭션 블록이라고도 합니다. 따라서 COMMIT 명령이 필요하지 않습니다.
  • AUTOCOMMIT이 켜져 있으면 모든 SQL 문이 자동으로 커밋되며, AUTOCOMMIT이 꺼져 있지 않는 한 롤백은 불가능합니다.
• AUTOCOMMIT을 비활성화할 때는 세션 수준에서만 설정하시고, 다음 사항을 유의하세요:
  • 비활성화되면 데이터베이스는 항상 트랜잭션 모드에 있으며, 반드시 명시적으로 COMMIT 또는 ROLLBACK 명령으로 종료해야 합니다.
  • AUTOCOMMIT이 꺼져 있을 때 실수가 발생하면 롤백을 쉽게 실행하여 모든 작업을 되돌릴 수 있습니다. 이는 변경 사항이 데이터베이스에 반영되지 않게 하여 실수로부터 빠르고 간단하게 복구할 수 있게 해줍니다.

AUTOVACUUM

PostgreSQL에서 VACUUM은 수동으로 데드 튜플을 정리하는 작업인 반면, AUTOVACUUM은 삭제되거나 오래된 업데이트 튜플을 자동으로 제거하는 주기적인 백그라운드 유틸리티 데몬입니다. AUTOVACUUM은 기본적으로 활성화되어 있으며, 데이터베이스에 많은 UPDATE 및 DELETE 명령이 실행되는 경우 그 파라미터를 테스트하고 조정해야 합니다. PostgreSQL은 MVCC 모델을 사용하여 동시에 읽기 요청을 완료할 수 있도록 이전 행 버전을 유지합니다. 이러한 명령이 실행되는 동안 행은 삭제되지 않으며, 트랜잭션이 완료될 때까지 이전 버전이 유지됩니다. COMMIT 명령이 주어지지 않으면, 트랜잭션이 여전히 실행 중이며, 해당 행이 여전히 필요할 수 있기 때문에 AUTOVACUUM은 이 행들을 삭제할 수 없습니다. AUTOCOMMIT을 비활성화하면 데이터베이스의 락과 AUTOVACUUM 유지 관리 장해와 같은 문제가 발생할 수 있습니다.

AUTOVACUUM을 비활성화하면 데드 튜플이 제거되지 않아 테이블 부풀림 현상이 발생할 수 있습니다. 이는 테이블과 인덱스의 디스크 사용량을 증가시켜 쿼리 실행 시간이 길어지고, 데드 튜플과 함께 활성 행을 읽어야 하므로 성능에 부정적인 영향을 미칩니다. AUTOVACUUM이 자동으로 데드 튜플을 제거하지 않으면, VACUUM FULL 명령을 사용하여 물리적으로 제거해야 할 수도 있습니다.

PostgreSQL에서 AUTOVACUUM을 사용하는 모범 사례는 다음과 같습니다:

• AUTOVACUUM이 제대로 작동하는지 확인하기 위해 pgstattuple 확장 기능을 사용하여 주기적으로 부풀림 현상에 대한 추정 보고서를 수행하세요. 다음 사항을 유의하세요:
  • 주기적으로 관리되고 축소되지 않는 경우 부풀림 현상은 디스크 사용량 증가와 성능 저하를 초래할 수 있으며, 관련 데이터 쿼리가 두 배 이상 오래 걸릴 수 있습니다. AUTOVACUUM을 적절히 설정하면 시간이 지남에 따라 데이터베이스 부풀림 현상을 최소화할 수 있습니다.
  • autovacuum_naptime 파라미터를 적절히 조정하여 AUTOVACUUM이 충분히 자주 실행되도록 하여 데이터베이스 부풀림 현상이 확산되는 것을 방지할 수 있습니다.
  • 쓰기 작업이 많은 워크로드와 인접한 장시간 실행 쿼리를 피하세요. 서버에서 실행 중인 워크로드를 완전히 이해하는 것이 중요합니다. AUTOVACUUM은 테이블에 약한 락을 생성합니다. INSERT, UPDATE, DELETE와 같은 일반적인 데이터베이스 작업은 계속 진행될 수 있지만, 인덱스와 테이블의 잘림에 영향을 미칠 수 있습니다.
• 테이블 사용 및 접근 패턴에 따라 AUTOVACUUM 설정을 조정하시길 바랍니다. 다음 사항을 유의하세요:
  • DELETE 및 INSERT 문이 많은 테이블에서 VACUUM 파라미터를 기반으로 테이블의 AUTOVACUUM 임계값을 테스트하고 설정해야합니다.
  • 바쁜 시간이 아닌 동안 AUTOVACUUM을 실행하는 최적의 전략을 계획하고 테스트하여 데이터베이스에 미치는 영향을 최소화하세요. 또한, 바쁜 시간 동안 락으로 인해 운영 시스템 워크로드가 방해받지 않도록 AUTOVACUUM의 실행 방법을 결정하시길 바랍니다.
  • AUTOVACUUM을 정기적으로 사용하지 않으면, VACUUM 실행 사이에 시스템이 과부하 상태가 되어 이를 해결해야 하거나 VACUUM FULL을 사용해야 할 위험이 있습니다.

Idle in transaction

PostgreSQL 연결 상태를 모니터링하는 것은 중요한 작업입니다. 연결 상태의 변화는 오류를 해결하는 출발점이 될 수 있습니다. PostgreSQL에는 트랜잭션이나 명령문이 가질 수 있는 네 가지 주요 상태가 있습니다:

• active – 쿼리를 실행 중인 상태
• idle – 쿼리를 실행하지 않고 유휴 상태에 있는 연결로, 메모리와 CPU 같은 서버 자원을 소모하며 성능 저하의 원인이 될 수 있음
• idle_in_transaction – 트랜잭션이 시작되었지만 현재 아무 작업도 하지 않고 사용자 입력을 기다리는 상태
• idle_in_transaction (aborted) – idle_in_transaction과 유사하지만, 트랜잭션 내 명령문이 오류를 일으킨 상태

PostgreSQL에서 열린 트랜잭션은 락을 유지하고 다른 쿼리를 차단하며, AUTOVACUUM과 VACUUM 성능을 저하시켜 테이블 부풀림 현상을 초래할 수 있습니다. 성능 문제를 일으키기 쉽지만 식별하기 어려운 중요한 상태 중 하나가 idle_in_transaction입니다. 이 상태는 데이터베이스가 BEGIN 명령을 실행하고 하나 이상의 테이블에 락을 설정한 후 사용자 입력을 기다리지만, 어떤 이유에서건 COMMIT이나 ROLLBACK 명령이 실행되지 않을 때 발생합니다. 이러한 연결은 PostgreSQL이 왜 기다리는지 알지 못하기 때문에 자동으로 종료되지 않고 이 상태가 영원히 유지되어, 트랜잭션 스레드를 계속 소모합니다. 프로세스가 대기하는 데 타당한 비즈니스 이유가 있을 수 있기 때문에 이 상태는 자동으로 해결되지 않습니다. 예를 들어, 문서를 읽는 데 시간이 걸리거나 이메일을 보내거나 받기 위해 일정 기간 대기해야 할 수도 있습니다. idle_in_transaction 문제를 해결하려면 데이터베이스 락을 이해하여 왜 발생되는지 알아내고 애플리케이션이 이를 어떻게 처리할지 알아야 합니다.

idle_in_transaction 상태는 쉽게 재현할 수 있습니다. 먼저 테이블을 생성하고 데이터를 추가합니다. 그런 다음 BEGIN을 입력하여 트랜잭션을 시작합니다. 트랜잭션이 시작된 후 COMMIT이나 ROLLBACK 없이 테이블에 컬럼을 추가하면 첫 번째 트랜잭션이 끝나지 않은 상태에서 두 번째 명령문이 시작되었기 때문에 idle_in_transaction 락이 발생합니다.

idle_in_transaction 상태를 PostgreSQL의 psql 세션에서 재현하려면 다음 코드를 실행하세요:

CREATE TABLE mydbtable (
id int GENERATED BY DEFAULT AS IDENTITY,
username varchar (50),
password varchar (50)
);
BEGIN;
alter table mytable add column last_update timestamp;

다른 psql 탭을 하나 더 연 후 다음 코드를 실행하세요:

SELECT * FROM mydbtable;

테이블에 락이 걸려있기 때문에 아무 일도 일어나지 않을 것입니다. 락을 해제하기 위해 첫번째 세션으로 돌아가 commit 이나 rollback 을 해야합니다.

COMMIT;

다음은 idle_in_transaction 세션을 관리하기 위한 모범 사례입니다:

• pg_stat_activity 테이블을 조회하여 현재 idle_in_transaction 상태인 쿼리를 찾습니다. 이 테이블과 그 사용법에 대한 자세한 정보는 pg_stat_activity를 참조하세요.
• 트랜잭션을 더 작고 관리하기 쉬운 조각으로 나누어 idle_in_transaction 상태를 피할 수 있습니다. 다음 사항을 유의하세요:
  • 쿼리가 세션 별 또는 데이터베이스 별 설정된 시간 이상 실행되지 않도록 준비합니다.
  • 주기적으로 타임아웃 로그를 확인하여 장시간 실행되는 트랜잭션을 감지합니다.
  • idle_in_transaction_session_timeout 파라미터를 설정하여 장시간 실행되는 트랜잭션이 취소되도록 고려하세요. 기본값은 0으로, 타임아웃이 없습니다.
  • pg_stat_activity를 사용하여 장시간 실행되는 쿼리와 그 상태 지속 시간을 확인합니다.
• 장시간 실행되는 저장 프로시저나 함수를 데이터베이스 층에서 애플리케이션 층으로 이동시킬 수 있습니다. 다음 사항을 유의하세요:
  • 애플리케이션에 작성된 오류 처리 로직으로 오류를 처리할 수 있습니다.
  • 쿼리 결과를 처리하기 전에 애플리케이션에서 트랜잭션을 종료하도록 코드를 작성합니다.
  • 불필요한 오류를 피하기 위해 애플리케이션과 데이터베이스 레이어에서 AUTOCOMMIT이 활성화되어 있는지 확인합니다.
  • pg_stat_activity 뷰를 사용하여 idle_in_transaction 트랜잭션을 모니터링하여, 이러한 세션이 VACUUM 및 다른 쿼리가 테이블에 접근하는 데 지장을 주지 않도록 하세요. 이를 통해 모든 열린 트랜잭션과 그 상태를 확인할 수 있습니다.

결론

이 글에서는 PostgreSQL의 주요 기능과 PostgreSQL 엔진의 특정 기능을 정의하고, 애플리케이션 아키텍처를 위한 모범 사례를 설명했습니다. PostgreSQL 용 Amazon RDS 및 Amazon Aurora PostgreSQL 호환 버전을 위한 애플리케이션을 설계할 때, 데이터베이스 설계와 파라미터 설정을 신중히 고려하면 데이터베이스 성능 저하로 인한 비용이 많이 드는 불편한 중단을 피하고, 다운스트림 애플리케이션의 부하로 인한 중단 문제를 줄일 수 있습니다. 이 글은 이러한 주제에 대한 포괄적인 자료는 아니지만, 애플리케이션 개발자를 위한 추가 PostgreSQL 아키텍처 및 튜닝 고려 사항을 논의하는 후속 게시물의 입문서로 활용될 것입니다.