作者
digoal
日期
2019-05-31
标签
PostgreSQL , zedstore , 列存 , api , access method
背景
PostgreSQL 11引入access method的功能，使得存储完全剥离，用户可以自定义存储例如，zheap存储。
CREATE ACCESS METHOD name
TYPE access_method_type
HANDLER handler_function
我们可以看到目前支持的am
```
postgres=# select * from pg_am;
oid | amname | amhandler | amtype
------+--------+----------------------+--------
2 | heap | heap_tableam_handler | t
403 | btree | bthandler | i
405 | hash | hashhandler | i
783 | gist | gisthandler | i
2742 | gin | ginhandler | i
4000 | spgist | spghandler | i
3580 | brin | brinhandler | i
(7 rows)
postgres=# \dA+
List of access methods
Name | Type | Handler | Description
--------+-------+----------------------+----------------------------------------
brin | Index | brinhandler | block range index (BRIN) access method
btree | Index | bthandler | b-tree index access method
gin | Index | ginhandler | GIN index access method
gist | Index | gisthandler | GiST index access method
hash | Index | hashhandler | hash index access method
heap | Table | heap_tableam_handler | heap table access method
spgist | Index | spghandler | SP-GiST index access method
(7 rows)
```
同时建表也使用了新的语法，using method
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
例如我们创建一个表，使用heap存储接口。
postgres=# create table abcd(id int, info text) using heap;
CREATE TABLE
这个接口开放后，原来蠢蠢欲动的内存表、列存表，甚至不得已使用fdw来支持的列存cstore_fdw, imcs，以及pg_variable等插件。现在都不需要那么做了。
《PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒》
https://github.com/citusdata/cstore_fdw
greenplum开源zedstore列存引擎
https://www.postgresql-archive.org/Zedstore-compressed-in-core-columnar-storage-td6081536.html#a6081725
CREATE TABLE <name> (column listing) USING zedstore;
使用列存储的分析能力提升杠杠的。
```
postgres=# SELECT AVG(i199) FROM (select i199 from layout offset 0) x; -- heap
avg
500000.500000000000
(1 row)
Time: 4679.026 ms (00:04.679)
postgres=# SELECT AVG(i199) FROM (select i199 from zlayout offset 0) x; -- zedstore
avg
500000.500000000000
(1 row)
Time: 379.710 ms
```
源码如下
https://github.com/greenplum-db/postgres/tree/zedstore
https://github.com/greenplum-db/postgres/tree/zedstore/src/backend/access/zedstore
- Performance improvement for queries selecting subset of columns
(reduced IO). - Reduced on-disk footprint compared to heap table. Shorter tuple
headers and also leveraging compression of similar type data - Be first-class citizen in the Postgres architecture (tables data can
just independently live in columnar storage) - Fully MVCC compliant
- All Indexes supported
- Hybrid row-column store, where some columns are stored together, and
others separately. Provide flexibility of granularity on how to
divide the columns. Columns accessed together can be stored
together. - Provide better control over bloat (similar to zheap)
- Eliminate need for separate toast tables
- Faster add / drop column or changing data type of column by avoiding
full rewrite of the table.
使用zedstore列存的好处：
统计分析时，如果访问的记录数很多，但是访问的列较少，使用列存可以降低扫描量
提高压缩比
zedstore支持行列混合存储，可以指定哪些列作为行存，哪些列作为列存储。
zedstore支持类似zheap的设计，减轻了高频度更新时膨胀的问题
预计还会看到很多的存储引擎加入PG的行列。
参考
https://www.postgresql-archive.org/Zedstore-compressed-in-core-columnar-storage-td6081536.html#a6081725
https://www.pgcon.org/2019/
https://www.postgresql.org/docs/12/sql-createtable.html
https://www.postgresql.org/docs/12/sql-create-access-method.html
https://github.com/greenplum-db/postgres/tree/zedstore
https://github.com/greenplum-db/postgres/tree/zedstore/src/backend/access/zedstore
《PostgreSQL 12 preview - psql增强, \dA 列出当前系统中的access method (table am, index am)》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
