Postgres Major Upgrades Without Fear: A Practical Shadow-Testing Pipeline with pg_upgrade --check, pg_stat_statements and Query Diffing

#testing #database #devops #postgres

Tuần này trên Hacker News có một thread hơn 700 điểm: Postgres 19 bị trễ vì người ta tìm ra nhiều bug hơn hẳn trước đây, một phần nhờ các công cụ fuzzing và AI agent đi soi code. Nghe thì có vẻ đáng lo, nhưng mình thấy đây là tin tốt. Bài học mình rút ra cho team mình lại khác: nếu chính core team còn tìm ra bug ở những chỗ không ngờ, thì việc bạn nâng major version Postgres chỉ vì đã đọc release notes là quá mạo hiểm.

Mình từng nâng một cluster từ 14 lên 16 mà chỉ chạy test suite của app. Test pass hết, deploy xong thì có một report query chậm từ 200ms lên 9 giây vì planner chọn plan khác. Từ đó mình làm một pipeline nhỏ gọi là shadow testing: chạy chính các query thật của production trên cả version cũ và version mới, rồi so kết quả và thời gian. Bài này chia sẻ lại cách làm, dùng PostgreSQL 17 → 18 làm ví dụ, nhưng áp dụng được cho mọi lần nâng major version.

Tại sao test suite của app là chưa đủ

Unit test và integration test thường chạy trên database gần như trống, với vài chục dòng fixture. Những thứ hay vỡ khi nâng major version lại nằm ở chỗ khác:

Planner thay đổi: cost model, cách ước lượng selectivity, các tối ưu mới (ví dụ PG18 có skip scan cho B-tree index nhiều cột). Đa số trường hợp nhanh hơn, nhưng sẽ có vài query bị chọn plan tệ hơn.
Thay đổi hành vi: hàm bị deprecate, collation thay đổi, cast ngầm khác đi. Mấy thứ này chỉ lộ ra với data thật.
Extension: PostGIS, pg_partman, timescaledb... phải có bản build cho version mới, và không phải lúc nào cũng có ngay.
Statistics: sau pg_upgrade, ở các bản trước 18 thì optimizer statistics không được giữ lại, phải chạy ANALYZE lại toàn bộ. Nếu quên thì vài giờ đầu sau upgrade query nào cũng chậm. PG18 đã giữ lại được phần lớn statistics, nhưng extended statistics vẫn phải analyze lại.

Nên mục tiêu là: test với data thật (hoặc snapshot đã ẩn danh) và workload thật.

Kiến trúc pipeline

flowchart LR
    A[Production PG17] -->|pg_stat_statements| B[Top N queries]
    A -->|snapshot / pg_dump| C[Clone PG17]
    C -->|pg_upgrade --check| D[Clone PG18]
    B --> E[Replay script]
    C --> E
    D --> E
    E --> F[Diff report: rows + timing + plan]

Ý tưởng rất đơn giản: lấy một snapshot, dựng hai bản giống hệt nhau, một bản giữ nguyên PG17, một bản nâng lên PG18. Sau đó replay cùng một tập query lên cả hai và so sánh.

Bước 1: Lấy workload thật và dựng môi trường

Đầu tiên, lấy các query quan trọng nhất từ pg_stat_statements. Mình lấy theo total_exec_time vì đó là những query ảnh hưởng nhiều nhất tới hệ thống:

# Export top 200 query theo tổng thời gian chạy
psql "$PROD_RO_URL" -At -F $'\t' -c "
  SELECT queryid, calls, round(mean_exec_time::numeric, 2), query
  FROM pg_stat_statements
  WHERE query NOT ILIKE '%pg_stat%'
    AND query ~* '^\s*(select|with)'
  ORDER BY total_exec_time DESC
  LIMIT 200;" > top_queries.tsv

# Dựng 2 container từ cùng một snapshot data
docker run -d --name pg17 -p 5417:5432 -e POSTGRES_PASSWORD=test \
  -v ./snapshot17:/var/lib/postgresql/data postgres:17

# Kiểm tra trước khi upgrade thật (không đụng vào data)
pg_upgrade --check \
  -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /data/pg17 -D /data/pg18

Vài lưu ý thực tế:

Chỉ replay SELECT/WITH read-only. Đừng replay UPDATE/DELETE trừ khi bạn chạy trong transaction rồi ROLLBACK.
pg_stat_statements đã normalize query thành $1, $2. Bạn cần sinh tham số thật, mình thường lấy từ log log_min_duration_statement hoặc viết vài sample value cho từng query.
pg_upgrade --check bắt được rất nhiều vấn đề sớm: extension thiếu, kiểu dữ liệu không tương thích, encoding lệch. Chạy nó trên clone trước, đừng chạy lần đầu trên production.
Sau upgrade, chạy vacuumdb --all --analyze-in-stages trên bản PG18 trước khi đo, không thì kết quả timing vô nghĩa.

Bước 2: Replay và diff kết quả

Đây là phần quan trọng nhất. Script Python dùng psycopg 3 (3.2+) chạy mỗi query trên cả hai server, so sánh hash của kết quả và thời gian chạy:

import hashlib, time, csv
import psycopg

OLD = 'postgresql://postgres:test@localhost:5417/app'
NEW = 'postgresql://postgres:test@localhost:5418/app'
RUNS = 5

def run(conn, sql, params):
    timings, digest = [], None
    for _ in range(RUNS):
        t0 = time.perf_counter()
        rows = conn.execute(sql, params).fetchall()
        timings.append((time.perf_counter() - t0) * 1000)
    # Sort để không bị lệch do thứ tự khi query không có ORDER BY
    digest = hashlib.sha256(repr(sorted(map(repr, rows))).encode()).hexdigest()
    timings.sort()
    return digest, timings[len(timings) // 2], len(rows)

def main(cases):
    with psycopg.connect(OLD) as old, psycopg.connect(NEW) as new:
        for c in (old, new):
            c.execute("SET statement_timeout = '30s'")
        for qid, sql, params in cases:
            h1, t1, n1 = run(old, sql, params)
            h2, t2, n2 = run(new, sql, params)
            ratio = t2 / max(t1, 0.01)
            flag = 'RESULT_DIFF' if h1 != h2 else ('SLOWER' if ratio > 1.5 else 'OK')
            print(f'{flag:12} {qid:>22} old={t1:8.1f}ms new={t2:8.1f}ms x{ratio:.2f} rows={n1}/{n2}')

Mình dùng median của 5 lần chạy để tránh nhiễu do cache lạnh. Ngưỡng 1.5x là con số mình chọn theo kinh nghiệm, bạn tự chỉnh tùy SLA. Hai loại flag cần chú ý:

RESULT_DIFF: nghiêm trọng. Kết quả khác nhau thì hoặc query của bạn không deterministic (thiếu ORDER BY với LIMIT, dùng random(), now()), hoặc có thay đổi hành vi thật. Lần nào cũng phải điều tra.
SLOWER: lấy EXPLAIN (ANALYZE, BUFFERS) ở cả hai bên rồi so.

Bước 3: So sánh plan khi query bị chậm

Với những query bị flag SLOWER, mình xuất plan dạng JSON rồi so node gốc để biết planner đổi chiến lược ở đâu:

sequenceDiagram
    participant S as Script
    participant O as PG17
    participant N as PG18
    S->>O: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    S->>N: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    O-->>S: plan A
    N-->>S: plan B
    S->>S: so Node Type, Rows estimate vs actual

Thường gặp nhất là estimate rows lệch rất xa so với actual rows. Cách xử lý theo thứ tự ưu tiên:

Chạy ANALYZE lại bảng liên quan (đôi khi chỉ do thiếu stats).
Tăng default_statistics_target cho cột cụ thể: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
Tạo extended statistics cho các cột tương quan: CREATE STATISTICS ... (dependencies, ndistinct) ON col_a, col_b FROM t;
Cuối cùng mới tính tới rewrite query hoặc thêm index.

Đừng vội tắt từng planner option như enable_nestloop = off ở cấp global. Đó là băng dán, sẽ làm hỏng các query khác.

Kết luận

Việc Postgres 19 bị trễ vì tìm ra nhiều bug hơn nhắc mình một điều: phần mềm càng được soi kỹ thì càng thấy nhiều chỗ chưa ổn, và database của bạn cũng không ngoại lệ. Vài điều nên làm ngay:

Bật pg_stat_statements ngay hôm nay nếu chưa có. Không có nó thì bạn không biết workload thật của mình trông ra sao.
Luôn chạy pg_upgrade --check trên clone trước, vài tuần trước ngày upgrade dự kiến, để có thời gian xử lý extension.
Shadow test với top 100-200 query theo total_exec_time. Thường chỉ tốn vài giờ setup nhưng bắt được những vấn đề mà test suite không bao giờ thấy.
Coi RESULT_DIFF là blocker, SLOWER là việc cần điều tra trước khi deploy.
Sau upgrade, chạy vacuumdb --analyze-in-stages và theo dõi pg_stat_statements trong 24-48 giờ đầu, so với baseline cũ.
Đừng nâng lên bản .0. Đợi tới .1 hoặc .2, nhất là với một release đã bị trễ vì nhiều bug như vậy.

Script trên chỉ khoảng 50 dòng nhưng đã cứu mình khỏi ít nhất hai sự cố production. Dựng nó một lần, giữ lại trong repo, mỗi lần nâng major version chỉ cần đổi image tag là chạy lại được.

DEV Community