894
PostgreSQL 11 preview - compress method 接口 - 暨開放接口係列
標簽
PostgreSQL , 開放接口 , udf , type , index , scan , language , fdw , sample , aggregate , operator , window , storage , compress
背景
PostgreSQL是一款擴展能力極強的數據庫，這也是PG可以深入各個垂直行業的原因。
PostgreSQL的開放式接口
已有非常多跟進自定義接口擴展出來的插件。
下麵是自定義接口對應的文檔。
1、自定義函數
https://www.postgresql.org/docs/10/static/xfunc.html
2、自定義數據類型
https://www.postgresql.org/docs/10/static/xtypes.html
3、自定義操作符
https://www.postgresql.org/docs/10/static/xoper.html
4、自定義聚合、窗口
https://www.postgresql.org/docs/10/static/xaggr.html
5、自定義數據掃描方法
https://www.postgresql.org/docs/10/static/custom-scan.html
6、自定義數據采樣方法
https://www.postgresql.org/docs/10/static/tablesample-method.html
7、自定義外部數據訪問接口
https://www.postgresql.org/docs/10/static/fdwhandler.html
8、自定義存儲過程語言
https://www.postgresql.org/docs/10/static/plhandler.html
9、自定義索引接口
https://www.postgresql.org/docs/10/static/indexam.html
11、自定義WAL日誌接口
https://www.postgresql.org/docs/10/static/generic-wal.html
12、自定義邏輯訂閱接口
https://www.postgresql.org/docs/10/static/logicaldecoding.html
13、自定義工作進程接口
https://www.postgresql.org/docs/10/static/bgworker.html
14、自定義存儲接口
https://commitfest.postgresql.org/15/1283/
15、自定義壓縮接口
https://commitfest.postgresql.org/15/1294/
16、自定義加密接口
著名擴展插件
1、空間
https://commitfest.postgresql.org/15/1294/
2、路由
3、化學
https://www.rdkit.org/docs/Cartridge.html
4、基因
https://wiki.postgresql.org/images/1/1b/Postbis_pgcon_eu_2012.pdf
5、圖像識別
https://wiki.postgresql.org/images/1/1b/Postbis_pgcon_eu_2012.pdf
6、圖式搜索
7、流式計算
8、外部訪問接口
https://wiki.postgresql.org/wiki/Fdw
9、推薦數據庫
https://github.com/DataSystemsLab/recdb-postgresql
10、分布式數據庫
https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases
11、列存儲
https://github.com/citusdata/cstore_fdw
哪裏可以找到擴展插件
github
https://wiki.postgresql.org/wiki/Main_Page
... ...
如何擴展
《找對業務G點, 體驗酸爽 - PostgreSQL內核擴展指南》
開放的壓縮接口
PostgreSQL 11 會引入的開放壓縮接口。
未來可以對任意類型、索引實施外部壓縮算法。
PostgreSQL每一種數據類型都有對應的in,out函數，加解壓可以在對應數據類型的in, out函數內實現，在in,out函數內增加加解壓的handler，可以方便各個類型調用。
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------------+------------------+-----------------------------+--------
pg_catalog | abstimeout | cstring | abstime | normal
pg_catalog | aclitemout | cstring | aclitem | normal
pg_catalog | any_out | cstring | "any" | normal
pg_catalog | anyarray_out | cstring | anyarray | normal
pg_catalog | anyelement_out | cstring | anyelement | normal
pg_catalog | anyenum_out | cstring | anyenum | normal
pg_catalog | anynonarray_out | cstring | anynonarray | normal
pg_catalog | anyrange_out | cstring | anyrange | normal
pg_catalog | array_out | cstring | anyarray | normal
pg_catalog | bit_out | cstring | bit | normal
pg_catalog | bittypmodout | cstring | integer | normal
pg_catalog | boolout | cstring | boolean | normal
pg_catalog | box_out | cstring | box | normal
pg_catalog | bpcharout | cstring | character | normal
pg_catalog | bpchartypmodout | cstring | integer | normal
pg_catalog | byteaout | cstring | bytea | normal
pg_catalog | cash_out | cstring | money | normal
pg_catalog | charout | cstring | "char" | normal
pg_catalog | cidout | cstring | cid | normal
pg_catalog | cidr_out | cstring | cidr | normal
pg_catalog | circle_out | cstring | circle | normal
pg_catalog | cstring_out | cstring | cstring | normal
pg_catalog | date_out | cstring | date | normal
pg_catalog | enum_out | cstring | anyenum | normal
pg_catalog | event_trigger_out | cstring | event_trigger | normal
pg_catalog | fdw_handler_out | cstring | fdw_handler | normal
pg_catalog | float4out | cstring | real | normal
pg_catalog | float8out | cstring | double precision | normal
pg_catalog | gtsvectorout | cstring | gtsvector | normal
pg_catalog | index_am_handler_out | cstring | index_am_handler | normal
pg_catalog | inet_out | cstring | inet | normal
pg_catalog | int2out | cstring | smallint | normal
pg_catalog | int2vectorout | cstring | int2vector | normal
pg_catalog | int4out | cstring | integer | normal
pg_catalog | int8out | cstring | bigint | normal
pg_catalog | internal_out | cstring | internal | normal
pg_catalog | interval_out | cstring | interval | normal
pg_catalog | intervaltypmodout | cstring | integer | normal
pg_catalog | json_out | cstring | json | normal
pg_catalog | jsonb_out | cstring | jsonb | normal
pg_catalog | language_handler_out | cstring | language_handler | normal
pg_catalog | line_out | cstring | line | normal
pg_catalog | lseg_out | cstring | lseg | normal
pg_catalog | macaddr8_out | cstring | macaddr8 | normal
pg_catalog | macaddr_out | cstring | macaddr | normal
pg_catalog | nameout | cstring | name | normal
pg_catalog | numeric_out | cstring | numeric | normal
pg_catalog | numerictypmodout | cstring | integer | normal
pg_catalog | oidout | cstring | oid | normal
pg_catalog | oidvectorout | cstring | oidvector | normal
pg_catalog | opaque_out | cstring | opaque | normal
pg_catalog | path_out | cstring | path | normal
pg_catalog | pg_ddl_command_out | cstring | pg_ddl_command | normal
pg_catalog | pg_dependencies_out | cstring | pg_dependencies | normal
pg_catalog | pg_lsn_out | cstring | pg_lsn | normal
pg_catalog | pg_ndistinct_out | cstring | pg_ndistinct | normal
pg_catalog | pg_node_tree_out | cstring | pg_node_tree | normal
pg_catalog | point_out | cstring | point | normal
pg_catalog | poly_out | cstring | polygon | normal
pg_catalog | range_out | cstring | anyrange | normal
pg_catalog | record_out | cstring | record | normal
pg_catalog | regclassout | cstring | regclass | normal
pg_catalog | regconfigout | cstring | regconfig | normal
pg_catalog | regdictionaryout | cstring | regdictionary | normal
pg_catalog | regnamespaceout | cstring | regnamespace | normal
pg_catalog | regoperatorout | cstring | regoperator | normal
pg_catalog | regoperout | cstring | regoper | normal
pg_catalog | regprocedureout | cstring | regprocedure | normal
pg_catalog | regprocout | cstring | regproc | normal
pg_catalog | regroleout | cstring | regrole | normal
pg_catalog | regtypeout | cstring | regtype | normal
pg_catalog | reltimeout | cstring | reltime | normal
pg_catalog | shell_out | cstring | opaque | normal
pg_catalog | smgrout | cstring | smgr | normal
pg_catalog | textout | cstring | text | normal
pg_catalog | tidout | cstring | tid | normal
pg_catalog | time_out | cstring | time without time zone | normal
pg_catalog | timestamp_out | cstring | timestamp without time zone | normal
pg_catalog | timestamptypmodout | cstring | integer | normal
pg_catalog | timestamptz_out | cstring | timestamp with time zone | normal
pg_catalog | timestamptztypmodout | cstring | integer | normal
pg_catalog | timetypmodout | cstring | integer | normal
pg_catalog | timetz_out | cstring | time with time zone | normal
pg_catalog | timetztypmodout | cstring | integer | normal
pg_catalog | tintervalout | cstring | tinterval | normal
pg_catalog | trigger_out | cstring | trigger | normal
pg_catalog | tsm_handler_out | cstring | tsm_handler | normal
pg_catalog | tsqueryout | cstring | tsquery | normal
pg_catalog | tsvectorout | cstring | tsvector | normal
pg_catalog | txid_snapshot_out | cstring | txid_snapshot | normal
pg_catalog | unknownout | cstring | unknown | normal
pg_catalog | uuid_out | cstring | uuid | normal
pg_catalog | varbit_out | cstring | bit varying | normal
pg_catalog | varbittypmodout | cstring | integer | normal
pg_catalog | varcharout | cstring | character varying | normal
pg_catalog | varchartypmodout | cstring | integer | normal
pg_catalog | void_out | cstring | void | normal
pg_catalog | xidout | cstring | xid | normal
pg_catalog | xml_out | cstring | xml | normal
postgres=# \df *.*in
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------------------------------+-----------------------------+----------------------------------------------+---------
pg_catalog | abstimein | abstime | cstring | normal
pg_catalog | aclitemin | aclitem | cstring | normal
pg_catalog | any_in | "any" | cstring | normal
pg_catalog | anyarray_in | anyarray | cstring | normal
pg_catalog | anyelement_in | anyelement | cstring | normal
pg_catalog | anyenum_in | anyenum | cstring | normal
pg_catalog | anynonarray_in | anynonarray | cstring | normal
pg_catalog | anyrange_in | anyrange | cstring, oid, integer | normal
pg_catalog | array_in | anyarray | cstring, oid, integer | normal
pg_catalog | bit_in | bit | cstring, oid, integer | normal
pg_catalog | bittypmodin | integer | cstring[] | normal
pg_catalog | boolin | boolean | cstring | normal
pg_catalog | box_contain | boolean | box, box | normal
pg_catalog | box_in | box | cstring | normal
pg_catalog | bpcharin | character | cstring, oid, integer | normal
pg_catalog | bpchartypmodin | integer | cstring[] | normal
pg_catalog | byteain | bytea | cstring | normal
pg_catalog | cash_in | money | cstring | normal
pg_catalog | charin | "char" | cstring | normal
pg_catalog | cidin | cid | cstring | normal
pg_catalog | cidr_in | cidr | cstring | normal
pg_catalog | circle_contain | boolean | circle, circle | normal
pg_catalog | circle_in | circle | cstring | normal
pg_catalog | cstring_in | cstring | cstring | normal
pg_catalog | date_in | date | cstring | normal
pg_catalog | domain_in | "any" | cstring, oid, integer | normal
pg_catalog | enum_in | anyenum | cstring, oid | normal
pg_catalog | event_trigger_in | event_trigger | cstring | normal
pg_catalog | fdw_handler_in | fdw_handler | cstring | normal
pg_catalog | float4in | real | cstring | normal
pg_catalog | float8in | double precision | cstring | normal
pg_catalog | gtsvectorin | gtsvector | cstring | normal
pg_catalog | index_am_handler_in | index_am_handler | cstring | normal
pg_catalog | inet_in | inet | cstring | normal
pg_catalog | int2in | smallint | cstring | normal
pg_catalog | int2vectorin | int2vector | cstring | normal
pg_catalog | int4in | integer | cstring | normal
pg_catalog | int8in | bigint | cstring | normal
pg_catalog | internal_in | internal | cstring | normal
pg_catalog | interval_in | interval | cstring, oid, integer | normal
pg_catalog | intervaltypmodin | integer | cstring[] | normal
pg_catalog | json_in | json | cstring | normal
pg_catalog | jsonb_in | jsonb | cstring | normal
pg_catalog | language_handler_in | language_handler | cstring | normal
pg_catalog | line_in | line | cstring | normal
pg_catalog | lseg_in | lseg | cstring | normal
pg_catalog | macaddr8_in | macaddr8 | cstring | normal
pg_catalog | macaddr_in | macaddr | cstring | normal
pg_catalog | namein | name | cstring | normal
pg_catalog | numeric_in | numeric | cstring, oid, integer | normal
pg_catalog | numerictypmodin | integer | cstring[] | normal
pg_catalog | oidin | oid | cstring | normal
pg_catalog | oidvectorin | oidvector | cstring | normal
pg_catalog | opaque_in | opaque | cstring | normal
pg_catalog | path_in | path | cstring | normal
pg_catalog | pg_ddl_command_in | pg_ddl_command | cstring | normal
pg_catalog | pg_dependencies_in | pg_dependencies | cstring | normal
pg_catalog | pg_lsn_in | pg_lsn | cstring | normal
pg_catalog | pg_ndistinct_in | pg_ndistinct | cstring | normal
pg_catalog | pg_node_tree_in | pg_node_tree | cstring | normal
pg_catalog | point_in | point | cstring | normal
pg_catalog | poly_in | polygon | cstring | normal
pg_catalog | range_in | anyrange | cstring, oid, integer | normal
pg_catalog | record_in | record | cstring, oid, integer | normal
pg_catalog | regclassin | regclass | cstring | normal
pg_catalog | regconfigin | regconfig | cstring | normal
pg_catalog | regdictionaryin | regdictionary | cstring | normal
pg_catalog | regnamespacein | regnamespace | cstring | normal
pg_catalog | regoperatorin | regoperator | cstring | normal
pg_catalog | regoperin | regoper | cstring | normal
pg_catalog | regprocedurein | regprocedure | cstring | normal
pg_catalog | regprocin | regproc | cstring | normal
pg_catalog | regrolein | regrole | cstring | normal
pg_catalog | regtypein | regtype | cstring | normal
pg_catalog | reltimein | reltime | cstring | normal
pg_catalog | shell_in | opaque | cstring | normal
pg_catalog | smgrin | smgr | cstring | normal
pg_catalog | textin | text | cstring | normal
pg_catalog | tidin | tid | cstring | normal
pg_catalog | time_in | time without time zone | cstring, oid, integer | normal
pg_catalog | timestamp_in | timestamp without time zone | cstring, oid, integer | normal
pg_catalog | timestamptypmodin | integer | cstring[] | normal
pg_catalog | timestamptz_in | timestamp with time zone | cstring, oid, integer | normal
pg_catalog | timestamptztypmodin | integer | cstring[] | normal
pg_catalog | timetypmodin | integer | cstring[] | normal
pg_catalog | timetz_in | time with time zone | cstring, oid, integer | normal
pg_catalog | timetztypmodin | integer | cstring[] | normal
pg_catalog | tintervalin | tinterval | cstring | normal
pg_catalog | trigger_in | trigger | cstring | trigger
pg_catalog | tsm_handler_in | tsm_handler | cstring | normal
pg_catalog | tsqueryin | tsquery | cstring | normal
pg_catalog | tsvectorin | tsvector | cstring | normal
pg_catalog | txid_snapshot_in | txid_snapshot | cstring | normal
pg_catalog | unknownin | unknown | cstring | normal
pg_catalog | uuid_in | uuid | cstring | normal
pg_catalog | varbit_in | bit varying | cstring, oid, integer | normal
pg_catalog | varbittypmodin | integer | cstring[] | normal
pg_catalog | varcharin | character varying | cstring, oid, integer | normal
pg_catalog | varchartypmodin | integer | cstring[] | normal
pg_catalog | void_in | void | cstring | normal
pg_catalog | xidin | xid | cstring | normal
pg_catalog | xml_in | xml | cstring | normal
目前正在討論的設計，語法如下
CREATE TABLE t(a tsvector COMPRESSED <cmname> WITH <options>);
ALTER TABLE t ALTER COLUMN a SET COMPRESSED <cmname> WITH <options>;
ALTER TABLE t ALTER COLUMN a SET NOT COMPRESSED;
Also there is syntax of binding compression methods to types:
ALTER TYPE <type> SET COMPRESSED <cmname>;
ALTER TYPE <type> SET NOT COMPRESSED;
jsonbd插件，使用了類似的方法實現jsonb數據的壓縮。
https://github.com/postgrespro/jsonbd
CREATE EXTENSION jsonbd;
CREATE COMPRESSION METHOD cm1 HANDLER jsonbd_compression_handler;
CREATE TABLE t(a JSONB);
ALTER TABLE t ALTER COLUMN a SET COMPRESSED cm1; 最後更新：2017-11-19 15:04:06