PostgreSQL 테이블 엔진

PostgreSQL 테이블 엔진

PostgreSQL 엔진은 원격 PostgreSQL 서버에 저장된 데이터에 대해 SELECT와 INSERT 쿼리를 허용해요. 현재 테이블 엔진은 PostgreSQL 버전 12 이상만 지원해요.

ClickHouse Managed Postgres 서비스를 확인해보세요. compute와 물리적으로 같은 위치에 있는 NVMe 스토리지로 구동되며, EBS 같은 네트워크 연결 스토리지를 사용하는 대안과 비교해 디스크 바운드 워크로드에서 최대 10배 빠른 성능을 제공하고, ClickPipes의 Postgres CDC 커넥터로 Postgres 데이터를 ClickHouse에 복제할 수 있게 해줘요.

출처: 문서

본문

테이블 생성하기

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = PostgreSQL({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})
SETTINGS
    [ postgresql_connection_pool_size=16, ]
    [ postgresql_connection_pool_wait_timeout=5000, ]
    [ postgresql_connection_pool_retries=2, ]
    [ postgresql_connection_pool_auto_close_connection=false, ]
    [ postgresql_connection_attempt_timeout=2 ]
;

CREATE TABLE 쿼리에 대한 자세한 설명은 해당 문서를 참고해요. 테이블 구조는 원본 PostgreSQL 테이블 구조와 달라질 수 있어요.

• 컬럼 이름은 원본 PostgreSQL 테이블과 같아야 하지만, 그중 일부 컬럼만 어떤 순서로든 사용할 수 있어요
• 컬럼 타입은 원본 PostgreSQL 테이블과 달라질 수 있어요. ClickHouse는 값을 cast하여 ClickHouse 데이터 타입으로 변환하려 해요
• external_table_functions_use_nulls 설정은 Nullable 컬럼을 처리하는 방식을 정의해요. 기본값: 1. 0이면 테이블 함수는 Nullable 컬럼을 만들지 않고 null 대신 기본값을 삽입해요. 이는 배열 내부의 NULL 값에도 적용돼요

엔진 매개변수

• host:port — PostgreSQL 서버 주소
• database — 원격 데이터베이스 이름
• table — 원격 테이블 이름, 또는 PostgreSQL에 그대로 전달되는 쿼리(테이블 이름 대신 쿼리 전달 참고)
• user — PostgreSQL 사용자
• password — 사용자 비밀번호
• schema — 기본이 아닌 테이블 스키마. 선택
• on_conflict — 충돌 해결 전략. 예: ON CONFLICT DO NOTHING. 선택. 참고: 이 옵션을 추가하면 삽입 효율이 떨어져요

Named collections(버전 21.11부터 사용 가능)은 프로덕션 환경에 권장돼요. 예시:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <schema>schema1</schema>
    </postgres_creds>
</named_collections>

일부 매개변수는 키-값 인자로 재정의할 수 있어요.

SELECT * FROM postgresql(postgres_creds, table='table1');

TLS/SSL

TLS/SSL 매개변수는 libpq로 전달되며 named collection 키 또는 뒤따르는 키-값 인자로 설정할 수 있어요: sslmode(disable, allow, prefer, require, verify-ca, verify-full), 그리고 두 가지 형태 중 하나로 인증서와 키. 설정하지 않으면 libpq 기본값이 적용돼요 (sslmode=prefer).

• sslrootcert (CA 인증서 또는 특수 값 system), sslcert (클라이언트 인증서), sslkey (클라이언트 개인 키)는 서버 로컬 파일의 경로예요. 서버 구성 파일에 정의된 named collection에서만 지정할 수 있고 쿼리에서 재정의할 수 없어요: 서버가 자신의 권한으로 파일을 열어요
• sslrootcert_pem, sslcert_pem, sslkey_pem은 경로 대신 해당 파일의 리터럴 내용을 받아들여요. 쿼리, SQL로 만든 named collection, named collection 재정의 등 어디에서나 지정할 수 있고 비밀번호처럼 로그와 SHOW 쿼리에서 마스킹돼요

예를 들어 암호화 연결을 요구하고 서버 인증서를 검증하려면:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <sslmode>verify-full</sslmode>
        <sslrootcert>/etc/clickhouse-server/postgresql-ca.crt</sslrootcert>
    </postgres_creds>
</named_collections>

구성 파일 없이, 쿼리에서 인증서 내용을 전달하는 같은 경우:

CREATE TABLE postgres_table (id UInt64, value String)
ENGINE = PostgreSQL('localhost:5432', 'database', 'table', 'user', 'password',
                    sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

설정

PostgreSQL 테이블 엔진(및 postgresql 테이블 함수)이 사용하는 연결 풀은 SETTINGS 절로 테이블마다 구성할 수 있어요. 설정을 지정하지 않으면 해당 쿼리 레벨 postgresql_* 설정의 값이 기본값이 돼요.

postgresql_connection_pool_size

연결 풀 크기 (모든 연결이 사용 중이면 쿼리는 일부 연결이 해제될 때까지 기다려요). 0이 아니어야 해요.

기본값: 16.

postgresql_connection_pool_wait_timeout

빈 풀에서 연결 풀 push/pop 타임아웃(밀리초). 0은 빈 풀에서 차단된다는 뜻이에요.

기본값: 5000.

postgresql_connection_pool_retries

연결 풀 push/pop 재시도 횟수.

기본값: 2.

postgresql_connection_pool_auto_close_connection

풀에 반환하기 전에 연결을 닫아요.

기본값: false.

postgresql_connection_attempt_timeout

PostgreSQL 엔드포인트에 연결하는 단일 시도의 연결 타임아웃(초). 이 값은 연결 URL의 connect_timeout 매개변수로 전달돼요.

기본값: 2.

예시:

CREATE TABLE pg_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
SETTINGS postgresql_connection_pool_size = 32, postgresql_connection_pool_auto_close_connection = 1;

구현 세부 사항

PostgreSQL 측의 SELECT 쿼리는 각 SELECT 쿼리 후 커밋하는 읽기 전용 PostgreSQL 트랜잭션 안에서 COPY (SELECT ...) TO STDOUT로 실행돼요. =, !=, >, >=, <, <=, IN 같은 단순한 WHERE 절은 PostgreSQL 서버에서 실행돼요. 모든 조인, 집계, 정렬, IN [ array ] 조건과 LIMIT 샘플링 제약은 PostgreSQL에 대한 쿼리가 끝난 뒤에만 ClickHouse에서 실행돼요.

테이블 이름 대신 쿼리 전달하기

테이블 이름 대신 table 인자는 PostgreSQL에 그대로 전달되는 SELECT 쿼리일 수 있어요. 테이블 구조는 쿼리 결과에서 추론돼요. 쿼리는 서브쿼리로 쓰거나 query 함수로 감쌀 수 있어요.

CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

이것은 조인, 집계나 다른 처리를 PostgreSQL로 푸시다운하는 데 유용해요. 그런 테이블은 읽기 전용이에요: INSERT가 허용되지 않아요. 같은 문법은 postgresql 테이블 함수에서도 지원돼요.

서브쿼리 형식 (SELECT ...)는 ClickHouse가 파싱하고 PostgreSQL 방언(PostgreSQL 식별자 따옴표와 문자열 리터럴 이스케이프)으로 재직렬화한 다음 서버로 보내요. 따라서 유효한 ClickHouse SQL이어야 해요. ClickHouse가 파싱하지 않는 PostgreSQL 고유 문법을 전달하려면 query('...') 형식을 사용하는데, 그 텍스트는 PostgreSQL에 그대로 전송돼요.

주변 ClickHouse 쿼리의 외부 WHERE, LIMIT, 집계 등은 전달된 쿼리로 푸시다운되지 않아요 — 전체 쿼리 결과를 가져온 뒤 ClickHouse에서 적용돼요. PostgreSQL에서 읽는 데이터를 제한하려면 필터를 전달된 쿼리 안에 넣어요. external_table_strict_query = 1이면 이 테이블의 컬럼에 대한 외부 필터는 로컬로 적용되는 대신 예외로 거부돼요. 전달된 쿼리로 푸시할 수 없기 때문이에요. 이 검사는 최상위 WHERE 조건과 최상위 AND의 각 결합(conjunct)을 다뤄요. 이 테이블의 컬럼에 대한 PREWHERE는 이 설정의 대상이 아니에요: 이 테이블 엔진은 PREWHERE를 지원하지 않으며, 설정과 관계없이 그런 쿼리는 ILLEGAL_PREWHERE로 거부돼요. 검사는 필터를 푸시다운할 수 있는 곳에서만 실행돼요: 이 테이블이 쿼리의 유일한 테이블일 때, INNER JOIN의 어느 쪽일 때, 또는 외부 조인의 보존(preserving) 쪽(LEFT JOIN의 왼쪽, RIGHT JOIN의 오른쪽)일 때요. LEFT/RIGHT JOIN의 비보존 쪽과 FULL JOIN의 어느 쪽에서는 아무것도 푸시다운되지 않고 검사도 되지 않으므로, 이 테이블의 컬럼에 대한 필터는 strict 모드에서도 조인 후 로컬로 적용돼요. 검사가 실행되는 곳에서 주변 쿼리에 조인된 다른 테이블을 참조하는 조건은 푸시다운되지 않고 검사에서 제외돼요. 조인된 쪽만 참조하든 OR 같은 하나의 비-AND 표현식 안에서 이 테이블과 섞든 상관없이요. 그런 조건은 평소 ClickHouse 평가 시점(조인 후 WHERE, 그 전 PREWHERE)을 유지하고 거부되지 않아요.

PostgreSQL 측의 INSERT 쿼리는 각 INSERT 문 후 자동 커밋되는 PostgreSQL 트랜잭션 안에서 COPY "table_name" (field1, field2, ... fieldN) FROM STDIN으로 실행돼요.

PostgreSQL Array 타입은 ClickHouse 배열로 변환돼요. 주의: PostgreSQL에서 type_name[]처럼 만들어진 배열 데이터는 같은 컬럼의 다른 테이블 행에 다른 차원의 다차원 배열을 포함할 수 있어요. 그러나 ClickHouse에서는 같은 컬럼의 모든 테이블 행에서 같은 차원 수의 다차원 배열만 허용돼요.

|로 나열되는 여러 복제본을 지원해요. 예:

CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');

PostgreSQL 딕셔너리 소스에 대한 복제본 우선순위가 지원돼요. 맵의 숫자가 클수록 우선순위가 낮아요. 가장 높은 우선순위는 0이에요.

아래 예시에서 복제본 example01-1이 가장 높은 우선순위를 가져요:

<postgresql>
    <port>5432</port>
    <user>clickhouse</user>
    <password>qwerty</password>
    <replica>
        <host>example01-1</host>
        <priority>1</priority>
    </replica>
    <replica>
        <host>example01-2</host>
        <priority>2</priority>
    </replica>
    <db>db_name</db>
    <table>table_name</table>
    <where>id=10</where>
    <invalidate_query>SQL_QUERY</invalidate_query>
</postgresql>

사용 예시

PostgreSQL의 테이블

postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));

CREATE TABLE

postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1

postgresql> SELECT * FROM test;
int_id | int_nullable | float | str  | float_nullable
--------+--------------+-------+------+----------------
       1 |              |     2 | test |
(1 row)

ClickHouse에서 테이블 만들고, 위에서 만든 PostgreSQL 테이블에 연결하기

이 예시는 PostgreSQL 테이블 엔진을 사용해 ClickHouse 테이블을 PostgreSQL 테이블에 연결하고 PostgreSQL 데이터베이스에 SELECT와 INSERT 문을 모두 사용해요.

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

SELECT 쿼리를 사용해 PostgreSQL 테이블의 초기 데이터를 ClickHouse 테이블에 삽입하기

postgresql 테이블 함수는 PostgreSQL에서 ClickHouse로 데이터를 복사하는데, 이는 PostgreSQL보다 ClickHouse에서 데이터를 조회하거나 분석해 쿼리 성능을 향상시키는 데 자주 사용되며, PostgreSQL에서 ClickHouse로 데이터를 마이그레이션하는 데도 사용할 수 있어요. PostgreSQL에서 ClickHouse로 데이터를 복사할 것이므로 ClickHouse에서 MergeTree 테이블 엔진을 사용하고 postgresql_copy라고 호출할게요.

CREATE TABLE default.postgresql_copy
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = MergeTree
ORDER BY (int_id);

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

PostgreSQL 테이블에서 ClickHouse 테이블로 증분 데이터 삽입하기

초기 삽입 후 PostgreSQL 테이블과 ClickHouse 테이블 사이의 지속적인 동기화를 수행한다면 ClickHouse에서 WHERE 절을 사용해 타임스탬프나 고유 시퀀스 ID를 기준으로 PostgreSQL에 추가된 데이터만 삽입할 수 있어요. 그러려면 이전에 추가된 최대 ID나 타임스탬프를 추적해야 해요. 예:

SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;

그런 다음 최대값보다 큰 PostgreSQL 테이블의 값을 삽입해요.

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
WHERE int_id > (SELECT max(int_id) FROM default.postgresql_copy);

결과 ClickHouse 테이블에서 데이터 선택하기

SELECT * FROM postgresql_copy WHERE str IN ('test');

┌─float_nullable─┬─str──┬─int_id─┐
│           ᴺᵁᴸᴸ │ test │      1 │
└────────────────┴──────┴────────┘

기본이 아닌 스키마 사용하기

postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');

함께 보기

• postgresql 테이블 함수
• PostgreSQL을 딕셔너리 소스로 사용

관련 콘텐츠

• 블로그: ClickHouse and PostgreSQL - a match made in data heaven - part 1
• 블로그: ClickHouse and PostgreSQL - a Match Made in Data Heaven - part 2

더 알아보기 (Learn more)

• postgresql 테이블 함수
• PostgreSQL을 딕셔너리 소스로 사용