開發營運建議 - ApsaraDB RDS

本文介紹RDS PostgreSQL的一些開發營運建議，協助您有效提升資料庫使用的規範性和穩定性，保持高效能。

用戶端串連池

使用綁定變數（PreparedStatement），消除資料庫SQL硬解析CPU消耗，對效能提升巨大。
減少idle connection數量。可以降低RDS PostgreSQL串連記憶體佔用，並提升GetSnapshotData()效率，提升效能。
使用應用串連池，避免短串連損耗和效能下降。如果應用沒有串連池，建議在應用程式層和資料庫之間架設串連池，例如使用PgBouncer或者Pgpool-II作為串連池。
應用串連池參數的配置建議：
- minimumIdle=1：控制串連池空閑串連的最小數量，減少idle connection。
  說明
  當前大多數串連池已取消了maxIdle參數，如果有，maxIdle參數設定為1。
- maxLifetime：建議設定為60分鐘，用來設定一個connection在串連池中的最大存活時間。可以極大降低串連Relcache記憶體導致OOM的機率。
- maximumPoolSize=15：單個串連池執行個體允許的最大串連數，15已經滿足絕大多數應用需求。如果串連池用戶端個數較少，負載集中，可以適當增大這些用戶端的maximumPoolSize。
說明
常見應用串連池參數的配置建議如下：
- HikariCP（Java，推薦的串連池）:
  minimumIdle=1, maximumPoolSize=15, idleTimeout=600000 (10 minutes), maxLifetime=3600000 (60 minutes)
- GORM（golang，推薦的串連池）:
  sqlDB.SetMaxIdleConns(1), sqlDB.SetMaxOpenConns(15), sqlDB.SetConnMaxLifetime(time.Hour)
- Druid（Java）：
  initialSize=1, minIdle=1, maxIdle=1, maxActive=15, testOnBorrow=false, testOnReturn=false, testWhileIdle=true,minEvictableIdleTimeMillis=600000 (10 minutes), maxEvictableIdleTimeMillis=900000 (15 minutes), timeBetweenEvictionRunsMillis=60000 (1 minutes), maxWait=6000 (6 seconds).
上述配置不涉及PreparedStatement配置，PreparedStatement需要另外配置。

效能與穩定性

在RDS PostgreSQL中，單個資料庫（DB）對應底層檔案系統中的一個檔案夾，資料庫中的表、分區、索引對應檔案夾中的檔案，如果資料庫對應檔案夾中的檔案數量超過2000萬，將可能出現磁碟空間滿的報錯。建議適當拆分資料庫或合并表檔案。
建立索引，對於線上業務，推薦使用CREATE INDEX CONCURRENTLY方式建立索引，不堵塞其他會話對被建立索引表的DML（INSERT、UPDATE或DELETE）操作。
重建索引，對PostgreSQL 12及以上大版本，使用REINDEX CONCURRENTLY。PostgreSQL 11及下大版本，使用CONCURRENTLY建立新索引成功後，再刪除老索引。
避免頻繁建立和刪除暫存資料表，以減少系統資料表資源的消耗。特別是ON COMMIT DROP，務必慎用。通常暫存資料表功能，可以使用WITH語句替代。
PostgreSQL13在分區表、HashAggregate(group by)、並行查詢等有較大的最佳化，建議升級PostgreSQL大版本。具體請參見RDS PostgreSQL升級資料庫大版本。
遊標使用後及時關閉。
使用TRUNCATE代替DELETE全表，提升效能。
PostgreSQL支援DDL事務，支援復原DDL，建議將DDL封裝在事務中執行，必要時可以復原，但是需要注意事務的長度，避免長時間堵塞DDL對象的讀操作。
如果有大批量的資料入庫，建議使用copy文法，或者INSERT INTO table VALUES (),(),...();的方式，提高寫入速度。

執行個體小版本建議

使用Replication Slot（例如邏輯訂閱），強烈建議升級小版本到20201230及以上，以開啟邏輯複製槽容錯移轉，同時設定最大複製槽延遲警示，以防止訂閱延遲或中斷造成Slot位點無法推進，進而造成PostgreSQL資料庫wal堆積。
開啟審計日誌或者效能洞察（log_statement=all），強烈建議升級小版本到20211031及以上。
說明
log_statement=all在高並發情境（active_conn > 50）下，提升效能約4倍，並且消除了log_statement=all導致的SysCpu飆升問題。

監控警示

強烈建議開啟RDS PostgreSQLCloudMonitor模板的警示項，參見管理警示。
您可以根據業務情況自行設定記憶體使用量率警示閾值，建議設定在85%到95%之間。

問題排查

尋找最耗費資源的SQL（Top SQL）：請參見尋找最耗費資源的 SQL（Top SQL）。

設計

許可權

許可權管理，以schema/role為單位分配許可權，建立readwrite/readonly帳號，遵循最小許可權原則，請參見RDS PostgreSQL許可權管理最佳實務。
若使用應⽤層讀寫分離，readonly用戶端建議使⽤readonly帳號，最小許可權原則，避免許可權誤用。

表

表結構中欄位定義的資料類型建議與應用程式中的定義保持一致，表之間欄位校對規則一致，避免報錯或無法使用索引的情況發生。
對於存在定期歷史資料刪除需求的業務，建議資料表按時間分區，按時間月或年區分，刪除時使用DROP或者TRUNCATE操作對應的子表，不建議使用DELETE操作。
對於頻繁更新的表，建議在建表時指定表的FILLFACTOR=85，每頁預留15%的空間用於HOT更新。
```
CREATE TABLE test123(id int, info text) WITH(FILLFACTOR=85);  
```
暫存資料表建議以tmp_開頭，子表建議根據業務情境以規則結尾，例如按年分區的主表如果為tbl，則子表為tbl_2016、tbl_2017等。

索引

B-Tree索引欄位至多2000位元組，如果存在超過2000位元組的欄位需要建立索引，建議使用函數索引（例如雜湊值索引）或分詞索引。
對於線性順序儲存的資料（如流式資料、時間欄位或自增欄位），通常查詢時使用範圍查詢，建議使用BRIN索引，減少索引的大小，加快資料插入速度。
```
CREATE INDEX idx ON tbl using BRIN(id);
```
建議避免全表掃描（巨量資料量掃描的資料分析除外），PostgreSQL支援幾乎所有資料類型的索引。
索引介面包括：B-Tree、Hash、GIN、GiST、SP-GiST、BRIN、RUM（擴充介面）、Bloom（擴充介面）、PASE（擴充介面）。
主鍵索引建議以pk_ 開頭，唯一索引建議以uk_開頭，普通索引建議以idx_開頭。

資料類型及字元集

建議選擇合適的資料類型，目標資料為數字時不建議使用字串，目標資料可以存為樹類型時不建議使用字串。
使用合理的資料類型，可以提高資料的查詢效率。
PostgreSQL支援的資料類型如下：精確的數字類型、浮點、貨幣、字串、字元、位元組流、日期、時間、布爾、枚舉、幾何、網路地址、位元流、文本、UUID、XML、JSON、數組、複合類型、範圍類型、對象、行號、大對象、ltree樹結構類型、cube多維類型、earth地球類型、hstore類型、pg_trgm相似類型、PostGIS（點、線段、面、路徑、經緯度、raster、拓撲等）、HyperLogLog（近似估值統計分析）。
字串定序LC_COLLATE推薦使用 'C'，而非 UTF8。LC_COLLATE=UTF8 效能相對差一些，並且索引需要明確指定UTF8 pattern ops，才能支援LIKE查詢。

預存程序

如果商務邏輯冗長，建議減少資料庫和程式之間的互動次數，使用資料庫預存程序（如 PL/pgSQL）或內建函數。PostgreSQL內建的PL/pgSQL函數語言提供處理複雜商務邏輯的功能。PostgreSQL還內建了分析函數、彙總函式、視窗函數、普通類型函數、複雜類型函數、數學函數和幾何函數等多種函數。

資料查詢

不建議使用COUNT(列名)或COUNT(常量)來替代COUNT(*)，COUNT(*)是SQL92定義的標準統計行數的文法，會統計NULL值（真實行數），而COUNT(列名)不會統計。
使用COUNT(多列列名)時，多列列名必須使用括弧，例如COUNT( (col1,col2,col3) )。注意使用COUNT(多列列名)時，所有NULL行都會被計數，所以效果與COUNT(*)一致。
不建議使用SELECT * FROM t，用具體的欄位列表代替*，避免返回用不到的欄位。
除ETL（Extract-Transform-Load）程式外，建議避免向用戶端返回巨量資料量，若資料量過大，應該考慮相應需求是否合理。
對於需要範圍查詢的情境，建議使用範圍類型以及GiST索引，提高範圍檢索的查詢效能。
如果應用經常訪問較大結果集的資料（例如100條），建議將資料彙總成1條，例如經常要按ID訪問此ID的資料，建議定期按ID彙總資料，查詢時返回的記錄數越少響應越快。

管理

建議開啟SQL洞察和審計，該功能支援查詢並匯出SQL語句執行歷史及其對應的各種資訊（資料庫、狀態、執行時間等），對SQL進行健康情況診斷、效能問題排查、業務流量分析等。更多資訊，請參見SQL洞察和審計。
如果您需要監控並記錄阿里雲帳號的活動，包括通過阿里雲控制台、OpenAPI、開發人員工具對雲上產品和服務的訪問和使用行為，建議使用Action Trail功能。您還可以將這些行為事件下載或儲存到Log Service或OSS儲存空間，然後進行行為分析、安全分析、資源變更行為追蹤和行為合規性審計等操作。更多資訊，請參見什麼是Action Trail。
DDL操作之前務必要經過評審，並且選擇在低峰期執行。
刪除和修改記錄時，為避免誤刪除，建議先使用SELECT確認後，再提交執行。如果商務邏輯上確定只更改1行，則添加LIMIT 1。
DDL操作（以及類似的可能擷取鎖的操作，例如VACUUM FULL、CREATE INDEX等）建議設定鎖等待，用於防止堵塞所有與該DDL鎖對象相關的查詢。
```
begin;  
SET local lock_timeout = '10s';  
-- DDL query;  
end;
```
EXPLAIN (ANALYZE) 語句的工作方式類似於EXPLAIN，主要區別在於前者會實際執行SQL。如果SQL涉及資料變更，即DML SQL（UPDATE、INSERT或DELETE），務必在事務中執行EXPLAIN (ANALYZE)，查看完成後再進行復原。
```
begin;  
EXPLAIN (ANALYZE) <DML(UPDATE/INSERT/DELETE) SQL>; 
rollback;
```
大大量刪除和更新資料時，建議分批次操作，不建議在一個事務中完成，以免一次產生較多垃圾。