PostgreSQL 日常維護：REINDEX、pg_repack、Bloat 偵測與健檢腳本 | PostgreSQL

-- 安裝 pgstattuple 擴充
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查詢索引碎片狀況
SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  (pgstatindex(indexrelid::regclass)).avg_leaf_density,
  (pgstatindex(indexrelid::regclass)).leaf_fragmentation,
  (pgstatindex(indexrelid::regclass)).tree_level
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 判斷標準：
-- leaf_fragmentation > 30% → 考慮 REINDEX
-- avg_leaf_density < 50%   → 頁面使用率過低
-- tree_level >= 4          → 索引過深

-- 重建單一索引（CONCURRENTLY 不鎖表）
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- 重建表上所有索引
REINDEX TABLE CONCURRENTLY orders;

-- 重建整個 Schema 的所有索引（PG14+）
REINDEX SCHEMA CONCURRENTLY public;

-- 確認 INVALID 索引（失敗後需清理）
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE indexname IN (
  SELECT relname FROM pg_class
  JOIN pg_index ON pg_class.oid = pg_index.indexrelid
  WHERE NOT pg_index.indisvalid
);

# Ubuntu / Debian
apt-get install postgresql-17-repack

# macOS
brew install pg_repack

# 在目標資料庫建立擴充
psql -d mydb -c "CREATE EXTENSION pg_repack;"

# 重組單張表（線上，不鎖定）
pg_repack --host=localhost --dbname=mydb \
  --username=postgres --table=orders

# 重組整個資料庫
pg_repack --host=localhost --dbname=mydb --username=postgres

# 只重組索引（不重組表本身）
pg_repack --host=localhost --dbname=mydb \
  --username=postgres --table=orders --only-indexes

# 搭配鎖等待超時
pg_repack --host=localhost --dbname=mydb \
  --username=postgres --table=orders --wait-timeout=60

# 乾跑模式（只顯示計畫）
pg_repack --host=localhost --dbname=mydb \
  --username=postgres --table=orders --dry-run

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 快速近似 Bloat（大表推薦，PG9.5+）
SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid)) AS table_size,
  ROUND((dead_tuple_percent + free_percent)::numeric, 2) AS bloat_pct
FROM pgstattuple_approx(oid)
JOIN pg_class ON true
WHERE relname = 'orders';

-- 基於系統目錄估算表 Bloat（速度快，不需擴充）
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  n_dead_tup,
  n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND n_dead_tup > 1000
ORDER BY dead_pct DESC
LIMIT 15;

-- 使用 pgstatindex 偵測索引 Bloat
SELECT
  schemaname,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  ROUND((pgstatindex(indexrelid)).leaf_fragmentation::numeric, 1) AS frag_pct,
  ROUND((pgstatindex(indexrelid)).avg_leaf_density::numeric, 1) AS fill_pct
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND pg_relation_size(indexrelid) > 10 * 1024 * 1024
ORDER BY frag_pct DESC
LIMIT 20;

-- 確認表的最後 ANALYZE 時間
SELECT
  schemaname, tablename,
  n_live_tup, n_dead_tup,
  n_mod_since_analyze,
  last_autoanalyze, last_analyze,
  ROUND(n_mod_since_analyze::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS mod_pct
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_mod_since_analyze DESC;

-- 需要手動 ANALYZE 的情況：
-- 1. 大量載入資料後（COPY、批次 INSERT）
-- 2. autovacuum 來不及追趕變動速率
-- 3. 執行計畫突然變差

-- 手動 ANALYZE（不鎖表）
ANALYZE orders;

-- ANALYZE 特定欄位
ANALYZE orders (user_id, created_at);

-- 調高特定欄位的統計目標（用於基數很高的欄位）
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE orders (user_id);

-- 降低統計目標（用於固定值域的欄位）
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 50;

-- 查看統計資訊的實際內容
SELECT attname, n_distinct, correlation,
  most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- 查看分割區列表與大小
SELECT
  parent.relname AS parent_table,
  child.relname AS partition_name,
  pg_size_pretty(pg_relation_size(child.oid)) AS partition_size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

-- 對特定分割區執行 VACUUM ANALYZE
VACUUM ANALYZE orders_2024_01;

-- 對特定分割區執行 REINDEX
REINDEX TABLE CONCURRENTLY orders_2024_01;

-- 舊分割區可以直接 DETACH，消除 Bloat 問題
ALTER TABLE orders DETACH PARTITION orders_2022;
DROP TABLE orders_2022;

-- 將舊資料批次移至歸檔表
DO $$
DECLARE
  v_batch_size INT := 10000;
  v_deleted INT;
BEGIN
  LOOP
    WITH moved AS (
      DELETE FROM orders
      WHERE created_at < NOW() - INTERVAL '2 years'
        AND id IN (
          SELECT id FROM orders
          WHERE created_at < NOW() - INTERVAL '2 years'
          LIMIT v_batch_size
          FOR UPDATE SKIP LOCKED
        )
      RETURNING *
    )
    INSERT INTO orders_archive SELECT * FROM moved;

    GET DIAGNOSTICS v_deleted = ROW_COUNT;
    EXIT WHEN v_deleted = 0;
    PERFORM pg_sleep(0.1);  -- 避免過度佔用 I/O
  END LOOP;
END $$;

# postgresql.conf — 日誌收集器設定
logging_collector = on
log_directory = 'log'

# 每天一個檔案，7 個檔案循環
log_filename = 'postgresql-%a.log'  -- %a = 星期縮寫
log_rotation_age = 1d
log_rotation_size = 500MB           -- 超過 500MB 立即輪替
log_truncate_on_rotation = on       -- 覆寫 7 天前的同名檔案

# /etc/logrotate.d/postgresql
/var/log/postgresql/*.log {
    daily
    rotate 14          # 保留 14 天
    compress           # gzip 壓縮舊日誌
    delaycompress      # 最新的舊日誌不壓縮
    missingok
    notifempty
    create 0640 postgres postgres
    sharedscripts
    postrotate
        /usr/bin/pg_ctlcluster 17 main reload
    endscript
}

# postgresql.conf
idle_in_transaction_session_timeout = '10min'  -- 閒置 Transaction 逾時
idle_session_timeout = '30min'                 -- 閒置連線逾時（PG14+）
statement_timeout = '5min'                     -- 查詢執行逾時
lock_timeout = '30s'                           -- 鎖等待逾時

-- 找出閒置超過 30 分鐘的連線
SELECT pid, usename, state, state_change, LEFT(query, 100)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND state_change < NOW() - INTERVAL '30 minutes'
  AND pid <> pg_backend_pid();

-- 終止殭屍連線
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '30 minutes'
  AND pid <> pg_backend_pid();

-- pg_cancel_backend  → 取消查詢但保留連線（SIGINT）
-- pg_terminate_backend → 終止整個後端程序（SIGTERM）

-- 1. 確認 autovacuum 運作
SELECT schemaname, tablename, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC LIMIT 10;

-- 2. 確認複製延遲
SELECT client_addr, application_name,
  ROUND(EXTRACT(EPOCH FROM replay_lag), 1) AS replay_lag_seconds
FROM pg_stat_replication;

-- 3. Long-running Query（超過 5 分鐘）
SELECT pid, usename,
  ROUND(EXTRACT(EPOCH FROM (NOW() - query_start)) / 60, 1) AS run_minutes,
  LEFT(query, 120) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes'
  AND pid <> pg_backend_pid();

-- 4. Transaction ID Wraparound 風險
SELECT datname,
  age(datfrozenxid) AS xid_age,
  2000000000 - age(datfrozenxid) AS xid_remaining
FROM pg_database WHERE datallowconn
ORDER BY xid_age DESC;
-- 警告閾值：xid_age > 1,500,000,000

#!/bin/bash
# /opt/scripts/pg_daily_maintenance.sh
# crontab: 0 2 * * * /opt/scripts/pg_daily_maintenance.sh

set -euo pipefail
DB="${PG_DB:-mydb}"
HOST="${PG_HOST:-localhost}"
USER="${PG_USER:-postgres}"
LOG="/var/log/pg_maintenance/$(date '+%Y-%m-%d').log"

mkdir -p /var/log/pg_maintenance

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG"; }

log "=== 開始日常維護 ==="

# 1. ANALYZE 變動量大的表
TABLES=$(psql -h "$HOST" -U "$USER" -d "$DB" -t -A -c "
  SELECT schemaname || '.' || tablename
  FROM pg_stat_user_tables
  WHERE n_mod_since_analyze > 100000
    AND (last_autoanalyze IS NULL OR last_autoanalyze < NOW() - INTERVAL '2 hours')
  LIMIT 10;
")

for T in $TABLES; do
  log "ANALYZE $T"
  psql -h "$HOST" -U "$USER" -d "$DB" -c "ANALYZE $T;" || log "WARNING: ANALYZE $T 失敗"
done

# 2. 清理殭屍連線
KILLED=$(psql -h "$HOST" -U "$USER" -d "$DB" -t -A -c "
  SELECT COUNT(*) FROM (
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
      AND state_change < NOW() - INTERVAL '30 minutes'
      AND pid <> pg_backend_pid()
  ) t;
")
log "終止殭屍連線數: $KILLED"

log "=== 日常維護完成 ==="

#!/bin/bash
# /opt/scripts/pg_weekly_repack.sh
# crontab: 0 3 * * 0 /opt/scripts/pg_weekly_repack.sh

DB="${PG_DB:-mydb}"
HOST="${PG_HOST:-localhost}"

BLOAT_TABLES=$(psql -h "$HOST" -U postgres -d "$DB" -t -A -c "
  SELECT tablename FROM pg_stat_user_tables
  WHERE schemaname = 'public'
    AND pg_total_relation_size(schemaname||'.'||tablename) > 500 * 1024 * 1024
    AND ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) > 30
  ORDER BY n_dead_tup DESC;
" 2>/dev/null)

for TABLE in $BLOAT_TABLES; do
  echo "重組表: $TABLE"
  pg_repack --host="$HOST" --dbname="$DB" --username=postgres \
    --table="$TABLE" --wait-timeout=120 || echo "WARNING: $TABLE 重組失敗"
done