특정 데이터 액세스 패턴, 특히 데이터 업데이트 패턴은 데이터베이스에 가해지는 부하를 악화시킬 수 있습니다. 가능하면 이러한 패턴을 피하세요.

이 문서는 피해야 할 패턴과 그에 대한 대안 권장 사항을 나열합니다.

고빈도 업데이트, 특히 동일한 행에 대한 업데이트#

많은 트랜잭션이 동시에 업데이트하는 단일 데이터베이스 행을 피하세요.

• 많은 프로세스가 동일한 행을 동시에 업데이트하려고 하면, 각 트랜잭션이 쓰기를 위해 행을 잠그기 때문에 대기열이 생성됩니다. 이로 인해 트랜잭션 처리 시간이 크게 증가할 수 있으며, Rails 연결 풀이 포화 상태가 되어 애플리케이션 전체 다운타임이 발생할 수 있습니다.

• 각 행 업데이트마다 PostgreSQL은 새 행 버전을 삽입하고 이전 버전을 삭제합니다. 트래픽이 많은 시나리오에서 이 방식은 vacuum과 WAL(write-ahead log) 압박을 유발하여 데이터베이스 성능을 저하시킬 수 있습니다.

이 패턴은 각 요청마다 집계(aggregate)를 계산하는 비용이 너무 클 때, 데이터베이스에 누적 합계를 유지하는 형태로 자주 발생합니다. 이러한 집계가 필요한 경우, 단일 행에 누적 합계를 유지하고 최근에 추가된 데이터(예: 개별 증분값)의 소규모 작업 집합을 함께 유지하는 방식을 고려하세요:

• 새 데이터를 도입할 때 작업 집합에 추가하세요. 이 삽입 작업은 잠금 경합을 유발하지 않습니다.

• 집계를 계산할 때 누적 합계와 작업 집합의 실시간 집계를 결합하여 최신 결과를 제공합니다.

• 작업 집합을 누적 합계에 통합하고 트랜잭션 내에서 지우는 주기적인 job을 추가하여, 읽기 작업에 필요한 작업량을 제한하세요.

넓은 테이블#

PostgreSQL은 행을 8 KB 페이지로 구성하고 한 번에 한 페이지씩 처리합니다. 테이블에서 행의 폭을 최소화하면 다음과 같은 측면을 개선할 수 있습니다:

• 순차 스캔 및 비트맵 인덱스 스캔 성능: 각 페이지에 더 많은 행이 들어갈수록 스캔해야 할 페이지 수가 줄어듭니다.

• Vacuum 성능: vacuum이 각 페이지에서 더 많은 행을 처리할 수 있습니다.

• 업데이트 성능: (HOT가 아닌) 업데이트 중에는 각 행 업데이트마다 모든 인덱스를 업데이트해야 합니다.

넓은 테이블 완화는 데이터베이스 팀의 100 GB 테이블 이니셔티브의 일환입니다. 테이블이 넓을수록 100 GB에 맞출 수 있는 행 수가 줄어들기 때문입니다.

테이블에 칼럼을 추가할 때, 새 칼럼의 데이터를 테이블의 다른 칼럼과 일대일 관계로 독립적으로 액세스할 의도가 있는지 고려하세요. 그렇다면 새 칼럼은 새 테이블로 분리하기에 좋은 후보가 될 수 있습니다.

여러 테이블이 이미 이런 방식으로 분리되었습니다. 예를 들면:

• search_data는 issues에서 분리되었습니다.

• project_pages_metadata는 projects에서 분리되었습니다.

• merge_request_diff_details는 merge_request_diffs에서 분리되었습니다.

데이터 모델 트레이드오프#

users, namespaces, projects와 같은 특정 테이블은 매우 넓어질 수 있습니다. 이러한 테이블은 대개 애플리케이션의 핵심이며 매우 자주 사용됩니다.

왜 이것이 문제인가요?

• 이러한 칼럼 중 많은 수가 인덱스에 포함되어 인덱스 쓰기 증폭(index write amplification)을 유발합니다. 테이블의 인덱스 수가 16개를 초과하면 쿼리 계획에 영향을 미치고, 경량 잠금(LWLock) 경합으로 이어질 수 있습니다.

• PostgreSQL의 업데이트는 삭제와 삽입의 조합으로 구현됩니다. 이는 각 칼럼이 자주 사용되지 않더라도 업데이트마다 반복적으로 복사됨을 의미합니다. 이는 생성되는 write ahead log(WAL)의 양에 영향을 미칩니다.

• 자주 업데이트되는 칼럼이 있을 경우, 각 업데이트마다 테이블의 모든 칼럼이 복사됩니다. 마찬가지로 생성되는 WAL의 증가로 이어지며, auto-vacuum의 작업량이 늘어납니다.

• PostgreSQL은 데이터를 페이지 내 행(row) 또는 튜플(tuple)로 저장합니다. 넓은 행은 페이지당 튜플 수를 줄이며, 이는 읽기 성능에 영향을 미칩니다.

이 문제의 가능한 해결책은 메인 테이블에 가장 중요한 칼럼만 유지하고, 나머지는 메인 테이블과 일대일 관계를 가진 별도의 테이블로 추출하는 것입니다. 좋은 후보는 last_activity_at처럼 매우 자주 업데이트되거나, 활성화 토큰처럼 거의 업데이트되지 않거나 사용되지 않는 칼럼입니다.

이러한 추출의 트레이드오프는 인덱스 전용 스캔(index-only scan)이 더 이상 불가능하다는 점입니다. 대신, 애플리케이션이 새 테이블과 조인하거나 추가 쿼리를 실행해야 합니다. 이 성능 영향을 수직 테이블 분리의 이점과 비교하여 평가해야 합니다.

이 주제에 관한 매우 좋은 에피소드가 PostgresFM 팟캐스트에 있습니다. PostgresAI의 @NikolayS와 PgMustard의 @michristofides가 이 주제를 더 깊이 논의합니다 - https://postgres.fm/episodes/data-model-trade-offs.

예시#

작성 시점에 75개의 칼럼을 가진 users 테이블을 살펴보겠습니다. 위의 기준과 일치하며 추출하기에 좋은 후보인 몇 가지 칼럼 그룹을 확인할 수 있습니다:

• encrypted_otp_secret, otp_secret_expires_at 등과 같은 OTP 관련 칼럼. 이 칼럼들은 수가 적으며, 한번 채워지면 자주 업데이트되지 않아야 합니다(전혀 없을 수도 있음).

• 이메일 확인과 관련된 칼럼 - confirmation_token, confirmation_sent_at, confirmed_at. 한번 채워지면 대부분 다시 업데이트되지 않습니다.

• password_expires_at, last_credential_check_at, admin_email_unsubscribed_at와 같은 타임스탬프. 이런 칼럼들은 매우 자주 업데이트되거나 전혀 업데이트되지 않습니다. 별도의 테이블에 있는 것이 더 좋습니다.

• unlock_token, incoming_email_token, feed_token과 같은 다양한 토큰(및 관련 칼럼들).

users.incoming_email_token에 집중해 보겠습니다 - GitLab.com의 모든 사용자에게 하나씩 설정되어 있으며, 이 토큰은 거의 업데이트되지 않습니다.

users에서 새 테이블로 추출하기 위해 다음을 수행해야 합니다:

• 릴리즈 M 예시

테이블 생성 (릴리즈 M)

• 새 테이블에서 읽고, 데이터가 아직 없으면 원래 칼럼으로 폴백하도록 애플리케이션을 업데이트합니다.

• 새 테이블을 백필(back-fill)하기 시작합니다.

• 릴리즈 N 예시

백필을 수행하는 백그라운드 마이그레이션을 완료합니다. 이는 필수 정지 이후 다음 릴리즈에서 수행해야 합니다.

• 릴리즈 N + 1 예시

새 테이블에서만 읽고 쓰도록 애플리케이션을 업데이트합니다.

• 원래 칼럼을 무시합니다. 이는 가이드에 설명된 대로 데이터베이스 칼럼을 안전하게 제거하는 프로세스를 시작합니다.

• 릴리즈 N + 2 예시

원래 칼럼을 삭제합니다.

• 릴리즈 N + 3 예시

원래 칼럼에 대한 무시 규칙을 제거합니다.

이는 긴 프로세스이지만, 애플리케이션을 중단하지 않고 추출을 수행하기 위해 필요합니다. 완료되면, 원래 칼럼과 관련 인덱스가 users 테이블에 더 이상 존재하지 않아 성능이 향상됩니다.