PgSQL - 内核特性 - 把DuckDB弄进来怎么样

PgSQL - 内核特性 - 把DuckDB弄进来怎么样

DuckDB是一款高性能的分析型数据库系统，支持了基于Push-based pipeline的向量化执行引擎。这么好的一款数据库，有办法直接弄到PgSQL里面，以利用其优秀的列式存储、向量化执行引擎等优秀特性吗？Hydra团队开源了一款插件pg_quack，将duckdb以表访问方法的方式加到PgSQL中，为PgSQL提供了新的存储引擎以及执行引擎。

1、执行器及表访问方法hook接入

1）增加了一个配置项quack.data_dir，用于指定duckdb表的存访目录

2）quack_init_tableam初始化duckdb表访问方法的hook

3）quack_init_hooks初始化duckdb执行器的hook

2、接入执行器的hook

包括两个hook，一个用于DML等查询，一个用于DDL等操作。

3、DML的hook

通过duckdb api来进行操作。由于duckdb是嵌入式，所以只需要将duckdb的lib库和头文件加进来就可以使用。

quack_executor_run->
  duckdb_database db = quack_open_database(MyDatabaseId, false);
  duckdb_connection connection = quack_open_connection(db);
  duckdb_query(connection, queryDesc->sourceText, &result)
  slot = MakeTupleTableSlot(queryDesc->tupDesc, &TTSOpsHeapTuple);
  row_count = duckdb_row_count(&result);//行数
  column_count = duckdb_column_count(&result);//列数
  for(idx_t row = 0; row < row_count; row++){
    ExecClearTuple(slot);
    for(idx_t col = 0; col < column_count; col++){
      if (duckdb_value_is_null(&result, col, row))
        slot->tts_isnull[col] = true;
      else{
        slot->tts_isnull[col] = false;
        quack_read_result(slot, &result, col, row);
      }
    }
    ExecStoreVirtualTuple(slot);
    dest->receiveSlot(slot, dest);//一行发送给用户
    释放该行的所有列
  }
  ...

4、DDL的hook

quack_process_utility->quack_execute_query：使用duckdb api执行
  //database目录quack_data_dir/"databaseOid".duckdb
  db = quack_open_database(MyDatabaseId, true);//创建/打开一个新的database
  duckdb_connect(db, &connection)//连接该database
  duckdb_query(connection, query, NULL);//执行
  duckdb_disconnect(&connection);//断开连接

主要利用duckdb的API进行操作。

5、duckdb表访问方法的hook

表访问方法如下：

static const TableAmRoutine quack_am_methods = {
  .type = T_TableAmRoutine,
 
 
  .slot_callbacks = quack_slot_callbacks,
 
 
  .scan_begin = quack_begin_scan,
  .scan_end = quack_end_scan,
  .scan_rescan = quack_rescan,
  .scan_getnextslot = quack_getnextslot,
 
 
  .parallelscan_estimate = quack_parallelscan_estimate,
  .parallelscan_initialize = quack_parallelscan_initialize,
  .parallelscan_reinitialize = quack_parallelscan_reinitialize,
 
 
  .index_fetch_begin = quack_index_fetch_begin,
  .index_fetch_reset = quack_index_fetch_reset,
  .index_fetch_end = quack_index_fetch_end,
  .index_fetch_tuple = quack_index_fetch_tuple,
 
 
  .tuple_fetch_row_version = quack_fetch_row_version,
  .tuple_tid_valid = quack_tuple_tid_valid,
  .tuple_get_latest_tid = quack_get_latest_tid,
 
 
  .tuple_satisfies_snapshot = quack_tuple_satisfies_snapshot,
  .index_delete_tuples = quack_index_delete_tuples,
 
 
 
 
  .tuple_insert = quack_tuple_insert,
  .tuple_insert_speculative = quack_tuple_insert_speculative,
  .tuple_complete_speculative = quack_tuple_complete_speculative,
  .multi_insert = quack_multi_insert,
  .tuple_delete = quack_tuple_delete,
  .tuple_update = quack_tuple_update,
  .tuple_lock = quack_tuple_lock,
  .finish_bulk_insert = quack_finish_bulk_insert,
 
 
  .relation_set_new_filenode = quack_relation_set_new_filenode,
  .relation_nontransactional_truncate = quack_relation_nontransactional_truncate,
  .relation_copy_data = quack_relation_copy_data,
  .relation_copy_for_cluster = quack_relation_copy_for_cluster,
  .relation_vacuum = quack_vacuum_rel,
  .scan_analyze_next_block = quack_scan_analyze_next_block,
  .scan_analyze_next_tuple = quack_scan_analyze_next_tuple,
  .index_build_range_scan = quack_index_build_range_scan,
  .index_validate_scan = quack_index_validate_scan,
 
 
  .relation_size = quack_relation_size,
  .relation_needs_toast_table = quack_relation_needs_toast_table,
 
 
  .relation_estimate_size = quack_estimate_rel_size,
 
 
  .scan_bitmap_next_block = NULL,
  .scan_bitmap_next_tuple = NULL,
  .scan_sample_next_block = quack_scan_sample_next_block,
  .scan_sample_next_tuple = quack_scan_sample_next_tuple
};

这样就将duckdb以表访问方法的方式加入PgSQL了

参考

https://github.com/hydradatabase/pg_quack

https://github.com/duckdb/duckdb/