Sửa lỗi PostgreSQL 'index row size exceeds maximum 2712' trên cột văn bản dài

TL;DR

Các index B-tree trong PostgreSQL giới hạn mỗi entry ở khoảng 2712 byte — tức 1/3 của một trang 8 KB. Lưu bất kỳ giá trị nào dài hơn vào cột text hoặc varchar và lệnh CREATE INDEX sẽ thất bại ngay lập tức:

ERROR: index row size 3496 exceeds maximum 2712 for index "table_column_idx"
DETAIL: Values larger than 1/3 of a buffer page cannot be indexed.
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

Các giải pháp nhanh nhất:

• Chỉ index N ký tự đầu tiên: CREATE INDEX ON t (left(col, 255));
• Index hash MD5 cho tìm kiếm khớp chính xác: CREATE INDEX ON t (md5(col));
• Chuyển sang index HASH nếu bạn chỉ cần so sánh =.
• Dùng GIN index với pg_trgm cho tìm kiếm LIKE / tương đồng.

Nguyên nhân gốc rễ

Mỗi trang index trong PostgreSQL có kích thước 8 KB. Sau khi trừ overhead của trang, một entry B-tree đơn lẻ bị giới hạn ở floor((8192 − overhead) / 3) = 2712 byte. Giới hạn này được biên dịch cứng vào kích thước trang của PostgreSQL — bạn không thể thay đổi nó lúc runtime hay trong postgresql.conf.

Cột text lưu các giá trị lớn trong heap thông qua TOAST, đôi khi lên đến vài MB mỗi hàng. Hoàn toàn ổn. Nhưng entry index B-tree của hàng đó phải vừa trọn vẹn trên một trang. Chỉ một hàng có giá trị cột vượt quá ~2704 byte theo encoding UTF-8 là đủ để hủy toàn bộ lệnh CREATE INDEX.

Lỗi này thường xảy ra nhất trong ba tình huống:

• Thêm ràng buộc UNIQUE vào cột text hiện có mà đã chứa các hàng dài.
• Chạy CREATE INDEX trên cột URL, JSON blob, hoặc trường văn bản tự do.
• Migrate từ MySQL, nơi độ dài prefix index được xử lý khác nhau ở cấp DDL.

Các cách khắc phục

Tùy chọn 1 — Functional index trên prefix (cách sửa phổ biến nhất)

Phần lớn trường hợp, kiểm tra tính duy nhất hoặc so sánh bằng chỉ cần vài trăm ký tự đầu tiên. Hãy index prefix thay vì toàn bộ giá trị:

-- Index 255 ký tự đầu tiên (điều chỉnh theo nhu cầu)
CREATE UNIQUE INDEX table_column_prefix_idx
  ON my_table (left(column_name, 255));

Có một điểm cần lưu ý: các câu query của bạn phải dùng cùng một biểu thức để PostgreSQL sử dụng được index.

-- Câu này sẽ dùng index
SELECT * FROM my_table WHERE left(column_name, 255) = left($1, 255);

-- Câu này sẽ KHÔNG dùng index (toàn bộ cột)
SELECT * FROM my_table WHERE column_name = $1;

Với các index không unique dùng để tăng tốc tìm kiếm, cách tiếp cận prefix hoạt động tốt — miễn là các prefix trùng lặp là hiếm trong dữ liệu của bạn.

Tùy chọn 2 — Index hash MD5 (chỉ dành cho so sánh bằng chính xác)

Cần khớp hoặc loại trùng lặp toàn bộ giá trị? Hãy hash cột đó. MD5 luôn tạo ra chuỗi hex 32 ký tự, nằm rất an toàn dưới giới hạn 2712 byte:

CREATE UNIQUE INDEX table_column_md5_idx
  ON my_table (md5(column_name));

Mẫu query:

SELECT * FROM my_table WHERE md5(column_name) = md5($1);

Một lưu ý: MD5 có nguy cơ đụng độ (collision) về mặt lý thuyết (cực kỳ hiếm). Với các tác vụ loại trùng lặp nhạy cảm về bảo mật — chẳng hạn đầu vào từ người dùng không tin cậy — hãy thêm một bước kiểm tra bằng nhau ở tầng ứng dụng sau khi index lookup xác nhận hàng ứng viên.

Tùy chọn 3 — Loại index HASH

Loại index HASH gốc của PostgreSQL không có giới hạn kích thước trên giá trị được index. Nó chỉ hỗ trợ so sánh =, nhưng đã an toàn khi gặp sự cố (crash-safe) từ PostgreSQL 10.

CREATE INDEX table_column_hash_idx
  ON my_table USING hash (column_name);

Hãy dùng khi:

• Mọi query đều dùng = — không có LIKE, không sắp xếp, không quét phạm vi.
• Bạn không cần index để đảm bảo tính duy nhất.

Tùy chọn 4 — GIN index với pg_trgm (tìm kiếm LIKE / tương đồng)

Đang thực hiện tìm kiếm LIKE '%keyword%' trên cột văn bản dài? Extension trigram được tạo ra cho mục đích này:

-- Bật extension một lần cho mỗi database
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX table_column_trgm_idx
  ON my_table USING gin (column_name gin_trgm_ops);

Index này hỗ trợ cả ba mẫu query sau:

SELECT * FROM my_table WHERE column_name LIKE '%search_term%';
SELECT * FROM my_table WHERE column_name ILIKE '%search_term%';
SELECT * FROM my_table WHERE column_name % 'approximate_match';

Tùy chọn 5 — Rút ngắn kiểu cột

Đôi khi các giá trị dài là lỗi chất lượng dữ liệu, không phải thiết kế có chủ đích. Nếu vậy, hãy sửa chính cột đó:

-- Kiểm tra các vi phạm hiện có trước khi đổi kiểu
ALTER TABLE my_table
  ADD CONSTRAINT column_name_max_len CHECK (char_length(column_name)  2704) AS over_limit_rows
FROM my_table;

Nếu over_limit_rows trả về 0, vấn đề có thể là overhead của index đa cột hoặc không khớp encoding — không phải giá trị cột thô. Nếu lớn hơn 0, bạn cần một trong các tùy chọn ở trên.

Xác minh

Sau khi áp dụng cách sửa, hãy đảm bảo index được tạo thành công:

-- Kiểm tra index tồn tại và xem định nghĩa của nó
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'my_table';

-- Xác nhận không có index INVALID (index được tạo một phần sẽ xuất hiện ở đây)
SELECT relname, indisvalid
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
WHERE indrelid = 'my_table'::regclass;

Sau đó chạy EXPLAIN trên một query thực tế để xác nhận planner thực sự sử dụng nó:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM my_table WHERE left(column_name, 255) = left('some_long_value', 255);

Index Scan hoặc Index Only Scan trong kết quả có nghĩa là bạn đã thành công. Seq Scan có nghĩa planner đã bỏ qua index — hãy kiểm tra lại xem biểu thức trong query có khớp với biểu thức dùng trong CREATE INDEX không.

Hướng dẫn quyết định nhanh

• Cần ràng buộc unique trên toàn bộ giá trị? → Functional index MD5 + kiểm tra kép ở tầng ứng dụng
• Cần tìm kiếm bằng nhanh, không cần unique? → HASH index
• Cần tìm kiếm LIKE / mờ? → GIN + pg_trgm
• Cần sắp xếp hoặc truy vấn phạm vi trên prefix? → B-tree index left(col, N)
• Giá trị cột quá dài do nhầm lẫn? → Ràng buộc lại kiểu cột