Cột generated của PostgreSQL để tăng tốc bộ lọc trang quản trị

Tại sao giao diện quản trị nhanh chậm và dễ rối\n\nGiao diện quản trị thường bắt đầu đơn giản: một bảng, vài bộ lọc, có thể một sắp xếp theo “mới nhất trước”. Rồi công việc thực sự bắt đầu. Support muốn tìm khách hàng theo tên, email, và số điện thoại. Sales muốn sắp xếp theo “hoạt động gần nhất”. Finance muốn “số dư quá hạn”. Mỗi yêu cầu thêm điều kiện, join, và tính toán phụ.\n\nHầu hết danh sách admin chậm vì cùng một lý do: mỗi lần click lại thay đổi truy vấn. Lọc và sắp xếp có thể buộc cơ sở dữ liệu phải quét nhiều hàng, đặc biệt khi truy vấn phải tính một giá trị cho từng hàng trước khi quyết định hàng nào khớp.\n\nMột bước ngoặt phổ biến là khi WHERE và ORDER BY đầy các biểu thức. Thay vì lọc trên một cột thuần, bạn lọc trên lower(email), date_trunc('day', last_seen_at), hoặc một CASE gom nhiều trạng thái vào một “bucket”. Những biểu thức đó không chỉ chậm hơn. Chúng làm SQL khó đọc hơn, khó đánh chỉ mục hơn, và dễ sai hơn.\n\nSQL quản trị lộn xộn thường đến từ vài mẫu lặp lại:\n\n- Một ô “search” kiểm tra nhiều trường với quy tắc khác nhau\n- Sắp xếp theo giá trị dẫn xuất (tên đầy đủ, điểm ưu tiên, “sự kiện có ý nghĩa gần nhất”)\n- Quy tắc nghiệp vụ sao chép qua nhiều màn hình (active vs inactive, paid vs overdue)\n- Các thủ thuật “giúp đỡ” nhỏ (trim, lower, coalesce) rải rác khắp nơi\n- Cùng một giá trị tính toán được dùng trong danh sách, bộ lọc, và sắp xếp\n\nNhóm thường cố che điều này ở lớp app: bộ tạo truy vấn động, join có điều kiện, hoặc tiền tính giá trị trong code. Cách này có thể hoạt động, nhưng tách logic giữa UI và database, khiến việc debug truy vấn chậm trở nên đau đầu.\n\nMục tiêu đơn giản: truy vấn nhanh và vẫn dễ đọc. Khi một giá trị tính toán xuất hiện nhiều lần qua các màn hình quản trị, cột generated của PostgreSQL giúp gom quy tắc vào một chỗ trong khi vẫn cho phép database tối ưu hoá.\n\n## Cột generated nói dễ hiểu là gì\n\nCột generated là một cột bình thường trong bảng mà giá trị của nó được tính từ các cột khác. Bạn không ghi giá trị đó trực tiếp. PostgreSQL sẽ điền nó theo biểu thức bạn định nghĩa.\n\nỞ PostgreSQL, cột generated được lưu trên đĩa. PostgreSQL tính giá trị khi một hàng được insert hoặc update, rồi lưu như mọi cột khác. Điều này thường là điều bạn muốn cho màn hình admin: đọc nhanh, và có thể đánh chỉ mục giá trị tính toán.\n\nĐiều này khác với việc lặp lại cùng phép tính trong mọi truy vấn. Nếu bạn cứ viết WHERE lower(email) = lower($1) hoặc sắp xếp bằng last_name || ', ' || first_name, bạn trả phí tính toán nhiều lần và SQL trở nên ồn ào. Cột generated chuyển phép tính lặp này vào định nghĩa bảng. Truy vấn của bạn đơn giản hơn, và kết quả nhất quán ở mọi chỗ.\n\nKhi dữ liệu nguồn thay đổi, PostgreSQL tự cập nhật giá trị generated cho hàng đó. Ứng dụng không phải nhớ giữ đồng bộ.\n\nMột mô hình tư duy hữu ích:\n\n- Định nghĩa công thức một lần.\n- PostgreSQL tính lúc ghi.\n- Truy vấn đọc nó như cột bình thường.\n- Vì được lưu, bạn có thể đánh chỉ mục nó.\n\nNếu sau này bạn thay đổi công thức, bạn cần thay đổi schema. Hãy lập kế hoạch như bất kỳ migration nào, vì các hàng hiện có sẽ được cập nhật để khớp biểu thức mới.\n\n## Những trường hợp nên dùng trường tính toán cho lọc và sắp xếp\n\nCột generated tỏa sáng khi giá trị luôn được dẫn xuất từ các cột khác và bạn thường xuyên lọc hoặc sắp xếp theo nó. Chúng ít hữu ích cho các báo cáo dùng một lần.\n\n### Trường tìm kiếm mà người dùng thực sự dùng\n\nTìm kiếm trong admin hiếm khi “thuần”. Người dùng mong hộp tìm kiếm xử lý văn bản bừa bộn, chữ hoa/chữ thường không nhất quán, và khoảng trắng thừa. Nếu bạn lưu một “search key” generated đã được chuẩn hoá, WHERE sẽ giữ được dễ đọc và hành vi đồng nhất trên các màn hình.\n\nỨng viên tốt gồm tên đầy đủ ghép lại, text đã lower và trim cho tìm kiếm không phân biệt hoa thường, phiên bản đã dọn khoảng trắng, hoặc nhãn trạng thái dẫn xuất từ nhiều cột.\n\nVí dụ: thay vì lặp lower(trim(first_name || ' ' || last_name)) trong mọi truy vấn, tạo full_name_key một lần và lọc trên đó.\n\n### Khóa sắp xếp theo cách con người sắp\n\nSắp xếp là nơi trường tính toán thường đem lại lợi ích nhanh nhất, vì sắp xếp có thể buộc PostgreSQL đánh giá biểu thức cho nhiều hàng.\n\nKhóa sắp xếp phổ biến gồm rank số (gói dịch vụ map thành 1, 2, 3), timestamp “hoạt động mới nhất” (ví dụ max của hai timestamp), hoặc mã được padding để sắp đúng khi là text.\n\nKhi khóa sắp xếp là một cột thuần đã được đánh chỉ mục, ORDER BY rẻ hơn nhiều.\n\n### Cờ dẫn xuất cho bộ lọc nhanh\n\nNgười dùng admin thích checkbox như “Overdue” hoặc “High value”. Những cái này hoạt động tốt như cột generated khi logic ổn định và chỉ dựa trên dữ liệu trong hàng.\n\nVí dụ, nếu danh sách khách cần “Has unread messages” và “Is overdue”, một boolean has_unread (từ unread_count > 0) và is_overdue (từ due_date < now() và paid_at is null) cho phép bộ lọc UI map thành điều kiện đơn giản.\n\n## Chọn giữa generated columns, indexes, và các tùy chọn khác\n\nGiao diện quản trị cần ba thứ: lọc nhanh, sắp xếp nhanh, và SQL bạn vẫn đọc được sau nhiều tháng. Quyết định thực sự là nơi đặt phép tính: trong bảng, trong chỉ mục, trong view, hay trong code app.\n\nGenerated columns phù hợp khi bạn muốn giá trị hành xử như cột thực: dễ tham chiếu, hiển thị trong select, và khó quên khi thêm bộ lọc mới. Chúng cũng kết hợp tự nhiên với chỉ mục bình thường.\n\nExpression indexes có thể nhanh để thêm vì bạn không thay đổi định nghĩa bảng. Nếu bạn chủ yếu quan tâm tốc độ và không ngại SQL hơi xấu, expression index thường đủ. Nhược điểm là độ đọc kém hơn, và bạn phụ thuộc vào planner khớp đúng biểu thức.\n\nViews hữu ích khi bạn muốn một “hình dạng” dữ liệu chia sẻ, đặc biệt nếu danh sách admin join nhiều bảng. Nhưng view phức tạp có thể che giấu công việc tốn kém và thêm một nơi khác để debug.\n\nTriggers có thể giữ một cột bình thường đồng bộ, nhưng thêm nhiều thành phần động. Chúng có thể làm bulk update chậm hơn và dễ bị bỏ sót khi troubleshooting.\n\nĐôi khi lựa chọn tốt nhất là một cột bình thường được app điền. Nếu người dùng có thể chỉnh sửa nó, hoặc nếu công thức thay đổi thường theo quyết định nghiệp vụ (không chỉ dữ liệu hàng), giữ rõ ràng sẽ dễ hiểu hơn.\n\nMột cách nhanh để chọn:\n\n- Muốn truy vấn dễ đọc và công thức ổn định chỉ dựa trên dữ liệu hàng? Dùng generated column.\n- Muốn tốc độ cho một bộ lọc cụ thể và không ngại SQL lộn xộn? Dùng expression index.\n- Cần hình dạng dữ liệu join, dùng cho báo cáo nhiều nơi? Xem xét view.\n- Cần logic cross-table hoặc side effects? Ưu tiên logic ở app trước, triggers là lựa chọn cuối cùng.\n\n## Từng bước: thêm cột generated và dùng trong truy vấn\n\nBắt đầu với một truy vấn danh sách admin chậm mà bạn cảm nhận được trong UI. Ghi ra các bộ lọc và sắp xếp mà màn hình dùng nhiều nhất. Cải thiện truy vấn đó trước.\n\nChọn một trường tính toán loại bỏ công việc lặp, và đặt tên rõ ràng theo snake_case để người khác đoán được nội dung mà không phải đọc lại biểu thức.\n\n### 1) Thêm cột generated (STORED)\n\nsql\nALTER TABLE customers\nADD COLUMN full_name_key text\nGENERATED ALWAYS AS (\n lower(concat_ws(' ', last_name, first_name))\n) STORED;\n\n\nXác thực trên hàng thực trước khi thêm chỉ mục:\n\nsql\nSELECT id, first_name, last_name, full_name_key\nFROM customers\nORDER BY id DESC\nLIMIT 5;\n\n\nNếu kết quả sai, sửa biểu thức ngay. STORED nghĩa là PostgreSQL sẽ giữ nó cập nhật cho mọi insert và update.\n\n### 2) Thêm chỉ mục khớp với màn hình admin\n\nNếu màn hình admin lọc theo status và sắp xếp theo tên, đánh chỉ mục theo mẫu đó:\n\nsql\nCREATE INDEX customers_status_full_name_key_idx\nON customers (status, full_name_key);\n\n\n### 3) Cập nhật truy vấn admin để dùng cột mới\n\nTrước đây, bạn có thể có một ORDER BY lộn xộn. Sau khi có cột mới, rõ ràng hơn:\n\nsql\nSELECT id, status, first_name, last_name\nFROM customers\nWHERE status = 'active'\nORDER BY full_name_key ASC\nLIMIT 50 OFFSET 0;\n\n\nDùng cột generated cho những phần người dùng thường lọc và sắp xếp hàng ngày, không phải cho màn hình hiếm khi dùng.\n\n## Mẫu chỉ mục khớp với hành vi thực tế của admin\n\nGiao diện admin lặp lại vài hành vi: lọc theo vài trường, sắp xếp theo một cột, và phân trang. Thiết lập tốt nhất hiếm khi là “đánh chỉ mục mọi thứ”. Là “đánh chỉ mục đúng hình dạng của truy vấn phổ biến nhất.”\n\nMột quy tắc thực tiễn: đặt các cột lọc phổ biến nhất lên trước, và cột sắp xếp phổ biến nhất ở cuối. Nếu bạn multi-tenant, workspace_id (hoặc tương tự) thường đứng đầu: (workspace_id, status, created_at).\n\nTìm kiếm văn bản là câu chuyện riêng. Nhiều ô tìm kiếm kết thúc với ILIKE '%term%', điều này khó tăng tốc với chỉ mục btree cơ bản. Một mẫu hữu ích là tìm trên một cột helper đã chuẩn hoá thay vì text thô (lowercase, trim, có thể ghép). Nếu UI của bạn có thể dùng tìm kiếm tiền tố (term%), một chỉ mục btree trên cột chuẩn hoá đó có thể giúp. Nếu phải chứa (%term%), hãy cân nhắc thắt chặt hành vi UI cho bảng lớn (ví dụ “email bắt đầu bằng”), hoặc giới hạn tìm kiếm trong một tập nhỏ hơn.\n\nCũng kiểm tra độ chọn lọc trước khi thêm chỉ mục. Nếu 95% hàng có cùng giá trị (như status = 'active'), đánh chỉ mục cột đó một mình sẽ ít có ích. Ghép với cột chọn lọc hơn, hoặc dùng partial index cho trường hợp thiểu số.\n\n## Ví dụ thực tế: danh sách khách vẫn nhanh\n\nHình dung một trang admin khách điển hình: ô tìm kiếm, vài bộ lọc (inactive, khoảng số dư), và cột “Last seen” có thể sắp xếp. Theo thời gian nó biến thành SQL khó đọc: LOWER(), TRIM(), COALESCE(), toán ngày, và CASE lặp lại qua các màn hình.\n\nMột cách giữ nhanh và dễ đọc là dồn các biểu thức lặp vào cột generated.\n\n### Bảng và các cột generated\n\nGiả sử bảng customers với name, email, last_seen, và balance. Thêm ba trường tính toán:\n\n- search_key: một chuỗi text đã chuẩn hoá cho tìm kiếm đơn giản\n- is_inactive: boolean để lọc mà không lặp logic ngày tháng\n- balance_bucket: nhãn để phân đoạn nhanh\n\nsql\nALTER TABLE customers\n ADD COLUMN search_key text\n GENERATED ALWAYS AS (\n lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))\n ) STORED,\n ADD COLUMN is_inactive boolean\n GENERATED ALWAYS AS (\n last_seen IS NULL OR last_seen < (now() - interval '90 days')\n ) STORED,\n ADD COLUMN balance_bucket text\n GENERATED ALWAYS AS (\n CASE\n WHEN balance < 0 THEN 'negative'\n WHEN balance < 100 THEN '0-99'\n WHEN balance < 500 THEN '100-499'\n ELSE '500+'\n END\n ) STORED;\n\n\nBây giờ truy vấn admin đọc giống như UI.\n\n### Lọc + sắp xếp dễ đọc\n\n“Khách không hoạt động, sắp xếp theo hoạt động mới nhất” trở thành:\n\nsql\nSELECT id, name, email, last_seen, balance\nFROM customers\nWHERE is_inactive = true\nORDER BY last_seen DESC NULLS LAST\nLIMIT 50;\n\n\nVà một tìm kiếm cơ bản là:\n\nsql\nSELECT id, name, email, last_seen, balance\nFROM customers\nWHERE search_key LIKE '%' || lower(trim($1)) || '%'\nORDER BY last_seen DESC NULLS LAST\nLIMIT 50;\n\n\nLợi ích thực sự là tính nhất quán. Cùng các trường cung cấp năng lượng cho nhiều màn hình mà không phải viết lại logic:\n\n- Hộp tìm kiếm danh sách khách dùng search_key\n- Tab “Inactive customers” dùng is_inactive\n- Các chip lọc số dư dùng balance_bucket\n\n## Sai lầm phổ biến và bẫy\n\nCột generated có vẻ là giải pháp đơn giản: đặt toán học vào bảng và giữ truy vấn sạch sẽ. Chúng chỉ giúp khi khớp với cách màn hình lọc và sắp xếp, và khi bạn thêm chỉ mục đúng.\n\nSai lầm phổ biến nhất:\n\n- Cho rằng nó tự động nhanh hơn mà không đánh chỉ mục. Giá trị tính toán vẫn cần chỉ mục để lọc hoặc sắp xếp nhanh ở quy mô lớn.\n- Nhồi quá nhiều logic vào một trường. Nếu cột generated biến thành một chương trình nhỏ, người ta sẽ ngừng tin tưởng nó. Giữ ngắn và đặt tên rõ ràng.\n- Dùng hàm không bất biến. PostgreSQL yêu cầu biểu thức cho stored generated column là immutable. Các hàm như now() và random() phá vỡ kỳ vọng và thường không được cho phép.\n- Bỏ qua chi phí ghi. Insert và update phải duy trì giá trị tính toán. Đọc nhanh hơn sẽ không đáng nếu import và tích hợp bị chậm quá nhiều.\n- Tạo các cột gần như trùng lặp. Chuẩn hoá một hai mẫu (như một khoá chuẩn hoá duy nhất) thay vì tích tụ năm cột tương tự.\n\nNếu danh sách admin của bạn có tìm kiếm chứa (ILIKE '%ann%'), một cột generated một mình sẽ không cứu vãn. Bạn có thể cần phương án tìm kiếm khác. Nhưng với các truy vấn “lọc và sắp xếp” hàng ngày, cột generated cộng với chỉ mục phù hợp thường làm cho hiệu năng dự đoán hơn nhiều.\n\n## Danh sách kiểm tra nhanh trước khi phát hành\n\nTrước khi đẩy thay đổi ra màn hình admin, xác nhận rằng giá trị tính toán, truy vấn và chỉ mục khớp nhau.\n\n- Công thức ổn định và dễ giải thích trong một câu.\n- Truy vấn thực sự dùng cột generated trong WHERE và/hoặc ORDER BY.\n- Chỉ mục khớp với cách dùng thực tế, không phải thử nghiệm một lần.\n- Bạn so sánh kết quả với logic cũ trên các edge case (NULL, chuỗi rỗng, khoảng trắng lạ, chữ hoa trộn lẫn).\n- Bạn kiểm tra hiệu năng ghi nếu bảng bận (import, cập nhật nền, tích hợp).\n\n## Bước tiếp theo: áp dụng cho màn hình admin của bạn\n\nChọn một điểm khởi đầu nhỏ nhưng có tác động lớn: 2-3 màn hình admin người ta mở hàng ngày (orders, customers, tickets). Ghi lại chỗ cảm thấy chậm (bộ lọc theo ngày, sắp xếp theo “last activity”, tìm kiếm theo tên ghép, lọc theo nhãn trạng thái). Rồi tiêu chuẩn hoá một tập ngắn các trường tính toán có thể tái sử dụng qua các màn hình.\n\nKế hoạch triển khai dễ đo lường và dễ hoàn tác:\n\n- Thêm cột generated với tên rõ ràng.\n- Chạy cặp song song cũ và mới trong thời gian ngắn nếu bạn thay thế logic có sẵn.\n- Thêm chỉ mục khớp với bộ lọc/sắp xếp chính.\n- Chuyển truy vấn màn hình dùng cột mới.\n- Đo trước và sau (thời gian truy vấn và số hàng quét), rồi xoá cách làm cũ.\n\nNếu bạn xây dựng công cụ admin nội bộ trên AppMaster (appmaster.io), những trường tính toán này phù hợp tốt vào mô hình dữ liệu chia sẻ: database mang quy tắc, và bộ lọc UI có thể trỏ tới tên trường rõ ràng thay vì lặp biểu thức qua các màn hình.