Khắc phục lỗi 'integer out of range' trong PostgreSQL: Hướng dẫn di chuyển dữ liệu an toàn

Tình huống

Ứng dụng của bạn đang hoạt động mượt mà cho đến khi, đột nhiên, mọi lượt đăng ký người dùng mới hoặc đặt hàng đều thất bại. Nhật ký hệ thống (logs) của bạn hiện đang báo một thông báo duy nhất và đầy bí ẩn:

ERROR: integer out of range

Điều này thường xảy ra trong quá trình INSERT thông thường trên bảng hoạt động mạnh nhất của bạn. Nó có nghĩa là một cột—thường là khóa chính tự tăng—đã chạm tới giới hạn tuyệt đối. Cơ sở dữ liệu từ chối chấp nhận dữ liệu mới, làm tê liệt ứng dụng của bạn ngay lập tức.

Tại sao điều này xảy ra

PostgreSQL sử dụng kiểu số nguyên 32-bit có dấu cho kiểu dữ liệu INTEGER (hoặc INT4) tiêu chuẩn. Kiểu này có giới hạn cứng:

• Tối thiểu: -2,147,483,648
• Tối đa: 2,147,483,647

Khi sequence hoặc dữ liệu nhập thủ công của bạn cố gắng lưu trữ giá trị 2,147,483,648, hệ thống sẽ báo lỗi. Đây là một "vấn đề của sự thành công" điển hình. Cơ sở dữ liệu của bạn phát triển nhanh hơn dự kiến của thiết kế schema ban đầu, và bạn đã cạn kiệt 31 bit dành cho các số dương.

Cách khắc phục nhanh: Thay đổi trực tiếp

Các bảng nhỏ—có ít hơn 5 triệu hàng—thường có thể xử lý bằng cách thay đổi trực tiếp. Nếu bạn có thể chấp nhận vài phút ngoại tuyến hoặc khóa bảng tạm thời, hãy sử dụng BIGINT (INT8). Điều này mở rộng giới hạn của bạn lên tới 9,22 tỷ tỷ, con số gần như vô hạn đối với hầu hết các trường hợp sử dụng.

-- Thay đổi kiểu dữ liệu cột trực tiếp
ALTER TABLE orders ALTER COLUMN id TYPE bigint;

Cảnh báo: Hãy cẩn thận với câu lệnh này. Nó sẽ kích hoạt việc ghi lại toàn bộ bảng và giữ khóa ACCESS EXCLUSIVE. Trên một bảng có 200 million hàng, thao tác này có thể mất một giờ, trong thời gian đó không ai có thể đọc hoặc ghi vào bảng.

Cách khắc phục an toàn: Di chuyển không gây gián đoạn (Zero-Downtime)

Môi trường có lưu lượng truy cập cao đòi hỏi một cách tiếp cận tinh vi hơn. Bạn không thể khóa bảng chính trong nhiều giờ. Thay vào đó, hãy sử dụng chiến lược nhiều bước để di chuyển dữ liệu trong chế độ chạy ngầm.

1. Thêm một cột BIGINT tạm thời

Bắt đầu bằng cách tạo một cột mới, cột này sau đó sẽ trở thành khóa chính của bạn.

ALTER TABLE orders ADD COLUMN id_new bigint;

2. Đồng bộ dữ liệu theo từng đợt nhỏ

Đừng bao giờ cố gắng cập nhật 500 triệu hàng cùng một lúc. Bạn sẽ làm đầy Write-Ahead Log (WAL) và có khả năng làm treo máy chủ. Thay vào đó, hãy cập nhật theo từng đợt từ 50,000 đến 100,000 hàng.

-- Ví dụ về logic cập nhật theo đợt
UPDATE orders SET id_new = id 
WHERE id_new IS NULL 
AND id BETWEEN 1 AND 100000;

3. Giữ dữ liệu đồng bộ bằng trigger

Trong khi bạn di chuyển dữ liệu cũ, các hàng mới vẫn đang được thêm vào. Hãy tạo một trigger để tự động sao chép giá trị id sang id_new cho mỗi thao tác INSERT hoặc UPDATE mới.

4. Hoán đổi cuối cùng

Sau khi các cột đã được đồng bộ hóa, hãy thực hiện hoán đổi bên trong một transaction duy nhất. Điều này giúp giảm thời gian khóa bảng xuống chỉ còn vài mili giây.

BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
-- Hoán đổi tên cột
ALTER TABLE orders RENAME COLUMN id TO id_old;
ALTER TABLE orders RENAME COLUMN id_new TO id;
-- Cập nhật sequence để trỏ vào cột mới
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;
COMMIT;

Quản lý Sequence

Nếu bạn sử dụng các cột SERIAL, lỗi thường bắt nguồn từ dung lượng của cột chứ không phải từ bản thân sequence. Tuy nhiên, bạn phải đảm bảo sequence không bị đặt lại hoặc bị chậm hơn dữ liệu. Sử dụng câu lệnh này để kiểm tra vị trí hiện tại:

SELECT nextval('orders_id_seq');

Nếu quá trình di chuyển làm sequence bị lệch vị trí, hãy đồng bộ hóa nó với ID cao nhất trong bảng của bạn một cách thủ công:

SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));

Xác minh

Kiểm tra kết quả bằng cách mô tả bảng trong bảng điều khiển psql:

\d orders

Cột "Type" cho ID của bạn bây giờ sẽ hiển thị là bigint. Để xác nhận việc khắc phục, hãy thử chèn một giá trị mà trước đó lẽ ra đã thất bại:

INSERT INTO orders (id, status) VALUES (3000000000, 'đã xác minh');

Nếu hàng đó được lưu, vấn đề tràn số của bạn đã được giải quyết.

Những điểm chính cần lưu ý

• Mặc định sử dụng BIGINT: Đối với các dự án mới, hãy sử dụng BIGINT cho tất cả các khóa chính. Chi phí lưu trữ là 8 byte thay vì 4, nhưng đó là một cái giá rất nhỏ để tránh việc hệ thống bị ngừng hoạt động sau này.
• Kiểm tra khóa ngoại: Nếu bạn thay đổi khóa chính thành BIGINT, bạn cũng phải thay đổi mọi khóa ngoại tham chiếu đến nó. Nếu không, các thao tác JOIN sẽ thất bại hoặc trở nên cực kỳ chậm.
• Giám sát chủ động: Đừng đợi đến khi xảy ra sự cố. Hãy truy vấn information_schema.sequences thường xuyên để tìm các cột đã đạt đến 80% giới hạn 2,1 tỷ của chúng.