3 lỗi PostgreSQL khiến API backend chậm: cách đo, sửa và kiểm chứng

Vì sao API vẫn chậm dù server “chưa quá tải”?

Một pattern rất quen thuộc trong backend: CPU app server thấp, RAM còn nhiều, PostgreSQL chưa báo lỗi, nhưng API vẫn có p95 latency vài giây. Dev nhìn log thấy endpoint /orders hoặc /users/:id/activity lúc nhanh lúc chậm, không rõ nguyên nhân. Thường vấn đề không nằm ở framework, cũng không phải “PostgreSQL yếu”, mà là query pattern tệ: scan quá nhiều dòng, index sai thứ tự, pagination càng về sau càng đắt, hoặc N+1 query âm thầm nhân latency lên hàng trăm lần.

Điểm nguy hiểm là các lỗi này ít lộ ở môi trường dev vì database chỉ có vài nghìn rows. Khi lên production với vài triệu đến vài trăm triệu rows, cùng một đoạn code có thể chuyển từ 30 ms thành 3 giây. Bài này đi thẳng vào 3 lỗi phổ biến nhất và cách đo/sửa có thể áp dụng ngay.

1. Chỉ nhìn query, không nhìn execution plan

Sai lầm đầu tiên là tối ưu bằng cảm giác. Thấy query có WHERE user_id = ? thì nghĩ đã ổn. Nhưng PostgreSQL có thể vẫn phải Seq Scan nếu không có index phù hợp, statistic cũ, hoặc điều kiện lọc không đủ selective.

Ví dụ API lấy danh sách order gần nhất của một user:

SELECT id, user_id, status, total_amount, created_at
FROM orders
WHERE user_id = $1
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Nếu bảng orders có 20 triệu rows và chỉ có index riêng lẻ trên user_id, PostgreSQL có thể phải lấy rất nhiều rows của user đó rồi sort theo created_at. Cách kiểm tra đúng là dùng:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Những thứ cần nhìn:

• Seq Scan: scan toàn bảng, nguy hiểm với bảng lớn.
• Rows Removed by Filter: số rows bị đọc rồi loại bỏ.
• Sort Method: nếu phải sort nhiều rows, latency sẽ tăng.
• shared read: phải đọc từ disk/page cache thay vì buffer.
• actual time: thời gian thực tế, không chỉ cost ước lượng.

Index tốt hơn cho query trên thường là composite index theo đúng filter + sort:

CREATE INDEX CONCURRENTLY idx_orders_user_status_created_at
ON orders (user_id, status, created_at DESC)
INCLUDE (total_amount);

Tại sao thứ tự này quan trọng? PostgreSQL dùng index hiệu quả nhất theo leftmost prefix. Query lọc bằng user_id, status, rồi cần order theo created_at DESC, nên index (user_id, status, created_at DESC) giúp database đi thẳng tới vùng dữ liệu cần lấy và trả về 20 rows đầu tiên mà không sort toàn bộ.

INCLUDE (total_amount) giúp index có thể cover thêm column cần trả về mà không ảnh hưởng đến thứ tự search key. Không phải lúc nào cũng cần INCLUDE, nhưng với endpoint đọc nhiều, payload nhỏ, đây là cách giảm heap fetch khá hiệu quả.

Lưu ý production: dùng CREATE INDEX CONCURRENTLY để tránh lock write dài. Đổi lại, lệnh này chạy lâu hơn và không được đặt trong transaction block.

2. Dùng OFFSET pagination cho dữ liệu lớn

LIMIT/OFFSET dễ viết, dễ hiểu, nhưng rất dễ làm API chậm theo số trang.

SELECT id, title, created_at
FROM posts
WHERE published = true
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Vấn đề: PostgreSQL vẫn phải đi qua 100.000 rows đầu tiên rồi bỏ đi, sau đó mới lấy 20 rows tiếp theo. Nghĩa là page càng sâu càng chậm. Với admin dashboard có người nhảy tới trang 5000, database sẽ bị ép làm việc vô ích.

Giải pháp thực tế là keyset pagination, còn gọi là cursor pagination:

-- First page
SELECT id, title, created_at
FROM posts
WHERE published = true
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: truyền cursor từ item cuối page trước
SELECT id, title, created_at
FROM posts
WHERE published = true
  AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Index tương ứng:

CREATE INDEX CONCURRENTLY idx_posts_published_created_id
ON posts (published, created_at DESC, id DESC);

Ở đây dùng thêm id để xử lý trường hợp nhiều row có cùng created_at. Nếu chỉ cursor bằng created_at, bạn có thể bị mất hoặc lặp record khi timestamp trùng.

Trade-off: keyset pagination không phù hợp nếu UI bắt buộc “nhảy tới trang số 837”. Nhưng với feed, timeline, list order, notification, audit log, message history, nó gần như luôn tốt hơn OFFSET. API response có thể trả cursor như sau:

{
  "data": [
    {
      "id": 99102,
      "title": "PostgreSQL index tuning",
      "created_at": "2026-05-05T02:15:00Z"
    }
  ],
  "next_cursor": {
    "created_at": "2026-05-05T02:15:00Z",
    "id": 99102
  }
}

Nếu dùng REST, cursor có thể encode base64 để tránh client phụ thuộc cấu trúc nội bộ:

GET /posts?limit=20&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0wNVQwMjoxNTowMFoiLCJpZCI6OTkxMDJ9

Điểm cần nhớ: cursor không phải để “bảo mật”. Nó chỉ là state pagination. Server vẫn phải validate limit, direction và quyền truy cập.

3. N+1 query: latency nhỏ nhân lên thành thảm họa

N+1 thường xuất hiện khi dùng ORM. Ví dụ lấy 50 users, sau đó mỗi user lại query riêng danh sách role hoặc profile.

Pseudo-code:

const users = await userRepo.find({ take: 50 });

return Promise.all(
  users.map(async (user) => ({
    ...user,
    roles: await roleRepo.findByUserId(user.id),
  }))
);

Nếu mỗi query role chỉ mất 10 ms, 50 query đã có thể tạo ra latency lớn, chưa kể connection pool bị chiếm dụng. Trong production, N+1 không chỉ làm endpoint chậm mà còn làm nghẽn database cho các endpoint khác.

Cách sửa thường là batch query:

SELECT u.id, u.email, u.created_at,
       r.id AS role_id, r.name AS role_name
FROM users u
LEFT JOIN user_roles ur ON ur.user_id = u.id
LEFT JOIN roles r ON r.id = ur.role_id
WHERE u.id = ANY($1);

Hoặc nếu muốn giữ shape JSON ngay từ PostgreSQL:

SELECT
  u.id,
  u.email,
  u.created_at,
  COALESCE(
    jsonb_agg(
      jsonb_build_object('id', r.id, 'name', r.name)
    ) FILTER (WHERE r.id IS NOT NULL),
    '[]'::jsonb
  ) AS roles
FROM users u
LEFT JOIN user_roles ur ON ur.user_id = u.id
LEFT JOIN roles r ON r.id = ur.role_id
WHERE u.id = ANY($1)
GROUP BY u.id;

Ở tầng app, bạn cũng có thể dùng DataLoader pattern: gom nhiều request findByUserId trong cùng event loop tick thành một query WHERE user_id = ANY($1). Cách này đặc biệt hữu ích với GraphQL, nơi N+1 rất dễ xảy ra khi resolver lồng nhau.

Để phát hiện N+1, đừng chỉ nhìn một query đơn lẻ. Hãy log số query trên mỗi request. Với Node.js/NestJS hoặc Go backend, nên có middleware gắn request_id, sau đó log query count và tổng DB time. Nếu một endpoint list 20 items mà bắn 41 queries, đó là tín hiệu xấu.

Một cách khác là bật pg_stat_statements để xem query nào đang tốn tổng thời gian nhiều nhất:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

mean_exec_time cao cho biết query đơn lẻ chậm. calls quá cao với query nhỏ có thể là N+1 hoặc polling quá dày. Trong thực tế, query 5 ms nhưng bị gọi 2 triệu lần/ngày vẫn đáng tối ưu hơn query 500 ms nhưng chỉ chạy vài lần.

Use case thực tế: endpoint lịch sử giao dịch

Giả sử bạn có backend fintech với endpoint:

GET /users/:id/transactions

Ban đầu implementation khá phổ biến:

• Query user.
• Query transactions bằng OFFSET.
• Với mỗi transaction, query merchant riêng.
• Sort theo created_at.
• Không có composite index đúng với user_id + created_at.

Ở môi trường staging với 10.000 transactions, endpoint chạy 80-120 ms. Lên production, một số user có hơn 500.000 transactions, page sâu bắt đầu lên 2-4 giây. Database CPU tăng, connection pool đầy vào giờ cao điểm.

Cách xử lý thực tế:

1. Đổi từ OFFSET sang keyset pagination bằng (created_at, id).
2. Tạo index:

CREATE INDEX CONCURRENTLY idx_transactions_user_created_id
ON transactions (user_id, created_at DESC, id DESC);

1. Batch merchant bằng merchant_id = ANY($1) thay vì query từng transaction.
2. Thêm log db_query_count và db_total_time_ms theo request.
3. Dùng EXPLAIN (ANALYZE, BUFFERS) trước/sau để xác nhận không còn sort lớn hoặc scan dư thừa.

Kết quả thường thấy không phải “nhanh hơn một chút”, mà là thay đổi về độ ổn định. Page đầu và page sâu có latency gần nhau hơn. DB ít bị spike hơn vì mỗi request đọc đúng vùng index cần thiết. Quan trọng hơn: team có cơ chế đo để không tái phạm, thay vì tối ưu theo cảm giác.

Kết luận: tối ưu PostgreSQL phải bắt đầu từ pattern truy cập

Ba takeaway cụ thể:

1. Trước khi thêm cache, hãy chạy EXPLAIN (ANALYZE, BUFFERS) cho query chậm nhất. Không có execution plan thì chỉ đang đoán.
2. Với list API lớn, ưu tiên keyset pagination thay vì OFFSET, đặc biệt cho feed, transaction, audit log, notification.
3. Theo dõi số query trên mỗi request và bật pg_stat_statements. Query nhỏ nhưng bị gọi quá nhiều cũng là bottleneck thật.

PostgreSQL đủ mạnh cho phần lớn backend, miễn là app không bắt nó scan, sort và query lặp một cách vô ích.