PostgreSQL初體驗及其與MySQL的對比

因為工作的原因接觸到了pgsql資料庫，對PostgreSQL的體系和運維操作也有了一定的瞭解。PostgreSQL在官網上標稱為世界上最先進的開源資料庫，而MySQL在官網上標稱的是世界上最流行的開源資料庫，可見PostgresSQL還是比較高調的。

一、PostgreSQL初體驗

首先是資料庫的安裝，PostgreSQL官網上不像MySQL那樣提供了二進位包的下載，PostgreSQL主要提供了RPM包下載和源碼下載，通常使用源碼編譯安裝，安裝步驟相對比較簡單：

######postgres單實例安裝
1、官網下載源碼包：https://www.postgresql.org/ftp/source/v14.8/

2、解壓
tar -xvf postgresql-14.0.tar.gz

3、新建postgres用戶
groupadd postgres
useradd -g postgres postgres

4、安裝依賴包
yum install *zlib*
yum install *libreadline*

5、編譯安裝
./configure
make && make install

6、修改安裝目錄所屬用戶組
chown -R postgres:postgres /usr/local/pgsql

7、新建postgresql的數據目錄
mkdir /pgdata
chown postgres:postgres /pgdata

8、配置環境變數
su - postgres
vi ~/.bash_profile
export PATH=$PATH:/usr/local/pgsql/bin

9、初始化資料庫
initdb -D /pgdata

10、啟動資料庫
pg_ctl -D /pgdata start

11、驗證是否可登錄
psql

安裝完成後，會自動在數據目錄下麵生成配置文件，根據實際情況首先需要修改配置文件postgresql.conf和訪問控制文件pg_hba.conf。修改完後通過pg_ctl命令重啟PG。

#####配置文件postgresql.conf
#connection control
listen_addresses = '*'  #不限制連接ip
max_connections = 1000
superuser_reserved_connections = 10 #為超級用戶保留的連接數

#memory management      
shared_buffers = 512MB    #推薦操作系統物理記憶體的1/4                          
work_mem = 8MB        #單個查詢操作(例如排序或哈希表)可使用的最大記憶體                 
maintenance_work_mem = 512MB       #維護性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的最大的記憶體  
max_files_per_process = 24800           
effective_cache_size = 1GB   #推薦操作系統物理記憶體的1/2

#log optimization
log_destination = 'csvlog'             
logging_collector = on          
log_directory = '/pgdata/logs'        # 日誌存放路徑，提前規劃在系統上創建好 
log_truncate_on_rotation = on       


#####訪問控制文件pg_hba.conf加上下麵這行
host    all             all             0.0.0.0/0               md5  

PostgreSQL通過WAL日誌進行主從同步，不同於MySQL通過binlog進行邏輯複製。並且PostgreSQL 在9.x之後引入了主從的流複製機制，所謂流複製，就是備伺服器通過tcp流從主伺服器中同步相應的數據，主伺服器在WAL記錄產生時即將它們以流式傳送給備伺服器，而不必等到WAL文件被填充。主從複製搭建的具體步驟可以參考如下：

#####主從同步配置
主庫創建同步賬號
CREATE ROLE replica login replication encrypted password 'Temp##2022';

主庫修改pg_hba.conf增加從庫訪問控制
host    replication     replica         10.2.111.192/32         md5

主庫重啟
pg_ctl -D /pgdata restart

停止從庫
pg_ctl stop -D /pgdata

清空從庫數據文件
rm -rf  /pgdata/*

從庫拉取主庫數據文件
pg_basebackup -h 10.2.111.192 -D /pgdata -p 5432 -U replica -Fp -Xs -Pv -R --checkpoint=fast

從庫postgresql.conf文件添加主庫信息
primary_conninfo = 'host=10.2.111.193 port=5432 user=replica password=Temp##2022'

啟動從庫
pg_ctl start -D /pgdata

主庫驗證主從同步正常
select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication;

備庫提升為主庫
pg_ctl promote -D /pgdata
pg_controldata -D /pgdata | grep cluster  #檢查資料庫狀態，為in production，說明備庫已提升為主庫

在PostgreSQL的資料庫邏輯存儲架構中，採用的是database-schema-table這樣一個三層的架構，和SQLServer一樣，SQLServer預設的模式是dbo，PostgresSQL中預設的模式是public。其實大多數應用中，database-table這樣兩層的架構足夠了，三層架構感覺還是複雜了一些。每個database下麵有兩個預設的系統schema：pg_catalog和information_schema，pg_catalog下麵的表主要描述的是pg實例的配置信息，information_schema下麵的表主要描述的當前database的數據字典信息。比如要查詢當前database下麵所有的表可以通過information_schema.tables表查詢。在用戶管理方面，PostgreSQL中角色的概念影響較深，用戶即角色，創建角色的時候指定login屬性即代表創建同名的用戶。

二、PostgreSQL與MySQL對比

1. 開源協議

PostgreSQL採用的是寬鬆的BSD開源協議，基於開源PostgreSQL代碼封裝成的軟體可以不公開源代碼，它也不強制任何特定的版權聲明，這使得它與許多其他開源和專有許可證相容。基於這一點，很多國產資料庫廠商採用了基於開源PG二次開發的資料庫選型方案，華為的opengauss就是基於PG9版本，而vastbase、mogdb又是基於opengauss，也可以認為是PostgreSQL系列的產品。

MySQL採用的是較為嚴格的GPLv2開源協議，該協議具有強傳染性，這意味著任何基於GPLv2 許可的代碼進行修改或擴展，並且要分發的派生作品，也必須在GPLv2開源協議下發佈，長期來看，具有傳染性的GPLv2開源協議更能把成果回饋社區，帶動社區的發展。國內基於MySQL的幾款資料庫TDSQL、GoldenDB在目前的國內的國產資料庫份額中占有相當一部分比例，特別是在銀行業。但是好像從來沒有見過他們的開源版本，這個要較真起來很可能是違反開源協議的。

2. 表組織形式

PostgreSQL底層的表組織形式採用的是堆表（heap table），在堆表中數據的按數據插入的順序進行排序，索引指向堆中行的指針（CTID），而不是實際的行數據。MySQL底層的表組織形式採用的是索引組織表（IOT），索引組織表中數據按主鍵或唯一索引進行排序，數據存儲在主鍵索引的葉子節點中。對於基於主鍵索引查詢的SQL語句，索引組織表不需要回表，性能更佳。

可能大家覺得堆表對於寫入的性能會更高效，畢竟堆表中數據可以迅速地添加到表的末尾，不需要重新排序或調整數據，不需要像IOT那樣頻繁地對數據頁進行合併或分裂來維護B+樹結構，但其實生產環境中一個表可能會有多個索引，對於PostgreSQL的B+樹索引的維護同樣會帶來很多開銷。所以那種表組織形式更好還需要看業務場景，通常來說索引組織表更適合於OLPT場景，堆表在OLAP場景中表現更好。

3. MVCC實現機制

MVCC實現機制和更新方式是一個問題，PostgreSQL採用的是異地更新（out-of-place update），它沒有undo表空間，PostgreSQL將歷史元組和最新元組都保存在Heap表中，這種方式的好處是無須做回滾操作，因此PostgreSQL的堆表需要存儲多個行版本數據。但是，假設事務不停地更新數據，那麼一條元組就會產生大量的歷史版本。其他事務在訪問時需要查看這些元組是否滿足可見性要求，這會增加讀操作的時延，降低數據掃描的效率。為了防止數據膨脹，PostgreSQL資料庫採用Vacuum機制清理表中的無效元組，PostgreSQL預設會打開auto vacuum機制。

MySQL、ORACLE採用的都是原地更新（in-place update），如果事務更新了一條元組，它可以“原地”更新這條元組，歷史元組會以Undo日誌記錄的形式保存到回滾段中，這樣就實現了元組的原地更新（Inplace Update）。當有併發事務需要訪問歷史元組時，可以從回滾段中“回滾”出這條元組，如果事務異常終止，則可以利用Undo日誌將數據恢復。當所有可能訪問歷史元組的事務全部結束後，Undo日誌中的歷史元組就可以被清理。由於Undo日誌被集中存儲到某一個回滾段，所以清理也較為便捷。

4. 多進程VS多線程

PostgreSQL採用的是多進程架構。優點主要在穩定性方面：在於每個連接都有自己的進程，一個進程崩潰不太會影響其他的進程，並且每個進程都有自己的記憶體空間，這可以減少記憶體泄漏或其他問題對整個系統的影響；缺點在於資源消耗更高：由於每個進程都有自己的記憶體空間，這可能導致更高的記憶體使用，並且進程間的上下文切換和進程間的通信開銷更大。

MySQL採用的是多線程架構。優點在於資源消耗更低：線程共用相同的記憶體空間，這通常導致更低的記憶體使用和更快的上下文切換。並且多線程可以更好的適用多核CPU架構處理高併發問題。多線程架構在穩定性方面不如多進程，一個線程的問題可能會影響到同一進程中的其他線程。