PostgreSQL에서 ClickHouse로 마이그레이션 — 데이터 마이그레이션

PostgreSQL에서 ClickHouse로 마이그레이션 — 데이터 마이그레이션 (1부)

이 가이드는 PostgreSQL에서 ClickHouse로 마이그레이션하는 방법을 다루는 시리즈의 1부예요. 실용 예시를 통해 실시간 복제(CDC) 접근으로 마이그레이션을 효율적으로 수행하는 방법을 보여줘요. 다루는 개념 중 상당수는 PostgreSQL에서 ClickHouse로의 수동 대량 데이터 전송에도 적용돼요.

출처: Migrating data

본문

이 문서는 PostgreSQL에서 ClickHouse로 마이그레이션하는 가이드의 1부예요. 실용 예시를 사용해서 실시간 복제(CDC) 접근으로 마이그레이션을 효율적으로 수행하는 방법을 보여줘요. 다루는 많은 개념은 PostgreSQL에서 ClickHouse로의 수동 대량 데이터 전송에도 적용돼요.

데이터셋 (Dataset)

Postgres에서 ClickHouse로의 일반적인 마이그레이션을 보여 주는 예시 데이터셋으로, 여기에 문서화된 Stack Overflow 데이터셋을 사용해요. 이 데이터셋은 2008년부터 2024년 4월까지 Stack Overflow에서 발생한 모든 post, vote, user, comment, badge를 담고 있어요. 이 데이터의 PostgreSQL 스키마는 아래와 같아요. PostgreSQL에서 테이블을 만들기 위한 DDL 명령은 여기에서 구할 수 있어요.

이 스키마는 가장 최적은 아닐 수 있지만, 기본 키, 외래 키, 파티셔닝, 인덱스를 포함한 많은 인기 있는 PostgreSQL 기능을 활용해요. 우리는 이 개념들을 각각 ClickHouse에 해당하는 것으로 마이그레이션할 거예요. 이 데이터셋을 PostgreSQL 인스턴스에 채워서 마이그레이션 단계를 테스트하고 싶은 사용자를 위해, DDL과 함께 pg_dump 포맷의 데이터를 다운로드용으로 제공했어요. 데이터 로드 명령은 아래와 같아요:

# users
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/users.sql.gz
gzip -d users.sql.gz
psql < users.sql

# posts
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/posts.sql.gz
gzip -d posts.sql.gz
psql < posts.sql

# posthistory
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/posthistory.sql.gz
gzip -d posthistory.sql.gz
psql < posthistory.sql

# comments
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/comments.sql.gz
gzip -d comments.sql.gz
psql < comments.sql

# votes
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/votes.sql.gz
gzip -d votes.sql.gz
psql < votes.sql

# badges
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/badges.sql.gz
gzip -d badges.sql.gz
psql < badges.sql

# postlinks
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/pdump/2024/postlinks.sql.gz
gzip -d postlinks.sql.gz
psql < postlinks.sql

ClickHouse에는 작지만 이 데이터셋은 Postgres에는 상당한 규모예요. 위 데이터는 2024년 첫 3개월을 다루는 부분집합을 나타내요.

우리의 예시 결과는 Postgres와 ClickHouse 사이의 성능 차이를 보여 주기 위해 전체 데이터셋을 사용하지만, 아래 문서화된 모든 단계는 더 작은 부분집합과 기능적으로 동일해요. 전체 데이터셋을 Postgres로 로드하려는 사용자는 여기를 참고하세요. 위 스키마가 부과하는 외래 키 제약 때문에 PostgreSQL용 전체 데이터셋은 참조 무결성을 만족하는 행만 담고 있어요. 그런 제약이 없는 Parquet 버전은 필요하면 ClickHouse에 쉽게 직접 로드할 수 있어요.

데이터 마이그레이션

실시간 복제 (CDC)

ClickPipes for PostgreSQL을 설정하려면 이 가이드를 참고하세요. 이 가이드는 다양한 유형의 소스 Postgres 인스턴스를 다뤄요. ClickPipes나 PeerDB를 사용한 CDC 접근으로, PostgreSQL 데이터베이스의 각 테이블이 ClickHouse에 자동 복제돼요. 업데이트와 삭제를 거의 실시간으로 처리하기 위해, ClickPipes는 ClickHouse에서 업데이트와 삭제를 처리하도록 특별히 설계된 ReplacingMergeTree 엔진으로 Postgres 테이블을 ClickHouse에 매핑해요.

ClickPipes로 데이터가 ClickHouse에 어떻게 복제되는지에 대한 더 많은 정보는 여기에서 확인할 수 있어요. CDC를 사용한 복제는 업데이트나 삭제 작업을 복제할 때 ClickHouse에 중복 행을 만든다는 점을 알아두는 게 중요해요. ClickHouse에서 그런 행을 처리하는 FINAL 수정자 사용 기법을 참고하세요.

ClickPipes를 사용해 users 테이블이 ClickHouse에서 어떻게 생성되는지 살펴볼게요:

CREATE TABLE users
(
    `id` Int32,
    `reputation` String,
    `creationdate` DateTime64(6),
    `displayname` String,
    `lastaccessdate` DateTime64(6),
    `aboutme` String,
    `views` Int32,
    `upvotes` Int32,
    `downvotes` Int32,
    `websiteurl` String,
    `location` String,
    `accountid` Int32,
    `_peerdb_synced_at` DateTime64(9) DEFAULT now64(),
    `_peerdb_is_deleted` Int8,
    `_peerdb_version` Int64
)
ENGINE = ReplacingMergeTree(_peerdb_version)
PRIMARY KEY id
ORDER BY id;

설정되면 ClickPipes가 PostgreSQL에서 ClickHouse로 모든 데이터 마이그레이션을 시작해요. 네트워크와 배포 크기에 따라 Stack Overflow 데이터셋에는 몇 분밖에 걸리지 않아요.

주기적 업데이트가 있는 수동 대량 로드

수동 접근을 사용하면 데이터셋의 초기 대량 로드를 다음으로 달성할 수 있어요:

  • 테이블 함수 — ClickHouse의 Postgres 테이블 함수를 사용해 Postgres에서 SELECT하고 ClickHouse 테이블에 INSERT하는 방식. 수백 GB 데이터셋까지의 대량 로드에 적합해요.
  • 내보내기 — CSV나 SQL 스크립트 파일 같은 중간 포맷으로 내보내기. 이 파일들은 클라이언트의 INSERT FROM INFILE 절로 또는 s3, gcs 같은 오브젝트 스토리지와 관련 함수를 사용해 ClickHouse로 로드할 수 있어요.

PostgreSQL에서 데이터를 수동으로 로드할 때는 먼저 ClickHouse에 테이블을 만들어야 해요. ClickHouse에서 테이블 스키마를 최적화하기 위해 동일하게 Stack Overflow 데이터셋을 사용하는 이 Data Modeling 문서를 참고하세요. PostgreSQL과 ClickHouse 사이의 데이터 타입은 다를 수 있어요. 각 테이블 컬럼에 동등한 타입을 확립하려면 Postgres 테이블 함수와 함께 DESCRIBE 명령을 사용할 수 있어요. 다음 명령은 PostgreSQL의 posts 테이블을 설명하며, 환경에 맞게 수정하세요:

DESCRIBE TABLE postgresql('<host>:<port>', 'postgres', 'posts', '<username>', '<password>')
SETTINGS describe_compact_output = 1

PostgreSQL과 ClickHouse 사이의 데이터 타입 매핑 개요는 부록 문서를 참고하세요. 이 스키마의 타입을 최적화하는 단계는 S3의 Parquet 같은 다른 소스에서 데이터를 로드했을 때와 동일해요. Parquet을 사용하는 이 대안 가이드에서 설명한 과정을 적용하면 다음 스키마가 나와요:

CREATE TABLE stackoverflow.posts
(
   `Id` Int32,
   `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   `AcceptedAnswerId` UInt32,
   `CreationDate` DateTime,
   `Score` Int32,
   `ViewCount` UInt32,
   `Body` String,
   `OwnerUserId` Int32,
   `OwnerDisplayName` String,
   `LastEditorUserId` Int32,
   `LastEditorDisplayName` String,
   `LastEditDate` DateTime,
   `LastActivityDate` DateTime,
   `Title` String,
   `Tags` String,
   `AnswerCount` UInt16,
   `CommentCount` UInt8,
   `FavoriteCount` UInt8,
   `ContentLicense`LowCardinality(String),
   `ParentId` String,
   `CommunityOwnedDate` DateTime,
   `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
COMMENT 'Optimized types'

간단한 INSERT INTO SELECT로 이 테이블을 채울 수 있어요. PostgresSQL에서 데이터를 읽어 ClickHouse에 삽입하는 거죠:

INSERT INTO stackoverflow.posts SELECT * FROM postgresql('<host>:<port>', 'postgres', 'posts', '<username>', '<password>')

0 rows in set. Elapsed: 146.471 sec. Processed 59.82 million rows, 83.82 GB (408.40 thousand rows/s., 572.25 MB/s.)

증분 로드는 차례로 스케줄링할 수 있어요. Postgres 테이블이 삽입만 받고 증가하는 id나 timestamp가 있다면, 위 테이블 함수 접근을 사용해 증분을 로드할 수 있어요. 즉 SELECT에 WHERE 절을 적용할 수 있죠. 이 접근은 업데이트가 같은 컬럼을 업데이트한다는 보장이 있다면 업데이트를 지원하는 데에도 사용될 수 있어요. 하지만 삭제를 지원하려면 전체 재로드가 필요하며, 테이블이 커질수록 이는 달성하기 어려울 수 있어요. CreationDate를 사용한 초기 로드와 증분 로드를 보여줄게요 (행이 업데이트되면 이것이 갱신된다고 가정):

-- initial load
INSERT INTO stackoverflow.posts SELECT * FROM postgresql('<host>', 'postgres', 'posts', 'postgres', '<password')

INSERT INTO stackoverflow.posts SELECT * FROM postgresql('<host>', 'postgres', 'posts', 'postgres', '<password') WHERE CreationDate > ( SELECT (max(CreationDate) FROM stackoverflow.posts)

ClickHouse는 =, !=, >, >=, <, <=, IN 같은 단순 WHERE 절을 PostgreSQL 서버로 푸시다운해요. 변경 집합을 식별하는 데 사용되는 컬럼에 인덱스가 있는지 확인하면 증분 로드를 더 효율적으로 만들 수 있어요.

쿼리 복제를 사용할 때 UPDATE 작업을 감지하는 가능한 방법은 XMIN 시스템 컬럼 (트랜잭션 ID)을 워터마크로 사용하는 것이에요 — 이 컬럼의 변화는 변경을 나타내므로 destination 테이블에 적용할 수 있어요. 이 접근을 사용하는 사용자는 XMIN 값이 래핑될 수 있고 비교에 전체 테이블 스캔이 필요해 변경 추적을 더 복잡하게 만든다는 점을 알아야 해요.

여기를 클릭해 2부로

더 알아보기 (Learn more)