데이터 레이아웃 및 액세스 패턴에 대한 모범 사례

데이터 액세스 및 특히 데이터 업데이트의 일부 패턴은 데이터베이스에 부담을 가중시킬 수 있습니다. 가능하다면 이러한 패턴을 피하십시오.

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

높은 빈도의 업데이트, 특히 같은 행에 대한 업데이트

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

  • 많은 프로세스가 동시에 동일한 행을 업데이트하려고 하면 각 트랜잭션이 행을 쓰기 위해 잠그면서 대기열이 형성됩니다. 이로 인해 트랜잭션 시간이 크게 증가할 수 있으며, Rails 연결 풀이 포화되어 전체 응용 프로그램 다운타임으로 이어질 수 있습니다.
  • 각 행 업데이트마다 PostgreSQL은 새로운 행 버전을 삽입하고 이전 버전을 삭제합니다. 고트래픽 시나리오에서 이 접근 방식은 베큄(vacuum) 및 WAL(Write-Ahead Log) 압력을 유발하여 데이터베이스 성능을 저하시킬 수 있습니다.

이러한 패턴은 종종 각 요청에 대해 계산하기에 너무 비용이 많이 드는 경우 발생합니다. 따라서 데이터베이스에 실행 중인 합계를 유지합니다. 이와 같은 합계가 필요한 경우에는 단일 행에 실행 총계 및 개별 증분과 같은 최근 추가된 데이터의 작은 작업 집합을 유지하도록 고려하십시오.

  • 새 데이터를 도입할 때 작업 집합에 추가합니다. 이러한 삽입은 잠금 경합을 유발시키지 않습니다.
  • 합계를 계산할 때 실행 총계와 작업 집합의 라이브 합계를 결합하여 최신 결과를 제공합니다.
  • 작업 집합을 실행 총계에 통합하고 리더가 필요로 하는 작업 양을 제한하는 트랜잭션에서 이를 지우는 주기적인 작업을 추가합니다.

넓은 테이블

PostgreSQL은 행을 8KB 페이지로 구성하고 한 번에 한 페이지씩 작동합니다. 테이블의 행 너비를 최소화함으로써 다음을 개선할 수 있습니다.

  • 순차 및 비트맵 인덱스 스캔 성능이 향상됩니다. 각 페이지에 더 많은 행이 포함되어 있으면 스캔해야 하는 페이지 수가 줄어듭니다.
  • 베큄 성능이 향상됩니다. 베큄은 각 페이지에서 더 많은 행을 처리할 수 있습니다.
  • 업데이트 성능이 향상됩니다. (비-HOT) 업데이트 중에 각 인덱스는 각 행 업데이트마다 업데이트해야 합니다.

넓은 테이블을 완화하는 것은 데이터베이스 팀의 100 GB 테이블 이니셔티브의 일부입니다. 더 넓은 테이블은 100GB에 더 적은 행을 포함시킬 수 있기 때문입니다.

테이블에 열을 추가할 때는 새 열의 데이터에 대해 테이블의 다른 열과 일대일 관계로 액세스할 것인지를 고려하십시오. 그렇다면 새 열은 새 테이블로 분할되는 좋은 후보가 될 수 있습니다.

이미 이와 같이 여러 테이블이 분할되었습니다. 예를 들면:

  • search_data가 issues에서 분할됨
  • project_pages_metadata가 projects에서 분할됨
  • merge_request_diff_details가 merge_request_diffs에서 분할됨

데이터 모델 트레이드 오프

users, namespaces, projects와 같은 특정 테이블은 매우 넓어질 수 있습니다. 이러한 테이블은 일반적으로 응용 프로그램의 중심이며 매우 자주 사용됩니다.

이것이 문제인 이유는 무엇인가요?

  • 이러한 열 중 많은 열이 인덱스에 포함되어 있으며, 이는 인덱스 쓰기 증폭을 유발합니다. 테이블의 인덱스 수가 16개 이상인 경우, 이는 쿼리 계획에 영향을 미치며 경량 잠금 (LWLock) 경합으로 이어질 수 있습니다.
  • PostgreSQL에서 업데이트는 삭제와 삽입의 조합으로 구현됩니다. 즉, 거의 사용하지 않는 열도 각 업데이트마다 반복해서 복사됩니다. 이는 생성된 Write Ahead Log (WAL)의 양에 영향을 미치게 됩니다.
  • 자주 업데이트되는 열이 있는 경우, 각 업데이트는 모든 테이블 열이 복사되는 결과를 가져옵니다. 다시 말해, 이로 인해 생성된 WAL이 증가하며 autovacuum에 더 많은 작업을 만듭니다.
  • PostgreSQL은 데이터를 행 또는 튜플로 저장합니다. 넓은 행은 페이지 당 튜플 수를 줄이고, 이는 읽기 성능에 영향을 줍니다.

이 문제에 대한 가능한 해결책은 주요 열만을 주 테이블에 유지하고, 나머지를 주 테이블과 일대일 관계를 가진 다른 테이블로 추출하는 것입니다. 매우 자주 업데이트되는 열(예: last_activity_at) 또는 드물게 업데이트되거나/또는 사용되는 열(예: 활성화 토큰)이 좋은 후보입니다.

이러한 추출과 함께 오는 트레이드 오프는 인덱스 전용 스캔이 더 이상 불가능해집니다. 대신 응용 프로그램은 새 테이블에 조인하거나 추가 쿼리를 실행해야 합니다. 이에 따른 성능 영향은 수직 테이블 분할의 이점과 비교되어야 합니다.

PostgresFM 패드캐스트에 이 주제에 대해 매우 좋은 에피소드가 있으며, PostgresAI의 @NikolayS와 PgMustard의 @michristofides가 이 주제에 대해 더 심층적으로 논의합니다 - https://postgres.fm/episodes/data-model-trade-offs.

예시

작성 시점의 users 테이블은 75개의 열이 있습니다. 위의 기준과 일치하는 몇 가지 열 그룹을 볼 수 있으며 추출에 좋은 후보입니다:

  • OTP 관련 열, 예: encrypted_otp_secret, otp_secret_expires_at 등. 이러한 열은 적은 수가 있으며, 값을 한 번 설정하면 자주(혹은 전혀) 업데이트되지 않아야 합니다.
  • 이메일 확인과 관련된 열 - 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 테이블에 더 이상 존재하지 않으며, 이로 인해 성능이 향상될 것입니다.