本节简单介绍了Greenplum开源的列式存储zedstore的安装和使用. **安装** 从Github上下载源码,与普通PG一样,编译安装即可 ``` [root@localhost postgres-zedstore]# ./configure --enable-debug --with-python --with-perl --with-tcl --with-gssapi --with-pam --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-dtrace --enable-depend --enable-cassert --with-systemd CFLAGS="-O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2" --prefix=/appdb/zedstore checking build system type... x86_64-pc-linux-gnu ... [root@localhost postgres-zedstore]# make -j4 ... [root@localhost postgres-zedstore]# make install ... PostgreSQL installation complete. ``` **Heap vs ZedStore** 创建用户,初始化数据库 ``` [zedstore@localhost ~]$ initdb -E utf8 -D /data/zedstore/testdb The files belonging to this database system will be owned by user "zedstore". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default text search configuration will be set to "english". Data page checksums are disabled. creating directory /data/zedstore/testdb ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default timezone ... PRC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /data/zedstore/testdb -l logfile start ``` 下面来对比一下head am和zedstore的性能差异 *Heap* ``` testdb=# create table t_heap(id int,c1 int,c2 varchar(20)); CREATE TABLE testdb=# insert into t_heap select x,x,'c2'||x from generate_series(1,5000000) as x; INSERT 0 5000000 testdb=# ``` 执行查询 ``` testdb=# explain analyze select avg(id),sum(c1),max(c2) from t_heap; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ ---------------------------- Finalize Aggregate (cost=69209.94..69209.95 rows=1 width=72) (actual time=964.313..964.314 rows=1 loops=1) -> Gather (cost=69209.71..69209.92 rows=2 width=72) (actual time=963.978..966.938 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=68209.71..68209.72 rows=1 width=72) (actual time=929.220..929.220 rows=1 loo ps=3) -> Parallel Seq Scan on t_heap (cost=0.00..52584.55 rows=2083355 width=17) (actual time=0.094..25 6.014 rows=1666667 loops=3) Planning Time: 17.157 ms Execution Time: 968.461 ms (8 rows) testdb=# ``` 执行时间为968ms 空间占用 ``` testdb=# select pg_size_pretty(pg_table_size('t_heap')); pg_size_pretty ---------------- 248 MB (1 row) ``` *ZedStore* ``` testdb=# create table t_zedstore(id int,c1 int,c2 varchar(20)) using zedstore; CREATE TABLE testdb=# insert into t_zedstore select x,x,'c2'||x from generate_series(1,5000000) as x; INSERT 0 5000000 testdb=# ``` 执行查询 ``` testdb=# explain analyze select avg(id),sum(c1),max(c2) from t_zedstore; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ ------------------------------- Finalize Aggregate (cost=24917.50..24917.51 rows=1 width=72) (actual time=1341.238..1341.239 rows=1 loops=1) -> Gather (cost=24917.27..24917.48 rows=2 width=72) (actual time=1341.046..1343.387 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=23917.27..23917.28 rows=1 width=72) (actual time=1328.432..1328.432 rows=1 l oops=3) -> Parallel Seq Scan on t_zedstore (cost=0.00..18968.87 rows=659787 width=17) (actual time=0.702. .837.075 rows=1666667 loops=3) Planning Time: 0.643 ms Execution Time: 1343.612 ms (8 rows) testdb=# ``` 使用zedstore的时间是1343ms，比起heap方式要慢接近40%。 空间占用 ``` testdb=# select pg_size_pretty(pg_table_size('t_zedstore')); pg_size_pretty ---------------- 97 MB (1 row) testdb=# ``` 不过使用zedstore，空间占用倒是只有原来的40%左右。 **参考资料** [PostgreSQL 基于access method api的列存zedstore](https://github.com/digoal/blog/blob/master/201905/20190531_03.md)