postgresql 和postgis區別是什么？

了存儲、查詢和修改空間關系的能力。本文中 ‘PostgreSQL’ 指代基本的關系數據庫功能，而 ‘PostGIS’ 指代擴展的空間操作特性。

商河ssl適用于網站、小程序/APP、API接口等需要進行數據傳輸應用場景，ssl證書未來市場廣闊！成為成都創新互聯公司的ssl證書銷售渠道，可以享受市場價格4-6折優惠！如果有意向歡迎電話聯系或者加微信：18980820575（備注：SSL證書合作）期待與您的合作！

客戶端－服務器構架

PostgreSQL 同眾多數據庫產品一樣，采用客戶端－服務器構架。客戶端向服務器發出請求并得到響應。這種機制同瀏覽器從網絡服務器獲取網頁類似。在 PostgreSQL 中，請求以 SQL 語言發出，而響應多為從數據庫提取的表單。

客戶端與服務器可以部署在同一臺設備上，即 PostgreSQL 可以在單一的計算機上使用。借由系統內部的 ‘loopback’ 通信機制，數據庫系統可以進行私密通訊。除非專門配置，外界是不能訪問這些信息的。

本位介紹三種客戶端：命令行， Quantum GIS ， pgAdmin 圖形化數據庫客戶端。

創造具有空間信息處理能力的數據庫

命令行客戶端在終端模擬器（Terminal Emulator）中運行。在 Applications 菜單的 Accessories 中打開一個終端模擬器，將顯示一個 Unix 風格的命令行界面。輸入：

psql -V

回車確認，將顯示 PostgreSQL 版本號。

一個 PostgreSQL 服務器中，可以將不同的任務組織到不同的數據庫。每個數據庫獨立運作，擁有專門的表單、顯示、用戶等。訪問 PostgreSQL 數據庫時將指定一個數據庫。

服務器上數據庫列表通過以下命令查詢：

psql -l

輸出將羅列 Live 上配置的幾個數據庫。這里演示新建一個。

PostgreSQL 使用 createdb 工具創建數據庫。這里建立的數據庫應帶有 PostGIS 的擴展功能，因此需要指定相應的模板。這里將新建數據庫稱為 demo 。命令為：

createdb-Ttemplate_postgisdemo

現在執行 psql-l 應當可以看到 demo 數據庫。

也可以使用 SQL 語言創建 PostGIS 數據庫。首先使用 dropdb 命令刪除之前創建的數據庫，然后使用 psql 命令開啟 SQL 命令解析器：

dropdbdemopsql-dpostgres

這樣就連接到了一個通用的系統數據庫 postgres 。輸入 SQL 命令建立新數據庫：

postgres=# CREATE DATABASE demo TEMPLATE=template_postgis;

現在可以轉換連接到新建的數據庫。若重新連接時可以使用 psql-ddemo 命令。但在 psql 系統內部也可以使用以下命令：

postgres=# \c demo

一個信息頁面將顯示當前已連接 demo 數據庫。輸入 \dt 列出當前數據庫內的表單，輸出如下：

demo=# \dtListofrelationsSchema|Name|Type|Owner--------+------------------+-------+-------public|geometry_columns|table|userpublic|spatial_ref_sys|table|user(2rows)

這兩個表格是 PostGIS 默認的。其中 spatial_ref_sys 存儲著合法的空間坐標系統。利用 SQL 查詢查看：

demo=# SELECT srid,auth_name,proj4text FROM spatial_ref_sys LIMIT 10;srid|auth_name|proj4text------+-----------+--------------------------------------3819|EPSG|+proj=longlat+ellps=bessel+towgs...3821|EPSG|+proj=longlat+ellps=aust_SA+no_d...3824|EPSG|+proj=longlat+ellps=GRS80+towgs8...3889|EPSG|+proj=longlat+ellps=GRS80+towgs8...3906|EPSG|+proj=longlat+ellps=bessel+no_de...4001|EPSG|+proj=longlat+ellps=airy+no_defs...4002|EPSG|+proj=longlat+a=6377340.189+b=63...4003|EPSG|+proj=longlat+ellps=aust_SA+no_d...4004|EPSG|+proj=longlat+ellps=bessel+no_de...4005|EPSG|+proj=longlat+a=6377492.018+b=63...(10rows)

以上顯示確認了該數據庫已經建立空間操作功能。數據庫中的 geometry_columns 用于記錄那些表格是有空間信息的。

手工建立空間數據表格

空間數據庫已經建立，現在可以建立具有空間信息的表格。

首先建立一個常規的表格存儲有關城市（cities）的信息。這個表格有兩欄，一個是 ID 編號，一個是城市名：

demo=# CREATE TABLE cities ( id int4, name varchar(50) );

現在添加一個空間欄用于存儲城市的位置。習慣上這個欄目叫做 the_geom 。它記錄了數據為什么類型（點、線、面）、有幾維（這里是二維）以及空間坐標系統。此處使用 EPSG:4326 坐標系統：

demo=# SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);

完成后，查詢 cities 表單應當顯示這個新欄目。同時頁面將顯示當前表達沒有記錄（0 rows）。

demo=# SELECT * from cities;id|name|the_geom----+------+----------(0rows)

為添加記錄，需要使用 SQL 命令。對于空間欄，使用 PostGIS 的 ST_GeomFromText 可以將文本轉化為坐標與參考系號的記錄：

demo=# INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');demo=# INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');demo=# INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');

當然，這樣的輸入方式難以操作。其它方式可以更快的輸入數據。就目前來說，表格內已經有了一些城市數據，可以先進行查詢等操作。

簡單查詢

標準的 SQL 操作都可以用于 PostGIS 表單：

demo=# SELECT * FROM cities;id|name|the_geom----+-----------------+----------------------------------------------------1|London,England|0101000020E6100000BBB88D06F016C0BF1B2FDD2406C149402|London,Ontario|0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D45403|EastLondon,SA|0101000020E610000040AB064060E93B4059FAD005F58140C0(3rows)

這里的坐標是無法閱讀的 16 進制格式。要以 WKT 文本顯示，使用 ST_AsText(the_geom) 或 ST_AsEwkt(the_geom) 函數。也可以使用 ST_X(the_geom) 和 ST_Y(the_geom) 顯示一個維度的坐標：

demo=# SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;id|st_astext|st_asewkt|st_x|st_y----+------------------------------+----------------------------------------+-------------+-----------1|POINT(-0.125751.508)|SRID=4326;POINT(-0.125751.508)|-0.1257|51.5082|POINT(-81.23342.983)|SRID=4326;POINT(-81.23342.983)|-81.233|42.9833|POINT(27.91162491-33.01529)|SRID=4326;POINT(27.91162491-33.01529)|27.91162491|-33.01529(3rows)

空間查詢：

PostGIS 為 PostgreSQL 擴展了許多空間操作功能。以上已經涉及了轉換空間坐標格式的 ST_GeomFromText 。多數空間操作以 ST（spatial type）開頭，在 PostGIS 文檔相應章節有羅列。這里回答一個具體的問題：以米為單位并假設地球是完美橢球，上面三個城市相互的距離是多少？

demo=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id p2.id;name|name|st_distance_sphere-----------------+-----------------+--------------------London,Ontario|London,England|5875766.85191657EastLondon,SA|London,England|9789646.96784908EastLondon,SA|London,Ontario|13892160.9525778(3rows)

輸出顯示了距離數據。注意 ‘WHERE’ 部分防止了輸出城市到自身的距離（0）或者兩個城市不同排列的距離數據（London, England 到 London, Ontario 和 London, Ontario 到 London, England 的距離是一樣的）。嘗試取消 ‘WHERE’ 并查看結果。

這里采取不同的橢球參數（橢球體名、半主軸長、扁率）計算：

demo=# SELECT p1.name,p2.name,ST_Distance_Spheroid(p1.the_geom,p2.the_geom,'SPHEROID["GRS_1980",6378137,298.257222]')FROMcitiesASp1,citiesASp2WHEREp1.idp2.id;name|name|st_distance_spheroid-----------------+-----------------+----------------------London,Ontario|London,England|5892413.63776489EastLondon,SA|London,England|9756842.65711931EastLondon,SA|London,Ontario|13884149.4140698(3rows)

制圖

以 PostGIS 數據制圖需要相應的客戶端支持。包括 Quantum GIS、gvSIG、uDig 在內的多種客戶端均可以。以下使用 Quantum GIS：

從 Desktop GIS 菜單啟動 Quantum GIS 并在其 layer 菜單選擇 AddPostGISlayers 。連接到 Natural Earth PostGIS 數據庫的參數在 Connections 下拉菜單中有。這里可以定義和儲存其它的配置。點擊 Edit 可以查看具體參數。點擊 Connect 連接：

?

系統將顯示所有空間信息表供選擇：

?

選擇 lakes 湖泊表單并點擊底部的 Add 添加。頂部的 Load 可以載入新的數據庫連接配置。數據將被導入：

?

界面上顯示出湖泊的分布。QGIS 并不理解湖泊一詞的含義，也許不會自動使用藍色。請查看其手冊了解如何設置。這里縮放到加拿大一處著名的湖泊群。

自動創建空間數據表單

OSGeo Live 的多數桌面 GIS 系統都可以將 shp 等文件導入數據庫。這里依然使用 QGIS 演示。

QGIS 中導入 shp 可以使用 PostGIS Manager 插件。在 Plugins 菜單選擇 FetchPlugins 導入最新的官方插件列表（需要網絡連接）。找到 PostGISManager 點擊 Installplugin 安裝。

?

完成后，在 Plugin 菜單點擊 PostGIS Manager 啟動。也可以點擊工具欄上大象與地球的圖標。

該插件將連接 Natural Earth 數據庫。若提示輸入密碼，留空即可。在開啟的界面中，選擇表單可以顯示相應的信息。預覽（Preview）選項卡可以顯示地圖預覽。這里選擇了 populated places 圖層并縮放到一個小島：

?

接下來使用 PostGIS Manager 將 shp 導入數據庫。這里使用 R 統計擴展包含的 North Carolina sudden infant death syndrome (SIDS) 數據：

在 Data 菜單選擇 Loaddatafromshapefile 選項。點擊 ... 選中 R maptools 中的 sids.shp 。

分庫分表 VS newsql數據庫

最近與同行 科技 交流，經常被問到分庫分表與分布式數據庫如何選擇，網上也有很多關于中間件+傳統關系數據庫（分庫分表）與NewSQL分布式數據庫的文章，但有些觀點與判斷是我覺得是偏激的，脫離環境去評價方案好壞其實有失公允。

本文通過對兩種模式關鍵特性實現原理對比，希望可以盡可能客觀、中立的闡明各自真實的優缺點以及適用場景。

首先關于“中間件+關系數據庫分庫分表”算不算NewSQL分布式數據庫問題，國外有篇論文pavlo-newsql-sigmodrec，如果根據該文中的分類，Spanner、TiDB、OB算是第一種新架構型，Sharding-Sphere、Mycat、DRDS等中間件方案算是第二種（文中還有第三種云數據庫，本文暫不詳細介紹）。

基于中間件（包括SDK和Proxy兩種形式）+傳統關系數據庫（分庫分表）模式是不是分布式架構？我覺得是的，因為存儲確實也分布式了，也能實現橫向擴展。但是不是"偽"分布式數據庫？從架構先進性來看，這么說也有一定道理。"偽"主要體現在中間件層與底層DB重復的SQL解析與執行計劃生成、存儲引擎基于B+Tree等，這在分布式數據庫架構中實際上冗余低效的。為了避免引起真偽分布式數據庫的口水戰，本文中NewSQL數據庫特指這種新架構NewSQL數據庫。

NewSQL數據庫相比中間件+分庫分表的先進在哪兒？畫一個簡單的架構對比圖：

這些大多也是NewSQL數據庫產品主要宣傳的點，不過這些看起來很美好的功能是否真的如此？接下來針對以上幾點分別闡述下的我的理解。

這是把雙刃劍。

CAP限制

想想更早些出現的NoSQL數據庫為何不支持分布式事務（最新版的mongoDB等也開始支持了），是缺乏理論與實踐支撐嗎？并不是，原因是CAP定理依然是分布式數據庫頭上的頸箍咒，在保證強一致的同時必然會犧牲可用性A或分區容忍性P。為什么大部分NoSQL不提供分布式事務？

那么NewSQL數據庫突破CAP定理限制了嗎？并沒有。NewSQL數據庫的鼻主Google Spanner（目前絕大部分分布式數據庫都是按照Spanner架構設計的）提供了一致性和大于5個9的可用性，宣稱是一個“實際上是CA”的，其真正的含義是 系統處于 CA 狀態的概率非常高，由于網絡分區導致的服務停用的概率非常小 ，究其真正原因是其打造私有全球網保證了不會出現網絡中斷引發的網絡分區，另外就是其高效的運維隊伍,這也是cloud spanner的賣點。詳細可見CAP提出者Eric Brewer寫的《Spanner, TrueTime 和CAP理論》。

完備性 ：

兩階段提交協議是否嚴格支持ACID，各種異常場景是不是都可以覆蓋？

2PC在commit階段發送異常，其實跟最大努力一階段提交類似也會有部分可見問題，嚴格講一段時間內并不能保證A原子性和C一致性（待故障恢復后recovery機制可以保證最終的A和C）。完備的分布式事務支持并不是一件簡單的事情，需要可以應對網絡以及各種硬件包括網卡、磁盤、CPU、內存、電源等各類異常，通過嚴格的測試。之前跟某友商交流，他們甚至說目前已知的NewSQL在分布式事務支持上都是不完整的，他們都有案例跑不過，圈內人士這么篤定，也說明了 分布式事務的支持完整程度其實是層次不齊的。

但分布式事務又是這些NewSQL數據庫的一個非常重要的底層機制，跨資源的DML、DDL等都依賴其實現，如果這塊的性能、完備性打折扣，上層跨分片SQL執行的正確性會受到很大影響。

性能

傳統關系數據庫也支持分布式事務XA，但為何很少有高并發場景下用呢？ 因為XA的基礎兩階段提交協議存在網絡開銷大，阻塞時間長、死鎖等問題，這也導致了其實際上很少大規模用在基于傳統關系數據庫的OLTP系統中。

NewSQL數據庫的分布式事務實現也仍然多基于兩階段提交協議，例如google percolator分布式事務模型，

采用原子鐘+MVCC+ Snapshot Isolation（SI），這種方式通過TSO(Timestamp Oracle)保證了全局一致性，通過MVCC避免了鎖，另外通過primary lock和secondary lock將提交的一部分轉為異步，相比XA確實提高了分布式事務的性能。

但不管如何優化，相比于1PC，2PC多出來的GID獲取、網絡開銷、prepare日志持久化還是會帶來很大的性能損失，尤其是跨節點的數量比較多時會更加顯著，例如在銀行場景做個批量扣款，一個文件可能上W個賬戶，這樣的場景無論怎么做還是吞吐都不會很高。

雖然NewSQL分布式數據庫產品都宣傳完備支持分布式事務，但這并不是說應用可以完全不用關心數據拆分，這些數據庫的最佳實踐中仍然會寫到，應用的大部分場景盡可能避免分布式事務。

既然強一致事務付出的性能代價太大，我們可以反思下是否真的需要這種強一致的分布式事務？尤其是在做微服務拆分后，很多系統也不太可能放在一個統一的數據庫中。嘗試將一致性要求弱化，便是柔性事務，放棄ACID(Atomicity,Consistency, Isolation, Durability)，轉投BASE(Basically Available,Soft state,Eventually consistent)，例如Saga、TCC、可靠消息保證最終一致等模型，對于大規模高并發OLTP場景，我個人更建議使用柔性事務而非強一致的分布式事務。關于柔性事務，筆者之前也寫過一個技術組件，最近幾年也涌現出了一些新的模型與框架（例如阿里剛開源的Fescar），限于篇幅不再贅述，有空再單獨寫篇文章。

HA與異地多活

主從模式并不是最優的方式，就算是半同步復制，在極端情況下（半同步轉異步）也存在丟數問題，目前業界公認更好的方案是基于paxos分布式一致性協議或者其它類paxos如raft方式，Google Spanner、TiDB、cockcoachDB、OB都采用了這種方式，基于Paxos協議的多副本存儲，遵循過半寫原則，支持自動選主，解決了數據的高可靠，縮短了failover時間，提高了可用性，特別是減少了運維的工作量，這種方案技術上已經很成熟，也是NewSQL數據庫底層的標配。

當然這種方式其實也可以用在傳統關系數據庫，阿里、微信團隊等也有將MySQL存儲改造支持paxos多副本的，MySQL也推出了官方版MySQL Group Cluster，預計不遠的未來主從模式可能就成為 歷史 了。

需要注意的是很多NewSQL數據庫廠商宣傳基于paxos或raft協議可以實現【異地多活】，這個實際上是有前提的，那就是異地之間網絡延遲不能太高 。以銀行“兩地三中心”為例，異地之間多相隔數千里，延時達到數十毫秒，如果要多活，那便需異地副本也參與數據庫日志過半確認，這樣高的延時幾乎沒有OLTP系統可以接受的。

數據庫層面做異地多活是個美好的愿景，但距離導致的延時目前并沒有好的方案。 之前跟螞蟻團隊交流，螞蟻異地多活的方案是在應用層通過MQ同步雙寫交易信息，異地DC將交易信息保存在分布式緩存中，一旦發生異地切換，數據庫同步中間件會告之數據延遲時間，應用從緩存中讀取交易信息，將這段時間內涉及到的業務對象例如用戶、賬戶進行黑名單管理，等數據同步追上之后再將這些業務對象從黑名單中剔除。由于雙寫的不是所有數據庫操作日志而只是交易信息，數據延遲只影響一段時間內數據，這是目前我覺得比較靠譜的異地度多活方案。

另外有些系統進行了單元化改造，這在paxos選主時也要結合考慮進去，這也是目前很多NewSQL數據庫欠缺的功能。

Scale橫向擴展與分片機制

paxos算法解決了高可用、高可靠問題，并沒有解決Scale橫向擴展的問題，所以分片是必須支持的。NewSQL數據庫都是天生內置分片機制的，而且會根據每個分片的數據負載(磁盤使用率、寫入速度等)自動識別熱點，然后進行分片的分裂、數據遷移、合并，這些過程應用是無感知的，這省去了DBA的很多運維工作量。以TiDB為例，它將數據切成region，如果region到64M時，數據自動進行遷移。

分庫分表模式下需要應用設計之初就要明確各表的拆分鍵、拆分方式（range、取模、一致性哈希或者自定義路由表）、路由規則、拆分庫表數量、擴容方式等。相比NewSQL數據庫，這種模式給應用帶來了很大侵入和復雜度，這對大多數系統來說也是一大挑戰。

這里有個問題是NewSQL數據庫統一的內置分片策略（例如tidb基于range）可能并不是最高效的，因為與領域模型中的劃分要素并不一致，這導致的后果是很多交易會產生分布式事務。 舉個例子，銀行核心業務系統是以客戶為維度，也就是說客戶表、該客戶的賬戶表、流水表在絕大部分場景下是一起寫的，但如果按照各表主鍵range進行分片，這個交易并不能在一個分片上完成，這在高頻OLTP系統中會帶來性能問題。

分布式SQL支持

常見的單分片SQL，這兩者都能很好支持。NewSQL數據庫由于定位與目標是一個通用的數據庫，所以支持的SQL會更完整，包括跨分片的join、聚合等復雜SQL。中間件模式多面向應用需求設計，不過大部分也支持帶拆分鍵SQL、庫表遍歷、單庫join、聚合、排序、分頁等。但對跨庫的join以及聚合支持就不夠了。

NewSQL數據庫一般并不支持存儲過程、視圖、外鍵等功能，而中間件模式底層就是傳統關系數據庫，這些功能如果只是涉及單庫是比較容易支持的。

NewSQL數據庫往往選擇兼容MySQL或者PostgreSQL協議，所以SQL支持僅局限于這兩種，中間件例如驅動模式往往只需做簡單的SQL解析、計算路由、SQL重寫，所以可以支持更多種類的數據庫SQL。

SQL支持的差異主要在于分布式SQL執行計劃生成器，由于NewSQL數據庫具有底層數據的分布、統計信息，因此可以做CBO，生成的執行計劃效率更高，而中間件模式下沒有這些信息，往往只能基于規則RBO（Rule-Based-Opimization），這也是為什么中間件模式一般并不支持跨庫join，因為實現了效率也往往并不高，還不如交給應用去做。

存儲引擎

傳統關系數據庫的存儲引擎設計都是面向磁盤的，大多都基于B+樹。B+樹通過降低樹的高度減少隨機讀、進而減少磁盤尋道次數，提高讀的性能，但大量的隨機寫會導致樹的分裂，從而帶來隨機寫，導致寫性能下降。NewSQL的底層存儲引擎則多采用LSM，相比B+樹LSM將對磁盤的隨機寫變成順序寫，大大提高了寫的性能。不過LSM的的讀由于需要合并數據性能比B+樹差，一般來說LSM更適合應在寫大于讀的場景。當然這只是單純數據結構角度的對比，在數據庫實際實現時還會通過SSD、緩沖、bloom filter等方式優化讀寫性能，所以讀性能基本不會下降太多。NewSQL數據由于多副本、分布式事務等開銷，相比單機關系數據庫SQL的響應時間并不占優，但由于集群的彈性擴展，整體QPS提升還是很明顯的，這也是NewSQL數據庫廠商說分布式數據庫更看重的是吞吐，而不是單筆SQL響應時間的原因。

成熟度與生態

分布式數據庫是個新型通用底層軟件，準確的衡量與評價需要一個多維度的測試模型，需包括發展現狀、使用情況、社區生態、監控運維、周邊配套工具、功能滿足度、DBA人才、SQL兼容性、性能測試、高可用測試、在線擴容、分布式事務、隔離級別、在線DDL等等，雖然NewSQL數據庫發展經過了一定時間檢驗，但多集中在互聯網以及傳統企業非核心交易系統中，目前還處于快速迭代、規模使用不斷優化完善的階段。

相比而言，傳統關系數據庫則經過了多年的發展，通過完整的評測，在成熟度、功能、性能、周邊生態、風險把控、相關人才積累等多方面都具有明顯優勢，同時對已建系統的兼容性也更好。

對于互聯網公司，數據量的增長壓力以及追求新技術的基因會更傾向于嘗試NewSQL數據庫，不用再考慮庫表拆分、應用改造、擴容、事務一致性等問題怎么看都是非常吸引人的方案。

對于傳統企業例如銀行這種風險意識較高的行業來說，NewSQL數據庫則可能在未來一段時間內仍處于 探索 、審慎試點的階段。基于中間件+分庫分表模式架構簡單，技術門檻更低，雖然沒有NewSQL數據庫功能全面，但大部分場景最核心的訴求也就是拆分后SQL的正確路由，而此功能中間件模式應對還是綽綽有余的，可以說在大多數OLTP場景是夠用的。

限于篇幅，其它特性例如在線DDL、數據遷移、運維工具等特性就不在本文展開對比。

總結

如果看完以上內容，您還不知道選哪種模式，那么結合以下幾個問題，先思考下NewSQL數據庫解決的點對于自身是不是真正的痛點：

如果以上有2到3個是肯定的，那么你可以考慮用NewSQL數據庫了，雖然前期可能需要一定的學習成本，但它是數據庫的發展方向，未來收益也會更高，尤其是互聯網行業，隨著數據量的突飛猛進，分庫分表帶來的痛苦會與日俱增。當然選擇NewSQL數據庫你也要做好承擔一定風險的準備。

如果你還未做出抉擇，不妨再想想下面幾個問題：

如果這些問題有多數是肯定的，那還是分庫分表吧。在軟件領域很少有完美的解決方案，NewSQL數據庫也不是數據分布式架構的銀彈。相比而言分庫分表是一個代價更低、風險更小的方案，它最大程度復用傳統關系數據庫生態，通過中間件也可以滿足分庫分表后的絕大多數功能，定制化能力更強。 在當前NewSQL數據庫還未完全成熟的階段，分庫分表可以說是一個上限低但下限高的方案，尤其傳統行業的核心系統，如果你仍然打算把數據庫當做一個黑盒產品來用，踏踏實實用好分庫分表會被認為是個穩妥的選擇。

很多時候軟件選型取決于領域特征以及架構師風格，限于筆者知識與所屬行業特點所限，以上僅為個人粗淺的一些觀點，歡迎討論。

數據庫為什么要分庫分表

1 基本思想之什么是分庫分表？

從字面上簡單理解，就是把原本存儲于一個庫的數據分塊存儲到多個庫上，把原本存儲于一個表的數據分塊存儲到多個表上。

2 基本思想之為什么要分庫分表？

數

據庫中的數據量不一定是可控的，在未進行分庫分表的情況下，隨著時間和業務的發展，庫中的表會越來越多，表中的數據量也會越來越大，相應地，數據操作，增

刪改查的開銷也會越來越大；另外，由于無法進行分布式式部署，而一臺服務器的資源（CPU、磁盤、內存、IO等）是有限的，最終數據庫所能承載的數據量、

數據處理能力都將遭遇瓶頸。

3 分庫分表的實施策略。

分庫分表有垂直切分和水平切分兩種。

3.1

何謂垂直切分，即將表按照功能模塊、關系密切程度劃分出來，部署到不同的庫上。例如，我們會建立定義數據庫workDB、商品數據庫payDB、用戶數據

庫userDB、日志數據庫logDB等，分別用于存儲項目數據定義表、商品定義表、用戶數據表、日志數據表等。

3.2

何謂水平切分，當一個表中的數據量過大時，我們可以把該表的數據按照某種規則，例如userID散列，進行劃分，然后存儲到多個結構相同的表，和不同的庫

上。例如，我們的userDB中的用戶數據表中，每一個表的數據量都很大，就可以把userDB切分為結構相同的多個userDB：part0DB、

part1DB等，再將userDB上的用戶數據表userTable，切分為很多userTable：userTable0、userTable1等，

然后將這些表按照一定的規則存儲到多個userDB上。

3.3 應該使用哪一種方式來實施數據庫分庫分表，這要看數據庫中數據量的瓶頸所在，并綜合項目的業務類型進行考慮。

如果數據庫是因為表太多而造成海量數據，并且項目的各項業務邏輯劃分清晰、低耦合，那么規則簡單明了、容易實施的垂直切分必是首選。

而

如果數據庫中的表并不多，但單表的數據量很大、或數據熱度很高，這種情況之下就應該選擇水平切分，水平切分比垂直切分要復雜一些，它將原本邏輯上屬于一體

的數據進行了物理分割，除了在分割時要對分割的粒度做好評估，考慮數據平均和負載平均，后期也將對項目人員及應用程序產生額外的數據管理負擔。

在現實項目中，往往是這兩種情況兼而有之，這就需要做出權衡，甚至既需要垂直切分，又需要水平切分。我們的游戲項目便綜合使用了垂直與水平切分，我們首先對數據庫進行垂直切分，然后，再針對一部分表，通常是用戶數據表，進行水平切分。

4 分庫分表存在的問題。

4.1 事務問題。

在執行分庫分表之后，由于數據存儲到了不同的庫上，數據庫事務管理出現了困難。如果依賴數據庫本身的分布式事務管理功能去執行事務，將付出高昂的性能代價；如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。

4.2 跨庫跨表的join問題。

在執行了分庫分表之后，難以避免會將原本邏輯關聯性很強的數據劃分到不同的表、不同的庫上，這時，表的關聯操作將受到限制，我們無法join位于不同分庫的表，也無法join分表粒度不同的表，結果原本一次查詢能夠完成的業務，可能需要多次查詢才能完成。

4.3 額外的數據管理負擔和數據運算壓力。

額

外的數據管理負擔，最顯而易見的就是數據的定位問題和數據的增刪改查的重復執行問題，這些都可以通過應用程序解決，但必然引起額外的邏輯運算，例如，對于

一個記錄用戶成績的用戶數據表userTable，業務要求查出成績最好的100位，在進行分表之前，只需一個order

by語句就可以搞定，但是在進行分表之后，將需要n個order

by語句，分別查出每一個分表的前100名用戶數據，然后再對這些數據進行合并計算，才能得出結果。

如何使用Navicat for PostgreSQL域

Navicat for PostgreSQL 11.1.13 最新版域常規屬性：

基礎類型類別：可選擇的基礎數據類型有：Base Type、Composite Type、Enum Type 和 Domain。需要注意的是，僅支持 PostgreSQL 8.2 或以上版本。

基礎類型模式：選擇基礎數據類型的模式。

基礎類型：從下拉列表選擇域的基礎數據類型。

維度：數組符的維度。

長度和比例：使用“長度”編輯框定義欄位的長度，使用“比例”編輯框定義小數點之后的位數。

默認：DEFAULT 子句為域數據類型列指定默認值，該值是任何沒有變量的表達式（但是子查詢是不允許），默認表達式的數據類型必須符合域的數據類型。如果沒有指定默認值，那么默認值是 null 值。

默認表達式將用于任何沒有為列指定值的插入操作，如果一個特定的列已定義默認值，它會無視任何關于域的默認。反過來，域默認會無視任何關于基礎數據類型的默認值。

不是 null：域的值不允許是 null。

擁有者：域函數的擁有者，定義域的用戶成為其擁有者。需要注意的是，支持 PostgreSQL 7.4 或以上版本。

檢查

檢查選項卡為管理域檢查提供。方便用戶創建新的、編輯或刪除選定的檢查。CHECK 子句指定完整性限制或測試，域的值必須符合。每個限制必須是一個生成 Boolean（布爾）結果的表達式，它應使用 VALUE 關鍵字來參照被測試的值。

postgresql 怎么自動創建分區

在數據庫日漸龐大的今天，為了方便對數據庫數據的管理，比如按時間，按地區去統計一些數據時，基數過于龐大，多有不便。很多商業數據庫都提供分區的概念，按不同的維度去存放數據，便于后期的管理，PostgreSQL也不例外。

PostgresSQL分區的意思是把邏輯上的一個大表分割成物理上的幾塊兒。分區不僅能帶來訪問速度的提升，關鍵的是，它能帶來管理和維護上的方便。

分區的具體好處是：

某些類型的查詢性能可以得到極大提升。

更新的性能也可以得到提升，因為表的每塊的索引要比在整個數據集上的索引要小。如果索引不能全部放在內存里，那么在索引上的讀和寫都會產生更多的磁盤訪問。

批量刪除可以用簡單的刪除某個分區來實現。

可以將很少用的數據移動到便宜的、轉速慢的存儲介質上。

在PG里表分區是通過表繼承來實現的，一般都是建立一個主表，里面是空，然后每個分區都去繼承它。無論何時，都應保證主表里面是空的。

小表分區不實際，表在多大情況下才考慮分區呢？PostgresSQL官方給出的建議是：當表本身大小超過了機器物理內存的實際大小時（the size of the table should exceed the physical memory of the database server），可以考慮分區。

PG目前（9.2.2）僅支持范圍分區和列表分區，尚未支持散列分區。

二、環境

系統環境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、實現分區

3.1 創建主表

david=# create table tbl_partition (

david(# id integer,

david(# name varchar(20),

david(# gender boolean,

david(# join_date date,

david(# dept char(4));

CREATE TABLE

david=#

3.2 創建分區表

david=# create table tbl_partition_201211 (

check ( join_date = DATE '2012-11-01' AND join_date DATE '2012-12-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201212 (

check ( join_date = DATE '2012-12-01' AND join_date DATE '2013-01-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201301 (

check ( join_date = DATE '2013-01-01' AND join_date DATE '2013-02-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201302 (

check ( join_date = DATE '2013-02-01' AND join_date DATE '2013-03-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201303 (

check ( join_date = DATE '2013-03-01' AND join_date DATE '2013-04-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201304 (

check ( join_date = DATE '2013-04-01' AND join_date DATE '2013-05-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201305 (

check ( join_date = DATE '2013-05-01' AND join_date DATE '2013-06-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=#

3.3 分區鍵上建索引

david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

CREATE INDEX

david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);

CREATE INDEX

david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);

CREATE INDEX

david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);

CREATE INDEX

david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);

CREATE INDEX

david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);

CREATE INDEX

david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);

CREATE INDEX

david=#

對于開發人員來說，希望數據庫是透明的，只管 insert into tbl_partition。對于數據插向哪個分區，則希望由DB決定。這點，ORACLE實現了，但是PG不行，需要前期人工處理下。

3.4 創建觸發器函數

david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.join_date = DATE '2012-11-01' AND

NEW.join_date DATE '2012-12-01' ) THEN

INSERT INTO tbl_partition_201211 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2012-12-01' AND

NEW.join_date DATE '2013-01-01' ) THEN

INSERT INTO tbl_partition_201212 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-01-01' AND

NEW.join_date DATE '2013-02-01' ) THEN

INSERT INTO tbl_partition_201301 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-02-01' AND

NEW.join_date DATE '2013-03-01' ) THEN

INSERT INTO tbl_partition_201302 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-03-01' AND

NEW.join_date DATE '2013-04-01' ) THEN

INSERT INTO tbl_partition_201303 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-04-01' AND

NEW.join_date DATE '2013-05-01' ) THEN

INSERT INTO tbl_partition_201304 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-05-01' AND

NEW.join_date DATE '2013-06-01' ) THEN

INSERT INTO tbl_partition_201305 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE FUNCTION

david=#

說明：如果不想丟失數據，上面的ELSE 條件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同時需要創建一張結構和tbl_partition 一樣的表tbl_partition_error_join_date，這樣，錯誤的join_date 數據就可以插入到這張表中而不是報錯了。

附上出處鏈接：

postgresql----數組類型和函數

postgresql支持數組類型，可以是基本類型，也可以是用戶自定義的類型。日常中使用數組類型的機會不多，但還是可以了解一下。不像C或JAVA高級語言的數組下標從0開始，postgresql數組下標從1開始，既可以指定長度，也可以不指定長度。且postgresql既支持一維數組，也支持多維數組，但是平時二維數組也就夠用了。

示例1.使用ARRAY構建數組類型

---1*4的一維數組test=#selectarray[1,2,3,4];

array? -----------{1,2,3,4}

(1 row)--2*2的二維數組test=#selectarray[[1,2],[3,4]];

array? ? ---------------{{1,2},{3,4}}

(1 row)--1*2的二維數組，基本類型為box矩形類型，且和上面相比box類型元素之間是以分號分隔的，其他所有類型的數據都是以逗號分隔的test=#selectarray[box'(1,1),(0,0)',box'(0,0),(-1,-1)'];

? ? ? ? array? ? ? ? ? ? -----------------------------{(1,1),(0,0);(0,0),(-1,-1)}

(1row)

示例2.創建一張表，字段包含數組類型

其中int[]表示數組長度無限制，int[4]表示數組長度為4.

test=#createtabletbl_array(aint[],bvarchar(32)[][],cint);CREATETABLEtest=#insertintotbl_array (a,b,c)values(array[1,2],array[[1,2,3],[4,5,6]],1);INSERT01test=#insertintotbl_array (a,b,c)values(array[1,2,3],array[[1,2],[4,5]],2);INSERT01test=#select*from tbl_array ;

a? ? |b| c ---------+-------------------+---{1,2}|{{1,2,3},{4,5,6}}|1 {1,2,3}|{{1,2},{4,5}}|2(2 rows)

test=#selecta[1],b[2]fromtbl_arraywherec=1;

a | b ---+---1|

(1 row)

test=#selecta[1],b[2][1]fromtbl_arraywherec=1;

a | b ---+---1|4(1 row)

test=#selecta[1],b[2][4]fromtbl_arraywherec=1;

a | b ---+---1|

(1row)

test=#updatetbl_arrayseta[1]=200wherea[1]=1;UPDATE1test=#selecta[1],b[2][4]from tbl_array ;

a? | b -----+---100|200|

(2rows)

也可以使用[下標:上標]方式來查詢數組連續的某些元素。

test=#selecta[2:3]from tbl_array ;

a? -------{2}

{2,3}

(2 rows)

test=#selecta[1:3]from tbl_array ;

a? ? -----------{100,2}

{200,2,3}

(2rows)

數組操作符與函數

操作符

操作符描述示例結果

=相等?SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3];?t

不等于?select ARRAY[1,2,3] ARRAY[1,2,4];?t

小于?select ARRAY[1,2,3] ARRAY[1,2,4];?t

大于?select ARRAY[1,4,3] ARRAY[1,2,4];?t

=小于或等于?select ARRAY[1,2,3] = ARRAY[1,2,3];?t

=大于或等于?select ARRAY[1,4,3] = ARRAY[1,4,3];?t

@包含?select ARRAY[1,4,3] @ ARRAY[3,1];?t

@包含于?select ARRAY[2,7] @ ARRAY[1,7,4,2,6];?t

重疊（是否有相同元素）?select ARRAY[1,4,3] ARRAY[2,1];?t

||數組與數組連接?select ARRAY[1,2,3] || ARRAY[4,5,6];?{1,2,3,4,5,6}

||數組與數組連接?select ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]];?{{1,2,3},{4,5,6},{7,8,9}}

||元素與數組連接?select 3 || ARRAY[4,5,6];?{3,4,5,6}

||數組與元素連接?select ARRAY[4,5,6] || 7;?{4,5,6,7}

函數

函數返回類型描述示例結果

array_append(anyarray,anyelement)anyarray?在數組末尾追加元素?

SELECT array_append(ARRAY[1,2], 3);

{1,2,3}

array_cat(anyarray,anyarray)anyarray?連接兩個數組?SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);?{1,2,3,4,5}

array_ndims(anyarray)int?返回數組維數?SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);?2

array_dims(anyarray)text?返回數組維數的文本表示?SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);?[1:2][1:3]

array_fill(anyelement,int[], [,int[]])anyarray使用提供的值和維度初始化一個數組，其中anyelement是值，第一個int[]是數組的長度，第二個int[]是數組下界，下界默認是1?SELECT array_fill(7, ARRAY[3], ARRAY[2]);?[2:4]={7,7,7}

array_length(anyarray,int)int?返回數組指定維度的長度?SELECT array_length(array[1,2,3], 1);?3

array_lower(anyarray,int)int?返回數組指定維度的下界?SELECT array_lower('[0:2]={1,2,3}'::int[], 1);?0

array_position(anyarray,anyelement[,int])int?返回數組元素anyelement從數組的[,int]位置（默認為1）開始第一次出現在數組中的位置，數組必須是一維的?SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');?2

array_positions(anyarray,anyelement)int[]?返回元素在數組中的所有位置?SELECT array_positions(ARRAY['A','A','B','A'], 'A');?{1,2,4}

array_prepend(anyelement,anyarray)anyarray?在數組開頭添加新的元素?SELECT array_prepend(1, ARRAY[2,3]);?{1,2,3}

array_remove(anyarray,anyelement)anyarray?從數組中刪除所有的指定元素，必須是一維數組?SELECT array_remove(ARRAY[1,2,3,2], 2);?{1,3}

array_replace(anyarray,anyelement,anyelement)anyarray?替換指定數組元素為新的元素?SELECT array_replace(ARRAY[1,2,5,4], 5, 3);?{1,2,3,4}

array_to_string(anyarray,text[,text])text?將數組元素使用分隔符連接為文本，NULL可以使用指定元素替換?SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*');?1,2,3,*,5

array_upper(anyarray,int)int?數組指定維度的上屆?SELECT array_upper(ARRAY[1,8,3,7], 1);?4

cardinality(anyarray)int?返回數組所有維度的長度總和，如果是空數組則返回0?SELECT cardinality(ARRAY[[1,2],[3,4]]);?4

string_to_array(text,text[,text])text[]?將文本使用分隔符分隔后轉換為數組，如果指定第三個參數，則第三個參數在數組中被轉換為NULL?SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');?{xx,NULL,zz}

unnest(anyarray)setof anyelement?將數組元素轉換為行?SELECT unnest(ARRAY[1,2]);?

1

2

unnest(anyarray,anyarray[, ...])setof anyelement, anyelement [, ...]?將多維數組轉換為行集合，其中第一個數組顯示為第一列，第二個數組顯示為第二列，以此類推。但是這個函數只在from子句中使用?SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);?

unnest | unnest

--------+----

1 | foo

2 | bar

| baz

分享文章：包含postgresql維度的詞條
文章來源：http://vcdvsql.cn/article20/dsdgojo.html

成都網站建設公司_創新互聯，為您提供定制網站、App設計、外貿網站建設、關鍵詞優化、App開發、響應式網站

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯