# Đưa báo cáo AI ra khỏi bảng giao dịch: EXPLAIN, materialized view và index đúng chỗ trên PostgreSQL

> Agent tự viết SQL không biết bảng nào đang phục vụ quầy thu ngân, nên FDE phải là người chặn nó trước khi DB của khách chậm lại.

Bản gốc: https://fdetimes.net/vi/bach-khoa/thuc-hanh-sql-tuning-materialized-view-index-table/

Thử hình dung tuần đầu bạn triển khai một agent báo cáo cho một chuỗi bán lẻ. Mỗi khi ai đó hỏi "doanh thu tuần này của từng cửa hàng", agent tự sinh SQL và quét thẳng bảng `orders`, đúng cái bảng mà máy thu ngân đang ghi vào liên tục. Demo chạy mượt. Lên production, đội vận hành bên khách bắt đầu hỏi vì sao DB nặng hơn hẳn.

Bài toán ở đây không nằm ở prompt. Agent không biết bảng nào phục vụ giao dịch, bảng nào dành cho phân tích; bạn phải biết thay nó. Bài này đi qua một quy trình bạn có thể làm ngay trên laptop với PostgreSQL: đặt mục tiêu, đọc kế hoạch thực thi, chuyển báo cáo sang materialized view, rồi mới tính đến index.

Schema dùng trong bài là giả định và đã được đơn giản hóa: bảng `orders(id, store_id, created_at, amount, is_paid)`. Bạn cần một PostgreSQL chạy local, quyền tạo bảng, và tốt nhất là một bản sao dữ liệu đủ lớn để kế hoạch thực thi trông giống production.

## Bước 1: con số nào cần đạt, câu lệnh nào đáng sửa?

Oracle định nghĩa SQL tuning là một quá trình lặp lại nhằm đạt các mục tiêu cụ thể, đo được và khả thi. Vì thế trước khi gõ lệnh nào, hãy viết mục tiêu ra giấy cùng khách. Ví dụ giả định: "báo cáo doanh thu tuần trả về dưới hai giây, và việc agent chạy báo cáo không làm chậm các lệnh ghi đơn hàng".

Bước tiếp theo là tìm câu lệnh đáng sửa. Tài liệu của Oracle khuyên xem lại lịch sử thực thi để tìm những câu lệnh chiếm phần lớn tải và tài nguyên hệ thống, thay vì sửa ngẫu nhiên.

Với agent, lịch sử này thường lộ ra một điều: vài mẫu truy vấn tổng hợp lặp đi lặp lại với tham số hơi khác nhau. Đó là những ứng viên đầu tiên.

**Kiểm tra:** bạn có một danh sách ngắn các câu lệnh gây tải và một con số mục tiêu mà khách đồng ý.

## Bước 2: đọc kế hoạch trước khi chạy thật

Theo Oracle, kế hoạch thực thi là công cụ chẩn đoán chính khi tối ưu thủ công. `EXPLAIN` cho bạn xem kế hoạch mà không chạy câu lệnh, nên đây là bước an toàn nhất trên DB của khách.

```sql
EXPLAIN
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;
```

Muốn số liệu thật, bạn cần `EXPLAIN ANALYZE`. Tài liệu PostgreSQL cảnh báo rõ: lệnh này chạy câu truy vấn thật, nên mọi tác dụng phụ vẫn xảy ra như bình thường. Với câu lệnh có ghi dữ liệu, PostgreSQL khuyên bọc trong transaction rồi rollback.

Nhưng đừng nhầm rollback với an toàn. Rollback chỉ gỡ phần dữ liệu bị thay đổi; còn câu tổng hợp nặng thì vẫn chạy trọn vẹn và vẫn đè lên DB giao dịch của khách.

Với một câu chỉ đọc như ví dụ dưới, bọc transaction không giảm được gì, nên hãy chạy nó trên bản sao dữ liệu, hoặc vào giờ thấp điểm sau khi đội vận hành đồng ý.

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;
```

Tùy chọn `BUFFERS` báo cáo I/O ở từng node của kế hoạch, và PostgreSQL 18 bật nó ngầm định khi có `ANALYZE`. Viết rõ ra vẫn có ích cho người đọc lại script của bạn.

## Bước 3: nhìn vào số dòng, đừng nhìn vào cost

Người mới hay so cột `cost` như thể đó là mili giây. Không phải. Tài liệu PostgreSQL nói đơn vị cost là tùy ý, không khớp với thời gian thực, và điều quan trọng nhất cần xem là số dòng ước tính có gần với thực tế hay không.

Giả sử node quét `orders` ghi ước tính 1.000 dòng, nhưng thực tế trả về 900.000 dòng. Planner đã chọn chiến lược cho một tập nhỏ rồi phải xử lý một tập lớn gấp 900 lần, và mọi quyết định phía trên node đó đều dựa trên con số sai. Đây là chỗ bạn ghi lại đầu tiên, trước cả khi nghĩ đến index.

**Kiểm tra:** với mỗi câu lệnh trong danh sách, bạn biết node nào đọc nhiều buffer nhất và node nào ước tính lệch nhiều nhất.

## Bước 4: cho agent một bản tóm tắt để đọc

Oracle liệt kê việc thiếu các cấu trúc truy cập như index và materialized view là một nguyên nhân điển hình khiến SQL chạy chậm. Với báo cáo AI, materialized view thường là lựa chọn đúng hơn: agent đọc một bảng đã tổng hợp sẵn thay vì đập vào bảng giao dịch.

Mô tả pattern Materialized View của Microsoft nhấn mạnh một tính chất đáng giá: view và dữ liệu trong đó hoàn toàn có thể vứt đi, vì luôn dựng lại được từ nguồn. Nó là một dạng cache chuyên dụng, nên bạn có thể thử, sai và dựng lại mà không đụng vào dữ liệu gốc của khách.

```sql
CREATE MATERIALIZED VIEW report_daily_store_sales AS
SELECT store_id,
date_trunc('day', created_at) AS day,
sum(amount) AS revenue,
count(*)    AS order_count
FROM orders
GROUP BY 1, 2;
```

**Điểm mấu chốt:** Báo cáo AI nên đọc một bản tóm tắt, không nên đọc thẳng bảng giao dịch của khách.

Câu hỏi khó nhất ở bước này không nằm ở SQL. Microsoft khuyên xác định mức dữ liệu cũ chấp nhận được trước, rồi mới chọn refresh theo sự kiện, theo lịch hay thủ công.

Nếu giám đốc vùng chỉ xem báo cáo mỗi sáng, dữ liệu cũ vài giờ là ổn; nếu họ dùng nó để điều hàng trong ngày thì không. Hãy hỏi câu đó trong buổi customer discovery, đừng tự đoán.

## Bước 5: refresh mà không khóa người đang đọc

`REFRESH MATERIALIZED VIEW` thông thường sẽ chặn các lệnh đọc view trong lúc chạy, nghĩa là agent đứng chờ. Bản `CONCURRENTLY` làm mới view mà không khóa các lệnh SELECT đồng thời, nhưng nó đòi một UNIQUE index và chỉ cho một lần refresh chạy tại một thời điểm.

```sql
CREATE UNIQUE INDEX report_daily_store_sales_key
ON report_daily_store_sales (store_id, day);

REFRESH MATERIALIZED VIEW CONCURRENTLY report_daily_store_sales;
```

Chi tiết dễ vấp: tài liệu PostgreSQL yêu cầu index đó chỉ dùng cột thường, không phải expression index và không có mệnh đề WHERE. Ở ví dụ trên, `date_trunc` đã nằm trong định nghĩa view, nên `day` là một cột thường của view và index hợp lệ. Nếu bạn đặt `date_trunc` vào trong index, refresh concurrently sẽ không chạy.

Cũng đừng quên view này được dựng từ `orders`: mỗi lần refresh toàn bộ, câu tổng hợp trong định nghĩa view lại quét bảng giao dịch, và Microsoft lưu ý refresh tốn compute. Vì thế hãy refresh thưa nhất mà ngưỡng dữ liệu cũ ở bước 4 cho phép, và đặt lịch vào giờ thấp điểm thay vì giữa ca bán hàng.

Index giúp agent đọc nhanh, nhưng không miễn phí. Microsoft lưu ý việc duy trì index trên view cộng thêm chi phí ghi trong mỗi chu kỳ refresh. Chỉ giữ những index mà truy vấn báo cáo thật sự dùng.

**Kiểm tra:** chạy lại `EXPLAIN` cho câu truy vấn của agent, lần này trỏ vào view, và so số buffer với bước 3.

## Bước 6: view cũ lặng lẽ là lỗi nguy hiểm nhất

Khi một trigger hay một job refresh bị lỡ, Microsoft cảnh báo view sẽ lặng lẽ trả về kết quả cũ. Với agent, điều này tệ hơn một lỗi rõ ràng: nó trả lời trơn tru bằng con số của hôm qua. Khuyến nghị là theo dõi thời điểm refresh gần nhất và cảnh báo khi tuổi view vượt quá ngưỡng đã thống nhất.

Một cách làm đơn giản: job refresh ghi thời điểm hoàn tất vào một bảng log nhỏ, và một kiểm tra định kỳ so thời điểm đó với ngưỡng ở bước 4. Bạn cũng có thể cho agent đọc thời điểm này và nói rõ "số liệu tính đến lúc…" trong câu trả lời, để người dùng không bị đánh lừa.

## Khi nào mới nghĩ đến index table?

Có những truy vấn không phải tổng hợp mà là tra cứu theo một khóa khác khóa chính, ví dụ tìm đơn theo mã khách. Pattern Index Table của Microsoft bàn về chuyện này ở mức kiến trúc, với một bảng index dựng riêng.

Mang nó sang index của PostgreSQL là một phép suy luận tương tự chứ không phải quy tắc của PostgreSQL, nhưng hai lời khuyên của pattern vẫn đáng để FDE nhớ.

Lời khuyên đầu tiên: đừng tạo index table mang tính phỏng đoán cho những truy vấn ứng dụng không chạy hoặc chỉ thỉnh thoảng chạy. Mỗi index table thêm chi phí bảo trì, nên cần được rà soát và gỡ bỏ khi không còn ai dùng. Áp vào agent, chỉ thêm khi lịch sử ở bước 1 cho thấy mẫu truy vấn đó lặp lại thật.

Lời khuyên thứ hai: khóa có độ chọn lọc thấp, như cột boolean `is_paid`, là ứng viên tồi. Index table khi đó vẫn tốn đủ chi phí lưu trữ và throughput nhưng gần như không thu hẹp được truy vấn. Một cờ chỉ có hai giá trị chia bảng làm đôi, chứ không giúp tìm nhanh vài dòng.

## Những lỗi hay gặp

Lỗi phổ biến nhất là chạy `EXPLAIN ANALYZE` trên production rồi yên tâm vì đã có ROLLBACK, trong khi tải thì vẫn đổ lên DB của khách. Lỗi thứ hai là so cost giữa hai kế hoạch rồi kết luận theo mili giây.

Lỗi thứ ba là dựng materialized view nhưng không ai thống nhất mức dữ liệu cũ, nên refresh chạy theo cảm tính và không có cảnh báo khi nó hỏng.

Ở chiều ngược lại, đừng vội thêm index cho mọi cột mà agent từng dùng để lọc. Mỗi index là chi phí ghi mà hệ thống giao dịch của khách phải trả, mãi mãi.

## Đưa kỹ năng này vào CV ra sao?

Nếu một mô tả công việc FDE nhắc đến việc làm trực tiếp với database của khách hay hiệu năng ở production, đây là kỹ năng nên đưa lên đầu khi bạn trả lời phỏng vấn.

Trong CV, đừng viết "thành thạo SQL". Hãy kể một dòng có cấu trúc: mục tiêu đo được, câu lệnh gây tải, thay đổi bạn làm (materialized view, UNIQUE index, refresh concurrently), và cách bạn giám sát dữ liệu cũ.

Một repo nhỏ có script, kế hoạch thực thi trước và sau, cùng README giải thích vì sao bạn không thêm index cho cột boolean, sẽ thuyết phục hơn mọi chứng chỉ.

Agent sẽ ngày càng giỏi viết SQL. Nhưng hiểu DB của khách đang phục vụ ai vào lúc nào, và vì sao không nên để agent đụng vào đó, vẫn là việc của FDE ngồi cạnh đội vận hành bên khách.

**Thử ngay tuần này:**

- Dựng PostgreSQL local, tạo bảng orders giả lập vài triệu dòng, chạy EXPLAIN (ANALYZE, BUFFERS) cho một truy vấn tổng hợp và ghi lại số dòng ước tính so với thực tế.
- Chuyển truy vấn đó thành materialized view có UNIQUE index, chạy REFRESH MATERIALIZED VIEW CONCURRENTLY rồi so kế hoạch thực thi trước và sau.
- Viết một README ngắn ghi mục tiêu, mức dữ liệu cũ chấp nhận được, giờ refresh và cách cảnh báo khi view quá cũ, rồi đưa vào portfolio.

## Nguồn

- [Introduction to SQL Tuning (Oracle Database 23)](https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/introduction-to-sql-tuning.html)

- [Using EXPLAIN (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/18/using-explain.html)

- [Materialized View Pattern - Azure Architecture Center](https://learn.microsoft.com/en-us/azure/architecture/patterns/materialized-view)

- [Index Table Pattern - Azure Architecture Center](https://learn.microsoft.com/en-us/azure/architecture/patterns/index-table)

- [REFRESH MATERIALIZED VIEW (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/18/sql-refreshmaterializedview.html)