引言

PostgreSQL 9.0 是一款強大且流行的關系型數據庫管理系統，以其可靠性、可擴展性和高級功能而聞名。對于任何希望掌握數據庫管理和性能優化的專業人士來說，精通 PostgreSQL 9.0 至關重要。

第 1 部分：數據庫管理

• 安裝和配置：了解 PostgreSQL 9.0 的安裝和配置過程，包括系統要求和最佳實踐。
• 數據庫創建和管理：創建、修改和刪除數據庫，管理用戶和權限，并處理備份和恢復。
• 表和列：創建、修改和刪除表，定義列、約束和索引，并組織數據以提高查詢性能。

第 2 部分：查詢優化

• 查詢分析：使用 EXPLAIN 命令分析查詢并識別瓶頸，以了解查詢執行計劃和優化策略。
• 索引：創建和管理索引以加快查詢速度，并理解不同索引類型的優缺點。

• 查詢調優：應用優化技巧，例如重寫查詢、使用臨時表和索引連接。

第 3 部分：高級功能

• 觸發器和存儲過程：創建觸發器和存儲過程以自動化任務、增強安全性并提高性能。
• 全文搜索：配置和使用 PostgreSQL 的全文搜索功能，以快速高效地搜索文本數據。
• 復制和分片：設置復制和分片以提高數據庫可用性和可擴展性。

第 4 部分：性能監視和故障排除

• 監視工具：使用內置工具和第三方軟件監視數據庫性能，例如 pg_stat_activity 和 pg_stat_statements。
• 日志文件：查看和分析日志文件以診斷問題和識別性能瓶頸。
• 故障排除：遵循系統化的方法診斷和解決數據庫問題，包括連接錯誤、查詢超時和數據損壞。

結論

精通 PostgreSQL 9.0 是提升數據庫管理和性能優化的關鍵技能。通過理解本指南中介紹的核心概念和技術，您可以優化數據庫性能、自動化任務并確保數據庫的高可用性和可靠性。掌握 PostgreSQL 9.0 將為您的職業道路打開許多機會，并幫助您在當今競爭激烈的 IT 領域脫穎而出。

---

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

特性MySQLPostgreSQL實例通過執行 MySQL 命令（mysqld）啟動實例。 一個實例可以管理一個或多個數據庫。 一臺服務器可以運行多個 mysqld 實例。 一個實例管理器可以監視 mysqld 的各個實例。 通過執行 Postmaster 進程（pg_ctl）啟動實例。 一個實例可以管理一個或多個數據庫，這些數據庫組成一個集群。 集群是磁盤上的一個區域，這個區域在安裝時初始化并由一個目錄組成，所有數據都存儲在這個目錄中。 使用 initdb 創建第一個數據庫。 一臺機器上可以啟動多個實例。 數據庫數據庫是命名的對象集合，是與實例中的其他數據庫分離的實體。 一個 MySQL 實例中的所有數據庫共享同一個系統編目。 數據庫是命名的對象集合，每個數據庫是與其他數據庫分離的實體。 每個數據庫有自己的系統編目，但是所有數據庫共享 pg_databases。 數據緩沖區通過 innodb_buffer_pool_size 配置參數設置數據緩沖區。 這個參數是內存緩沖區的字節數，InnoDB 使用這個緩沖區來緩存表的數據和索引。 在專用的數據庫服務器上，這個參數最高可以設置為機器物理內存量的 80%。 Shared_buffers 緩存。 在默認情況下分配 64 個緩沖區。 默認的塊大小是 8K。 可以通過設置 文件中的 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 編寫。 系統配置文件數據庫配置客戶機連接文件_ 支持有限的 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的劣勢：MySQLPostgreSQL最重要的引擎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-錯誤。 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中可以不必建專門的降序索引了。

達芬奇安裝的時候為什么不能取消PostgreSQL9.0.4.1

達芬奇的數據系統底層是基于數據庫管理的（包括工作臨時狀態的暫存），所以這個數據庫系統PostgreSQL9.0.4.1是必備的，不能取消。

PostgreSQL@K8s 性能優化記

本文作者蔡松露，作為云猿生數據的CTO和聯合創始人，以及阿里云數據庫的資深技術專家，他分享了在Kubernetes（K8s）環境中對比ECS（云服務器）上的PostgreSQL（ECS PG）和KubeBlocks（一個云原生數據庫管理系統）的性能優化經驗。 他針對K8s上的數據庫性能提出了優化策略，以確保在分布式架構中數據庫能穩定并滿足生產需求。 隨著企業傾向于全K8s架構，將數據庫遷移到Kubernetes面臨挑戰。 KubeBlocks，由曹偉（鳴嵩）團隊開發，作為數據庫管控平臺，支持多種數據庫，如MySQL、PG、Redis等。 蔡松露通過實際操作，首先調整了PG實例的資源限制，進行了Sysbench讀寫壓力測試，以模擬生產環境。 在一系列的壓測中，他發現網絡延遲、磁盤帶寬瓶頸、Checkpoint與鎖競爭以及WAL日志創建過程中的問題對性能有很大影響。 通過調整臟頁回收比例、WAL日志大小和啟用Huge Page，他逐步縮小了問題范圍，最后將焦點鎖定在PostgreSQL內核實現上，特別是WAL日志創建和同步機制。 在優化過程中，蔡松露調整了wal_init_zero設置，并考慮了文件系統支持，以減少加鎖頻率。 他還建議根據存儲機制關閉full_page_write以降低IO競爭。 通過引入pgBouncer來處理高并發，最終優化后的KubeBlocks在并發量增加時表現出更好的性能和穩定性。 總結來說，蔡松露的優化策略顯示，盡管K8s環境下運行PostgreSQL存在挑戰，但通過細致的性能分析和針對性優化，可以顯著提升數據庫在Kubernetes環境下的性能，使其滿足生產級別的要求。

如何從PostgreSQL9.1.7升級到9.2.4

具體過程如下：步驟1：安裝postgreSQL 9.2.4具體過程參見：如何安裝PostgreSQL 9.2.4 For Windows?步驟2：配置鑒權口令文件pg_upgrade會在升級過程中多次連接新舊數據庫，所以可以在pg_中將鑒權方式設為Trust或者在使用MD5鑒權方式下配置文件（文件位置：%APPDATA%\postgresql\）。 這樣可以避免在升級過程中提示鑒權失敗而導致升級不能正常進行。 待升級成功后可以移除或清空文件內容。 步驟3：安裝插件將舊數據庫服務器中使用的插件同樣安裝至新數據庫服務器中。 步驟4：停止新舊數據庫服務通過命令行或服務管理界面進行出現問題：原因：未使用管理員權限運行命令提示符（CMD）步驟5：用非windows系統管理員權限用戶做升級前準備RUNAS /USER:postgres SET PATH=D:\Program Files\PostgreSQL\9.2\bin;%PATH%;步驟6：進入postgres用戶目錄，執行pg_upgrade（如果環境變量設置正確，會使用新版本下的pg_upgrade），會有升級相應的日志文件生成于此命令格式：pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option...]其中會使用 - r 參數：作用是在系統升級成功后仍然保留日志文件。 如果升級過程中出現問題，可以通過windos事件檢查器及相應升級日志文件進行分析。 pg_upgrade_ pg_upgrade_ pg_upgrade_ pg_upgrade_server_ pg_upgrade_ 出現問題：(1) 沒有配置或pg_，檢查日志文件有如下錯誤信息could not connect to old postmaster started with the command:D:/Program Files (x86)/PostgreSQL/9.1/bin/pg_ctl -w -l pg_upgrade_ -D D:/Users/lv/Data/PostgreSQL/9.1 -o -p -b startcannot write to log file pg_upgrade_ (2) PATH環境變量配置錯誤引起的失敗手冊上的示例為：SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.0\bin; 這樣舊數據庫服務器環境變量將會使其路徑優先被搜索到，導致提示該版本下的pg_upgrade不支持升級到新數據庫版本。 (3) 失敗后根據提示重新用initdb生成數據庫時未創建用戶postgres (4) 創建用戶時未創建口令,檢查pg_upgrade_，有如下錯誤信息connection to database failed: fe_sendauth: no password supplied (5) 升級進程未結束再次運行pg_upgrade不成功可以在進程管理中停止所有postgresql進程，然后重新進行升級(6) 升級成功后，日志文件被自動刪除，可以在命令行中用-r參數指定保留。 步驟7：恢復對配置文件（pg_，）進行的修改主要是將鑒權方式修改回比較安全的模式步驟8：升級后處理升級完成后，pg_upgrade會提示需要運行腳本或批處理文件檢查新數據庫服務器及刪除舊數據庫服務器數據。

有用PostgreSQL的嗎，哪個package好用一些

特性MySQLPostgreSQL實例通過執行 MySQL 命令（mysqld）啟動實例。 一個實例可以管理一個或多個數據庫。 一臺服務器可以運行多個 mysqld 實例。 一個實例管理器可以監視 mysqld 的各個實例。 通過執行 Postmaster 進程（pg_ctl）啟動實例。 一個實例可以管理一個或多個數據庫，這些數據庫組成一個集群。 集群是磁盤上的一個區域，這個區域在安裝時初始化并由一個目錄組成，所有數據都存儲在這個目錄中。 使用 initdb 創建第一個數據庫。 一臺機器上可以啟動多個實例。 數據庫數據庫是命名的對象集合，是與實例中的其他數據庫分離的實體。 一個 MySQL 實例中的所有數據庫共享同一個系統編目。 數據庫是命名的對象集合，每個數據庫是與其他數據庫分離的實體。 每個數據庫有自己的系統編目，但是所有數據庫共享 pg_databases。 數據緩沖區通過 innodb_buffer_pool_size 配置參數設置數據緩沖區。 這個參數是內存緩沖區的字節數，InnoDB 使用這個緩沖區來緩存表的數據和索引。 在專用的數據庫服務器上，這個參數最高可以設置為機器物理內存量的 80%。 Shared_buffers 緩存。 在默認情況下分配 64 個緩沖區。 默認的塊大小是 8K。 可以通過設置 文件中的 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 編寫。 系統配置文件數據庫配置客戶機連接文件_ 支持有限的 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的劣勢：MySQLPostgreSQL最重要的引擎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-錯誤。 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、 9.0精通、 解鎖數據庫管理和性能優化的秘密、

上一篇：深入淺出PostgreSQL90適用于初學者和高級用

下一篇：PostgreSQL90開創數據庫新時代的全面指南PO