데이터 레이아웃 및 접근 패턴에 대한 모범 사례

특정 데이터 접근 패턴, 특히 데이터 업데이트는 데이터베이스에 부담을 줄 수 있습니다.

가능하면 피하세요.

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

동일한 행에 대한 고주파수 업데이트

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

  • 여러 프로세스가 동시에 동일한 행을 업데이트하려고 하면, 각 트랜잭션이 쓰기를 위해 행을 잠그므로 대기열에 쌓입니다. 이는 트랜잭션 시간을 크게 증가시킬 수 있으며, Rails 연결 풀이 포화되어 애플리케이션 전체가 다운될 수 있습니다.
  • 각 행 업데이트에 대해 PostgreSQL은 새 행 버전을 삽입하고 오래된 버전을 삭제합니다. 고트래픽 시나리오에서는 이 접근 방식이 진공 및 WAL(쓰기 선행 로그) 압력을 유발하여 데이터베이스 성능을 감소시킬 수 있습니다.

이 패턴은 집계 계산이 각 요청에 대해 너무 비쌀 때 자주 발생하므로, 데이터베이스에 실행 중인 합계를 유지합니다. 이러한 집계가 필요하다면, 한 행에 실행 중인 총계와 최근에 추가된 데이터의 작은 작업 세트(예: 개별 증분)를 유지하는 것을 고려하세요:

  • 새로운 데이터를 도입할 때, 작업 세트에 추가하세요. 이러한 삽입은 잠금 경쟁을 일으키지 않습니다.
  • 집계를 계산할 때, 실행 중인 총계와 작업 세트의 라이브 집계를 결합하여 최신 결과를 제공합니다.
  • 주기적으로 작업 세트를 실행 중인 총계로 포함하고 트랜잭션에서 지우는 작업을 추가하여 독자의 작업량을 제한합니다.

넓은 테이블

PostgreSQL은 행을 8 KB 페이지로 정리하고 한 번에 한 페이지에서 작업합니다.

테이블의 행 폭을 최소화함으로써 다음을 개선할 수 있습니다:

  • 각 페이지에 더 많은 행이 포함되면 스캔해야 할 페이지 수가 줄어들기 때문에 순차적 및 비트맵 인덱스 스캔 성능.
  • 각 페이지에서 더 많은 행을 처리할 수 있으므로 진공 성능.
  • (비-HOT) 업데이트 중에 각 업데이트마다 각 인덱스가 업데이트되어야 하므로 업데이트 성능.

넓은 테이블 완화는 데이터베이스 팀의 100 GB 테이블 이니셔티브의 한 부분으로, 넓은 테이블은 100 GB에서 더 적은 행을 수용할 수 있습니다.

테이블에 열을 추가할 때, 새로운 열의 데이터를 다른 열과 일대일 관계로 접근할 의도가 있는지 고려하세요. 그렇다면 새로운 열은 새 테이블로 분할하기에 좋은 후보가 될 수 있습니다.

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

  • search_data는 issues에서 분할되었습니다.
  • project_pages_metadata는 projects에서 분할되었습니다.
  • merge_request_diff_details는 merge_request_diffs에서 분할되었습니다.

데이터 모델 트레이드오프

users, namespaces, projects와 같은 특정 테이블은 매우 넓어질 수 있습니다.

이러한 테이블은 일반적으로 애플리케이션의 중심에 있으며, 매우 자주 사용됩니다.

왜 이것이 문제가 될까요?

  • 이러한 열 대부분은 인덱스에 포함되어 있어 인덱스 쓰기 증폭을 초래합니다. 테이블의 인덱스 수가 16개를 초과하면 쿼리 계획에 영향을 미치고 경량 잠금(LWLock) 경쟁으로 이어질 수 있습니다.
  • PostgreSQL에서의 업데이트는 삭제와 삽입의 조합으로 구현됩니다. 이 말은 드물게 사용되더라도 각 열이 매번 복사된다는 뜻입니다. 이는 생성된 쓰기 선행 로그(WAL)의 양에 영향을 미칩니다.
  • 자주 업데이트되는 열이 있을 경우, 각 업데이트는 모든 테이블 열이 복사되는 결과를 초래합니다. 다시 말해, 이는 생성된 WAL을 증가시키고 자동 진공을 위한 작업량을 증가시킵니다.
  • PostgreSQL은 데이터를 페이지 내에서 행 또는 튜플로 저장합니다. 넓은 행은 페이지당 튜플 수를 줄이며, 이는 읽기 성능에 영향을 미칩니다.

이 문제에 대한 가능한 해결책은 주요 테이블에 가장 중요한 열만 보관하고 나머지를 다른 테이블로 추출하여 주요 테이블과 일대일 관계를 갖는 것입니다.

좋은 후보는 매우 자주 업데이트되는 열(예: last_activity_at) 또는 드물게 업데이트되거나 사용되는 열(예: 활성화 토큰)입니다.

이런 추출의 트레이드오프는 인덱스 전용 스캔이 더 이상 가능하지 않다는 것입니다. 대신, 애플리케이션은 새 테이블에 조인하거나 추가 쿼리를 실행해야 합니다. 이로 인한 성능 영향은 수직 테이블 분할의 이점과 비교하여 평가되어야 합니다.

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

예시

users 테이블을 살펴보겠습니다. 작성 시점에서 이 테이블은 75개의 열을 가지고 있습니다.

위 기준에 맞는 몇 가지 열 그룹을 확인할 수 있으며, 이들은 추출하기 좋은 후보입니다:

  • 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에서 새로운 테이블로 추출하기 위해서는 다음과 같은 작업을 해야 합니다:

  1. 릴리스 M 예시
    • 테이블 생성 (릴리스 M)
    • 새로운 테이블에서 데이터를 읽도록 애플리케이션을 업데이트하고, 데이터가 아직 없는 경우 원래 열로 되돌리게 합니다.
    • 새로운 테이블을 백필 시작합니다.
  2. 릴리스 N 예시
    • 백필을 수행하는 백그라운드 마이그레이션을 완료합니다. 이는 필수 중단 이후의 다음 릴리스에서 진행되어야 합니다.
  3. 릴리스 N + 1 예시
    • 애플리케이션이 새로운 테이블에서만 읽고 쓸 수 있도록 업데이트합니다.
    • 원래 열은 무시합니다. 이는 데이터베이스 열을 안전하게 제거하는 프로세스를 시작합니다. 이는 우리의 가이드에 설명되어 있습니다.
  4. 릴리스 N + 2 예시
    • 원래 열을 제거합니다.
  5. 릴리스 N + 3 예시
    • 원래 열에 대한 무시 규칙을 제거합니다.

이것은 긴 과정이지만, 애플리케이션에 지장을 주지 않고 추출을 수행하기 위해 필요합니다. 완료되면 원래 열과 관련 인덱스는 더 이상 users 테이블에 존재하지 않게 되며, 이는 성능 개선으로 이어질 것입니다.