-- ✅ 关键字大写
SELECT id, name, email
FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20;

-- ✅ 使用显式列名，避免 SELECT *
SELECT id, name, email FROM users;

-- ✅ 使用参数化查询，避免 SQL 注入
-- cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

-- ✅ 使用 TIMESTAMPTZ
CREATE TABLE events (
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- ✅ 外键必须有索引
CREATE TABLE orders (
    customer_id INT REFERENCES customers(id)
);
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- ✅ 大批量操作使用事务
BEGIN;
INSERT INTO ...;
INSERT INTO ...;
COMMIT;

-- ✅ 使用 EXPLAIN ANALYZE 验证慢查询

# postgresql.conf
autovacuum = on
autovacuum_max_workers = 3               # 并行工作者数
autovacuum_naptime = 1min                # 检查间隔
autovacuum_vacuum_threshold = 50         # 最少死行数
autovacuum_vacuum_scale_factor = 0.1     # 死行比例（10%）
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05   # 分析阈值（5%）
autovacuum_vacuum_cost_delay = 2ms       # IO 限制延迟
autovacuum_vacuum_cost_limit = 200       # IO 限制

-- 为大表设置更低的触发阈值
ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1% 死行就触发
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 0        -- 不限速
);

-- 查看表的 VACUUM 状态
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 查看正在进行的 VACUUM
SELECT pid, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';