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

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

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

높은 주파수의 업데이트, 특히 같은 행에 대해

하나의 행이 동시에 많은 트랜잭션에 의해 업데이트되는 것을 피하십시오.

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

이러한 패턴은 각 요청에 대해 집계를 계산하는 것이 너무 많은 자원을 소모한다는 경우에 자주 발생합니다. 따라서 데이터베이스에 지속적인 합계를 유지하는 경우가 있습니다. 이러한 집계가 필요한 경우에는 단일 행에 실행 총계 및 최근 추가된 데이터와 같은 작은 작업 집합을 유지하는데 고려해야 합니다.

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

넓은 테이블

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

  • 순차 및 비트맵 인덱스 스캔 성능이 향상됩니다. 각 페이지에 더 많은 행이 포함되어 있으면 더 적은 페이지를 스캔해야 합니다.
  • VACUUM 성능이 향상됩니다. VACUUM은 각 페이지에서 더 많은 행을 처리할 수 있습니다.
  • 업데이트 성능이 향상됩니다. (HOT가 아닌) 업데이트 도중 각 인덱스에 대해 모든 행 업데이트가 이루어져야 합니다.

넓은 테이블의 완화는 데이터베이스 팀의 100GB 테이블 계획의 일부로, 넓은 테이블은 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 양이 증가하고 자동 가비지 컬렉션에 더 많은 작업을 만들어냅니다.
  • PostgreSQL은 데이터를 행 또는 튜플로 페이지에 저장합니다. 넓은 행은 페이지 당 튜플 수를 줄이고 이는 읽기 성능에 영향을 줍니다.

이 문제에 대한 가능한 해결책은 주요 열만을 주 테이블에 유지하고 나머지를 다른 테이블로 추출하여 주 테이블과 일대일 관계를 형성하는 것입니다. 자주 업데이트되는 열이거나 드물게 업데이트되거나/사용되는 열과 같은 열이 좋은 후보입니다.

이러한 추출에 따른 트레이드오프는 인덱스 전용 스캔 대신에 애플리케이션이 새 테이블에 조인하거나 추가 쿼리를 실행해야 한다는 것입니다. 이러한 성능 영향은 수직 테이블 분할의 이점과 저울질되어야 합니다.

PostgresFM pod캐스트에는 이에 관한 매우 좋은 에피소드가 있으며, 여기서 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 테이블에 존재하지 않게 되어 성능이 향상됩니다.