如何扩大postgresql存储空间

1. 概述

cstore_fdw实现了 PostgreSQL 数据库的列式存储。列存储非常适合用于数据分析的场景，数据分析的场景下数据是批量加载的。

这个扩展使用了Optimized Row Columnar (ORC)数据存储格式，ORC改进了Facebook的RCFile格式，带来如下好处:

压缩:将内存和磁盘中数据大小削减到2到4倍。可以扩展以支持不同压缩算法。

列投影:只提取和查询相关的列数据。提升IO敏感查询的性能。

跳过索引:为行组存储最大最小统计值，并利用它们跳过无关的行。

2. 使用

cstore_fdw的安装和使用都非常简单，可以参考官方资料。

thub.com/citusdata/cstore_fdw

注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。

下面做几个简单的性能对比，看看cstore_fdw究竟能带来多大的性能提升。

2.1 数据加载

2.1.1 普通表

CREATE TABLE tb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

)

注:要和普通表的全表扫描作对比，所以不建主键和索引。

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy tb1 from STDIN with CSV"

COPY 10000000

1.56user 1.00system 6:42.39elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

776inputs+0outputs (17major+918minor)pagefaults 0swaps

real6m42.402s

user0m15.174s

sys 0m14.904s

postgres=# select pg_total_relation_size('tb1'::regclass)

pg_total_relation_size

------------------------

1161093120

(1 row)

postgres=# \timing

Timing is on.

postgres=# analyze tb1

ANALYZE

Time: 11985.070 ms

插入1千万条记录，数据占用存储大小1.16G，插入耗时6分42秒，分析耗时12秒。

2.1.2 cstore表

$ mkdir -p /home/chenhj/data94/cstore

CREATE EXTENSION cstore_fdw

CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw

CREATE FOREIGN TABLE cstb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

)

SERVER cstore_server

OPTIONS(filename '/home/chenhj/data94/cstore/cstb1.cstore',

compression 'pglz')

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text, www.hnnedu.com id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy cstb1 from STDIN with CSV"

COPY 10000000

1.53user 0.78system 7:35.15elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

968inputs+0outputs (20major+920minor)pagefaults 0swaps

real7m35.520s

user0m14.809s

sys 0m14.170s

[postgres@node2 chenhj]$ ls -l /home/chenhj/data94/cstore/cstb1.cstore

-rw------- 1 postgres postgres 389583021 Jun 23 17:32 /home/chenhj/data94/cstore/cstb1.cstore

postgres=# \timing

Timing is on.

postgres=# analyze cstb1

ANALYZE

Time: 5946.476 ms

插入1千万条记录，数据占用存储大小390M，插入耗时7分35秒，分析耗时6秒。

使用cstore列存储后，数据占用存储大小降到普通表的3分之1。需要说明的是，由于所有TEXT列填充了随机数据，压缩率不算高，某些实际的应用场景下压缩效果会比这更好。

2.2 Text列的like查询性能对比

2.2.1 普通表

清除文件系统缓存，并重启PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# free

total used free sharedbuffers cached

Mem: 2055508 7713561284152 0 9900 452256

-/+ buffers/cache: 3092001746308

Swap: 4128760 3876243741136

[root@node2 ~]# echo 1 >/proc/sys/vm/drop_caches

[root@node2 ~]# free

total used free sharedbuffers cached

Mem: 2055508 3267881728720 0228 17636

-/+ buffers/cache: 3089241746584

Swap: 4128760 3819123746848

对Text列执行like查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.420.00 95.40

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 58.55 330.68 212.0873514414714848

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real0m7.051s

user0m0.001s

sys 0m0.004s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.430.00 95.39

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 58.90 381.53 211.9084895974714956

耗时7.1秒，产生IO读1.14G,IO写108K。

不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.6秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.430.00 95.39

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 58.81 332.20 213.0673503014714364

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real0m1.601s

user0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.430.00 95.38

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 58.80 332.12 213.0173503374714364

2.2.2 cstore表

清除文件系统缓存，并重启PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 >/proc/sys/vm/drop_caches

对Text列执行like查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.380.00 95.45

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 58.12 376.42 209.0484920174716048

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real0m2.786s

user0m0.002s

sys 0m0.003s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.380.00 95.44

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 58.12 378.75 208.8985507614716048

耗时2.8秒，产生IO读59M,IO写0K。执行时间优化的虽然不是太多，但IO大大减少，可见列投影起到了作用。

不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.4秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.360.00 95.47

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 57.75 376.33 207.5885508094716524

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real0m1.424s

user0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.800.000.383.360.00 95.47

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 57.70 375.96 207.3885508094716588

2.3 对Int列执行=查询

2.3.1 普通表

清除文件系统缓存，并重启PostgreSQL后

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 >/proc/sys/vm/drop_caches

对Int列执行=查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.790.000.373.330.00 95.50

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 57.25 373.21 205.6785608974717624

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

1

(1 row)

real0m6.844s

user0m0.002s

sys 0m0.006s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.790.000.373.340.00 95.49

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 57.60 422.57 205.5496991614717708

耗时6.8秒，产生IO读1.14G,IO写84K

不清缓存，再执行一次。消耗时间降到1.1秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14_x86_64_(2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.790.000.373.330.00 95.50

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn

dm-2 57.44 421.37 204.9796991774718032

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

性MySQLPostgreSQL

实例通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。

通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。

数据库数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。

数据缓冲区通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。

数据库连接客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

身份验证MySQL 在数据库级管理身份验证。 基本只支持密码认证。PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证

加密可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。

审计可以对 querylog 执行 grep。可以在表上使用 PL/pgSQL 触发器来进行审计。

查询解释使用 EXPLAIN 命令查看查询的解释计划。使用 EXPLAIN 命令查看查询的解释计划。

备份、恢复和日志InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。

JDBC 驱动程序可以从 参考资料 下载 JDBC 驱动程序。可以从 参考资料 下载 JDBC 驱动程序。

一、 PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些。

二、任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）。

三、PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

四、PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的 *** 作，这个和PGSQL的MVCC实现有关系。

五、PG 的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。

六、PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MYSQL就差的很远，很多分析功能都不支持，腾讯内部数据存储主要是MYSQL，但是数据分析主要是HADOOP+PGSQL。

七、PG 的有多种集群架构可以选择，plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便， *** 作非常简单。

八、一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。

九，对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

十，pgsql对于numa架构的支持比mysql强一些，比MYSQL对于读的性能更好一些，pgsql提交可以完全异步，而mysql的内存表不够实用（因为表锁的原因）

最后说一下我感觉 PG 不如 MySQL 的地方。

第一，MySQL有一些实用的运维支持，如 slow-query.log ，这个pg肯定可以定制出来，但是如果可以配置使用就更好了。

第二是mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分，

第三点，MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。

第四点，从测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多.

第五点，对于web应用来说,mysql 5.6 的内置MC API功能很好用，PGSQL差一些。

另外一些：

pgsql和mysql都是背后有商业公司，而且都不是一个公司。大部分开发者，都是拿工资的。

说mysql的执行速度比pgsql快很多是不对的，速度接近，而且很多时候取决于你的配置。

对于存储过程，函数，视图之类的功能，现在两个数据库都可以支持了。

另外多线程架构和多进程架构之间没有绝对的好坏，oracle在unix上是多进程架构，在windows上是多线程架构。

很多pg应用也是24/7的应用，比如skype. 最近几个版本VACUUM基本不影响PGSQL 运行，8.0之后的PGSQL不需要cygwin就可以在windows上运行。

至于说对于事务的支持，mysql和pgsql都没有问题。

---