PolarDB-PG | PostgreSQL + 阿里云OSS 实现高效低价的海量数据冷热存储分离

背景

数据库里的历史数据越来越多, 占用空间大, 备份慢, 恢复慢, 查询少但是很费钱, 迁移慢. 怎么办?

冷热分离方案:

• 使用PostgreSQL 或者 PolarDB-PG, 将历史数据存成parquet文件格式, 放到aliyun OSS存储里面. 使用duckdb_fdw对OSS内的parquet文件进行查询.

  • 《DuckDB DataLake 场景使用举例 - aliyun OSS对象存储parquet》
  • 《PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP》

• duckdb 存储元数据(parquet 映射)

  • 《DuckDB parquet 分区表 / Delta Lake(数据湖) 应用》

方案特点:

• 内网oss不收取网络费用, 只收取存储费用, 非常便宜.
• oss分几个档, 可以根据性能需求选择.
• parquet为列存储, 一般历史数据的分析需求多, 性能不错.
• duckdb 支持 parquet下推过滤, 数据过滤性能也不错.
• 存储在oss内, 可以使用oss的函数计算功能, 仅计算时收费. 而且使用OSS存储打破数据孤岛, OSS与PG和PolarDB以及其他数据源打通, 形成数据联邦, 更容易发挥更大层面的数据价值.

架构如下:

 PolarDB-PG 或 PostgreSQL   
          ↑↓
          ↑↓
 热数据: 高速本地存储     
          ↑↓                                  ↓↓            
          ↑↓                                  ↓↓             
 LibDuckDB ForeignServer 层:                  ↓↓ 
   1、(通过 duckdb_fdw 读写OSS)    2、(通过 postgres_scanner 读高速本地存储)
            ↑↓                                              
            ↑↓                                               
 归档数据: OSS 冷暖存储 (Parquet格式)    

demo

在以下debian 容器中部署

1、部署duckdb和依赖的parquet、httpfs插件

《Debian学习入门 - (作为服务器使用, Debian 操作系统可能是长期更好的选择?)》

确认编译了httpfs 和 parquet 插件

root@9b780f5ea2e8:~/duckdb/build/release/extension# pwd    
/root/duckdb/build/release/extension    
root@9b780f5ea2e8:~/duckdb/build/release/extension# ll    
total 72K    
-rw-r--r--  1 root root 2.3K Mar  3 06:16 cmake_install.cmake    
-rw-r--r--  1 root root 6.2K Mar  3 06:16 Makefile    
drwxr-xr-x 15 root root 4.0K Mar  3 06:16 .    
drwxr-xr-x  2 root root 4.0K Mar  3 06:16 CMakeFiles    
drwxr-xr-x  4 root root 4.0K Mar  3 06:40 jemalloc    
drwxr-xr-x 10 root root 4.0K Mar  3 06:43 ..    
drwxr-xr-x  4 root root 4.0K Mar  3 06:45 icu    
drwxr-xr-x  3 root root 4.0K Mar  3 06:47 parquet    
drwxr-xr-x  4 root root 4.0K Mar  3 06:47 tpch    
drwxr-xr-x  4 root root 4.0K Mar  3 06:47 tpcds    
drwxr-xr-x  3 root root 4.0K Mar  3 06:47 fts    
drwxr-xr-x  3 root root 4.0K Mar  3 06:48 httpfs    
drwxr-xr-x  3 root root 4.0K Mar  3 06:48 visualizer    
drwxr-xr-x  5 root root 4.0K Mar  3 06:49 json    
drwxr-xr-x  4 root root 4.0K Mar  3 06:49 excel    
drwxr-xr-x  4 root root 4.0K Mar  3 06:50 sqlsmith    
drwxr-xr-x  3 root root 4.0K Mar  3 06:50 inet    

2、安装postgresql 或 PolarDB开源版本.

PolarDB开源版本部署请参考: 《如何用 PolarDB 证明巴菲特的投资理念 - 包括PolarDB on Docker简单部署》

以下是使用postgresql的例子:

apt install -y curl fastjar    
mkdir /home/postgres    
useradd postgres    
chown postgres:postgres /home/postgres     
su - postgres    
curl https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.bz2 -o ./postgresql-15.2.tar.bz2    
tar -jxvf postgresql-15.2.tar.bz2    
cd postgresql-15.2    
./configure --prefix=/home/postgres/pg15.2    
make world -j 4    
make install-world    

3、部署duckdb_fdw

su - postgres    
git clone --depth 1 https://github.com/alitrack/duckdb_fdw    

将duckdb的lib包拷贝到postgresql的lib目录

root@9b780f5ea2e8:~/duckdb/build/release/src# pwd    
/root/duckdb/build/release/src    
root@9b780f5ea2e8:~/duckdb/build/release/src# ll libduckdb.so    
-rwxr-xr-x 1 root root 58M Mar  3 06:42 libduckdb.so    
    
cp libduckdb.so /home/postgres/pg15.2/lib/    

安装duckdb_fdw插件

su - postgres    
export PATH=/home/postgres/pg15.2/bin:$PATH    
cd duckdb_fdw  
USE_PGXS=1 make    
USE_PGXS=1 make install    

4、初始化postgresql数据库集群

initdb -D /home/postgres/pgdata -E UTF8 --lc-collate=C  -U postgres    

5、简单配置一下pg配置文件

vi /home/postgres/pgdata/postgresql.conf    
    
listen_addresses = '0.0.0.0'            
port = 1921                    
max_connections = 100                
unix_socket_directories = '/tmp,.'        
shared_buffers = 128MB                
dynamic_shared_memory_type = posix        
max_wal_size = 1GB    
min_wal_size = 80MB    
log_destination = 'csvlog'            
logging_collector = on        
log_directory = 'log'                
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        
log_file_mode = 0600                
log_rotation_age = 1d                
log_rotation_size = 10MB            
log_truncate_on_rotation = on            
log_timezone = 'Etc/UTC'    
datestyle = 'iso, mdy'    
timezone = 'Etc/UTC'    
lc_messages = 'C'                
lc_monetary = 'C'                
lc_numeric = 'C'                
lc_time = 'C'                    
default_text_search_config = 'pg_catalog.english'    

6、启动数据库, 加载duckdb_fdw插件

pg_ctl start -D /home/postgres/pgdata    
    
    
$ psql -h 127.0.0.1 -p 1921      
psql (15.2)    
Type "help" for help.    
    
postgres=# create extension duckdb_fdw ;    
CREATE EXTENSION    

创建oss实验环境

可以使用阿里云云起实验免费创建oss实验环境, 参考如下:

《DuckDB DataLake 场景使用举例 - aliyun OSS对象存储parquet》

1、初始化实验环境后, 得到一些需要的内容如下, 将被duckdb用于连接oss.

AK ID: xxxxxx    
      
AK Secret: xxxxxx    
      
Endpoint外网域名: oss-cn-shanghai.aliyuncs.com    
      
Bucket名称: adc-oss-labs01969    
      
Object路径: ECSOSS/u-bimcc3ei/    

duckdb读写OSS的方法

COPY <table_name> TO 's3://<Bucket名称>/<Object路径>/filename';      
      
SELECT * FROM read_parquet('s3://<Bucket名称>/<Object路径>/filename');      

在debian中, 测试duckdb是否能正常使用OSS, 并生成100万测试数据, 写入oss.

root@9b780f5ea2e8:~/duckdb/build/release# pwd    
/root/duckdb/build/release    
    
root@9b780f5ea2e8:~/duckdb/build/release# ./duckdb     
v0.7.1 b00b93f    
Enter ".help" for usage hints.    
Connected to a transient in-memory database.    
Use ".open FILENAME" to reopen on a persistent database.    
    
D load 'httpfs';     
D set s3_access_key_id='xxxxxx';               // AK ID      
D set s3_secret_access_key='xxxxxx';     // AK Secret      
D set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';             // Endpoint外网域名|内网域名      
D COPY (select id, md5(random()::text) as info, now() as ts from range(0,1000000) as t(id)) TO 's3://adc-oss-labs01969/ECSOSS/u-bimcc3ei/test_duckdb1.parquet';     

测试创建视图是否正常使用

IT-C02YW2EFLVDL:release digoal$ ./duckdb     
v0.7.1 b00b93f    
Enter ".help" for usage hints.    
Connected to a transient in-memory database.    
Use ".open FILENAME" to reopen on a persistent database.    
D set s3_access_key_id='xxxxxx';     
D set s3_secret_access_key='xxxxxx';     
D set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';     
  
将parquet文件映射为view   
D create or replace view test_duckdb1 as     
  SELECT * FROM read_parquet('s3://adc-oss-labs01969/ECSOSS/u-bimcc3ei/test_duckdb1.parquet');    
    
D select count(*) from test_duckdb1;    
┌──────────────┐    
│ count_star() │    
│    int64     │    
├──────────────┤    
│      1000000 │    
└──────────────┘    
    
D select * from main."test_duckdb1" limit 10;    
100% ▕████████████████████████████████████████████████████████████▏     
┌───────┬──────────────────────────────────┬────────────────────────────┐    
│  id   │               info               │             ts             │    
│ int64 │             varchar              │  timestamp with time zone  │    
├───────┼──────────────────────────────────┼────────────────────────────┤    
│     0 │ 87a144c45874838dbcd3255c215ababc │ 2023-03-08 17:28:12.902+08 │    
│     1 │ cce8d1f5d58e72e9f34a36ccd87188ed │ 2023-03-08 17:28:12.902+08 │    
│     2 │ 0ea50d2769b01c26537e09902dc5f732 │ 2023-03-08 17:28:12.902+08 │    
│     3 │ 70a6c5f594def5d1d1bbb993260a2fd7 │ 2023-03-08 17:28:12.902+08 │    
│     4 │ 5a7924f417b480210601508e2c144a2f │ 2023-03-08 17:28:12.902+08 │    
│     5 │ d1fde1c1dc8f268d9eb9fce477653bb0 │ 2023-03-08 17:28:12.902+08 │    
│     6 │ 1aac9556fd1b259c56ecef3ef4636a66 │ 2023-03-08 17:28:12.902+08 │    
│     7 │ 04181693f9b6c8576bb251612ffbe318 │ 2023-03-08 17:28:12.902+08 │    
│     8 │ 332b9bb9d00e8fa53a5661804bd1b41a │ 2023-03-08 17:28:12.902+08 │    
│     9 │ f0189d662187cc436662a458577a7ed2 │ 2023-03-08 17:28:12.902+08 │    
├───────┴──────────────────────────────────┴────────────────────────────┤    
│ 10 rows                                                     3 columns │    
└───────────────────────────────────────────────────────────────────────┘    
Run Time (s): real 9.773 user 1.121633 sys 0.928902    
    
D .timer on    
D select max(id) from test_duckdb1;    
┌─────────┐    
│ max(id) │    
│  int64  │    
├─────────┤    
│  999999 │    
└─────────┘    
Run Time (s): real 0.482 user 0.087439 sys 0.065868    

在postgresql中使用duckdb_fdw访问oss内的parquet文件

你可以创建duckdb内存数据库, 也可以指定为一个持久化文件, 使用持久化文件的话可以拥有一些元数据存储的能力, 不用每次都创建映射和配置.

下面用的是内存存储(非持久化)例子:

在psql内执行

postgres=# CREATE SERVER DuckDB_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database ':memory:');      
CREATE SERVER      
    
-- 设置为保持连接(会话内保持)    
    
postgres=# alter server duckdb_server options ( keep_connections 'true');    
ALTER SERVER    

接下来创建一个duckdb视图, 用以查询parquet.

一定要分开执行:

SELECT duckdb_execute('duckdb_server',     
$$    
set s3_access_key_id='xxxxxx';     
$$);    
    
SELECT duckdb_execute('duckdb_server',     
$$    
set s3_secret_access_key='xxxxxx';     
$$);    
    
SELECT duckdb_execute('duckdb_server',     
$$    
set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';     
$$);    
    
SELECT duckdb_execute('duckdb_server',     
$$    
create or replace view test_duckdb1 as     
SELECT * FROM read_parquet('s3://adc-oss-labs01969/ECSOSS/u-bimcc3ei/test_duckdb1.parquet');    
$$);    

检查是否保持连接

postgres=# select * from duckdb_fdw_get_connections();    
  server_name  | valid     
---------------+-------    
 duckdb_server | t    
(1 row)    

创建duckdb_fdw外部表, 指向刚才创建的duckdb视图:

create foreign TABLE ft_test_duckdb1(    
id int,    
info text,    
ts timestamp)    
SERVER duckdb_server OPTIONS (table 'test_duckdb1');     

我们查看一下duckdb_fdw的下推能力, 非常帮, 过滤、limit、排序、distinct等都进行了下推, 详细参考duckdb_fdw开源项目:

postgres=# explain verbose select id from ft_test_duckdb1 limit 1;    
                                QUERY PLAN                                    
--------------------------------------------------------------------------    
 Foreign Scan on public.ft_test_duckdb1  (cost=1.00..1.00 rows=1 width=4)    
   Output: id    
   SQLite query: SELECT "id" FROM main."test_duckdb1" LIMIT 1    
(3 rows)    
    
postgres=# explain verbose select * from ft_test_duckdb1 where id<100;    
                                       QUERY PLAN                                            
-----------------------------------------------------------------------------------------    
 Foreign Scan on public.ft_test_duckdb1  (cost=10.00..401.00 rows=401 width=44)    
   Output: id, info, ts    
   SQLite query: SELECT "id", "info", "ts" FROM main."test_duckdb1" WHERE (("id" < 100))    
(3 rows)    
    
postgres=# explain verbose select * from ft_test_duckdb1 where id<100 order by ts limit 100;    
                                                           QUERY PLAN                                                               
--------------------------------------------------------------------------------------------------------------------------------    
 Foreign Scan on public.ft_test_duckdb1  (cost=1.00..1.00 rows=1 width=44)    
   Output: id, info, ts    
   SQLite query: SELECT "id", "info", "ts" FROM main."test_duckdb1" WHERE (("id" < 100)) ORDER BY "ts" ASC NULLS LAST LIMIT 100    
(3 rows)    
    
postgres=# explain verbose select count(distinct id) from ft_test_duckdb1;    
                              QUERY PLAN                                  
----------------------------------------------------------------------    
 Foreign Scan  (cost=1.00..1.00 rows=1 width=8)    
   Output: (count(DISTINCT id))    
   SQLite query: SELECT count(DISTINCT "id") FROM main."test_duckdb1"    
(3 rows)    

postgres=# select * from ft_test_duckdb1 limit 1;    
 id |               info               |           ts                
----+----------------------------------+-------------------------    
  0 | 87a144c45874838dbcd3255c215ababc | 2023-03-08 09:28:12.902    
(1 row)    

执行查询, 看看性能如何. 以下对比pg本地表、parquet(实验环境在公网, 如果是内网还不好说谁快谁慢.)

postgres=# create table t as select * from ft_test_duckdb1 ;    
SELECT 1000000    
Time: 21196.441 ms (00:21.196)    

postgres=# \timing    
Timing is on.    
    
postgres=# select count(distinct id) from ft_test_duckdb1;    
  count      
---------    
 1000000    
(1 row)    
    
Time: 1281.537 ms (00:01.282)    
    
postgres=# select count(distinct id) from t;    
  count      
---------    
 1000000    
(1 row)    
    
Time: 260.007 ms    
    
    
postgres=# select count(*) from ft_test_duckdb1 where id<100;    
 count     
-------    
   100    
(1 row)    
    
Time: 806.976 ms    
postgres=# select count(*) from t where id<100;    
 count     
-------    
   100    
(1 row)    
    
Time: 60.254 ms    

多个会话同时访问相同server, 相同foreign table

使用同一个server, 每次建立连接都会新建一个duckdb inmemory进程. 每次都需要设置oss配置, 创建duckdb view. 然后就能通过ft读取数据.

session a:

• 访问正常

postgres=# select * from ft_test_duckdb1 limit 1;  
 id |               info               |           ts              
----+----------------------------------+-------------------------  
  0 | 77e736e2033e489f3134607dcfd63d05 | 2023-03-09 05:45:25.506  
(1 row)  

session b:

• 访问正常

postgres=# select * from ft_test_duckdb1 limit 1;  
 id |               info               |           ts              
----+----------------------------------+-------------------------  
  0 | 77e736e2033e489f3134607dcfd63d05 | 2023-03-09 05:45:25.506  
(1 row)  

通过duckdb_fdw将历史数据写入oss, 实现历史数据归档操作

准备工作, 配置需要密码连接postgresql.

vi pg_hba.conf  
# IPv4 local connections:  
host    all             all             127.0.0.1/32            md5  
  
pg_ctl reload -D $PGDATA  
  
psql  
alter role postgres encrypted password '123456';  

1、建立pg本地表

postgres=# create table t1 (id int, info text, ts timestamp);  
CREATE TABLE  
postgres=# insert into t1 select generate_series(1,1000000), md5(random()::text), clock_timestamp();  
INSERT 0 1000000  

2、在duckdb中使用postgres插件可以读取pg本地表的数据

root@9b780f5ea2e8:~# cd duckdb/build/release/  
root@9b780f5ea2e8:~/duckdb/build/release# ./duckdb   
v0.7.1 b00b93f  
Enter ".help" for usage hints.  
Connected to a transient in-memory database.  
Use ".open FILENAME" to reopen on a persistent database.  
D load 'postgres';  
D select * from POSTGRES_SCAN_PUSHDOWN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1') limit 1;  
┌───────┬──────────────────────────────────┬────────────────────────────┐  
│  id   │               info               │             ts             │  
│ int32 │             varchar              │         timestamp          │  
├───────┼──────────────────────────────────┼────────────────────────────┤  
│     1 │ c8ecbcc36395bfa4d39b414e306c1b81 │ 2023-03-09 05:49:30.184854 │  
└───────┴──────────────────────────────────┴────────────────────────────┘  
D   

3、在duckdb中可以打通pg和oss, 也就是将pg的数据写入oss

set s3_access_key_id='xxxxxx';     
set s3_secret_access_key='xxxxxx';     
set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';     
  
COPY (  
select * from POSTGRES_SCAN_PUSHDOWN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')  
)   
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/abc.parquet';   
  
100% ▕████████████████████████████████████████████████████████████▏   

4、紧接着, 直接在pg里面使用duckdb_fdw插件, 让duckdb来读取pg的数据写入oss.

SELECT duckdb_execute('duckdb_server',     
$$    
install 'postgres';   
$$);    
  
SELECT duckdb_execute('duckdb_server',     
$$    
load 'postgres';   
$$);    
  
  
SELECT duckdb_execute('duckdb_server',     
$$    
COPY (  
select * from POSTGRES_SCAN_PUSHDOWN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')  
)   
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet';   
$$);    

使用如上方法install postgres时, 会自动从duckdb官方下载对应版本编译好的插件, 例如:

• https://extensions.duckdb.org/v0.7.1/linux_amd64/postgres_scanner.duckdb_extension.gz

详见:

• https://duckdb.org/docs/extensions/overview.html

方法没问题, 目前bug可能和gpdb postgres_fdw遇到的问题一样, 感兴趣的朋友可以参与一起解决:

https://github.com/alitrack/duckdb_fdw/issues/15

postgres=#   
SELECT duckdb_execute('duckdb_server',     
$$    
COPY (  
select * from POSTGRES_SCAN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')  
)   
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet';   
$$);    
ERROR:  HV00L: SQL error during prepare: IO Error: Unable to connect to Postgres at dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456: libpq is incorrectly linked to backend functions   
     
COPY (  
select * from POSTGRES_SCAN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')  
)   
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet';   
  
LOCATION:  sqlite_prepare_wrapper, duckdb_fdw.c:504  

相关代码sqlite3_prepare_v2:

/* Wrapper for sqlite3_prepare */  
static void  
sqlite_prepare_wrapper(ForeignServer *server, sqlite3 * db, char *query, sqlite3_stmt * *stmt,  
                       const char **pzTail, bool is_cache)  
{  
    int            rc;  
    // db = sqlite_get_connection(server, false);  
  
    // elog(DEBUG1, "duckdb_fdw : %s %s %p %p %p %p\n", __func__, query, server,db,&stmt,stmt);  
      
    rc = sqlite3_prepare_v2(db, query, -1, stmt, pzTail);  
    // elog(DEBUG1, "duckdb_fdw : %s %s %d \n", __func__, query, rc);  
    if (rc != SQLITE_OK)  
    {  
        ereport(ERROR,  
                (errcode(ERRCODE_FDW_UNABLE_TO_CREATE_EXECUTION),  
                 errmsg("SQL error during prepare: %s %s", sqlite3_errmsg(db), query)  
                 ));  
    }  
    /* cache stmt to finalize at last */  
    if (is_cache)  
        sqlite_cache_stmt(server, stmt);  
}  

gpdb类似的一个issue.

https://github.com/greenplum-db/gpdb/issues/11400

https://github.com/greenplum-db/gpdb/commit/667f0c37bc6d7bce7be8b758652ef95ddb823e19

Fix postgres_fdw's libpq issue (#10617)  
* Fix postgres_fdw's libpq issue  
  
When using posgres_fdw, it reports the following error:  
unsupported frontend protocol 28675.0: server supports 2.0 to 3.0  
  
root cause: Even if postgres_fdw.so is dynamic linked to libpq.so  
which is compiled with the option -DFRONTEND, but when it's loaded  
in gpdb and run, it will use the backend libpq which is compiled together  
with postgres program and reports the error.  
  
We statically link libpq into postgres_fdw and hide all the symbols  
of libpq.a with --exclude-libs=libpq.a to make it uses the frontend  
libpq.  
  
As postgres_fdw is compiled as a backend without -DFRONTEND, and linked  
to libpq which is a frontend, but _PQconninfoOption's length is  
different between backend and frontend as there is a macro in it.  
The backend's _PQconninfoOption has field connofs, but the frontend  
doesn't. This leads to the crash of postgres_fdw. So we delete the  
frontend macro in _PQconninfoOption.  
  
* Add FRONTEND macro on including libpq header files  
  
postgres_fdw is compiled as a backend, it needs the server's  
header files such as executor/tuptable.h. It also needs libpq  
to connect to a remote postgres database, so it's staticly linked  
to libpq.a which is compiled as a frontend using -DFRONTEND.  
  
But the struct PQconninfoOption's length is different between  
backend and frontend, there is no "connofs" field in frontend.  
When postgres_fdw calls the function "PQconndefaults" implemented  
in libpq.a and uses the returned PQconninfoOption variable, it crashes,  
because the PQconninfoOption variable returned by libpq.a doesn't contain  
the "connofs" value, but the postgres_fdw thinks it has, so it crashes.  
  
In last commit, we remove the FRONTEND macro in struct PQconninfoOption  
to make PQconninfoOption is same in backend and frontend, but that brings  
an ABI change. To avoid that, we revert that, and instead, we add  
the FRONTEND macro on including libpq header files, so that  
postgres_fdw can process the libpq's variables returned by libpq.a's  
functions as frontend.  
  
* Report error if the libpq-fe.h is included before postgres_fdw.h  
postgres_fdw needs to include frontend mode libpq-fe.h, so if  
the libpq-fe.h is included before the postgres_fdw.h, and we  
don't know if it is frontend mode, so we just report the error here.  

感谢steven贡献duckdb_fdw

未来duckdb_fdw的优化期待:
1、在server中加入更多的option, 例如

• 设置s3的参数, 连接时就默认配置好, 这样的话就可以直接查询foreign table, 不需要每次都需要通过execute接口来配置.

• 启动时设置allow_unsigned_extensions, 允许使用未签名的外部extension.

  • https://duckdb.org/docs/extensions/overview.html

参考

https://github.com/alitrack/duckdb_fdw

《Debian学习入门 - (作为服务器使用, Debian 操作系统可能是长期更好的选择?)》

《DuckDB DataLake 场景使用举例 - aliyun OSS对象存储parquet》

《用duckdb_fdw加速PostgreSQL分析计算, 提速40倍, 真香.》

《PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP》

《如何用 PolarDB 证明巴菲特的投资理念 - 包括PolarDB on Docker简单部署》