Sửa lỗi PostgreSQL 'temporary file size exceeds temp_file_limit'

Lỗi Gặp Phải

Bạn chạy một truy vấn — sắp xếp lớn, hash join, hoặc GROUP BY trên hàng triệu dòng — và PostgreSQL cắt đứt nó ngay lập tức:

ERROR: temporary file size exceeds temp_file_limit (1048576kB)

Truy vấn không bị chậm. Nó chết hẳn. PostgreSQL cần ghi dữ liệu trung gian ra đĩa, lượng dữ liệu ghi ra vượt ngưỡng temp_file_limit, và thế là xong.

Nguyên Nhân

PostgreSQL có một lượng RAM cố định cho mỗi thao tác sắp xếp hoặc hash, được thiết lập bởi work_mem. Khi dữ liệu không vừa, nó sẽ tràn ra đĩa dưới dạng file tạm. Tham số temp_file_limit đặt giới hạn trên cho dung lượng đĩa mà một phiên làm việc có thể dùng cho những file đó.

Vượt qua giới hạn đó thì truy vấn bị hủy — không có kết quả một phần, không có thử lại. Giới hạn mặc định thường là 1 GB (1048576 kB), nghe có vẻ lớn cho đến khi bạn join hai bảng 500 MB mà không có index.

Các nguyên nhân phổ biến:

• Sắp xếp hoặc nhóm hàng triệu dòng trên cột không có index
• Hash join qua các bảng lớn không được lọc trước
• DISTINCT, ORDER BY, hoặc UNION trên tập kết quả có nhiều cột
• Nhiều CTE mà mỗi cái đều vật chất hóa một tập kết quả trung gian lớn

Cách Sửa 1 — Tăng work_mem (Bắt Đầu Từ Đây)

work_mem càng lớn thì càng nhiều dữ liệu được giữ trong RAM trước khi chạm đến đĩa. Thử với ghi đè ở cấp phiên làm việc trước — không cần khởi động lại:

SET work_mem = '256MB';
-- Sau đó chạy lại truy vấn của bạn

Truy vấn hoàn thành? Áp dụng vĩnh viễn trong postgresql.conf:

# postgresql.conf
work_mem = 64MB   # mặc định là 4MB

Tải lại cấu hình mà không cần khởi động lại:

SELECT pg_reload_conf();
-- hoặc: sudo systemctl reload postgresql

Chú ý phép tính trước khi tăng giá trị này cho toàn hệ thống. work_mem áp dụng cho mỗi node sắp xếp, mỗi phiên làm việc. Một truy vấn có 10 node sắp xếp chạy trên 100 kết nối đồng thời có thể tiêu tốn 10 × 100 × work_mem RAM — tức 256 GB nếu work_mem = 256MB. Giữ giá trị toàn cục dưới 10–20% tổng RAM. Với các truy vấn phân tích nặng, hãy ghi đè ở cấp phiên làm việc hoặc role thay vì dùng toàn cục.

Cách Sửa 2 — Tăng hoặc Bỏ Giới Hạn temp_file_limit

Với các tác vụ thực sự cần ghi tràn nhiều — ETL hàng đêm, xuất dữ liệu kho — hãy tăng giới hạn thay vì chống lại nó. Giá trị tính bằng kilobyte; -1 nghĩa là không giới hạn.

-- Chỉ cho phiên hiện tại:
SET temp_file_limit = '4GB';  -- hoặc -1 để không giới hạn

-- Vĩnh viễn cho một role cụ thể:
ALTER ROLE etl_user SET temp_file_limit = '4GB';

-- Toàn cục trong postgresql.conf:
temp_file_limit = 4194304   # 4 GB tính bằng kB

Sau khi chỉnh sửa postgresql.conf:

SELECT pg_reload_conf();

Đừng đặt -1 cho toàn hệ thống. Trên một server dùng chung với 20 người dùng ứng dụng, một truy vấn mất kiểm soát có thể lấp đầy đĩa và kéo sập mọi phiên làm việc khác. Chỉ cho phép không giới hạn với các role DBA hoặc ETL đáng tin cậy.

Cách Sửa 3 — Tối Ưu Truy Vấn

Đôi khi giới hạn không phải vấn đề — mà chính truy vấn mới là vấn đề. Xem kế hoạch thực thi trước:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

Hai điều cần chú ý: Sort Method: external merge Disk và Hash Batches: > 1. Cả hai đều có nghĩa là thao tác đã tràn ra đĩa.

Thêm index để tránh sắp xếp

-- Sắp xếp hoặc nhóm theo cột không có index buộc phải duyệt toàn bộ để sắp xếp.
CREATE INDEX idx_orders_created ON orders(created_at);

Lọc dữ liệu sớm hơn

-- Tệ: sắp xếp 10 triệu dòng, rồi bỏ đi 9,99 triệu
SELECT * FROM events ORDER BY ts DESC LIMIT 100;

-- Tốt hơn: lọc theo một tenant trước, sắp xếp một phần nhỏ dữ liệu
SELECT * FROM events WHERE tenant_id = 42 ORDER BY ts DESC LIMIT 100;

Bỏ DISTINCT hoặc ORDER BY không cần thiết trong CTE

-- Mỗi CTE có ORDER BY hoặc DISTINCT đều vật chất hóa một tập kết quả tạm.
-- Bỏ việc sắp xếp bên trong CTE trừ khi logic thực sự yêu cầu.
WITH recent AS (
  SELECT user_id, action FROM logs
  WHERE created_at > now() - interval '7 days'
  -- không ORDER BY ở đây
)
SELECT ...;

Phân giai đoạn kết quả trung gian lớn

Một truy vấn join 6 bảng và tổng hợp 50 triệu dòng là đang tự chuốc họa. Hãy chia nhỏ ra:

CREATE TEMP TABLE stage AS
  SELECT user_id, sum(amount) AS total
  FROM transactions
  WHERE status = 'completed'
  GROUP BY user_id;

CREATE INDEX ON stage(user_id);

SELECT u.name, s.total
FROM users u
JOIN stage s USING (user_id)
WHERE s.total > 1000;

Mỗi bước nhỏ hơn, dễ cho bộ lập kế hoạch xử lý, và dễ cho bạn debug hơn.

Kiểm Tra Sau Khi Sửa

Sau khi áp dụng cách sửa, kiểm tra xem truy vấn có hoàn thành không và lượng dùng file tạm có giảm không:

-- Xác nhận cài đặt hiện tại
SHOW work_mem;
SHOW temp_file_limit;

-- Tổng lượng dùng file tạm cho database hiện tại:
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

-- Các truy vấn đang chạy và lượng dùng file tạm (PostgreSQL 14+ với pg_stat_statements):
SELECT pid, left(query, 80) AS query_snippet, wait_event_type, state
FROM pg_stat_activity
WHERE state = 'active';

Chạy kiểm tra tích lũy trước và sau truy vấn của bạn. Nếu temp_bytes hầu như không thay đổi, dữ liệu đã được giữ trong RAM.

Phòng Ngừa

• Gán temp_file_limit theo role, không phải toàn cục. Phân chia hợp lý: 1GB cho người dùng ứng dụng, -1 cho role DBA và ETL.
• Điều chỉnh work_mem phù hợp với khối lượng công việc. Cơ sở dữ liệu OLAP thường có thể dùng 64–256MB toàn cục. Cơ sở dữ liệu OLTP nên giữ ở 4–16MB toàn cục và ghi đè theo phiên cho các truy vấn nặng.
• Bật log_temp_files để phát hiện các truy vấn mất kiểm soát trước khi chúng trở thành sự cố:

# postgresql.conf
log_temp_files = 0        # 0 = ghi log mỗi lần ghi file tạm
# log_temp_files = 10240  # hoặc chỉ ghi log các file lớn hơn 10 MB

• Đưa EXPLAIN ANALYZE vào quy trình review cho mọi truy vấn mới chạm đến hơn vài trăm nghìn dòng. Phát hiện một lần sắp xếp external merge trong môi trường phát triển là miễn phí. Phát hiện nó lúc 2 giờ sáng trên môi trường production thì không.