PG外部数据源的列式存储

1 安装软件

yum -y install protobuf protobuf-c protobuf-c-devel openssl openssl-devel

2 安装cstore_fdw

--下载源码
git clone https://github.com/citusdata/cstore_fdw.git


--修改Makefile中的PG_CONFIG值为pg_config所在的目录
--执行make && make install	

3 修改数据库配置文件postgresql.conf并重启数据库服务
shared_preload_libraries = ‘cstore_fdw’
4 登录数据库创建扩展和服务

create extension cstore_fdw;
create server cstore_server foreign data wrapper cstore_fdw ;	

5 创建普通表和外部表

--普通表
CREATE TABLE customer
(
    customer_id TEXT,
    review_date DATE,
    review_rating INTEGER,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_sales_rank BIGINT,
    product_group TEXT,
    product_category TEXT,
    product_subcategory TEXT,
    similar_product_ids CHAR(10)[]
);
--外部表
CREATE FOREIGN TABLE customer_reviews
(
    customer_id TEXT,
    review_date DATE,
    review_rating INTEGER,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_sales_rank BIGINT,
    product_group TEXT,
    product_category TEXT,
    product_subcategory TEXT,
    similar_product_ids CHAR(10)[]
)
SERVER cstore_server
OPTIONS(compression 'pglz');	

6 拷贝数据到普通表

copy customer to '/home/highgo/customer_data.csv' with csv;	

7 向外部表中插入同样的数据

insert into customer_reviews select * from customer;	

8 查询普通表所占磁盘大小

select pg_size_pretty(pg_relation_size('customer'));	

9 查询经过列式存储的表占磁盘的大小

cd /home/highgo/highgo/database/4.5/xudata/cstore_fdw/14507
ll -h