PostgreSQL 用戶資源管理

PostgreSQL 提供了多種機制來管理和限制用戶對數據庫資源的使用，以下是全面的資源管理方法：

1 連接限制

1.1 限制最大連接數

-- 在 postgresql.conf 中設置
max_connections = 100  -- 全局最大連接數-- 為特定用戶設置連接限制
ALTER ROLE username CONNECTION LIMIT 10;

1.2 空閑連接超時

-- 設置空閑連接自動斷開時間(秒)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '300s';

2 查詢資源限制

2.1 基本查詢限制

-- 設置語句超時(毫秒)
ALTER ROLE username SET statement_timeout = '60s';-- 設置鎖等待超時
ALTER ROLE username SET lock_timeout = '5s';

2.2 高級資源控制 (pg_stat_statements)

-- 啟用擴展
CREATE EXTENSION pg_stat_statements;-- 查看資源消耗最多的查詢
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

3 內存控制

3.1 工作內存限制

-- 設置每個操作的內存限制
ALTER ROLE username SET work_mem = '16MB';-- 設置維護操作的內存限制
ALTER ROLE username SET maintenance_work_mem = '256MB';

3.2 共享緩沖區

-- 在 postgresql.conf 中設置
shared_buffers = 4GB  -- 通常設為系統內存的25%

4 磁盤空間配額

4.1 表空間配額

-- 創建專用表空間
CREATE TABLESPACE user_space OWNER username LOCATION '/path/to/data';-- 設置配額
ALTER USER username SET default_tablespace = 'user_space';

4.2 數據庫大小監控

-- 查看用戶擁有的數據庫對象大小
SELECT pg_size_pretty(pg_total_relation_size(relid)) as size, relname as table
FROM pg_catalog.pg_statio_user_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(relid) DESC;

5 使用資源組 (PostgreSQL 12+)

-- 創建資源組
CREATE RESOURCE GROUP user_groupWITH (cpu_rate_limit=30, memory_limit=30);-- 將用戶分配到資源組
ALTER ROLE username SET resource_group = 'user_group';

6 基于擴展的精細控制

6.1 pg_qualstats 監控謂詞使用

CREATE EXTENSION pg_qualstats;-- 查看最常使用的謂詞
SELECT * FROM pg_qualstats ORDER BY execution_count DESC;

6.2 pg_hint_plan 控制執行計劃

-- 強制使用特定索引
/*+ IndexScan(table_name index_name) */
SELECT * FROM table_name WHERE condition;

7 審計與監控

7.1 啟用審計日志

-- 在 postgresql.conf 中設置
log_statement = 'all'  -- 記錄所有語句
log_duration = on
log_line_prefix = '%m [%p] %u@%d '

7.2 使用 pgBadger 分析日志

pgbadger /var/log/postgresql/postgresql-*.log -o report.html

8 最佳實踐建議

1. 分層管理：

   • 為不同業務創建不同用戶
   • 按業務重要性分配資源

2. 定期審查：

   -- 查看用戶資源設置
   SELECT rolname, rolconnlimit, rolconfig 
   FROM pg_roles 
   WHERE rolconfig IS NOT NULL;
   

3. 自動化監控：

   • 設置警報閾值
   • 使用 Prometheus + Grafana 監控

4. 資源隔離：

   • 重要業務使用專用實例
   • 使用連接池管理連接

5. 文檔記錄：

   • 記錄資源分配策略
   • 維護變更日志