Các kỹ thuật tối ưu hóa PostgreSQL phi truyền thống

 (hakibenita.com)
6 điểm bởi GN⁺ 2026-01-21 | 1 bình luận | Chia sẻ qua WhatsApp
  • Giới thiệu 3 cách tiếp cận sáng tạo để tăng hiệu năng truy vấn PostgreSQL bằng những phương pháp vượt ra ngoài khuôn mẫu quen thuộc, không chỉ là thêm chỉ mục hay viết lại truy vấn
  • Loại bỏ full table scan dựa trên CHECK constraint, tối ưu cardinality thấp bằng chỉ mục dựa trên hàm, và triển khai ràng buộc unique bằng Hash index
  • 1. Dùng thiết lập constraint_exclusion để tránh các lần quét không cần thiết với những truy vấn có điều kiện sai
  • 2. Dùng chỉ mục dựa trên hàm và virtual generated column để giảm kích thước chỉ mục và đảm bảo tính nhất quán của truy vấn
  • 3. Kết hợp Hash index với exclusion constraint để triển khai hiệu quả ràng buộc unique cho cột văn bản lớn, giúp tiết kiệm đáng kể dung lượng lưu trữ

Loại bỏ full table scan dựa trên CHECK constraint

  • Dù đã có CHECK constraint chỉ cho phép các giá trị 'free', 'pro' trong cột plan, nếu chạy truy vấn sai với 'Pro' thì PostgreSQL vẫn quét toàn bộ bảng
    • Trong execution plan, hệ thống đọc toàn bộ 100.000 hàng nhưng kết quả thực tế là 0 hàng
  • Nếu đặt tham số constraint_exclusion thành 'on', PostgreSQL sẽ xét đến constraint và bỏ qua hoàn toàn việc quét
    • Thời gian thực thi giảm từ 7,4ms xuống 0,008ms
  • Giá trị mặc định là 'partition', và trong các truy vấn đơn giản, chi phí lập kế hoạch có thể còn tăng lên
    • Tuy vậy, trong môi trường BI và báo cáo, nơi người dùng thường nhập sai điều kiện, thiết lập 'on' lại rất hữu ích

Tối ưu cardinality thấp bằng chỉ mục dựa trên hàm

  • Khi chạy truy vấn tổng hợp doanh thu theo ngày trên bảng sale có 10 triệu bản ghi bán hàng, full scan mất 627ms
  • Nếu thêm B-Tree index cho cột sold_at, thời gian giảm còn 187ms, nhưng kích thước chỉ mục tăng lên 214MB
  • Nếu tạo chỉ mục dựa trên hàm trên biểu thức date_trunc('day', sold_at), kích thước giảm xuống còn 66MB và thời gian thực thi còn nhanh hơn, ở mức 145ms
    • Nhờ cardinality thấp nên có thể áp dụng index deduplication
  • Tuy nhiên, biểu thức trong truy vấn phải khớp chính xác với định nghĩa chỉ mục, nên cần duy trì tính nhất quán của biểu thức
    • Để làm điều này, có thể tạo VIEW chứa cùng biểu thức, hoặc
    • Thêm virtual generated column được hỗ trợ từ PostgreSQL 18 để tự động hóa sự nhất quán này
  • Khi dùng virtual generated column, chỉ mục sẽ được sử dụng tự động, đồng thời đạt được chỉ mục nhỏ hơn, truy vấn nhanh hơn và biểu thức nhất quán
  • Tuy nhiên, trong PostgreSQL 18, việc tạo chỉ mục trên virtual column vẫn chưa được hỗ trợ, và dự kiến sẽ có trong phiên bản 19

Triển khai ràng buộc unique bằng Hash index

  • Với bảng urls lưu các URL dài, nếu tạo unique index dựa trên B-Tree để ngăn URL trùng lặp, kích thước chỉ mục sẽ lên tới 154MB
  • Hash index nhỏ hơn nhiều vì không lưu giá trị thực mà chỉ lưu giá trị băm
    • PostgreSQL mặc định không hỗ trợ unique Hash index, nhưng
    • Có thể dùng exclusion constraint để triển khai vòng qua ràng buộc unique dưới dạng EXCLUDE USING HASH (url WITH =)
  • Với cách này, khi chèn dữ liệu trùng vẫn sẽ phát sinh lỗi, và hiệu năng truy vấn cũng nhanh hơn B-Tree (0.022ms so với 0.046ms)
  • Kích thước chỉ mục là 32MB, tức nhỏ hơn hơn 5 lần so với B-Tree
  • Nhược điểm:
    • Không thể tham chiếu bằng khóa ngoại (không dùng được ràng buộc REFERENCES)
    • Hạn chế tương thích với cú pháp INSERT ... ON CONFLICT
    • Có thể thay thế bằng cú pháp ON CONFLICT ON CONSTRAINT hoặc MERGE
  • Hash index phù hợp để đảm bảo tính unique cho các cột văn bản lớn, và là một phương án thay thế tiết kiệm không gian khi không cần khóa ngoại

Bài viết liên quan

1 bình luận

 
GN⁺ 2026-01-21
Ý kiến Hacker News

  • Chỉ mục có kích thước 214MB, tức khoảng bằng một nửa toàn bộ bảng
    Từ góc nhìn của nhà phân tích thì rất tốt, nhưng về hiệu năng ghi thì phát sinh vấn đề write amplification
    Chỉ mục được thiết kế khác nhau tùy theo tỷ lệ đọc/ghi, và đó cũng là lý do tồn tại của data warehouse hoặc read replica
    Nếu phải phục vụ quá nhiều người dùng thì tốt hơn là không đặt các chỉ mục BI/OLAP trên DB OLTP

    • Tôi nghĩ sẽ rất tốt nếu PostgreSQL hỗ trợ clustered index (Index Organized Table của Oracle)
      Nếu mẫu truy cập bảng ổn định, bản thân bảng có thể trở thành chỉ mục để đạt hiệu quả mà không gây write amplification

  • Tôi nghĩ ở ví dụ đầu tiên, định nghĩa Plan dưới dạng enum type sẽ tốt hơn
    Nó nhẹ hơn văn bản, và khi nhập bộ lọc sai thì sẽ trả về lỗi thay vì kết quả rỗng, nên an toàn hơn

  • Đây là một bài viết xuất sắc. Tôi đã dùng PostgreSQL và MySQL hàng chục năm, nhưng đọc xong vẫn thấy mình mới chỉ biết một phần nhỏ của những gì có thể làm được

    • Tôi cũng đã dùng Postgres hơn 10 năm, nhưng mỗi lần đọc tài liệu vẫn có cảm giác mình chỉ đang gãi trên bề mặt. Một hệ thống thật sự mạnh mẽ
    • PostgreSQL giống như Emacs. Bề ngoài có vẻ đơn giản nhưng thực chất có độ linh hoạt gần như cấp hệ điều hành

  • Phần thú vị nhất với tôi là cú pháp MERGE được nhắc ở cuối bài
    Bình thường tôi xử lý upsert bằng INSERT ... ON CONFLICT DO UPDATE, nhưng MERGE có vẻ mạnh hơn và dùng được trong nhiều tình huống hơn

    • MERGE đã có trong chuẩn SQL từ lâu, nhưng Postgres trì hoãn triển khai vì vấn đề không nguyên tử trong mô hình MVCC
      Bài viết trên blog pganalyze cũng có giải thích
      Cá nhân tôi thích INSERT ... ON CONFLICT hơn, chỉ dùng MERGE khi thật sự cần và xử lý lỗi thật cẩn thận
    • Xét về đồng thời, INSERT ... ON CONFLICT dễ dự đoán hơn
      Xem thêm bài so sánh của modern-sql.com
    • Nếu là chèn batch số lượng lớn thì dùng COPY INTO với định dạng binary là nhanh nhất. Gần như không có overhead phía máy chủ

  • Chỉ mục BRIN không được đề cập trong bài cũng rất đáng chú ý
    Nếu dữ liệu tăng đơn điệu, đây là loại chỉ mục rất nhỏ và nhanh, rất lý tưởng

    • Không nhất thiết phải hoàn toàn đơn điệu. Chỉ cần phần lớn là đơn điệu thì vẫn hoạt động rất tốt
      Ví dụ như dữ liệu timestamp nhận từ máy chủ, dù thứ tự có hơi lệch một chút cũng vẫn phù hợp
      Với UUIDv7, có thể cần điều chỉnh pages_per_range

  • Tôi luôn thấy tiếc vì hash index không thể áp đặt ràng buộc duy nhất
    Có vẻ chỉ cần một chút glue code để chuyển nó thành exclusion constraint là giải quyết được, nên tôi thắc mắc vì sao đến giờ vẫn chưa có

  • Việc kiểm tra tính duy nhất dựa trên hash không được hỗ trợ trong chỉ mục vì không xử lý được va chạm
    Giải pháp được đề xuất cũng gặp đúng vấn đề đó

    • Hoàn toàn không đúng. Chỉ mục chỉ lưu hash, nhưng bảng thì lưu toàn bộ giá trị
      Postgres chỉ xem là trùng lặp khi cả hash lẫn giá trị thực đều khớp
    • Có thể kiểm chứng trong ví dụ dbfiddle

  • Nội dung bài viết khá mới mẻ. Cột ảo và hash index rất thú vị, nhưng tôi vẫn có cảm giác chúng chưa thực sự được tích hợp hoàn toàn vào hệ sinh thái

    • Cột ảo gần như đã hoàn thiện. Phần lớn đã được triển khai trong PostgreSQL 18
      Hash index trước đây bị hạn chế nhiều, nhưng đang dần được cải thiện; ràng buộc duy nhất tự động là phần việc còn lại

  • Tôi nghĩ dùng stored generated column thì có thể tạo chỉ mục ngay mà?

    • Bài viết có giải thích vì sao muốn tránh cách này
      PostgreSQL hỗ trợ từ phiên bản 14, nhưng vì kết quả được lưu vật lý và chiếm thêm dung lượng lưu trữ
    • Không biết liệu có thể tạo partial index dựa trên biểu thức hay không
    • Cuối cùng thì vẫn làm tăng dung lượng lưu trữ, nên trong ví dụ của bài, đây là hướng muốn tránh

  • Từ khi chuyển lên cloud, tôi ít còn tự trực tiếp xử lý pgsql trong môi trường máy chủ cố định
    Tôi tò mò phần tô sáng cú pháp SQL trong bài là tính năng tích hợp sẵn hay là công cụ riêng

    • Tôi dùng pgcli. Nó có nhiều tính năng tiện như hiển thị trạng thái transaction, tự động hoàn thành, tô sáng cú pháp
      Tuy nhiên, khi sao chép truy vấn dài thì sau mỗi lần xuống dòng nó tự động thêm khoảng trắng, hơi bất tiện
    • Nếu dùng IDE như IntelliJ thì cũng có thể có cả tô sáng cú pháp lẫn tự động hoàn thành