PostgreSQL 확장

postgres 확장은 DuckDB가 실행 중인 PostgreSQL 데이터베이스 인스턴스에서 데이터를 직접 읽고 쓸 수 있게 해줘요. 데이터는 기저 PostgreSQL 데이터베이스에서 직접 쿼리할 수 있고, PostgreSQL 테이블에서 DuckDB 테이블로 또는 그 반대로 로드할 수 있답니다. 함께 알아볼까요?

출처: 문서

본문

postgres 확장은 DuckDB가 실행 중인 PostgreSQL 데이터베이스 인스턴스에서 데이터를 직접 읽고 쓸 수 있게 해줘요. 데이터는 기저 PostgreSQL 데이터베이스에서 직접 쿼리할 수 있어요. PostgreSQL 테이블에서 DuckDB 테이블로, 또는 그 반대로 데이터를 로드할 수 있어요. 구현 세부 사항과 배경은 [공식 발표]({% post_url 2022-09-30-postgres-scanner %})를 참고하세요.

설치와 로드 (Installing and Loading)

postgres 확장은 첫 사용 시 공식 확장 저장소에서 투명하게 [자동 로드]({% link docs/current/extensions/overview.md %}#autoloading-extensions)돼요. 직접 설치하고 로드하려면 다음을 실행하세요:

INSTALL postgres;
LOAD postgres;

연결 (Connecting)

PostgreSQL 데이터베이스를 DuckDB에서 접근 가능하게 하려면 postgres 또는 postgres_scanner 타입과 함께 ATTACH 명령을 사용하세요.

localhost에서 실행 중인 PostgreSQL 인스턴스의 public 스키마에 읽기-쓰기 모드로 연결하려면:

ATTACH '' AS postgres_db (TYPE postgres);

주어진 파라미터로 PostgreSQL 인스턴스에 읽기 전용 모드로 연결하려면:

ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE postgres, READ_ONLY);

기본적으로 모든 스키마가 연결돼요. 큰 인스턴스로 작업할 때는 특정 스키마만 연결하는 것이 유용할 수 있어요. 이는 SCHEMA 명령으로 할 수 있어요.

ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE postgres, SCHEMA 'public');

더 이상 사용되지 않음 (Deprecated) 옛 postgres_attach 함수는 더 이상 사용되지 않아요. 새 ATTACH 구문으로 전환하는 것이 권장돼요.

구성 (Configuration)

ATTACH 명령은 입력으로 libpq 연결 문자열이나 PostgreSQL URI를 받아요.

아래는 몇 가지 예시 연결 문자열과 흔히 사용되는 파라미터예요. 사용 가능한 파라미터의 전체 목록은 PostgreSQL 문서에서 찾을 수 있어요.

dbname=postgresscanner
host=localhost port=5432 dbname=mydb connect_timeout=10

Name	Description	Default
`dbname`	데이터베이스 이름	[user]
`host`	연결할 호스트 이름	`localhost`
`hostaddr`	호스트 IP 주소	`localhost`
`passfile`	패스워드가 저장된 파일 이름	`~/.pgpass`
`password`	PostgreSQL 패스워드	(empty)
`port`	포트 번호	`5432`
`user`	PostgreSQL 사용자 이름	current user

예시 URI는 postgresql://username@hostname/dbname이에요.

Secrets로 구성 (Configuring via Secrets)

PostgreSQL 연결 정보는 [secrets]({% link docs/current/configuration/secrets_manager.md %})로도 지정할 수 있어요. 자세한 내용은 [해당 페이지]({% link docs/current/core_extensions/postgres/secrets.md %})를 참고하세요.

환경 변수로 구성 (Configuring via Environment Variables)

PostgreSQL 연결 정보는 환경 변수로도 지정할 수 있어요. 이것은 연결 정보가 외부에서 관리되어 환경에 전달되는 프로덕션 환경에서 유용할 수 있어요.

export PGPASSWORD="secret"
export PGHOST=localhost
export PGUSER=owner
export PGDATABASE=mydatabase

그런 다음 연결하려면 duckdb 프로세스를 시작하고 다음을 실행하세요:

ATTACH '' AS p (TYPE postgres);

사용법 (Usage)

PostgreSQL 데이터베이스의 테이블은 일반 DuckDB 테이블처럼 읽을 수 있지만, 기저 데이터는 쿼리 시점에 PostgreSQL에서 직접 읽혀요.

SHOW ALL TABLES;

name
uuids

SELECT * FROM uuids;

u
6d3d2541-710b-4bde-b3af-4711738636bf
NULL
00000000-0000-0000-0000-000000000001
ffffffff-ffff-ffff-ffff-ffffffffffff

특히 큰 테이블의 경우, 시스템이 PostgreSQL에서 테이블을 계속 다시 읽는 것을 막기 위해 PostgreSQL 데이터베이스의 사본을 DuckDB에 만드는 것이 바람직할 수 있어요.

표준 SQL을 사용해 PostgreSQL에서 DuckDB로 데이터를 복사할 수 있어요, 예를 들어:

CREATE TABLE duckdb_table AS FROM postgres_db.postgres_tbl;

PostgreSQL에 데이터 쓰기 (Writing Data to PostgreSQL)

PostgreSQL에서 데이터를 읽는 것 외에도, 확장은 표준 SQL 쿼리로 테이블을 만들고, 데이터를 PostgreSQL에 넣고, PostgreSQL 데이터베이스를 수정할 수 있게 해줘요.

이를 통해 예를 들어 PostgreSQL 데이터베이스에 저장된 데이터를 Parquet로 내보내거나, Parquet 파일의 데이터를 PostgreSQL로 읽어 들이는 데 DuckDB를 사용할 수 있어요.

아래는 PostgreSQL에 새 테이블을 만들고 데이터를 로드하는 간단한 예시예요.

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres);
CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);
INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');

PostgreSQL 테이블에 대한 많은 연산이 지원돼요. 이 모든 연산은 PostgreSQL 데이터베이스를 직접 수정하고, 이후 연산의 결과는 PostgreSQL을 사용해 읽을 수 있어요. 수정을 원하지 않으면 ATTACH를 READ_ONLY 속성으로 실행해서 기저 데이터베이스의 수정을 막을 수 있어요. 예를 들어:

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres, READ_ONLY);

아래는 지원되는 연산 목록이에요.

`CREATE TABLE`

CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);

`INSERT INTO`

INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');

`SELECT`

SELECT * FROM postgres_db.tbl;

id	name
42	DuckDB

`COPY`

PostgreSQL과 DuckDB 사이에서 테이블을 양방향으로 복사할 수 있어요:

COPY postgres_db.tbl TO 'data.parquet';
COPY postgres_db.tbl FROM 'data.parquet';

이 복사는 PostgreSQL 이진 와이어 인코딩을 사용해요. DuckDB는 이 인코딩으로 데이터를 파일에 쓸 수도 있는데, 직접 연결 관리를 하고 싶다면 선택한 클라이언트로 PostgreSQL에 로드할 수 있어요:

COPY 'data.parquet' TO 'pg.bin' WITH (FORMAT postgres_binary);

생성된 파일은 DuckDB를 사용해 파일을 PostgreSQL에 복사한 다음 psql이나 다른 클라이언트로 PostgreSQL에서 덤프한 것과 동일한 결과예요:

DuckDB:

COPY postgres_db.tbl FROM 'data.parquet';

PostgreSQL:

\copy tbl TO 'data.bin' WITH (FORMAT BINARY);

[COPY FROM DATABASE 문]({% link docs/current/sql/statements/copy.md %}#copy-from-database--to)으로 데이터베이스의 전체 사본을 만들 수도 있어요:

COPY FROM DATABASE postgres_db TO my_duckdb_db;

`UPDATE`

UPDATE postgres_db.tbl
SET name = 'Woohoo'
WHERE id = 42;

`DELETE`

DELETE FROM postgres_db.tbl
WHERE id = 42;

`ALTER TABLE`

ALTER TABLE postgres_db.tbl
ADD COLUMN k INTEGER;

`DROP TABLE`

DROP TABLE postgres_db.tbl;

`CREATE VIEW`

CREATE VIEW postgres_db.v1 AS SELECT 42;

`CREATE SCHEMA` / `DROP SCHEMA`

CREATE SCHEMA postgres_db.s1;
CREATE TABLE postgres_db.s1.integers (i INTEGER);
INSERT INTO postgres_db.s1.integers VALUES (42);
SELECT * FROM postgres_db.s1.integers;

i
42

DROP SCHEMA postgres_db.s1;

`DETACH`

DETACH postgres_db;

트랜잭션 (Transactions)

CREATE TABLE postgres_db.tmp (i INTEGER);
BEGIN;
INSERT INTO postgres_db.tmp VALUES (42);
SELECT * FROM postgres_db.tmp;

이것은 다음을 반환해요:

i
42

ROLLBACK;
SELECT * FROM postgres_db.tmp;

이것은 빈 테이블을 반환해요.

PostgreSQL에서 SQL 쿼리 실행 (Running SQL Queries in PostgreSQL)

`postgres_query` 테이블 함수 (The `postgres_query` Table Function)

postgres_query 테이블 함수는 연결된 데이터베이스 안에서 임의의 읽기 쿼리를 실행할 수 있게 해줘요. postgres_query는 쿼리를 실행할 연결된 PostgreSQL 데이터베이스의 이름과, 실행할 SQL 쿼리를 받아요. 쿼리 결과가 반환돼요. 작은따옴표 문자열은 작은따옴표를 두 번 반복해서 이스케이프돼요.

postgres_query(attached_database::VARCHAR, query::VARCHAR)

예를 들어:

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres);
SELECT * FROM postgres_query('postgres_db', 'SELECT * FROM cars LIMIT 3');

brand	model	color
Ferrari	Testarossa	red
Aston Martin	DB2	blue
Bentley	Mulsanne	gray

`postgres_execute` 함수 (The `postgres_execute` Function)

postgres_execute 함수는 PostgreSQL에서 임의의 쿼리를 실행할 수 있게 해주며, 데이터베이스의 스키마와 내용을 업데이트하는 문장도 포함해요.

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres);
CALL postgres_execute('postgres_db', 'CREATE TABLE my_table (i INTEGER)');

설정 (Settings)

확장은 다음 구성 파라미터를 노출해요.

Name	Description	Default
`pg_array_as_varchar`	PostgreSQL 배열을 varchar로 읽기 - 혼합 차원 배열 읽기를 가능하게 함	`false`
`pg_connection_cache`	연결 캐시를 사용할지 여부	`true`
`pg_connection_limit`	최대 동시 PostgreSQL 연결 수	`64`
`pg_debug_show_queries`	DEBUG SETTING: PostgreSQL로 보내는 모든 쿼리를 stdout에 출력	`false`
`pg_experimental_filter_pushdown`	필터 푸시다운 사용 여부 (현재 실험적)	`true`
`pg_pages_per_task`	태스크당 페이지 수	`1000`
`pg_use_binary_copy`	데이터를 읽기 위해 BINARY copy 사용 여부	`true`
`pg_null_byte_replacement`	Postgres에 NULL 바이트를 쓸 때 주어진 문자로 대체	`NULL`
`pg_use_ctid_scan`	테이블 ctid를 사용해 스캔을 병렬화할지 여부	`true`

스키마 캐시 (Schema Cache)

PostgreSQL에서 스키마 데이터를 계속 가져오는 것을 피하기 위해 DuckDB는 테이블 이름, 컬럼 등 같은 스키마 정보를 캐시해요. PostgreSQL 인스턴스에 대한 다른 연결을 통해(예: 테이블에 새 컬럼이 추가되는 등) 스키마에 변경이 있으면 캐시된 스키마 정보가 오래될 수 있어요. 이 경우 pg_clear_cache 함수를 실행해 내부 캐시를 비울 수 있어요.

CALL pg_clear_cache();

버전 1.5.5에서는 "staleness query"를 사용한 스키마 변경 자동 감지 지원이 추가됐어요 (Brandon Freeman이 duckdb/duckdb-postgres#514에서 기여):

pg_staleness_query_enabled 옵션(BOOLEAN, 기본값: FALSE)이 활성화되면, 카탈로그 접근 시마다 모든 테이블의 Postgres pg_class.xmin 컬럼 값을 확인하는 쿼리가 실행되고, 변경이 감지되면 캐시를 자동으로 다시 로드해요
Postgres-wire 호환 데이터베이스의 경우 pg_staleness_query 옵션으로 커스텀 "staleness query"를 설정할 수 있어요.

hstore 컬럼 작업 (Working with hstore Columns)

DuckDB는 hstore 컬럼의 데이터를 그 텍스트 표현의 VARCHAR로 반환해요, 예: key=>value, foo=>bar. 주어진 키의 값을 읽으려면 [postgres_hstore_get]({% link docs/current/core_extensions/postgres/functions.md %}#postgres_hstore_get) 함수를, 전체 키/값 쌍 집합을 후속 처리를 위해 JSON으로 변환하려면 [postgres_hstore_to_json]({% link docs/current/core_extensions/postgres/functions.md %}#postgres_hstore_to_json)을 사용할 수 있어요.

SELECT postgres_hstore_get('a=>b, c=>d', 'a');
-- b

SELECT postgres_hstore_get('a=>b, c=>d', 'missingkey');
-- NULL

SELECT postgres_hstore_to_json('a=>b, c=>d, e => null');
-- {"a": "b", "c": "d", e: null}

PostgreSQL 확장