pg數據庫引擎,數據庫 pgsql

本文目錄一覽：

• 1、pgsql的主鍵存儲方式
• 2、MySQL與PostgreSQL比較 哪個數據庫更好
• 3、對比其他數據庫軟件，postgresql有什麼優勢
• 4、linux 系統下 怎麼更改postgre 下pg
• 5、為什麼postgrelsql的性能沒有mysql好

pgsql的主鍵存儲方式

PostgreSQL的穩定性極強，Innodb等索引在崩潰，斷電之類的災難場景下 抗擊打能力有了長足進步，然而很多 MqSQL用戶 都遇到過 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可以之hi語句級的鏡像或分片，slony可以進行字段級的同步配置，standby 可以構建 WAL文件級或流式的讀寫分離集群，同步頻率和集群策略調整方便。

一般關係型數據庫字符串有長度限制 8k 左右，無限長 TEXT類型的功能受限，只能作為外部大數據訪問。而 PG 的 TEXT 類型 可以直接訪問且無長度限制， SQL語法內置 正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG的話，文檔數據庫都可以省了。

PgSQL對於 numa 架構的支持比 MySQL強一些，比 MySQL對於讀的性能更好一些， PgSQL提交可以完全異步提交，而 MySQL的內存表不夠實用（因為表鎖的原因）。

pgsql除了存儲正常的數據類型外，還支持存儲

array，不管是一維數組還是多維數組均支持。

json和jsonb，相比使用 text存儲要高效很多。

json和 jsonb在更高的層面上看起來幾乎是一樣的，但是存儲實現上是不同的。

json存儲完的文本，json列會每次都解析存儲的值，它不支持索引，但 可以為創建表達式索引。

jsonb存儲的二進制格式，避免了重新解析數據結構。它支持索引，這意味着 可以不使用指定索引就能查詢任何路徑。

當我們比較寫入數據速度時，由於數據存儲 的方式的原因，jsonb會比 json 稍微的慢一點。json列會每次都 解析存儲的值，這意味着鍵的順序要和輸入的 時候一樣。但是 jsonb不同，以二進制格式存儲且不保證鍵的順序。因此如果有軟件需要依賴鍵的順序，jsonb可能不是最佳選擇。使用 jsonb的優勢還在於可以輕易的整合關係型數據和非關係型 數據 ，PostgreSQL對於 mongodb這類數據庫是一個不小的威脅，畢竟如果一個表中只有一列數據的類型是半結構化的，沒有必要為了遷就它而整個表的設計都採用 schemaless的結構。

1. CPU限制

PGSQL

沒有CPU核心數限制，有多少CPU核就用多少

MySQL

能用128核CPU，超過128核用不上

2. 配置文件參數

PGSQL

一共有255個參數，用到的大概是80個，參數比較穩定，用上個大版本配置文件也可以啟動當前大版本數據庫

MySQL

一共有707個參數，用到的大概是180個，參數不斷增加，就算小版本也會增加參數，大版本之間會有部分參數不兼容情況

3. 第三方工具依賴情況

PGSQL

只有高可用集群需要依靠第三方中間件，例如：patroni+etcd、repmgr

MySQL

大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，學習成本高，高可用集群也需要第三方中間件，官方MGR集群還沒成熟

4. 高可用主從複製底層原理

PGSQL

物理流複製，屬於物理複製，跟SQL Server鏡像/AlwaysOn一樣，嚴格一致，沒有任何可能導致不一致，性能和可靠性上，物理複製完勝邏輯複製，維護簡單

MySQL

主從複製，屬於邏輯複製，（sql_log_bin、binlog_format等參數設置不正確都會導致主從不一致）

大事務並行複製效率低，對於重要業務，需要依賴 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修復主從一致

主從複製出錯嚴重時候需要重搭主從

MySQL的邏輯複製並不阻止兩個不一致的數據庫建立複製關係

5. 從庫只讀狀態

PGSQL

系統自動設置從庫默認只讀，不需要人工介入，維護簡單

MySQL

從庫需要手動設置參數super_read_only=on，讓從庫設置為只讀，super_read_only參數有bug，鏈接：;wfr=spiderfor=pc

6. 版本分支

PGSQL

只有社區版，沒有其他任何分支版本，PGSQL官方統一開發，統一維護，社區版有所有功能，不像SQL Server和MySQL有標準版、企業版、經典版、社區版、開發版、web版之分

國內外還有一些基於PGSQL做二次開發的數據庫廠商，例如：Enterprise DB、瀚高數據庫等等，當然這些只是二次開發並不算獨立分支

MySQL

由於歷史原因，分裂為三個分支版本，MariaDB分支、Percona分支 、Oracle官方分支，發展到目前為止各個分支基本互相不兼容

Oracle官方分支還有版本之分，分為標準版、企業版、經典版、社區版

7. SQL特性支持

PGSQL

SQL特性支持情況支持94種，SQL語法支持最完善，例如：支持公用表表達式（WITH查詢）

MySQL

SQL特性支持情況支持36種，SQL語法支持比較弱，例如：不支持公用表表達式（WITH查詢）

關於SQL特性支持情況的對比，可以參考：

8. 主從複製安全性

PGSQL

同步流複製、強同步（remote apply)、高安全，不會丟數據

PGSQL同步流複製：所有從庫宕機，主庫會罷工，主庫無法自動切換為異步流複製（異步模式），需要通過增加從庫數量來解決，一般生產環境至少有兩個從庫

手動解決：在PG主庫修改參數synchronous_standby_names =”，並執行命令： pgctl reload ，把主庫切換為異步模式

主從數據完全一致是高可用切換的第一前提，所以PGSQL選擇主庫罷工也是可以理解

MySQL

增強半同步複製 ，mysql5.7版本增強半同步才能保證主從複製時候不丟數據

mysql5.7半同步複製相關參數：

參數rpl_semi_sync_master_wait_for_slave_count 等待至少多少個從庫接收到binlog，主庫才提交事務，一般設置為1，性能最高

參數rpl_semi_sync_master_timeout 等待多少毫秒，從庫無回應自動切換為異步模式，一般設置為無限大，不讓主庫自動切換為異步模式

所有從庫宕機，主庫會罷工，因為無法收到任何從庫的應答包

手動解決：在MySQL主庫修改參數rpl_semi_sync_master_wait_for_slave_count=0

9. 多字段統計信息

PGSQL

支持多字段統計信息

MySQL

不支持多字段統計信息

10. 索引類型

PGSQL

多種索引類型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表達式索引）

MySQL

btree 索引，全文索引（低效），表達式索引(需要建虛擬列)，hash 索引只在內存表

11. 物理表連接算法

PGSQL

支持 nested-loop join 、hash join 、merge join

MySQL

只支持 nested-loop join

12. 子查詢和視圖性能

PGSQL

子查詢，視圖優化，性能比較高

MySQL

視圖謂詞條件下推限制多，子查詢上拉限制多

13. 執行計劃即時編譯

PGSQL

支持 JIT 執行計劃即時編譯，使用LLVM編譯器

MySQL

不支持執行計劃即時編譯

14. 並行查詢

PGSQL

並行查詢（多種並行查詢優化方法），並行查詢一般多見於商業數據庫，是重量級功能

MySQL

有限，只支持主鍵並行查詢

15. 物化視圖

PGSQL

支持物化視圖

MySQL

不支持物化視圖

16. 插件功能

PGSQL

支持插件功能，可以豐富PGSQL的功能，GIS地理插件，時序數據庫插件， 向量化執行插件等等

MySQL

不支持插件功能

17. check約束

PGSQL

支持check約束

MySQL

不支持check約束，可以寫check約束，但存儲引擎會忽略它的作用，因此check約束並不起作用（mariadb 支持）

18. gpu 加速SQL

PGSQL

可以使用gpu 加速SQL的執行速度

MySQL

不支持gpu 加速SQL 的執行速度

19. 數據類型

PGSQL

數據類型豐富，如 ltree，hstore，數組類型，ip類型，text類型，有了text類型不再需要varchar，text類型字段最大存儲1GB

MySQL

數據類型不夠豐富

20. 跨庫查詢

PGSQL

不支持跨庫查詢，這個跟Oracle 12C以前一樣

MySQL

可以跨庫查詢

21. 備份還原

PGSQL

備份還原非常簡單，時點還原操作比SQL Server還要簡單，完整備份+wal歸檔備份（增量）

假如有一個三節點的PGSQL主從集群，可以隨便在其中一個節點做完整備份和wal歸檔備份

MySQL

備份還原相對不太簡單，完整備份+binlog備份（增量）

完整備份需要percona的XtraBackup工具做物理備份，MySQL本身不支持物理備份

時點還原操作步驟繁瑣複雜

22. 性能視圖

PGSQL

需要安裝pg_stat_statements插件，pg_stat_statements插件提供了豐富的性能視圖：如：等待事件，系統統計信息等

不好的地方是，安裝插件需要重啟數據庫，並且需要收集性能信息的數據庫需要執行一個命令：create extension pg_stat_statements命令

否則不會收集任何性能信息，比較麻煩

MySQL

自帶PS庫，默認很多功能沒有打開，而且打開PS庫的性能視圖功能對性能有影響（如：內存佔用導致OOM bug）

23. 安裝方式

PGSQL

有各個平台的包rpm包，deb包等等，相比MySQL缺少了二進制包，一般用源碼編譯安裝，安裝時間會長一些，執行命令多一些

MySQL

有各個平台的包rpm包，deb包等等，源碼編譯安裝、二進制包安裝，一般用二進制包安裝，方便快捷

24. DDL操作

PGSQL

加字段、可變長字段類型長度改大不會鎖表，所有的DDL操作都不需要藉助第三方工具，並且跟商業數據庫一樣，DDL操作可以回滾，保證事務一致性

MySQL

由於大部分DDL操作都會鎖表，例如加字段、可變長字段類型長度改大，所以需要藉助percona-toolkit裡面的pt-online-schema-change工具去完成操作

將影響減少到最低，特別是對大表進行DDL操作

DDL操作不能回滾

25. 大版本發布速度

PGSQL

PGSQL每年一個大版本發布，大版本發布的第二年就可以上生產環境，版本迭代速度很快

PGSQL 9.6正式版推出時間：2016年

PGSQL 10 正式版推出時間：2017年

PGSQL 11 正式版推出時間：2018年

PGSQL 12 正式版推出時間：2019年

MySQL

MySQL的大版本發布一般是2年~3年，一般大版本發布後的第二年才可以上生產環境，避免有坑，版本發布速度比較慢

MySQL5.5正式版推出時間：2010年

MySQL5.6正式版推出時間：2013年

MySQL5.7正式版推出時間：2015年

MySQL8.0正式版推出時間：2018年

26. returning語法

PGSQL

支持returning語法，returning clause 支持 DML 返回 Resultset，減少一次 Client – DB Server 交互

MySQL

不支持returning語法

27. 內部架構

PGSQL

多進程架構，並發連接數不能太多，跟Oracle一樣，既然跟Oracle一樣，那麼很多優化方法也是相通的，例如：開啟大頁內存

MySQL

多線程架構，雖然多線程架構，但是官方有限制連接數，原因是系統的並發度是有限的，線程數太多，反而系統的處理能力下降，隨着連接數上升，反而性能下降

一般同時只能處理200 ~300個數據庫連接

28. 聚集索引

PGSQL

不支持聚集索引，PGSQL本身的MVCC的實現機制所導致

MySQL

支持聚集索引

29. 空閑事務終結功能

PGSQL

通過設置 idle_in_transaction_session_timeout 參數來終止空閑事務，比如：應用代碼中忘記關閉已開啟的事務，PGSQL會自動查殺這種類型的會話事務

MySQL

不支持終止空閑事務功能

30. 應付超大數據量

PGSQL

不能應付超大數據量，由於PGSQL本身的MVCC設計問題，需要垃圾回收，只能期待後面的大版本做優化

MySQL

不能應付超大數據量，MySQL自身架構的問題

31. 分布式演進

PGSQL

HTAP數據庫：cockroachDB、騰訊Tbase

分片集群： Postgres-XC、Postgres-XL

MySQL

HTAP數據庫：TiDB

分片集群： 各種各樣的中間件，不一一列舉

32. 數據庫的文件名和命名規律

PGSQL

PGSQL在這方面做的比較不好，DBA不能在操作系統層面（停庫狀態下）看清楚數據庫的文件名和命名規律，文件的數量，文件的大小

一旦操作系統發生文件丟失或硬盤損壞，非常不利於恢復，因為連名字都不知道

PGSQL表數據物理文件的命名/存放規律是： 在一個表空間下面，如果沒有建表空間默認在默認表空間也就是base文件夾下，例如：/data/base/16454/3599

base：默認表空間pg_default所在的物理文件夾

16454：表所在數據庫的oid

3599：就是表對象的oid，當然，一個表的大小超出1GB之後會再生成多個物理文件，還有表的fsm文件和vm文件，所以一個大表實際會有多個物理文件

由於PGSQL的數據文件布局內容太多，大家可以查閱相關資料

當然這也不能全怪PGSQL，作為一個DBA，時刻做好數據庫備份和容災才是正道，做介質恢復一般是萬不得已的情況下才會做

MySQL

數據庫名就是文件夾名，數據庫文件夾下就是表數據文件，但是要注意表名和數據庫名不能有特殊字符或使用中文名，每個表都有對應的frm文件和ibd文件，存儲元數據和表/索引數據，清晰明了，做介質恢復或者表空間傳輸都很方便

33. 權限設計

PGSQL

PGSQL在權限設計這塊是比較坑爹，拋開實例權限和表空間權限，PGSQL的權限層次有點像SQL Server，db=》schema=》object

要說權限，這裡要說一下Oracle，用Oracle來類比

在ORACLE 12C之前，實例與數據庫是一對一，也就是說一個實例只能有一個數據庫，不像MySQL和SQL Server一個實例可以有多個數據庫，並且可以隨意跨庫查詢

而PGSQL不能跨庫查詢的原因也是這樣，PGSQL允許建多個數據庫，跟ORACLE類比就是有多個實例（之前說的實例與數據庫是一對一）

一個數據庫相當於一個實例，因為PGSQL允許有多個實例，所以PGSQL單實例不叫一個實例，叫集簇（cluster），集簇這個概念可以查閱PGSQL的相關資料

PGSQL裡面一個實例/數據庫下面的schema相當於數據庫，所以這個schema的概念對應MySQL的database

注意點：正因為是一個數據庫相當於一個實例，PGSQL允許有多個實例/數據庫，所以數據庫之間是互相邏輯隔離的，導致的問題是，不能一次對一個PGSQL集簇下面的所有數據庫做操作

必須要逐個逐個數據庫去操作，例如上面說到的安裝pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有數據庫都做性能收集的話，需要逐個數據庫去執行加載命令

又例如跨庫查詢需要dblink插件或fdw插件，兩個數據庫之間做查詢相當於兩個實例之間做查詢，已經跨越了實例了，所以需要dblink插件或fdw插件，所以道理非常簡單

權限操作也是一樣逐個數據庫去操作，還有一個就是PGSQL雖然像SQL Server的權限層次結構db=》schema=》object，但是實際會比SQL Server要複雜一些，還有就是新建的表還要另外授權

在PGSQL裡面，角色和用戶是一樣的，對新手用戶來說有時候會傻傻分不清，也不知道怎麼去用角色，所以PGSQL在權限設計這一塊確實比較坑爹

MySQL

使用mysql庫下面的5個權限表去做權限映射，簡單清晰，唯一問題是缺少權限角色

user表

db表

host表

tables_priv表

columns_priv表

1. 架構對比

Mysql：多線程

PostgreSql：多進程

多線程架構和多進程架構之間沒有絕對的好壞，例如oracle在unix上是多進程架構，在windows上是多線程架構。

2. 對存儲過程及事務的支持能力

MySql對於無事務的MyISAM表，採用表鎖定，一個長時間運行的查詢很可能會長時間的阻礙，而PostgreSQL不會尊在這種問題。

PostgreSQL支持存儲過程，要比MySql好，具備本地緩存執行計劃的能力。

3. 穩定性及性能

高並發讀寫，負載逼近極限下，PG的性能指標仍可以維持雙曲線甚至對數曲線，到頂峰之後不再下降，而 MySql 明顯出現一個波峰後下滑（5.5版本後Mysql企業版有優化，需要付費）

MySql的InnoDB引擎，可以充分優化利用系統的所有內存，超大內存下PG對內存使用的不那麼充分（需要根據內存情況合理分配）。

4. 高可用

InnoDB的基於回滾實現的 MVCC 機制，對於 PG 新老數據一起放的基於 XID 的 MVCC機制，是佔優的。新老數據一起存放，需要定時觸發 VACUUM，會帶來多餘的 IO 和數據庫對象加鎖開銷，引起數據庫整理的並發能力下降。而且 VACUUM 清理不及時，還可能會引發數據膨脹

5. 數據同步方式：

Mysql到現在也是異步複製，pgsql可以做到同步、異步、半同步複製。

Mysql同步是基於binlog複製，屬於邏輯複製，類似於oracle golden gate，是基於stream的複製，做到同步很困難，這種方式更加適合異步複製；

Pgsql的同是基於wal，屬於物理複製，可以做到同步複製。同時，pgsql還提供stream複製。

Mysql的複製可以用多級從庫，但是在9.2之前，PgSql不能用從庫帶從庫。

Pgsql的主從複製屬於物理複製，相對於Mysql基於binlog的邏輯複製，數據的一致性更加可靠，複製性能更高，對主機性能的影響也更小。

6. 權限控制對比

MySql允許自定義一套不同的數據級、表級和列的權限，運行指定基於主機的權限

Mysql的merge表提供了 一個獨特管理多個表的方法。myisampack可以對只讀表進行壓縮，以後仍然可以直接訪問該表中的行。

7. SQL語句支持能力

PG有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，例如分析函數（Oracle的叫法，PG里叫window函數）

支持用多種語言來寫存儲過程，對於R的支持也很好。這一點上Mysql就差的很遠，很多分析功能都不支持。

PgSql對錶名大小寫的處理，只有在Sql語句中，表明加雙引號，才區分大小寫。

在Sql的標準實現上要比Mysql完善，而且功能實現比較嚴謹。

對錶連接支持比較完整，優化器的功能比較完整，支持的索引類型很多，複雜查詢能力較強。

Mysql採用索引組織表，這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作，但是對錶結果設計存在約束；

Mysql的Join操作的性能非常的差，只支持Nest Join，所以一旦數據量大，性能就非常的差。PostgresSQL除了支持 Nest Join 和 Sort Merge Join，PostgreSQL還支持正則表達式查詢，MySql不支持。

8. 數據類型支持能力

PostgreSQL可以更方便的使用UDF（用戶定義函數）進行擴展。

有豐富的幾何類型，實際上不止集合類型，PG有大量的字典、數組、bitmap等數據類型，因此PG多年來在 GIS 領域處於優勢地位。相比之下Mysql就差很多，instagram就是因為PG的空間數據擴展 PostGIS遠遠強於 MySql的 my spatial 而採用 PgSql的。Mysql中的空間數據類型有4種，分別是 CEOMETRY、POINT、LINESTRING、POLYGON，其空間索引只能在存儲引擎為 MyiSam的表中創建，用SPATIAL關鍵字進行擴展，使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL。不同的存儲親情有差別。MyISAM和InnoDB 都支持 spatial extensions，但差別在於：如果使用MyISAM，可以建立 spatial index，而 InnoDB是不支持的。

pgsql對json支持比較好，還有很逆天的fdw功能，就是把別的數據庫中的表當自己的用。

pgsql的字段類型支持的多，有很多mysql沒有的類型，但是實際中有時候用到。

一半關係型數據庫的字符串長度8k左右，無限長的 TEXT 類型的功能受限，只能作為外部帶數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL 語法內置正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG 的話，文檔數據庫都可以省了。

postgresql 有函數，用於報表、統計很方便

PG支持 R-Trees這樣可擴展的索引類型，可以方便的處理一些特殊數據。

PG可以使用函數和條件所以，使得數據庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。

9. 如可過程容錯能力

大批量數據入庫，PostgreSql要求所有的數據必須完全滿足要求，有一條錯誤，整個數據入庫過程失敗。MySql無此問題。

10. 表組織方式

pgsql用繼承的方式實現分區表，讓分區表的使用不方便且性能差，這點比不上mysql。

pg主表採用堆表存放，MySQL採用索引組織表，能夠支持比MySql更大的數據量。

MySql分區表的實現要優於PG的基於繼承表的分區實現，主要體現在分區個數達到成千上萬後的處理性能差異很大。

11. 開發結構

對於web應用來所，mysql 5.6 的內置 MC API 功能很好用，PgSQL差一些。

PG的“無鎖定”特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和 PGSQL的 MVCC 實現有關係。

好文要頂 關注我 收藏該文  

茄子777

粉絲 – 0 關注 – 0

+加關注

00

« 上一篇： 多線程中的wait與join

» 下一篇： 負載均衡相關

posted @ 2022-11-02 16:20 茄子777 閱讀(55) 評論(0) 編輯 收藏 舉報

刷新評論刷新頁面返回頂部

登錄後才能查看或發表評論，立即 登錄 或者 逛逛 博客園首頁

【推薦】阿里雲新人特惠，爆款雲服務器2核4G低至0.46元/天

【推薦】雙十一同價！騰訊云云服務器搶先購，低至4.2元/月

編輯推薦：

· 一個有趣的 nginx HTTP 400 響應問題分析

· 誰說.NET沒有GC調優？只改一行代碼就讓程序不再佔用內存

· 為什麼標準庫的模板變量都是 inline 的

· .net 如何優雅的使用 EFCore

· 在 C# 中使用 Halcon 開發視覺檢測程序

閱讀排行：

· Entity Framework Core 7中高效地進行批量數據插入

· 除了 filter 還有什麼置灰網站的方式？

· 快速繪製流程圖「GitHub 熱點速覽 v.22.47」

· 使用.NET7和C#11打造最快的序列化程序-以MemoryPack為例

· 私藏！資深數據專家SQL效率優化技巧 ⛵

MySQL與PostgreSQL比較 哪個數據庫更好

特性 MySQL PostgreSQL

實例 通過執行 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 驅動程序。

表類型 取決於存儲引擎。例如，NDB 存儲引擎支持分區表，內存引擎支持內存表。 支持臨時表、常規表以及範圍和列表類型的分區表。不支持哈希分區表。 由於PostgreSQL的表分區是通過表繼承和規則系統完成了，所以可以實現更複雜的分區方式。

索引類型 取決於存儲引擎。MyISAM：BTREE，InnoDB：BTREE。 支持 B-樹、哈希、R-樹和 Gist 索引。

約束 支持主鍵、外鍵、惟一和非空約束。對檢查約束進行解析，但是不強制實施。 支持主鍵、外鍵、惟一、非空和檢查約束。

存儲過程和用戶定義函數 支持 CREATE PROCEDURE 和 CREATE FUNCTION 語句。存儲過程可以用 SQL 和 C++ 編寫。用戶定義函數可以用 SQL、C 和 C++ 編寫。 沒有單獨的存儲過程，都是通過函數實現的。用戶定義函數可以用 PL/pgSQL（專用的過程語言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 編寫。

觸發器 支持行前觸發器、行後觸發器和語句觸發器，觸發器語句用過程語言複合語句編寫。 支持行前觸發器、行後觸發器和語句觸發器，觸發器過程用 C 編寫。

系統配置文件 my.conf Postgresql.conf

數據庫配置 my.conf Postgresql.conf

客戶機連接文件 my.conf pg_hba.conf

XML 支持 有限的 XML 支持。 有限的 XML 支持。

數據訪問和管理服務器 OPTIMIZE TABLE —— 回收未使用的空間並消除數據文件的碎片

myisamchk -analyze —— 更新查詢優化器所使用的統計數據（MyISAM 存儲引擎）

mysql —— 命令行工具

MySQL Administrator —— 客戶機 GUI 工具 Vacuum —— 回收未使用的空間

Analyze —— 更新查詢優化器所使用的統計數據

psql —— 命令行工具

pgAdmin —— 客戶機 GUI 工具

並發控制 支持表級和行級鎖。InnoDB 存儲引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 語句在事務級設置隔離級別。 支持表級和行級鎖。支持的 ANSI 隔離級別是 Read Committed（默認 —— 能看到查詢啟動時數據庫的快照）和 Serialization（與 Repeatable Read 相似 —— 只能看到在事務啟動之前提交的結果）。使用 SET TRANSACTION 語句在事務級設置隔離級別。使用 SET SESSION 在會話級進行設置。

MySQL相對於PostgreSQL的劣勢：

MySQL

PostgreSQL

最重要的引擎InnoDB很早就由Oracle公司控制。目前整個MySQL數據庫都由Oracle控制。

BSD協議，沒有被大公司壟斷。

對複雜查詢的處理較弱，查詢優化器不夠成熟

很強大的查詢優化器，支持很複雜的查詢處理。

只有一種表連接類型:嵌套循環連接(nested-loop),不支持排序-合併連接(sort-merge join)與散列連接(hash join)。

都支持

性能優化工具與度量信息不足

提供了一些性能視圖，可以方便的看到發生在一個表和索引上的select、delete、update、insert統計信息，也可以看到cache命中率。網上有一個開源的pgstatspack工具。

InnoDB的表和索引都是按相同的方式存儲。也就是說表都是索引組織表。這一般要求主鍵不能太長而且插入時的主鍵最好是按順序遞增，否則對性能有很大影響。

不存在這個問題。

大部分查詢只能使用表上的單一索引;在某些情況下，會存在使用多個索引的查詢,但是查詢優化器通常會低估其成本,它們常常比表掃描還要慢。

不存在這個問題

表增加列，基本上是重建表和索引，會花很長時間。

表增加列，只是在數據字典中增加表定義，不會重建表

存儲過程與觸發器的功能有限。可用來編寫存儲過程、觸發器、計劃事件以及存儲函數的語言功能較弱

除支持pl/pgsql寫存儲過程，還支持perl、python、Tcl類型的存儲過程：pl/perl，pl/python，pl/tcl。

也支持用C語言寫存儲過程。

不支持Sequence。

支持

不支持函數索引，只能在創建基於具體列的索引。

不支持物化視圖。

支持函數索引，同時還支持部分數據索引，通過規則系統可以實現物化視圖的功能。

執行計劃並不是全局共享的, 僅僅在連接內部是共享的。

執行計劃共享

MySQL支持的SQL語法(ANSI SQL標準)的很小一部分。不支持遞歸查詢、通用表表達式（Oracle的with 語句）或者窗口函數（分析函數）。

都 支持

不支持用戶自定義類型或域(domain)

支持。

對於時間、日期、間隔等時間類型沒有秒以下級別的存儲類型

可以精確到秒以下。

身份驗證功能是完全內置的，不支持操作系統認證、PAM認證，不支持LDAP以及其它類似的外部身份驗證功能。

支持OS認證、Kerberos 認證 、Ident 的認證、LDAP 認證、PAM 認證

不支持database link。有一種叫做Federated的存儲引擎可以作為一個中轉將查詢語句傳遞到遠程服務器的一個表上,不過,它功能很粗糙並且漏洞很多

有dblink，同時還有一個dbi-link的東西，可以連接到oracle和mysql上。

Mysql Cluster可能與你的想象有較大差異。開源的cluster軟件較少。

複製(Replication)功能是異步的,並且有很大的局限性.例如,它是單線程的(single-threaded),因此一個處理能力更強的Slave的恢復速度也很難跟上處理能力相對較慢的Master.

有豐富的開源cluster軟件支持。

explain看執行計劃的結果簡單。

explain返回豐富的信息。

類似於ALTER TABLE或CREATE TABLE一類的操作都是非事務性的.它們會提交未提交的事務，並且不能回滾也不能做災難恢復

DDL也是有事務的。

PostgreSQL主要優勢：

1. PostgreSQL完全免費，而且是BSD協議，如果你把PostgreSQL改一改，然後再拿去賣錢，也沒有人管你，這一點很重要，這表明了PostgreSQL數據庫不會被其它公司控制。oracle數據庫不用說了，是商業數據庫，不開放。而MySQL數據庫雖然是開源的，但現在隨着SUN被oracle公司收購，現在基本上被oracle公司控制，其實在SUN被收購之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的數據都是放在InnoDB引擎中的，反正我們公司都是這樣的。所以如果MySQL的市場範圍與oracle數據庫的市場範圍衝突時，oracle公司必定會犧牲MySQL，這是毫無疑問的。

2. 與PostgreSQl配合的開源軟件很多，有很多分布式集群軟件，如pgpool、pgcluster、slony、plploxy等等，很容易做讀寫分離、負載均衡、數據水平拆分等方案，而這在MySQL下則比較困難。

3. PostgreSQL源代碼寫的很清晰，易讀性比MySQL強太多了，懷疑MySQL的源代碼被混淆過。所以很多公司都是基本PostgreSQL做二次開發的。

4. PostgreSQL在很多方面都比MySQL強，如複雜SQL的執行、存儲過程、觸發器、索引。同時PostgreSQL是多進程的，而MySQL是線程的，雖然並發不高時，MySQL處理速度快，但當並發高的時候，對於現在多核的單台機器上，MySQL的總體處理性能不如PostgreSQL，原因是MySQL的線程無法充分利用CPU的能力。

目前只想到這些，以後想到再添加，歡迎大家拍磚。

PostgreSQL與oracle或InnoDB的多版本實現的差別

PostgreSQL與oracle或InnoDB的多版本實現最大的區別在於最新版本和歷史版本是否分離存儲，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分離了數據,索引本身沒有分開。

PostgreSQL的主要優勢在於：

1. PostgreSQL沒有回滾段，而oracle與innodb有回滾段，oracle與Innodb都有回滾段。對於oracle與Innodb來說，回滾段是非常重要的，回滾段損壞，會導致數據丟失，甚至數據庫無法啟動的嚴重問題。另由於PostgreSQL沒有回滾段，舊數據都是記錄在原先的文件中，所以當數據庫異常crash後，恢復時，不會象oracle與Innodb數據庫那樣進行那麼複雜的恢復，因為oracle與Innodb恢復時同步需要redo和undo。所以PostgreSQL數據庫在出現異常crash後，數據庫起不來的幾率要比oracle和mysql小一些。

2. 由於舊的數據是直接記錄在數據文件中，而不是回滾段中，所以不會象oracle那樣經常報ora-01555錯誤。

3. 回滾可以很快完成，因為回滾並不刪除數據，而oracle與Innodb，回滾時很複雜，在事務回滾時必須清理該事務所進行的修改，插入的記錄要刪除，更新的記錄要更新回來(見row_undo函數)，同時回滾的過程也會再次產生大量的redo日誌。

4. WAL日誌要比oracle和Innodb簡單，對於oracle不僅需要記錄數據文件的變化，還要記錄回滾段的變化。

PostgreSQL的多版本的主要劣勢在於：

1、最新版本和歷史版本不分離存儲，導致清理老舊版本需要作更多的掃描，代價比較大，但一般的數據庫都有高峰期，如果我們合理安排VACUUM，這也不是很大的問題，而且在PostgreSQL9.0中VACUUM進一步被加強了。

2、由於索引中完全沒有版本信息，不能實現Coverage index scan，即查詢只掃描索引，直接從索引中返回所需的屬性，還需要訪問表。而oracle與Innodb則可以;

進程模式與線程模式的對比

PostgreSQL和oracle是進程模式，MySQL是線程模式。

進程模式對多CPU利用率比較高。

進程模式共享數據需要用到共享內存，而線程模式數據本身就是在進程空間內都是共享的，不同線程訪問只需要控制好線程之間的同步。

線程模式對資源消耗比較少。

所以MySQL能支持遠比oracle多的更多的連接。

對於PostgreSQL的來說，如果不使用連接池軟件，也存在這個問題，但PostgreSQL中有優秀的連接池軟件軟件，如pgbouncer和pgpool，所以通過連接池也可以支持很多的連接。

堆表與索引組織表的的對比

Oracle支持堆表，也支持索引組織表

PostgreSQL只支持堆表，不支持索引組織表

Innodb只支持索引組織表

索引組織表的優勢：

表內的數據就是按索引的方式組織，數據是有序的，如果數據都是按主鍵來訪問，那麼訪問數據比較快。而堆表，按主鍵訪問數據時，是需要先按主鍵索引找到數據的物理位置。

索引組織表的劣勢：

索引組織表中上再加其它的索引時，其它的索引記錄的數據位置不再是物理位置，而是主鍵值，所以對於索引組織表來說，主鍵的值不能太大，否則佔用的空間比較大。

對於索引組織表來說，如果每次在中間插入數據，可能會導致索引分裂，索引分裂會大大降低插入的性能。所以對於使用innodb來說，我們一般最好讓主鍵是一個無意義的序列，這樣插入每次都發生在最後，以避免這個問題。

由於索引組織表是按一個索引樹，一般它訪問數據塊必須按數據塊之間的關係進行訪問，而不是按物理塊的訪問數據的，所以當做全表掃描時要比堆錶慢很多，這可能在OLTP中不明顯，但在數據倉庫的應用中可能是一個問題。

　 PostgreSQL9.0中的特色功能：

PostgreSQL中的Hot Standby功能

也就是standby在應用日誌同步時，還可以提供只讀服務，這對做讀寫分離很有用。這個功能是oracle11g才有的功能。

PostgreSQL異步提交（Asynchronous Commit）的功能：

　 這個功能oracle中也是到oracle11g R2才有的功能。因為在很多應用場景中，當宕機時是允許丟失少量數據的，這個功能在這樣的場景中就特別合適。在PostgreSQL9.0中把synchronous_commit設置為false就打開了這個功能。需要注意的是，雖然設置為了異步提交，當主機宕機時，PostgreSQL只會丟失少量數據，異步提交並不會導致數據損壞而數據庫起不來的情況。MySQL中沒有聽說過有這個功能。

PostgreSQL中索引的特色功能：

PostgreSQL中可以有部分索引，也就是只能表中的部分數據做索引，create index 可以帶where 條件。同時PostgreSQL中的索引可以反向掃描，所以在PostgreSQL中可以不必建專門的降序索引了。

對比其他數據庫軟件，postgresql有什麼優勢

一、 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應用中很重要。

linux 系統下 怎麼更改postgre 下pg

1. 安裝Linux操作系統注意把gdb、Emacs或DDD這些開發工具都安裝上。如果是在虛擬機上安裝，依然需要設置Linux系統的網絡環境；另外需要設置文件共享，方便windows下面的postgreSQL源碼能在Linux下面訪問到。

2. 安裝PostgreSQL

useradd postgre

（自動建立 postgre 組；設計人員為了安全考慮，PostgreSQL 不能以root 用戶運行，所以必須建立對應的用戶和組。）

解壓到 /usr/local/src

tar xvfz postgresql-8.4.tar.gz

cd postgresql-8.4

./configure –prefix=/usr/local/pgsql –enable-debug –enable-assert –without-readline –without-zlib

make

make install

chown -R postgre.postgre /usr/local/pgsql

3. 設置Postgres環境變量（非必須）

vi ~postgre/.bash_profile

添加：

PGLIB=/usr/local/pgsql/lib

PGDATA=$HOME/data

PATH=$PATH:/usr/local/pgsql/bin

MANPATH=$MANPATH:/usr/local/pgsql/man

export PGLIB PGDATA PATH MANPATH

4. 建立數據庫

以 postgres 用戶登錄：

su postgre

建立數據庫目錄：

mkdir data

啟動數據庫引擎：

initdb –D “數據庫目錄”

之後可以根據提示，通過psql進入數據庫

5. 構造PostgreSQL調試環境

先 psql template1進去，然後

select pg_backend_pid();

獲得id，就是gdb後面用到的數字

gdb /usr/local/pgsql/bin/postgres 997(pid的數字)

如果只使用gdb，全部是命令行界面；而Emac、DDD分別是彩色、黑白用戶交互式圖形界面。

6. 使用gdb進行調試

為什麼postgrelsql的性能沒有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差一些。