Postgres PK가 33씩 뛰는 auto increase 현상 트러블 슈팅기(해결은 아니고 .. 원인 찾기 !)

개발 끄적끄적 .. ✍️·2023년 3월 12일

1 → 34 → 67 ?

어느 날 데이터베이스 일부 테이블에서 id 컬럼(pk)의 값이 순차적으로 증가하지 않고 일부 값을 건너뛰며 생성되어 있는 것을 발견했습니다. 처음에는 일부 컬럼이 제거 되었나 생각이 되었지만 건너 뛴 간격 만큼 어떠한 작업이 진행되는 테이블이 아니었습니다.

또 유심히 살펴보니 증가 폭이 일정함을 발견했습니다. 1 → 34 → 67 과 같이 33씩 건너 뛰고 있었습니다. 이를 통해 이것은 우연이 아닌 특정 패턴을 가지는 이슈일 것이라 예상을 하고 문제를 바라보기 시작하고 찾아보기 시작했습니다. 찾아보니 같은 이슈에 대한 stackoverflow 글을 발견 할 수 있었습니다.

Aurora for PostgreSQL + pk 점프 현상: Why is id as SERIAL discontinuous values after failover in RDS Aurora PostgreSQL?

Sequence

💡 Sequence: 자동으로 순차적으로 증가하는 유일한 순번을 반환하는 데이터베이스 객체

💡 Regclass: 시퀀스의 ObjectID(OID)

이번 트러블 슈팅을 관통하는 하나의 개념은 Sequence입니다. sequence는 주로 ID와 같이 순차적으로 증가하는 컬럼에 자주 사용됩니다. 테이블과는 독립적으로 저장되고 생성. 여러테이블에서 참조해서 공유되지 않도록 주의 해야합니다. 시퀀스는 RollBack이 되지 않습니다. 시퀀스를 재설정하지 않는 방식으로 트랜잭션이 진행되기 때문에 INSERT 진행 중 실패하게 된다면 생성된 시퀀스 값이 손실됩니다.

시퀀스를 롤백하지 않는 이유는 크게 두 가지 입니다.

A트랜잭션에서 시퀀스를 1을 가져갔고 B트랜잭션이 이어 시퀀스 2를 가져갑니다. 그런데 A가 롤백됨으로 이미 증가한 시퀀스를 -1 하거나 0으로 초기화한다면 정상적으로 처리된 B트랜잭션에서 부여된 시퀀스 2와 추후에 생성될 시퀀스는 중복이 생길 수도 있습니다.
또한 무결성을 위해 이미 부여된 시퀀스를 수정 한다면 병목이 발생 하여 성능 문제를 야기할 수 있습니다.

따라서 트랜잭션 중 실패한 시퀀스는 따로 재설정하지 않습니다. 이는 은행 번호표와 같은데, 은행에서 번호표를 뽑았는데 중간에 고객이 기다리다가 지쳐 나가면 이전 번호를 채우는 것이 아니라 그 다음 번호로 진행하는 것을 생각하면 이해하기 쉽습니다. 따라서 트랜잭션 내에서 할당되었지만 사용되지 않은 번호는 트랜잭션이 종료되면 손실되어 시퀀스에 구멍이 발생할 수 있습니다.

트러블 슈팅이라고 적고 삽질이라고 읽는다..

try1. 중복되는 PK sequence가 존재하지 않을까 ?

맨 처음 했던 생각은 혹여나 다른 테이블에서 문제가 되는 테이블의 sequence 값을 참조하여 사용하지 않을까 하는 의심이었습니다. 현재 모든 테이블의 PK는 sequence로 부여되고 있었는데, 이 값을 다른 테이블에서 참조하여 사용한다면 충분히 그럴 수 있다는 판단이었습니다. 그래서 전체 테이블의 sequence를 조회해보았더니 중복되는 sequnce 값은 존재하지 않았습니다. (혹여나 하는 마음이었지만 .. 그 누가 테이블 생성 시 default로 부여되는 이 값을 굳이 굳이 조작할 일이 있을까 하는 .. 또한 그 증가폭이 33이라는 일정함은 어떻게 설명하지 ..)

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' ORDER BY relname;

try2. PK의 증가 값이 1이 아닌 다른 값인가 ?

또 한 편으로는 PK 증가 값이 1이 아닌 다른 값인가에 대한 의심이 들었습니다. 하지만 이 역시 확인해보니 증가폭(increment)이 1인 것을 확인했습니다.

SELECT sequence_name, increment FROM information_schema.sequences ORDER BY sequence_name;

try3. sequence cache_value ?

찾아보니 PostgreSQL 시퀀스를 캐싱해 메모리에 캐싱해놓으면 엑세스 효율이 올라간다는 사실을 발견했습니다. 이 시퀀스 캐싱은 세션 레벨에서 동작하기 때문에 서로 다른 세션에서 nextval 시퀀스 값을 조회하면 cache_size 만큼 차이가 날 수 있음을 알게 되었습니다. 혹여나 어떠한 시스템의 이유로 캐싱된 데이터가 사라졌다면 시퀀스 값이 일정한 간격 만큼 증가할 수 있다고 생각했습니다. 그래서 캐시를 활용해 시퀀스를 관리하고 있는지 확인해보았지만 아쉽게도 모든 sequence의 cache_size는 1이었습니다.

SELECT sequencename, cache_size FROM pg_catalog.pg_sequences;

SEQ_LOG_VALS !

/*
 * We don't want to log each fetching of a value from a sequence,
 * so we pre-log a few fetches in advance. In the event of
 * crash we can lose (skip over) as many values as we pre-logged.
 */
#define SEQ_LOG_VALS    32

그러다가 발견한 것이 SEQ_LOG_VALS 입니다. PostgreSQL은 시스템 상에서 가져온 sequence 값을 일일이 기록하지 않고 한 번에 여러개를 가져오고 이 값을 한 번에 기록합니다. 이 값을 SEQ_LOG_VALS 라고 하고, PostgreSQL은 32개의 숫자를 미리 가져오고 시퀀스 부여를 진행되면서 가져온 SEQ_LOG_VALS를 모두 소진하면 WAL(Write Ahead Log)에 기록하여 팔로워 노드에 알리고 다시 32개를 가져와 다시 시작하는 구조를 가지고 있습니다.

다만 이 값은 충돌이 발생하면 이 상태 값을 잃을 수 있으며 이전에 사전 할당된 모든 번호가 모두 "사용"되었다는 가정으로 새롭게 시작합니다. 현재 이 값은 시스템상 기본 디폴트로 #define SEQ_LOG_VALS 32 로 설정 되어있음(PostgreSQL.ver 마다 다를수도) (PostgreSQL SEQ_LOG_VALS)

그래도.. 왜 33씩 뛰는지 알게 되었다 !

충돌이 나면 이전까지 시퀀스를 부여를 기록하던 SEQ_LOG_VALS가 손실되고 새롭게 SEQ_LOG_VALS(32) + cahce_value(1) = 33 값이 추가된 값이 새롭운 시퀀스로 부여되기 때문에 서두의 문제제기에서 발견한 1 → 34 → 67 이라는 일정한 패턴이 발견된 것입니다.

또한 알아야 할 것은 충돌 또는 대기 상태로의 장애 조치 후 시퀀스가 일부 값을 건너뛸 수 있다는 것입니다. 시스템상 어떠한 이유에 의해서 시퀀스 번호를 받고 에러, 비정상적인 트랜잭션 종료 등이 발생하면 PK 값이 일정하지 않고 중간 중간 구멍이 발생할 수 있습니다. 중요한 것은 이러한 시퀀스로 부여되는 PK이 순차적인지, 직렬인지가 아닌 고유 해야 한다는 점입니다. 인조식별되는 PK에 순번에 유의미하게 설계한다면 성능 이슈는 물론 엄청난 복잡합이 동반됩니다.

그래서 앞으로 남은 미션

가장 의문이었던 “33”이라는 규칙적이지만 미스테리한 숫자의 원인은 찾게되었습니다. 그 자체로 의미를 둘 수 있고 PK 중간 중간 구멍이 날 수 있음을 인정하지만 우리 시스템 상 어떠한 이유에서 지속적으로 SEQ_LOG_VALS가 손실되는지 찾아야 할 것 같습니다. 대부분의 (아니 거의 모든) 문서에서 “충돌” 이라는 표현을 사용하고 있습니다. 이 충돌의 범위가 어디까지인지, 어떠한 이벤트로 인해 발생하는 것인지 이제부터 조금 더 깊게 생각을 해봐야할 것 같습니다.