PostgreSQL 實用擴充精選：pg_cron、pg_repack 與 pg_hint_plan | PostgreSQL

# postgresql.conf（需重啟）
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'           # 元資料存放資料庫
cron.use_background_workers = on          # 使用 Background Worker 執行
cron.max_running_jobs = 32                # 同時執行的最大任務數
cron.log_run = on                         # 記錄執行結果

CREATE EXTENSION pg_cron;

-- 定期 VACUUM ANALYZE（每天凌晨 3 點）
SELECT cron.schedule(
    'nightly-vacuum',
    '0 3 * * *',
    'VACUUM ANALYZE orders'
);

-- 清理過期資料（每小時清理 30 天前的日誌）
SELECT cron.schedule(
    'cleanup-old-logs',
    '0 * * * *',
    $$DELETE FROM app_logs
      WHERE created_at < now() - interval '30 days'$$
);

-- 刷新 Materialized View（每 15 分鐘）
SELECT cron.schedule(
    'refresh-dashboard-mv',
    '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats'
);

-- 定期重置 pg_stat_statements（每月 1 日）
SELECT cron.schedule(
    'monthly-reset-stats',
    '0 0 1 * *',
    'SELECT pg_stat_statements_reset()'
);

-- 在其他資料庫中執行任務（pg_cron 1.4+ / PG14+）
SELECT cron.schedule_in_database(
    'vacuum-app-db',
    '0 4 * * *',
    'VACUUM ANALYZE users',
    'app_database'
);

-- 查看所有排程任務
SELECT jobid, schedule, command, database, username, active
FROM cron.job ORDER BY jobid;

-- 查看任務執行歷史
SELECT jobid, status, return_message,
       start_time, end_time,
       end_time - start_time AS duration
FROM cron.job_run_details
ORDER BY start_time DESC LIMIT 20;

-- 停用任務（不刪除）
UPDATE cron.job SET active = false WHERE jobname = 'nightly-vacuum';

-- 刪除任務
SELECT cron.unschedule('nightly-vacuum');

# Debian/Ubuntu
sudo apt-get install postgresql-17-repack
# RHEL/CentOS
sudo yum install pg_repack_17

CREATE EXTENSION pg_repack;

# 重組整張表（含所有索引）
pg_repack -d mydb -t orders

# 只重組特定索引
pg_repack -d mydb --index idx_orders_customer_id

# 重組資料庫中所有表
pg_repack -d mydb --all-tables

# 並行處理（pg_repack 1.4.7+）
pg_repack -d mydb -t orders --jobs 4

# Dry run（只顯示將執行的操作）
pg_repack -d mydb -t orders --dry-run

# 只重建索引
pg_repack -d mydb -t orders --only-indexes

# postgresql.conf（需重啟）
shared_preload_libraries = 'pg_hint_plan'
pg_hint_plan.enable_hint = on
pg_hint_plan.debug_print = off

CREATE EXTENSION pg_hint_plan;

-- 強制使用 Index Scan
/*+ IndexScan(orders idx_orders_customer_id) */
SELECT * FROM orders WHERE customer_id = 123;

-- 強制使用 Seq Scan
/*+ SeqScan(orders) */
SELECT * FROM orders WHERE customer_id = 123;

-- 強制特定 Join 方式
/*+ HashJoin(orders customers) */
SELECT o.*, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id;

-- 組合多個 Hint
/*+ IndexScan(o idx_orders_date) NestLoop(o c) Leading(o c) */
SELECT o.*, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01';

-- 設定查詢級別的參數
/*+ Set(random_page_cost 1.1) Set(enable_seqscan off) */
SELECT * FROM large_table WHERE id = 42;

SELECT
    left(query, 100),
    calls,
    shared_blks_hit,
    shared_blks_read,
    round(100.0 * shared_blks_hit /
        nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 100
ORDER BY shared_blks_read DESC
LIMIT 20;

SELECT
    left(query, 100),
    calls,
    pg_size_pretty(wal_bytes::bigint) AS wal_written,
    wal_records,
    wal_fpi   -- Full Page Image 數量
FROM pg_stat_statements
ORDER BY wal_bytes DESC
LIMIT 10;

SELECT
    left(query, 100),
    calls,
    round(jit_generation_time::numeric, 2) AS jit_gen_ms,
    round(jit_optimization_time::numeric, 2) AS jit_opt_ms
FROM pg_stat_statements
WHERE jit_functions > 0
ORDER BY jit_generation_time DESC
LIMIT 10;

SELECT pg_stat_statements_reset(
    userid => (SELECT oid FROM pg_roles WHERE rolname = 'app_user'),
    dbid   => (SELECT oid FROM pg_database WHERE datname = 'mydb'),
    queryid => 0
);

-- PG13+ word_similarity：更精準的子詞相似度
SELECT word_similarity('SQL', 'PostgreSQL database');

-- 拼字校正應用
SELECT name, similarity(name, 'postgre') AS sim
FROM products
WHERE name % 'postgre'
ORDER BY name <-> 'postgre'
LIMIT 5;

-- hstore 最實用場景：Trigger 中記錄欄位差異
INSERT INTO audit_log (changed_fields)
VALUES (hstore(NEW) - hstore(OLD));  -- 只記錄改變的欄位

CREATE EXTENSION btree_gist;

-- 排班系統：同一員工不可同時在兩個班次
CREATE TABLE shift_assignments (
    id          serial PRIMARY KEY,
    employee_id integer NOT NULL,
    shift_range tstzrange NOT NULL,
    EXCLUDE USING gist (
        employee_id WITH =,
        shift_range WITH &&
    )
);

-- IP 位址分配：同一子網不可重疊
CREATE TABLE ip_allocations (
    id         serial PRIMARY KEY,
    network_id integer NOT NULL,
    ip_range   int4range NOT NULL,
    EXCLUDE USING gist (
        network_id WITH =,
        ip_range WITH &&
    )
);

CREATE EXTENSION btree_gin;

-- 複合 GIN 索引：同時利用標量和陣列過濾
CREATE INDEX idx_products_cat_tags
ON products USING gin(category, tags);

SELECT * FROM products
WHERE category = 'electronics'
  AND tags @> ARRAY['wireless'];

shared_preload_libraries = 'pg_stat_statements, auto_explain'

CREATE EXTENSION pg_stat_statements;

CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gist;
CREATE EXTENSION citext;
CREATE EXTENSION pgcrypto;