如果想要在PostgreSQL中存儲二進制數(shù)據(jù)，例如存儲Word、Excel文檔，圖片文件等，可以使用bytea類型的列。bytea類型是PostgreSQL特有的存儲二進制數(shù)據(jù)的字段類型，與SQL標準中的BLOB和BINARY
衡東網(wǎng)站建設公司成都創(chuàng)新互聯(lián),衡東網(wǎng)站設計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗。已為衡東1000+提供企業(yè)網(wǎng)站建設服務。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站制作要多少錢，請找那個售后服務好的衡東做網(wǎng)站的公司定做！
LARGE OBJECT類型異曲同工。這在PostgreSQL文檔的bytea類型介紹中有所說明。
接下來先說說如何向表中插入、更新bytea數(shù)據(jù)。
PostgreSQL允許在SQL命令中包含bytea類型的數(shù)據(jù)，以便能夠使用INSERT向表中插入含有二進制數(shù)據(jù)的記錄，使用UPDATE和調(diào)用與bytea類型相關的函數(shù)更新和操作bytea類型數(shù)據(jù)。二進制數(shù)據(jù)是一個字節(jié)序列，然而SQL命令是文本字符串，怎樣在SQL中寫入二進制數(shù)據(jù)呢？答案很簡單，把每一個字節(jié)轉(zhuǎn)換成對應的三位十進制數(shù)字的八進制數(shù)字符串表示，以雙斜線做為前綴，即0x00表示為\\000、0x2C表示為\\02C、0xFF表示為\\377，并按照bytea類型的要求在字符串前端的單引號外注明E。舉例如下：
INSERT INTO table1 (fileid, filename, content) VALUES (1, 'filename.doc', E'\\000\\001\\002');
INSERT INTO table1 (fileid, filename, content) VALUES (2, 'anotherfile.jpg', E'\\000\\377');
UPDATE table1 SET content = E'\\000\\000\\000' WHERE fileid
= 1;
UPDATE table1 SET content = content || E'\\377\\377\\377' WHERE fileid
= 2;
可以在INSERT
INTO中包含整個文件的bytea類型字符串，也可以像上面第四行那樣，分塊追加。對于短小的二進制數(shù)據(jù)，在命令控制臺中編輯SQL命令也未嘗不可。但是如果要存儲一個圖片文件或者Word文檔之類的大型二進制數(shù)據(jù)的時候，就需要借助數(shù)據(jù)訪問借口，或者自己寫一個字節(jié)轉(zhuǎn)換程序，直接操作SQL語句。
插入bytea數(shù)據(jù)后可以使用SELECT語句獲取它。如下所示：
SELECT content FROM table1;
在命令控制臺中，我們會看到以輸入時的字符串格式輸出二進制數(shù)據(jù)，這是PostgreSQL做的轉(zhuǎn)換。在Python中使用psycopg2模塊，執(zhí)行上述SELECT語句后能夠獲得原始的二進制字節(jié)字符串，可以直接寫入二進制文件。
順便說明一下。對于字節(jié)的轉(zhuǎn)換，PostgreSQL的文檔說的非常詳細，按照零字節(jié)、單引號、斜線，以及字符的可打印性分別作了討論。原因是需要逃逸單引號和斜線字符，另外可打印字符可以不作轉(zhuǎn)換，直接出現(xiàn)。
PostgreSQL的主要優(yōu)點：
1、對事務的支持與MySQL相比，經(jīng)歷了更為徹底的測試。對于一個嚴肅的商業(yè)應用來說，事務的支持是不可或缺的。
2、MySQL對于無事務的MyISAM表。采用表鎖定，一個長時間運行的查詢很可能會長時間地阻礙對表的更新。而PostgreSQL不存在這樣的問題。
3、PostgreSQL支持存儲過程，而目前MySQL不支持，對于一個嚴肅的商業(yè)應用來說，作為數(shù)據(jù)庫本身，有眾多的商業(yè)邏輯的存在，此時使用存儲過程可以在較少地增加數(shù)據(jù)庫服務器的負擔的前提下，對這樣的商業(yè)邏輯進行封裝，并可以利用數(shù)據(jù)庫服務器本身的內(nèi)在機制對存儲過程的執(zhí)行進行優(yōu)化。此外存儲過程的存在也避免了在網(wǎng)絡上大量的原始的SQL語句的傳輸，這樣的優(yōu)勢是顯而易見的。
4、對視圖的支持，視圖的存在同樣可以最大限度地利用數(shù)據(jù)庫服務器內(nèi)在的優(yōu)化機制。而且對于視圖權限的合理使用，事實上可以提供行級別的權限，這是MySQL的權限系統(tǒng)所無法實現(xiàn)的。
5、對觸發(fā)器的支持，觸發(fā)器的存在不可避免的會影響數(shù)據(jù)庫運行的效率，但是與此同時，觸發(fā)器的存在也有利于對商業(yè)邏輯的封裝，可以減少應用程序中對同一商業(yè)邏輯的重復控制。合理地使用觸發(fā)器也有利于保證數(shù)據(jù)的完整性。
6、對約束的支持。約束的作用更多地表現(xiàn)在對數(shù)據(jù)完整性的保證上，合理地使用約束，也可以減少編程的工作量。
1. 概述
cstore_fdw實現(xiàn)了 PostgreSQL 數(shù)據(jù)庫的列式存儲。列存儲非常適合用于數(shù)據(jù)分析的場景，數(shù)據(jù)分析的場景下數(shù)據(jù)是批量加載的。
這個擴展使用了Optimized Row Columnar (ORC)數(shù)據(jù)存儲格式，ORC改進了Facebook的RCFile格式，帶來如下好處:
壓縮:將內(nèi)存和磁盤中數(shù)據(jù)大小削減到2到4倍?？梢詳U展以支持不同壓縮算法。
列投影:只提取和查詢相關的列數(shù)據(jù)。提升IO敏感查詢的性能。
跳過索引:為行組存儲最大最小統(tǒng)計值，并利用它們跳過無關的行。
2. 使用
cstore_fdw的安裝和使用都非常簡單，可以參考官方資料。
thub.com/citusdata/cstore_fdw
注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。
下面做幾個簡單的性能對比，看看cstore_fdw究竟能帶來多大的性能提升。
2.1 數(shù)據(jù)加載
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
real 6m42.402s
user 0m15.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千萬條記錄，數(shù)據(jù)占用存儲大小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, 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
real 7m35.520s
user 0m14.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千萬條記錄，數(shù)據(jù)占用存儲大小390M，插入耗時7分35秒，分析耗時6秒。
使用cstore列存儲后，數(shù)據(jù)占用存儲大小降到普通表的3分之1。需要說明的是，由于所有TEXT列填充了隨機數(shù)據(jù)，壓縮率不算高，某些實際的應用場景下壓縮效果會比這更好。
2.2 Text列的like查詢性能對比
2.2.1 普通表
清除文件系統(tǒng)緩存，并重啟PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# free
total used free shared buffers cached
Mem: 2055508 771356 1284152 0 9900 452256
-/+ buffers/cache: 309200 1746308
Swap: 4128760 387624 3741136
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
[root@node2 ~]# free
total used free shared buffers cached
Mem: 2055508 326788 1728720 0 228 17636
-/+ buffers/cache: 308924 1746584
Swap: 4128760 381912 3746848
對Text列執(zhí)行l(wèi)ike查詢
[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.80 0.00 0.38 3.42 0.00 95.40
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.55 330.68 212.08 7351441 4714848
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m7.051s
user 0m0.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.80 0.00 0.38 3.43 0.00 95.39
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.90 381.53 211.90 8489597 4714956
耗時7.1秒，產(chǎn)生IO讀1.14G,IO寫108K。
不清文件系統(tǒng)緩存，不重啟PostgreSQL，再執(zhí)行一次。消耗時間降到1.6秒，幾乎不產(chǎn)生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.80 0.00 0.38 3.43 0.00 95.39
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.81 332.20 213.06 7350301 4714364
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m1.601s
user 0m0.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.80 0.00 0.38 3.43 0.00 95.38
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.80 332.12 213.01 7350337 4714364
2.2.2 cstore表
清除文件系統(tǒng)緩存，并重啟PostgreSQL
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
對Text列執(zhí)行l(wèi)ike查詢
[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.80 0.00 0.38 3.38 0.00 95.45
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.12 376.42 209.04 8492017 4716048
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m2.786s
user 0m0.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.80 0.00 0.38 3.38 0.00 95.44
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 58.12 378.75 208.89 8550761 4716048
耗時2.8秒，產(chǎn)生IO讀59M,IO寫0K。執(zhí)行時間優(yōu)化的雖然不是太多，但IO大大減少，可見列投影起到了作用。
不清文件系統(tǒng)緩存，不重啟PostgreSQL，再執(zhí)行一次。消耗時間降到1.4秒，幾乎不產(chǎn)生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.80 0.00 0.38 3.36 0.00 95.47
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.75 376.33 207.58 8550809 4716524
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"
count
--------
100000
(1 row)
real 0m1.424s
user 0m0.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.80 0.00 0.38 3.36 0.00 95.47
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.70 375.96 207.38 8550809 4716588
2.3 對Int列執(zhí)行=查詢
2.3.1 普通表
清除文件系統(tǒng)緩存，并重啟PostgreSQL后
[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart
[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches
對Int列執(zhí)行=查詢
[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.79 0.00 0.37 3.33 0.00 95.50
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.25 373.21 205.67 8560897 4717624
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
count
-------
1
(1 row)
real 0m6.844s
user 0m0.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.79 0.00 0.37 3.34 0.00 95.49
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.60 422.57 205.54 9699161 4717708
耗時6.8秒，產(chǎn)生IO讀1.14G,IO寫84K
不清緩存，再執(zhí)行一次。消耗時間降到1.1秒，幾乎不產(chǎn)生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.79 0.00 0.37 3.33 0.00 95.50
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dm-2 57.44 421.37 204.97 9699177 4718032
[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"
count
-------
序列數(shù)可以增加postgresql數(shù)據(jù)表的檢索速度，同時降低數(shù)據(jù)查詢時的資源消耗。那么如何在postgresql中創(chuàng)建序列數(shù)并且應用呢？下面我給大家分享一下。
工具/材料
pgAdmin4
創(chuàng)建序列數(shù)
01
首先打開pgAdmin4，展開postgresql數(shù)據(jù)庫，找到模式下面的public選項，如下圖所示
02
接下來在public下面右鍵單擊序列，然后點擊Create下面的sequence選項，如下圖所示
03
在彈出的創(chuàng)建Sequence界面中首先給序列數(shù)起一個名字，如下圖所示，注意都用英文
04
然后切換到Definition頁卡，定義一下序列的增加量，如下圖所示，其中maximum根據(jù)自己的需要進行設置
05
最后回到數(shù)據(jù)庫主界面，你會看到序列下面多出了一個項，這就是我們創(chuàng)建的序列數(shù)了，如下圖所示
在數(shù)據(jù)表中應用序列數(shù)
01
首先選中一個數(shù)據(jù)表，點擊右側(cè)的編輯按鈕，如下圖所示
02
在彈出的編輯界面中切換到Columns頁卡，點擊ID簽名的編輯按鈕，如下圖所示
03
最后在字段的編輯界面中切換到Variables選項卡，然后在Value列中通過nextval函數(shù)帶入剛才定義的序列數(shù)即可，如下圖所示
一個bigint它占用了八個字節(jié)的存儲空間，可以精確的表示從-2^63到2^63-1(即從-9，223，372，036，854，775，808到 9，223，372，036，854，775，807)之間的整數(shù)。所以恩，不是說一個數(shù)字，而是從-2^63到2^63-1都可以.
varchar 是按字節(jié)來存儲，如 varchar(10),會占用11個字節(jié)，其中10個是用來定義字符，還有一個字節(jié)是用來存儲列的長度
先不考慮數(shù)據(jù)庫設計的是否合理
你是想新建表，之后把數(shù)據(jù)存進去？還是建立一個臨時表把數(shù)據(jù)查出來？
首先你兩個表要有一定的關聯(lián)關系，兩個表的 SIP一樣？DIP一樣？還是User一樣？
根據(jù)一樣的作為兩個表的關聯(lián)條件
select * from 表1 t1 left join 表2 t2 on t1.SIP =t2.SIP
重慶分公司
重慶分公司
