iDempiere 效能優化與 Partition Table：DBA 完全攻略

iDempiere 運行久了，資料量增長會影響效能。本文從 DBA 角度分析效能優化策略，包含 Partition Table、索引設計、慢查詢分析與備份計畫。

一、iDempiere 資料庫特性

表數量眾多

iDempiere 有數百張表，主要分類：

• AD_* 表：Application Dictionary，存系統定義
• C_* 表：Commercial（銷售、客戶、訂單）
• M_* 表：Material（產品、庫存、倉庫）
• A_* 表：Asset（資產）
• Fact_Acct：會計分錄（成長最快）

快速成長的表

二、Partition Table

iDempiere 原生支援 Partition Table，可從 UI 操作！

查詢已設定分區的表

SELECT TableName, IsPartition 
FROM AD_Table 
WHERE IsPartition = 'Y';

適合分區的表

• AD_PInstance：依 Created 日期分區
• Fact_Acct：依 DateAcct 分區
• AD_ChangeLog：依 Created 分區

分區優點

• 查詢只掃描相關分區，效能提升
• 可獨立備份/刪除舊分區
• 維護操作（VACUUM）更快

三、索引策略

常用查詢欄位建索引

-- 訂單表常用查詢
CREATE INDEX idx_c_order_bpartner ON C_Order(C_BPartner_ID);
CREATE INDEX idx_c_order_docstatus ON C_Order(DocStatus);
CREATE INDEX idx_c_order_date ON C_Order(DateOrdered);

-- 複合索引
CREATE INDEX idx_c_order_multi 
ON C_Order(AD_Client_ID, AD_Org_ID, DocStatus, DateOrdered);

檢查缺少索引的 Foreign Key

SELECT 
    tc.table_name, 
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
    SELECT 1 FROM pg_indexes 
    WHERE tablename = tc.table_name 
    AND indexdef LIKE '%' || kcu.column_name || '%'
);

四、慢查詢分析

啟用 pg_stat_statements

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- 查詢最慢的 SQL
SELECT 
    query,
    calls,
    total_time / calls as avg_time,
    rows / calls as avg_rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

常見效能問題

五、Stored Procedure 設計

SP 參數取得（透過 PInstance）

CREATE OR REPLACE FUNCTION my_process(pinstance numeric)
RETURNS void AS $$
DECLARE
    p_Record_ID NUMERIC;
BEGIN
    -- 取得參數
    SELECT pi.record_id INTO p_Record_ID
    FROM ad_pinstance pi
    WHERE pi.ad_pinstance_id = pinstance;
    
    -- 執行業務邏輯
    UPDATE my_table SET processed = 'Y'
    WHERE my_table_id = p_Record_ID;
    
    -- 回寫成功狀態
    UPDATE ad_pinstance
    SET isprocessing = 'N',
        result = 1,
        errormsg = 'OK',
        updated = now()
    WHERE ad_pinstance_id = pinstance;
    
EXCEPTION WHEN OTHERS THEN
    -- 錯誤處理
    UPDATE ad_pinstance
    SET isprocessing = 'N',
        result = 0,
        errormsg = SQLERRM,
        updated = now()
    WHERE ad_pinstance_id = pinstance;
END;
$$ LANGUAGE plpgsql;

六、備份策略

完整備份

pg_dump -h localhost -U adempiere idempiere > backup_full.sql

分區表備份

# 只備份特定分區
pg_dump -t 'fact_acct_2024*' idempiere > backup_fact_2024.sql

冷熱資料分離

• 熱資料：當年度，保留在主資料庫
• 冷資料：歷史年度，備份後可刪除或移至歸檔庫

七、監控建議

• 定期檢查表大小：SELECT pg_size_pretty(pg_total_relation_size('table_name'))
• 監控連線數：SELECT count(*) FROM pg_stat_activity
• 設定慢查詢 log：log_min_duration_statement = 1000（1秒以上）
• 定期 VACUUM ANALYZE

常見問題 FAQ

Q1: AD_PInstance 表太大可以清嗎？

可以清除舊資料，但建議保留近期的（如 30 天內），方便追蹤 Process 執行記錄。

Q2: 如何知道哪些表最大？

SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::text))
FROM pg_tables WHERE schemaname = 'adempiere'
ORDER BY pg_total_relation_size(tablename::text) DESC
LIMIT 20;

Q3: Partition Table 可以從 UI 設定嗎？

可以！在 Table and Column 視窗勾選 IsPartition，並設定分區欄位。

Q4: 多久做一次 VACUUM？

PostgreSQL 預設有 autovacuum，但高負載系統建議每天手動執行 VACUUM ANALYZE。

Q5: 備份要包含 AD_Attachment 嗎？

這張表存上傳檔案（BLOB），可能很大。建議評估是否將附件移到外部儲存（如 S3），減輕資料庫負擔。