FDW(Foreign Data Wrapper)는 원격지(Remote)에 있는 데이터베이스에 접근해 데이터를 읽고 쓰기 위한 PostgreSQL 확장을 말합니다. 2003년에 발표된 SQL 규칙 SQL/MED(SQL Management of External Data – 외부 데이터 관리를 위한 규칙)을 지원하기 위해 PostgreSQL 9.1 버전부터 개발되고 있는 기능입니다.
FDW에는 Remote 서버에 있는 관계형 데이터베이스(RDBMS)에 접근해 데이터를 가져오는 타입 외에도 NoSQL 데이터베이스, 파일 등 여러 타입이 있습니다만, 본 문서에서는 원격지의 관계형 데이터베이스, 그 중에서도 같은 PostgreSQL DBMS에 접근해 데이터를 가져오는 확장인 postgres-fdw를 예로 들어 FDW의 구조와 동작 방식에 대해 설명하고자 합니다.
References
https://www.interdb.jp/pg/pgsql04.html
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
https://www.postgresql.org/docs/current/postgres-fdw.html
기본적인 사용법
postgres-fdw는 원격 서버에 위치한 PostgreSQL 서버에서 로컬로 데이터를 가져오는 기능을 지원하는 C 언어로 작성된 PostgreSQL 확장입니다. PostgreSQL 확장 이므로 로컬 데이터베이스에서 사용하기에 앞서 로딩하는 과정을 거쳐야 합니다.
CREATE EXTENSION postgres_fdw;
필요한 라이브러리가 로딩되었으면, 접속할 원격 Postgres 서버와 해당 서버의 사용자 정보를 정의해야 합니다. 아래 예시는 호스트 remote_host의 5432번 포트에 동작중인 Postgres 서버의 remote_db 데이터베이스에 접근하기 위한 서버 및 사용자 매핑 정보를 생성하는 과정입니다.
CREATE SERVER remote_pg_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (HOST ‘remote_host’, PORT ‘5432’, DBNAME ‘remote_db’);CREATE USER MAPPING FOR CURRENT_USER
SERVER remote_pg_server
OPTIONS (USER ‘remote_user’, PASSWORD ‘remote_pass’);
원격 서버를 정의할 때 사용할 수 있는 주요 옵션들은 다음과 같습니다.
Foreign Server Options
| Parameter 이름 | 설명 |
| host | 원격 PostgreSQL 서버의 Host 주소입니다. |
| port | 원격 PostgreSQL 서버의 Port 번호입니다. |
| dbname | 원격 PostgreSQL 서버의 Database 이름입니다. |
| connect_timeout | 만들어진 Connection을 유지할 Timeout입니다. 단위는 Second 입니다. |
| use_remote_estimate | 원격으로 EXPLAIN 명령어를 실행해 Cost Optimization을 할 지 여부를 설정합니다. 기본값은 False (사용하지 않음) 입니다. |
| fdw_startup_cost | 모든 Foreign Table을 대상으로 한 Scan에 있어서 적용되는 startup cost의 가중치입니다. 원격 서버를 대상으로 커넥션을 생성하고, 쿼리를 보내기에 앞서 Parsing 하는 동작 등을 고려한 값입니다. 기본값은 100 입니다. |
| fdw_tuple_cost | 모든 Foreign Table을 대상으로 한 Scan에 있어서 적용되는 튜플 당 cost의 가중치입니다. 네트워크를 통해 스캔한 Tuple을 가져오는 것을 고려한 값입니다. 기본값은 0.01 입니다. |
원격 서버에 있는 DBMS에 정의된 테이블에 접근해 데이터를 읽거나 쓰기 위해서는 해당 테이블의 Column 타입과 구조를 먼저 정의해야 합니다. 이렇게 정의한 원격 서버에 있는 DBMS 테이블을 로컬 테이블과 구분하기 위해 Foreign Table(외래 테이블)이라고 부릅니다.
아래 예시는 원격 서버의 remote_tbl이라는 이름의 테이블에 접근하기 위해 foreign_tbl이라는 이름의 Foreign Table을 내 로컬에서 정의해주는 과정입니다.
CREATE FOREIGN TABLE foreign_tbl (
c1 int,
c2 varchar,
c3 date
) SERVER remote_pg_server
OPTIONS (TABLE_NAME ‘remote_tbl’);
column_name 옵션을 이용하면 원격 테이블과 로컬 Foreign Table의 Column 이름을 다르게 정의해서 사용할 수도 있습니다.
CREATE FOREIGN TABLE foreign_tbl_2 (
c1 int,
v2 varchar OPTIONS (column_name ‘c2’) NULL
) SERVER remote_pg_server
OPTIONS (TABLE_NAME ‘remote_tbl’);
이렇게 생성한 Foreign Table은 내 로컬에 있는 Table 처럼 사용할 수 있습니다.
SELECT c1, c2, c3 FROM foreign_tbl;
SELECT a.c1 FROM foreign_tbl a
JOIN local_tbl b
ON a.c1 = b.c1;— Foreign Table에 새로운 Row를 삽입
INSERT INTO foreign_tbl VALUES (
42, ‘text’, ‘2023-03-10’
);— Foreign Table의 Row를 업데이트
UPDATE foreign_tbl
SET c2 = ‘Varchar’
WHERE c1 = 42;— Foreign Table의 Row를 삭제
DELETE FROM foreign_tbl
WHERE c1 = 42;— 로컬 서버의 Foreign Table 정의를 바꾸는 DML
ALTER FOREIGN TABLE foreign_tbl
ALTER COLUMN c3 TYPE varchar;ALTER FOREIGN TABLE foreign_tbl
DROP COLUMN c3;ALTER FOREIGN TABLE foreign_tbl
ADD COLUMN c3 date;
원격 서버의 Schema 가져오기
앞서 보여드린 예제와 같이 Foreign Table들을 하나하나 정의하는 방식으로도 원격 서버에서 데이터를 가져올 수 있지만, 정의하고자 하는 테이블이 많은 경우에는 Schema 단위로 원격 서버의 테이블 정의를 그대로 가져오는 게 더 편할 수 있습니다. IMPORT FOREIGN SCHEMA 명령어를 사용합니다.
아래 예시는 원격 서버 remote_pg_server 의 스키마 remote_schema 에 정의된 모든 테이블을 로컬 스키마 local_schema 에 Foreign Table로 정의하는 SQL 구문입니다.
IMPORT FOREIGN SCHEMA remote_schema
FROM SERVER remote_pg_server
INTO local_schema;
옵션 LIMIT TO 를 이용하면 특정 Table 들만을 가져올 수 있습니다.
아래 예제는 remote_schema에서 테이블 payment, actor만을 가져옵니다.
IMPORT FOREIGN SCHEMA remote_schema
EXCEPT (rental, inventory)
FROM SERVER remote_pg_server
INTO local_schema;
FDW의 동작 방식
FDW를 이용해 Foreign Table을 대상으로 쿼리를 실행할 때, 내 로컬 Postgres 서버와 원격 Postgres 서버에서 이루어지는 작업들을 Flow Chart로 나타내면 아래 그림과 같습니다.
- Parser와 Anaylzer를 거치며 SQL Statement를 Query Tree로 변환합니다.
use_remote_estimate옵션이 활성화 된 경우, Planner는 원격 서버에 연결합니다.use_remote_estimate옵션이 활성화 된 경우, Planner가 원격 서버에서EXPLAIN명령어를 실행하여 Cost를 계산합니다.use_remote_estimate옵션이 활성화 되어 있지 않은 경우, Planner는 로컬에 저장된 Foreign Table의 통계 정보를 바탕으로 Cost를 계산합니다.- Planner는 deparsing 과정을 거쳐 Plan Tree를 다시 평문 SQL statement로 변환하여 Executor에 전달합니다.
- Executor는 원격 서버에 SQL statement를 전달하고 결과를 반환받습니다.
use_remote_estimate 옵션은 원격 서버를 대상으로 쿼리를 수행하기에 앞서, EXPLAIN 명령어를 이용해 Cost를 원격 서버에서 직접 계산해서 반환할 지 여부를 설정하는 옵션입니다. 기본값은 false이며 원격 서버 혹은 원격 테이블을 대상으로 설정할 수 있습니다. 원격 서버와 테이블에 모두 해당 옵션이 설정된 경우, 원격 테이블의 설정값이 우선되며 해당 원격 테이블을 대상으로 하는 쿼리는 같은 원격 서버의 다른 테이블들과 다르게 동작할 수 있습니다.
Query Tree 생성
Foreign 테이블에 대한 Query Tree를 생성할 때도, 로컬 테이블을 대상으로 한 쿼리의 Query Tree를 생성할 때와 마찬가지로 해당 테이블에 대한 정의를 참조해야 합니다. 카탈로그 pg_catalog의 pg_class와 pg_foreign_table을 참조하여 Foreign 테이블에 대한 정보를 가져옵니다.
Plan Tree 생성
Plan Tree를 생성하는 과정은 Foreign 서버 객체를 만들 때 사용한 use_remote_estimate 옵션에 따라 달라질 수 있습니다.
CREATE SERVER my_foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host ‘remote_host’,
port ‘5432’,
dbname ‘remote_db’);ALTER SERVER my_foreign_server
OPTIONS (ADD use_remote_estimate ‘true’);— 이미 use_remote_estimate 옵션이 있는 경우는 SET
ALTER SERVER my_foreign_server
OPTIONS (SET use_remote_estimate ‘false’);
해당 옵션을 활성화한 경우 Planner는 로컬에서 Cost를 계산하지 않고 Remote 서버에 연결해 EXPLAIN 쿼리를 수행해 Cost를 계산합니다. 해당 옵션이 활성화되지 않은 경우, Planner는 로컬에 수집된 Foreign 테이블에 대한 통계 정보를 바탕으로 Cost를 계산합니다.
Foreign 테이블을 대상으로 한 쿼리의 Cost를 계산할 때는 Parameter fdw_startup_cost와 fdw_tuple_cost가 같이 고려됩니다. 각각 Foreign 테이블을 대상으로 한 Scan을 시작할 때 걸리는 시간과 한 Tuple을 읽어들일 때 걸리는 시간을 고려하기 위해 설정하는 Parameter 들로, Remote 서버를 대상으로 쿼리를 수행할 때 걸리는 추가적인 Network Overhead(Connection을 맺고, 네트워크 통신으로 데이터를 가져오는)를 고려한 값입니다.
또한 use_remote_estimate 옵션을 사용하지 않는 경우에는 로컬에서 Remote 쿼리의 Sort 비용을 정확하게 알 수 있는 방법이 원칙적으로 존재하지 않습니다. 따라서 Remote 쿼리에서 정렬이 수행되는 경우, 적당한 값을 나머지 전체 쿼리를 대상으로 계산한 run_cost에 곱해줌으로써 근사치를 구합니다. Parameter fdw_sort_multiplier로 기본값은 1.2 입니다.
주의해야 할 점으로는, postgres-fdw 을 사용하는 경우에만 Cost 계산 방법 및 EXPLAIN 쿼리의 결과 형식(Startup cost와 Total cost를 같이 반환하는) 이 로컬 PostgreSQL 서버와 호환되기 때문에 use_remote_estimate 옵션을 통해 Remote 서버에서 Cost 계산을 할 수 있다는 점입니다. 예를 들면 원격 MySQL 서버에 접속해서 데이터를 가져오는 Extension인 mysql-fdw 의 경우, MySQL의 EXPLAIN 쿼리가 해당 쿼리를 실행해서 반환될 Row의 숫자만을 반환하기 때문에 원격으로 Cost를 계산할 수 없습니다.
원격 SQL 쿼리 실행
Executor 모듈에서 해당 Plan을 Remote 서버에서 실행하는 방식은 Extension에 따라 달라집니다. postgres-fdw의 경우 Transaction을 이용해 원격 서버에서 쿼리를 실행합니다.
postgres-fdw에서 원격 쿼리를 실행할 때는 Transaction의 Isolation Level을 기본적으로 REPEATABLE READ, Local Transaction의 Isolation Level이 SERIALIZABLE일 경우에는 SERIALIZABLE로 설정하여 쿼리를 수행합니다. 쿼리가 Remote 서버에서 여러 테이블을 한꺼번에 조회하는 경우를 고려한 설정으로, Remote 서버에서 동시에 업데이트가 이루어는 경우에도 Transaction 스냅샷에 따라 일정한 읽기 결과를 보장하기 위함입니다.
멀티 테이블 쿼리
아래 예시처럼 2개의 Foreign 테이블을 조회하는 쿼리를 수행한다고 가정했을 때, 실제 로컬 PostgreSQL 서버에서 처리하는 과정을 살펴봅시다.
SELECT * FROM tbl_a AS a, tbl_b AS b
WHERE a.id = b.id AND
a.id < 200;
- Remote Estimation을 사용하지 않는 경우
use_remote_estimate 옵션이 사용하지 않음으로 설정되어 있는 경우, Foreign Scan을 통해 원격 서버에 있는 데이터를 모두 가져온 뒤 로컬에서 테이블 tbl_a와 tbl_b의 행들을 Join해서 결과를 계산해야 합니다.
EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b
WHERE a.id = b.id AND a.id < 200;
——————————————————————————————————————————————————————Merge Join (cost=532.31..700.34 rows=10918 width=16)
Merge Cond: (a.id = b.id)
-> Sort (cost=200.59..202.72 rows=853 width=8)
Sort Key: a.id
-> Foreign Scan on tbl_a a (cost=100.00..159.06 rows=853 width=8)
-> Sort (cost=331.72..338.12 rows=2560 width=8)
Sort Key: b.id
-> Foreign Scan on tbl_b b (cost=100.00..186.80 rows=2560 width=8)
(8 rows)
— (5-1)
START TRANSACTION ISOLATION LEVEL REPEATABLE READ
— (5-2)
DECLARE c1 CURSOR FOR
SELECT id, data FROM public.tbl_a WHERE ((id < 200))
DECLARE c1 CURSOR FOR
SELECT id, data FROM public.tbl_a WHERE ((id < 200))
DECLARE c1 CURSOR FOR
SELECT id, data FROM public.tbl_a WHERE ((id < 200))
— (5-3)
FETCH 100 FROM c1
— …
FETCH 100 FROM c1
— (5-4)
DECLARE c2 CURSOR FOR
SELECT id, data FROM public.tbl_b
DECLARE c2 CURSOR FOR
SELECT id, data FROM public.tbl_b
DECLARE c2 CURSOR FOR
SELECT id, data FROM public.tbl_b
— (5-5)
FETCH 100 FROM c2
— …
FETCH 100 FROM c2
CLOSE c2
CLOSE c1
COMMIT TRANSACTION
(5-1) ISOLATION LEVEL을 REPEATABLE READ로 설정한 원격 Transaction을 시작합니다.
(5-2) 테이블 tbl_a에 대한 SELECT 구문을 처리하기 위한 커서 c1을 정의합니다.
(5-3) 커서 c1에 대한 실행결과를 가져오기 위해 FETCH 명령어를 실행합니다.
(5-4) 테이블 tbl_b에 대한 SELECT 구문을 처리하기 위한 커서 c2를 정의합니다.
(5-5) 커서 c2에 대한 실행결과를 가져오기 위해 FETCH 명령어를 실행합니다.
(5-6) 커서 c1을 닫습니다.
(5-7) 커서 c2를 닫습니다.
(5-8) Transaction을 COMMIT 합니다.
- Remote Estimation을 하는 경우
use_remote_estimate 옵션을 사용하는 경우에는 Remote 서버에서 실제 쿼리를 수행하기에 앞서 EXPLAIN 명령을 수행해 각 Plan Path의 Cost를 계산하고, Remote 서버에서 INNER JOIN 등의 처리를 하는 게 더 효율적인 경우 해당 Path를 선택합니다.
아래 예제는 실제 Foreign Table인 tbl_a, tbl_b를 대상으로 한 쿼리에 대해 EXPLAIN 명령어를 이용해 Query Plan을 가져온 결과입니다.
EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b
WHERE a.id = b.id AND a.id < 200;
QUERY PLAN
————————————————————————————-
Foreign Scan (cost=134.35..244.45 rows=80 width=16)
Relations: (public.tbl_a a) INNER JOIN (public.tbl_b b)
(2 rows)
즉 원격 서버에서 실행한 EXPLAIN 쿼리들의 결과를 종합한 결과, Foreign Scan 후 원격 서버에서 INNER JOIN을 수행해 Merge하는 쿼리가 가장 효율적이라 판단되었음을 알 수 있습니다. 이렇게 원격 서버에서 일부 Operation을 직접 처리하는 것을 Pushdown 이라고 부릅니다.
실제 수행되는 쿼리는 아래와 같습니다.
— (3-1)
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
— (3-2)
EXPLAIN SELECT id, data FROM public.tbl_a WHERE ((id < 200));
EXPLAIN SELECT id, data FROM public.tbl_b;
— …
EXPLAIN SELECT a.id, a.data, b.id, b.data FROM (
public.tbl_a a INNER JOIN public.tbl_b b
ON (a.id = b.id) AND (a.id < 200));
— (5-1)
DECLARE c1 CURSOR FOR
SELECT a.id, a.data, b.id, b.data FROM (
public.tbl_a a INNER JOIN public.tbl_b b
ON (a.id = b.id) AND (a.id < 200));
— (5-2)
FETCH 100 FROM c1;
— (5-3)
CLOSE c1;
— (5-4)
COMMIT;
(3-1) ISOLATION LEVEL을 REPEATBLE READ로 설정한 원격 트랜잭션을 시작합니다.
(3-2) 작성한 Plan Path들의 Cost를 계산하기 위해 EXPLAIN 명령어를 실행합니다. 본 예시에서는 아래와 같은 7개의 EXPLAIN 명령어가 실행됩니다.
EXPLAIN SELECT id, data FROM public.tbl_a WHERE ((id < 200))
EXPLAIN SELECT id, data FROM public.tbl_b
EXPLAIN SELECT id, data FROM public.tbl_a WHERE ((id < 200)) ORDER BY id ASC NULLS LAST
EXPLAIN SELECT id, data FROM public.tbl_a WHERE ((((SELECT null::integer)::integer) = id)) AND ((id < 200))
EXPLAIN SELECT id, data FROM public.tbl_b ORDER BY id ASC NULLS LAST
EXPLAIN SELECT id, data FROM public.tbl_b WHERE ((((SELECT null::integer)::integer) = id))
EXPLAIN SELECT r1.id, r1.data, r2.id, r2.data FROM (public.tbl_a r1 INNER JOIN public.tbl_b r2 ON (((r1.id = r2.id)) AND ((r1.id < 200))))
(5-1) 위 단락에서와 같이 tbl_a와 tbl_b 에 대해 INNER JOIN을 수행하는 Path가 가장 효율적으로 판단되므로, 커서 c1을 정의하고 아래와 같이 SELECT 쿼리를 수행합니다.
SELECT r1.id, r1.data, r2.id, r2.data FROM
(public.tbl_a r1 INNER JOIN public.tbl_b r2
ON (((r1.id = r2.id)) AND ((r1.id < 200))));
(5-2) 원격 서버에서 실행된 결과를 받아옵니다.
(5-3) 커서 c1을 닫습니다.
(5-4) 트랜잭션을 커밋합니다.
Sort, Aggregate 쿼리
Version 10 이후의 PostgreSQL 에서는 ORDER BY 같은 정렬, 혹은 avg(), count() 같은 Aggregate 함수들도 원격 서버로 Pushdown 하여 실행할 수 있습니다.
아래 예시처럼 원격 테이블의 데이터를 정렬하는 쿼리의 Query Plan을 EXPLAIN 명령어로 살펴보겠습니다.
EXPLAIN VERBOSE SELECT * FROM inventory AS i
WHERE inventory_id < 1800 ORDER BY inventory_id DESC;
QUERY PLAN
————————————————————————————————–
Foreign Scan on public.inventory i (cost=100.28..204.75 rows=1799 width=20)
Output: inventory_id, film_id, store_id, last_update
Remote SQL: SELECT inventory_id, film_id, store_id, last_update FROM public.inventory WHERE ((inventory_id < 1800)) ORDER BY inventory_id DESC NULLS FIRST
(3 row)
정렬 Operation을 Pushdown으로 원격 서버에서 실행하는 것이 가능하므로, Remote SQL: 단락에서처럼 정렬을 원격 서버에서 수행하여 하나의 Foreign Scan 노드로 Query Plan이 작성되었음을 확인할 수 있습니다.
avg(), count() 함수처럼 여러 개의 입력값에 대해 하나의 결과값을 반환하는 함수를 집계 함수 (Aggregate Function)이라고 합니다. 정렬과 마찬가지로 대상 Column들의 인덱스를 참조할 수 있는 원격 서버에서 직접 실행한 후 결과를 반환하는 것이 대부분 더 효율적입니다.
아래 예시에서처럼 avg() 함수를 실행하는 경우를 살펴보겠습니다.
EXPLAIN VERBOSE SELECT avg(amount) FROM payment;
QUERY PLAN
—————————————————————————————
Foreign Scan (cost=389.29..389.32 rows=1 width=32)
Output: (avg(amount))
Relations: Aggregate on (public.payment)
Remote SQL: SELECT avg(amount) FROM public.payment
(4 row)
마찬가지로 원격에서 avg() 함수를 실행하는 게 가능하므로, Remote SQL: 단락에서처럼 집계를 원격 서버에서 수행하여 하나의 Foreign Scan 노드로 Query Plan이 작성되었음을 확인할 수 있습니다.
Version 10 부터의 PostgreSQL에서는 기본적으로 Sorting, Aggregate 함수를 원격 서버에서 실행(Pushdown)합니다. 하지만 원격 서버의 가용 메모리 work_mem 크기가 너무 작아 Disk를 이용해야 하는 경우, 원격에서 해당 Path를 실행하기에 Disk I/O가 많이 발생하여 로컬로 데이터를 모두 가져온 후 정렬을 수행하는 것보다 비효율적일 수 있습니다. 이런 경우는 Pushdown을 실행하는 Path가 효율적이지 않아 선택되지 않습니다.
또한 조건절의 Expression이 Volatile 하거나 Shippable 하지 않은 경우에는 Pushdown으로 원격에서 실행할 수 없습니다. Volatile한 Expression은 clock_timestamp() 처럼 호출할 때마다 값이 바뀔 수 있는 표현식을 의미하며, built-in 오브젝트가 아닌 값들 중 shippable extension이 사용자에 의해 지정되지 않은 오브젝트들을 Shippable 하지 않다고 표현합니다.
지금까지 PostgreSQL의 Foreign Data Wrapper에 관해 알아보았습니다
‘PostgreSQL의 TOAST’를 바로 이어서 확인해보세요!
