寫在前面的話：本文內(nèi)容有對應(yīng)50分鐘視頻講解，有興趣可以訪問PG與開發(fā)者共舞。

很多數(shù)據(jù)庫的對比的活動都是基于數(shù)據(jù)庫本身的一些的底層的功能以及純數(shù)據(jù)庫方面的對比，等等。更多方面數(shù)據(jù)庫是要為應(yīng)用來服務(wù)的，而生產(chǎn)應(yīng)用的大部分都是與程序員有關(guān)的,怎么服務(wù)于程序員，提高程序員的舒適感，也是一個重要的話題，提高了程序員的工作效率，減少代碼量，將邏輯下移，減少程序中的BUG，這是一個數(shù)據(jù)庫可以做到的，不是每個企業(yè)都是互聯(lián)網(wǎng)企業(yè)，不是每個業(yè)務(wù)都是高并發(fā)的業(yè)務(wù)，我們有大量復(fù)雜業(yè)務(wù)，如果將業(yè)務(wù)邏輯全部都在程序端實現(xiàn)，勢必會增加程序員的勞動力，開發(fā)周期延長，后續(xù)的程序的靈活性也會受到部分限制。

所以這次的課，主要是面對程序員，服務(wù)于程序員，有一部分老板也可能對降低成本，尤其是開發(fā)的成本很感興趣，另外可能也有部分純軟件開發(fā)企業(yè)會對后續(xù)軟件的維護，尤其是數(shù)據(jù)庫的維護，感興趣，因為復(fù)雜的數(shù)據(jù)庫高可用結(jié)構(gòu)，尤其分庫分表，限制了純開發(fā)企業(yè)的開發(fā)軟件的方式和后期與簽約企業(yè)的運維維護方面的難度。

那么下面我們就從幾點來闡述，PG到底和開發(fā)者是一個什么關(guān)系，到底開發(fā)者在使用PG 能不能得到好處的問題。

以下單純以服務(wù)于開發(fā)人員和客戶的觀點進行本次課程的闡述，并且會輔以一些例子來說明。

我相信在看到上面的圖上的文字，有一部分非互聯(lián)網(wǎng)的開發(fā)人員，已經(jīng)坐不住的，但實際上是這樣的，互聯(lián)網(wǎng)企業(yè)使用的數(shù)據(jù)庫的要求很多，當然這也是由于數(shù)據(jù)庫的原理造成的問題，但需要開發(fā)人員來買單。

在軟件開發(fā)中，最讓開發(fā)人員頭疼的問題之一，就是字段的預(yù)留的大小的問題，某些大型的軟件開發(fā)企業(yè)和互聯(lián)網(wǎng)企業(yè)，在使用開源數(shù)據(jù)庫中大多都會遇到這個問題，因為某些開源數(shù)據(jù)庫的字段大小，與字段擴展和系統(tǒng)的性能是有"綁定“的關(guān)系， 有的時候都要進行一番“辯論”。所以每次開發(fā)和DBA之間的沖突就因為某些問題開始產(chǎn)生矛盾?；ハ喽疾焕斫?#xff0c;軟件開發(fā)不理解對方為什么要嚴格的去扣一個字段的大小，254和512有差嗎，如果以后擴展，還要麻煩，我為什么不能使用更大的字段，并且后續(xù)的開發(fā)中某些系統(tǒng)的對接表還要記住之間的字段大小匹配，我太難了，DBA方也有自己的理由，明明如果你使用了特別大的字段后，系統(tǒng)的性能會明顯下降，為了系統(tǒng)性能我要求難道不對嗎，后續(xù)你要擴展，我還要冒著風(fēng)險給你加字段擴展字段，我容易嗎？

所以矛盾就產(chǎn)生了，并且來來回回的審核和修改，讓開發(fā)人員浪費時，DBA的時間也就這樣浪費了，互相的成本都很大。

PostgreSQL 在這方面和其他的開源數(shù)據(jù)庫不太一樣，甚至和商業(yè)數(shù)據(jù)庫也不大一樣，他的寬容度和包容度都比較大，在設(shè)計和開發(fā)軟件的時候，不需要那么用力，我們以 postgresql 11 作為我們的例子，這是PG11版本中tip，由于原理的原因，在PG 中text 和 varchar之間是沒有什么性能的差別的。(當然如果你往一個字段里面放置幾十MB ，上百MB的情況，另當別論)，所以在字段大小設(shè)計方面的問題的矛盾點就被化解了。

我們下面看一下一個建表的語句，

下面是從pg_admin中導(dǎo)出的語句

其實也是聽到一個聲音，就是PG在某些方面不如別的數(shù)據(jù)庫在語句的寫法上寬泛，實際上第一張圖是手寫的，如果不和你說明估計也不大能分的很清楚與某些其他數(shù)據(jù)庫建表語句的明顯區(qū)別，PG對語句的兼容性并不低，下圖是從PG_ADMIN導(dǎo)出的語句，最終實際的PG標準的語句是這樣的，這也明顯的看出, PG的包容性。

說完P(guān)G的包容性，下面就的繼續(xù)說PG的多態(tài)性，這個問題其實要從PG支持的數(shù)據(jù)類型來開始，PG支持的數(shù)據(jù)類型是相當?shù)亩?。其中必然要談的是一個種特殊的PG的數(shù)據(jù)類型 hstore

insert into hstore_test (id,name,history) values (1,'postgresql','from => "IBM_Research",origination => "inges",time => "1970"')

可以看到與JSON 格式對比，hstore 在處理比較隨意的數(shù)據(jù)上。

SELECT name, history->'from' as history FROM hstore_test WHERE history->'origination' = 'inges';

這樣的數(shù)據(jù)的處理方式，在實際的應(yīng)用中是很有意義的，例如項目不穩(wěn)定，客戶經(jīng)常沒有準主意，一會一變，等等這樣的情況，開發(fā)人員都可以用這樣的模式來設(shè)計初期的項目中的數(shù)據(jù)存儲，并且這樣的的數(shù)據(jù)也可以保證系統(tǒng)的性能和查詢的。不會對開發(fā)人員有過高的要求和特殊的規(guī)定。

其實這樣的設(shè)計是一個數(shù)據(jù)庫包容能力和多態(tài)性的一種體現(xiàn)，完全為項目和應(yīng)用以及程序員的便利性去考慮，這也是一個數(shù)據(jù)庫中適應(yīng)結(jié)構(gòu)化和半結(jié)構(gòu)化數(shù)據(jù)，完全面向應(yīng)用，降低開發(fā)的難度和成本做出的最大的誠意。

除此以外，PG對于程序員在模糊查詢中的誠意也是滿滿的，我們都知道在開發(fā)一些系統(tǒng)中，用戶的挑剔和項目經(jīng)理以及需求經(jīng)理對于開發(fā)人員來說，奇葩的需求，天天有，例如客戶對一個系統(tǒng)中用戶的留言點評，想進行一個查詢，每天都要有，根據(jù)幾個關(guān)鍵詞來進行查找客戶是否有不滿意，或者意見的情況，當然這些詞也是變化的，模糊查詢中最讓人討厭的就是兩邊都是

%% 百分號。而PG 對于這樣的情況是可以走索引進行查詢的。那我們將這一塊的內(nèi)容和下邊的索引的內(nèi)容進行一個合并，通過兩個例子來講一講。

結(jié)合上面的查詢，開發(fā)人員對于索引的支持也是關(guān)心的，因可能由于某些邏輯或前期設(shè)計的問題，造成我們的數(shù)據(jù)表必須要有大量的索引來解決部分查詢性能問題，例如某些表中的數(shù)據(jù)的一列只有幾種類型，或者我們的表的數(shù)據(jù)量比較大，最后造成索引和表都特別大的情況，本身就帶來性能問題

PG 本身在設(shè)計上，就考慮了這點，索引的類型很多， B-tree, Hash, GiST, SP-GiST, GIN and BRIN， 這里大部分數(shù)據(jù)庫除了b-tree索引類型以外，其他的類型的索引是不支持，或許這里面的 GIST GIN Brin 等索引的類型大部分沒有用過PG的人也是第一次聽說。

那我們就來挑兩個索引來說說 GIN 和 BRIN 索引，這兩種索引都是有針對性的

1 模糊查詢

我們先建立一個函數(shù)用來生成隨機的數(shù)據(jù)

create or replace function gen_hanzi(int) returns text as $$ declare res text; begin if $1 >=1 then select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1); return res; end if; return null; end; $$ language plpgsql strict;

insert into text_search (address) select gen_hanzi(10) from generate_series(1,1000000);

插入200萬條數(shù)據(jù)，隨機產(chǎn)生漢字。

create index CONCURRENTLY idx_ts_address on text_search using gin (address gin_trgm_ops);

創(chuàng)建相關(guān)GIN 索引來應(yīng)用trgm 函數(shù)

通過上面的操作，我想大家已經(jīng)對于PG的 GIN 索引在模糊查詢中的實力有所了解，這也是四大數(shù)據(jù)庫中，其他的數(shù)據(jù)庫都不能快速簡便解決的問題，所以開發(fā)人員如果遇到這樣的需求，在頭痛的時候，可以問自己一句，為什么沒有用PG,浪費本應(yīng)該節(jié)省下的時間和成本。因為其他的數(shù)據(jù)庫要做這樣的事情，是需要其他的附加的其他的數(shù)據(jù)庫來完成，例如ES， 這樣的情況為了一個字段，一個奇葩需求而要付出的代價就被PG 輕松的化解了。

當然這還不是最神奇的，我們還有一些應(yīng)用，表的數(shù)據(jù)量很大，同時也要建立索引，而索引隨著表的變大，就會變得越來越大，這就是b+tree的問題，而PG中的BRIN索引就是為了解決這樣的問題而來的，怎么能更減小索引，并且與btree 的性能相近,與范圍查詢配合效果是很不錯的.

我們用一個例子來證明一下

CREATE TABLE testtab (id int NOT NULL PRIMARY KEY,date TIMESTAMP NOT NULL, level INTEGER, msg TEXT)

INSERT INTO testtab (id, date, level, msg) SELECT g, CURRENT_TIMESTAMP + ( g || 'minute' ) :: interval, random() * 6, md5(g::text)

FROM generate_series(1,8000000) as g;

create index CONCURRENTLY idx_tss_brin on testtab using brin (date);

create index CONCURRENTLY idx_tss_btree on testtab (date);

在創(chuàng)建表和索引后，我們對比同樣的功能，兩個索引的大小相比，BTREE 是171MB 而 BRIN 索引只有 64kb.

這時估計很多人都會想，那效果一定大打折扣。那我們來看看，到底查詢的性能會不會因為不同的索引而造成查詢的性能特別大的差距。

explain analyze select * from public.testtab where date between '2020-03-23 06:15:01.41099' and '2020-03-23 06:19:01.41099';

通過上面兩個查詢的對比，實際上差距并不是很大，相對于他們的索引的大小來說。所以如果一個表中有很多范圍類型的查詢，就可以采取brin方式的索引，來降低大表對索引的空間的占用。

Brin索引，BRIN代表Block Range Index，BRIN是為處理非常大的表而設(shè)計,用一種塊的概念，塊范圍是表中物理上相鄰的一組頁,對于每個塊范圍，索引將存儲一些摘要信息。

BRIN索引可以通過常規(guī)的位圖索引掃描來滿足查詢，索引所存儲的摘要信息與查詢條件一致，則返回每個范圍內(nèi)所有頁面中的所有元組。查詢執(zhí)行程序負責(zé)重新檢查這些元組，并丟棄那些不匹配查詢條件的元組——換句話說，這些索引是有損的。由于BRIN索引非常小，與順序掃描相比，掃描索引只增加了很少的開銷，但是可以避免掃描已知不包含匹配元組的表的大部分數(shù)據(jù)。

說完索引，我們在來說說PG中的繼承表，繼承表這個概念我個人沒有再ORACLE , SQL SERVER ，MYSQL ，或者其他類似 MONGODB 中聽說過，當然也可能是我比較孤陋寡聞。PG的繼承表是一種在開發(fā)中很有用的技術(shù)，舉例，我們的開發(fā)中已經(jīng)有一張很大的數(shù)量表，其中已經(jīng)有一些信息，但這些信息是主營業(yè)務(wù)的，同時隨著業(yè)務(wù)的發(fā)展，我們又有了一些其他的業(yè)務(wù)，雖然是其他的業(yè)務(wù)，但這些業(yè)務(wù)大部分是主營業(yè)務(wù)的擴展，需求方要求開發(fā)，那你要在這個業(yè)務(wù)中繼續(xù)開發(fā)，如果有這樣的要求，就一定會要你進行原表的信息添加，也就是加字段，這就要產(chǎn)生一個問題，字段添加在原表，這并不是每個開發(fā)愿意做的事情，尤其是高級的開發(fā)，一張表本身在設(shè)計初期其實已經(jīng)根據(jù)當時的情況作了規(guī)劃，怎么能在不影響原有的規(guī)劃，并且又能繼承原表的字段，產(chǎn)生一張新表，并在此基礎(chǔ)上進行字段的添加修改，或者隨著業(yè)務(wù)的變化，很可能會將這段抹去，這都是繼承表能給開發(fā)者的恩賜。

通過上圖來看,實際上繼承表的設(shè)計是很有意思的,如果在發(fā)散性的思維,可能會創(chuàng)造出更多的用法.

最后不同的程序員在以前使用不同的數(shù)據(jù)庫，例如ORACLE MYSQL 或者SQL SERVER ，尤其某些程序員在使用完ORACLE 后，在使用其他的數(shù)據(jù)庫時會產(chǎn)生一些疑問，別的數(shù)據(jù)庫是instance 下有數(shù)據(jù)庫，而ORACLE 是instance 下都是表，通過schema(用戶)來對表進行訪問的控制。

而PG 則是兩種習(xí)慣都適應(yīng)，你可以在PG數(shù)據(jù)庫里面使用類似ORACLE 的schema 的經(jīng)驗來訪問表，規(guī)劃表，同時更可以使用MYSQL 熟悉的方式，將不同的表放置不同的數(shù)據(jù)庫來進行分割。所以對于大部分程序要都是友好的。最后要闡述的是沒有一種數(shù)據(jù)庫在當今可以霸占一個企業(yè),一個企業(yè)使用 N種數(shù)據(jù)庫是很平常的事情。所以運維人員也需要有包容的心態(tài)來面對越來越多的曾經(jīng)不熟悉的數(shù)據(jù)庫的加入，增加企業(yè)的競爭力，降低成本。

本文對應(yīng)視頻講解，點擊瀏覽

以上內(nèi)容由東方瑞通資深講師 Austin原創(chuàng)，13年專業(yè)DBA經(jīng)驗，曾任互聯(lián)網(wǎng)金融公司Senior DBA、500強制藥企業(yè)Senior DBA，精通Mysql、PostgreSQL、Mongo DB、SQLServer。

總結(jié)

以上是生活随笔為你收集整理的pg数据库json数据类型_PostgreSQL与开发者起舞—让数据库更好服务于开发的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。