데이터 레이아웃 및 액세스 패턴의 최상의 사례

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

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

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

동시에 많은 트랜잭션이 동일한 행을 업데이트하려고 시도하는 경우 그것을 피하십시오.

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

이러한 패턴은 종종 각 요청에 대해 집계가 너무 비용이 많이 들기 때문에 데이터베이스에 누산 금액이 유지되어 발생합니다. 그러한 집계가 필요한 경우, 단일 행에 누산 총계와 최근에 추가된 데이터와 같은 작은 작업 집합을 유지하는 대안을 고려하십시오.

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

넓은 테이블

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의 업데이트는 삭제 및 삽입의 조합으로 구현됩니다. 따라서 거의 사용되지 않는 열일지라도 각 업데이트마다 반복되어 복사됩니다. 이는 생성된 Write Ahead Log (WAL)의 양에 영향을 미칩니다.
  • 자주 업데이트되는 열이 있는 경우, 각 업데이트는 모든 테이블 열이 복사되게 합니다. 이는 다시 생성된 WAL의 증가 및 자동 진공에 더 많은 작업을 만들어냅니다.
  • PostgreSQL은 행 또는 튜플로 데이터를 저장합니다. 넓은 행은 페이지당 튜플 수를 줄이고 이는 읽기 성능에 영향을 줍니다.

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

이러한 추출과 함께 발생하는 교환은 색인만 스캔하는 것이 더 이상 가능하지 않다는 것입니다. 대신, 응용 프로그램은 새로운 테이블을 결합하거나 추가 쿼리를 실행해야 합니다. 이러한 성능 영향은 수직 테이블 분할의 이점과 저스트라 고려되어야 합니다.

이 주제에 대한 매우 좋은 에피소드가 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)
    • 응용 프로그램을 새 테이블에서 읽도록 업데이트하고, 데이터가 아직 없는 경우 원래 열로 되돌아갑니다.
    • 새 테이블에 데이터를 채우기 시작
  2. N 릴리스 예시
    • 백그라운드 마이그레이션을 완료하여 데이터를 채우기 시작. 필수 업그레이드 중지(필수 업그레이드 중지여기를 참조) 이후의 다음 릴리스에서 수행되어야 합니다.
  3. N + 1 릴리스 예시
    • 응용 프로그램을 새 테이블에서만 읽고 쓰도록 업데이트
    • 원래 열을 무시합니다. 이는 가이드에 설명된 대로 데이터베이스 열을 안전하게 제거하는 프로세스를 시작합니다.
  4. N + 2 릴리스 예시
    • 원래 열을 삭제합니다.
  5. N + 3 릴리스 예시
    • 원래 열에 대한 무시 규칙을 제거합니다.

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