PostgreSQL 11 preview - compress method 接口 - 暨开放接口系列

10 minute read

背景

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、路由

http://pgrouting.org/

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、图式搜索

http://www.agensgraph.com/

7、流式计算

https://www.pipelinedb.com/

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://pgxn.org/

http://pgfoundry.org/

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;  

Flag Counter

digoal’s 大量PostgreSQL文章入口