"""
03_vacuum_bloat.py —— 制造死元组并观察 VACUUM 前后大小变化

依赖：
    pip install "psycopg[binary]>=3.1"

要点：
    1. 反复 UPDATE 同一批行，观察 pg_stat_user_tables.n_dead_tup 与表大小
    2. 普通 VACUUM 不缩小磁盘，但 n_dead_tup 归零
    3. VACUUM FULL 真正缩小到接近实际数据
"""
from __future__ import annotations

import psycopg

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
TABLE = "ch8_event_log"


def stats(label: str) -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        cur = conn.execute(f"""
            SELECT pg_size_pretty(pg_relation_size('{TABLE}'))
        """)
        size = cur.fetchone()[0]
        cur = conn.execute("""
            SELECT n_live_tup, n_dead_tup
            FROM pg_stat_user_tables WHERE relname='ch8_event_log'
        """)
        live, dead = cur.fetchone()
    print(f"  [{label:<24}] size={size:>12}  live={live:>6}  dead={dead:>6}")


def make_dead_tuples(rounds: int) -> None:
    with psycopg.connect(DSN, autocommit=True) as conn:
        for r in range(rounds):
            conn.execute(
                f"UPDATE {TABLE} SET payload = payload || '+' "
                f"WHERE id BETWEEN 1 AND 5000"
            )


def main() -> None:
    print("=== VACUUM / VACUUM FULL 演示 ===\n")

    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"VACUUM FULL {TABLE}")
        conn.execute(f"ANALYZE {TABLE}")

    stats("初始（VACUUM FULL 后）")

    make_dead_tuples(rounds=5)
    # 让 PG 把统计同步出来
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"ANALYZE {TABLE}")
    stats("反复 UPDATE 5 轮后")

    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"VACUUM (VERBOSE) {TABLE}")
        conn.execute(f"ANALYZE {TABLE}")
    stats("普通 VACUUM 后（大小不变）")

    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(f"VACUUM FULL {TABLE}")
        conn.execute(f"ANALYZE {TABLE}")
    stats("VACUUM FULL 后（缩回去）")

    print("\n说明：")
    print("  - 普通 VACUUM 把死元组空间标记为可重用，不归还 OS")
    print("  - VACUUM FULL 重写整表，物理大小回到最小，但锁表")
    print("  - 生产环境建议用 pg_repack 在线代替 VACUUM FULL")


if __name__ == "__main__":
    main()