标签
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、化学
http://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;