sql plan 변경 모니터링

• 목적: 기존의 sql의 실행계획이 변경되었는지 모니터링하여 장애 예방한다.
• 방안: extension 중 pg_store_plans를 활용 주기적으로 변경된 plan이 있는지 확인한다.

plan 변경 감지 쿼리

WITH plan_changes AS (
    SELECT
        p.queryid,
        s.query,
        p.plan AS new_plan,
        LAG(p.plan) OVER (PARTITION BY p.queryid ORDER BY p.calls) AS old_plan
    FROM
        pg_store_plans p
    JOIN
        pg_stat_statements s ON p.queryid = s.queryid
)
SELECT
    queryid,
    query,
    old_plan,
    new_plan
FROM
    plan_changes
WHERE
    old_plan IS NOT NULL  -- 이전 실행 계획이 존재하는 경우만
    AND old_plan <> new_plan;  -- 실행 계획이 변경된 경우만

1. pg_store_plans.plan과 pg_stat_statements.queryid를 활용하여 각 SQL의 실행 계획을 추적
2. LAG(p.plan) OVER (...)을 이용해 이전 실행 계획(old_plan)과 현재 실행 계획(new_plan)을 비교
3. 실행 계획이 변경된 경우만 필터링하여 출력

---

pg_cron을 이용해서 일정 주기로 해당 쿼리 실행

SELECT cron.schedule(
    '0 * * * *',  -- 매 정각 실행
    $$ INSERT INTO plan_changes_log
        SELECT queryid, query, old_plan, new_plan, now()
        FROM (
            WITH plan_changes AS (
                SELECT
                    p.queryid,
                    s.query,
                    p.plan AS new_plan,
                    LAG(p.plan) OVER (PARTITION BY p.queryid ORDER BY p.calls) AS old_plan
                FROM
                    pg_store_plans p
                JOIN
                    pg_stat_statements s ON p.queryid = s.queryid
            )
            SELECT queryid, query, old_plan, new_plan
            FROM plan_changes
            WHERE old_plan IS NOT NULL AND old_plan <> new_plan
        ) AS changes;
    $$ 
);

---

실행계획이 변경되었을 경우

1. 최근 ANALYZE 또는 VACUUM 실행 여부 확인

   SELECT relname, last_vacuum, last_analyze
   FROM pg_stat_all_tables
   WHERE last_analyze IS NOT NULL
   ORDER BY last_analyze DESC;