Cstore_fdw實現PostgreSQL數據庫的列存儲。列存儲非常適合數據分析場景，在這些場景中，數據是成批加載的。
該擴展使用優化的行列(ORC)數據存儲格式，改進了臉書的RCFile格式，並帶來以下好處:
壓縮:將內存和磁盤中的數據大小減少到原來的2到4倍。可以擴展以支持不同的壓縮算法。
列投影:只提取與查詢相關的列數據。提高IO敏感查詢的性能。
跳過索引:存儲行組的最大和最小統計信息，並使用它們跳過不相關的行。
使用
cstore_fdw的安裝和使用非常簡單，可以參考官方資料。
thub.com/citusdata/cstore_fdw
註)註意cstore_fdw只支持PostgreSQL9.3和9.4。
下面做幾個簡單的性能對比，看看cstore_fdw能帶來多大的性能提升。
2.1數據加載
2.1.1常用表
創建表tb1
（
id int，
c1文本，
c2文本，
c3文本，
c4文本，
c5文本，
c6文本，
c7文本，
c8文本，
c9文本，
c10文本
);
註意:與普通表的全表掃描相比，不構建主鍵和索引。
[postgres @ node 2 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，id::text，id::text:text from generate _ series(1，10000000)id " | time psql-p 40382-c " copy TB 1 from
復制1000000
1.56用戶1.00系統6:42.39已用0% CPU(0 avg text+0 avg data 7632 max resident)k
776輸入+0輸出(17主+918輔)缺頁0交換
真正的6m42.402s
用戶0m15.174s
sys 0m14.904s
postgres = # select pg _ total _ relation _ size(' TB 1 '::regclass)；
pg_total_relation_size
-
1161093120
(1行)
postgres = # \計時
計時開始。
postgres = # analyze TB 1；
分析
時間:11985.070毫秒
插入65438+1億條記錄，數據的存儲大小為1.16G，插入耗時6分42秒，分析耗時12秒。
2.1.2厘米存儲表
$ mkdir-p/home/chenhj/data 94/c store
創建擴展cstore _ fdw
創建服務器cstore _ server外部數據包裝器cstore _ fdw
創建外部表cstb1
（
id int，
c1文本，
c2文本，
c3文本，
c4文本，
c5文本，
c6文本，
c7文本，
c8文本，
c9文本，
c10文本
)
服務器cstore_server
選項(文件名'/home/chenhj/data 94/cs store/cstb 1 . cs store '，
壓縮' pglz ')；
[postgres @ node 2 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，id::text，id::text來自generate_series(1，10000000)id " | time psql-p 40382-c "復制cstb1
復制1000000
1.53用戶0.78系統7:35.15已用0% CPU(0 avg text+0 avg data 7632 max resident)k
968輸入+0輸出(20主+920副)缺頁0交換
真實7m35.520s
用戶0m14.809s
sys 0m14.170s
[postgres @ node 2 chenhj]$ ls-l/home/chenhj/data 94/c store/cstb 1 . c store
-rw-1 postgres postgres 389583021 Jun 23 17:32/home/chenhj/data 94/c store/cstb 1 . c store
postgres = # \計時
計時開始。
postgres = # analyze cstb 1；
分析
時間:5946.476毫秒
插入10萬條記錄，數據存儲大小為390M，插入耗時7分35秒，分析耗時6秒。
使用cstore列存儲後，數據占用的存儲大小減少到1，是普通表的3倍。需要註意的是，因為所有的文本列都是用隨機數據填充的，所以壓縮比並不高，在壹些實際應用場景下壓縮效果會更好。
2.2文本列的相似查詢性能比較
2.2.1常用表
清除文件系統緩存並重新啟動PostgreSQL。
[postgres @ node 2 chenhj]$ pg _ CTL-D/home/chenhj/data 94-l日誌文件94重新啟動
[root@node2 ~]# free
緩存的已用空閑共享緩沖區總數
記憶:2055508 771356 1284152 0 9900 452256
-/+緩沖區/緩存:309200 1746308
互換:4128760 387624 3741136
[root @ node 2 ~]# echo 1 & gt；/proc/sys/VM/drop _ cache
[root@node2 ~]# free
緩存的已用空閑共享緩沖區總數
記憶:2055508 326788 1728720 0 228 17636
-/+緩沖區/緩存:308924 1746584
互換:4128760 381912 3746848
對文本列執行like查詢。
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.42 0.00 95.40
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
德國馬克-2 58.55 330.68 212.08 7351441 4714848
[postgres @ node 2 chenhj]$ time psql-p 40382-c " select count(*)from TB 1 where c 1 like ' % 66 ' "
數數
-
100000
(1行)
real 0m7.051s
用戶0m0.001s
sys 0m0.004s
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.43 0.00 95.39
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
德國馬克-2 58.90 381.53 211.90 8489597 4714956
產生IO讀1.14g，IO寫108K需要7.1秒。
文件系統緩存不清楚，不重啟PostgreSQL，再次執行。消耗時間降低到1.6秒，幾乎不產生IO。
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.43 0.00 95.39
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
德國馬克-2 58.81 332.20 21 3.06 7350301 471 4364
[postgres @ node 2 chenhj]$ time psql-p 40382-c " select count(*)from TB 1 where c 1 like ' % 66 ' "
數數
-
100000
(1行)
real 0m1.601s
用戶0m0.002s
sys 0m0.001s
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.43 0.00 95.38
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
德國馬克-2 58.80 332.12 213.01 7350337 4714364
2.2.2商店表
清除文件系統緩存並重新啟動PostgreSQL。
[postgres @ node 2 chenhj]$ pg _ CTL-D/home/chenhj/data 94-l日誌文件94重新啟動
[root @ node 2 ~]# echo 1 & gt；/proc/sys/VM/drop _ cache
對文本列執行like查詢。
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.38 0.00 95.45
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
58.12 376.42 209.04 8492017 4716048
[postgres @ node 2 chenhj]$ time psql-p 40382-c " select count(*)from cstb 1 where c 1 like ' % 66 ' "
數數
-
100000
(1行)
真正的0m2.786s
用戶0m0.002s
sys 0m0.003s
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.38 0.00 95.44
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
58.12 378.75 208.89 8550761 4716048
耗時2.8秒，結果是59M，IO讀，0K IO寫。雖然進行時間優化的人不多，但是IO卻大大減少，可見列投影起了作用。
文件系統緩存不清楚，不重啟PostgreSQL，再次執行。消耗時間降低到1.4秒，幾乎不產生IO。
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.36 0.00 95.47
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
207.58 8550809 4716524
[postgres @ node 2 chenhj]$ time psql-p 40382-c " select count(*)from cstb 1 where c 1 like ' % 66 ' "
數數
-
100000
(1行)
real 0m1.424s
用戶0m0.002s
sys 0m0.001s
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.80 0.00 0.38 3.36 0.00 95.47
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
57.70 375.96 207.38 8550809 4716588
2.3 execute =對Int列進行查詢
2.3.1常用表
清除文件系統緩存並重新啟動PostgreSQL後
[postgres @ node 2 chenhj]$ pg _ CTL-D/home/chenhj/data 94-l日誌文件94重新啟動
[root @ node 2 ~]# echo 1 & gt；/proc/sys/VM/drop _ cache
Execute =對Int列進行查詢。
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.79 0.00 0.37 3.33 0.00 95.50
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
德國馬克57.25 373.21 205.67 8560897 4717624
[postgres @ node 2 chenhj]$ time psql-p 40382-c " select count(*)from TB 1 where id = 666666 "
數數
-
1
(1行)
real 0m6.844s
用戶0m0.002s
sys 0m0.006s
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.79 0.00 0.37 3.34 0.00 95.49
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
9699161 4717708
耗時6.8秒，導致IO讀1.14g，IO寫84K。
緩存不清楚。再運行壹次。消耗時間減少到1.1秒，幾乎不產生IO。
[postgres @ node 2 chenhj]$ iostat-k DM-2
Linux 2 . 6 . 32-71 . el6 . x86 _ 64(node 2)06/23/14 _ x86 _ 64 _(2個CPU)
平均CPU:%用戶%好的%系統%低等待%偷用%空閑
0.79 0.00 0.37 3.33 0.00 95.50
設備:TPS kB _ read/s kB _ wrtn/s kB _ read kB _ wrtn
德國馬克-2 57.44 421.37 204.97 9699177 4718032
[postgres @ node 2 chenhj]$ time psql-p 40382-c " select count(*)from TB 1 where id = 666666 "
數數
-