데이터 레이아웃 및 액세스 패턴의 최상의 방법

일부 데이터 액세스 패턴 및 특히 데이터 업데이트는 데이터베이스에 부하를 유발할 수 있습니다. 가능하면 이러한 패턴을 피하세요.

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

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

가능한 많은 트랜잭션이 동시에 동일한 행을 업데이트하는 것을 피하세요.

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

이러한 패턴은 특히 각 요청에 대해 집계하는 것이 너무 비용이 많이 들기 때문에, 데이터베이스에 누적된 합계가 유지되어야 하는 경우에 발생합니다. 이러한 집계가 필요한 경우, 단일 행의 런닝 총계와 개별 증분과 같은 최근 추가된 데이터의 작은 작업 집합을 유지하는 것을 고려해보세요.

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

넓은 테이블

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의 증가와 auto-vacuum에 더 많은 작업을 생성합니다.
  • PostgreSQL은 데이터를 행 또는 튜플로 페이지에 저장합니다. 넓은 행은 페이지당 튜플 수를 줄이므로 읽기 성능에 영향을 줍니다.

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

이러한 분리로 인해 인덱스 전용 스캔은 더 이상 불가능해집니다. 대신, 응용 프로그램은 새 테이블에 조인하거나 추가 쿼리를 실행해야 합니다. 이러한 성능 영향은 수직 테이블 분할의 이점과 대조하여 고려되어야 합니다.

이 주제에 대한 PostgresFM 팟 캐스트에 매우 좋은 에피소드가 있으며, PostgresAI의 @NikolayS와 PgMustard의 @michristofides가 이 주제에 대해 보다 심층적으로 논의하고 있습니다 - 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)
    • 새 테이블에서 데이터를 읽는 응용 프로그램을 업데이트하고, 데이터가 아직 없는 경우 원래 열로 되돌아갑니다.
    • 새 테이블을 back-fill하기 시작합니다
  2. N 릴리스 예시
    • 백그라운드 마이그레이션의 마무리를 함으로써 back-fill을 완료합니다. 필요한 중단과 함께 다음 릴리스에 수행되어야 합니다.
  3. N + 1 릴리스 예시
    • 새 테이블에서 데이터를 읽고 쓰도록 응용 프로그램을 업데이트하고, 원래 열을 무시합니다. 이는 마이그레이션 중에 안전하게 데이터베이스 열을 제거하기 시작합니다. 자세한 내용은 가이드에서 확인하세요.
  4. N + 2 릴리스 예시
    • 원래 열 제거
  5. N + 3 릴리스 예시
    • 원래 열의 무시 규칙 제거

이는 번거로운 과정이지만, 응용 프로그램을 방해하지 않고 추출을 하기 위해서 필요합니다. 완료되면 원래의 열과 관련된 인덱스가 users 테이블에 더 이상 존재하지 않게 되어 성능이 향상될 것입니다.