Aiden Tran
Quay lại bài viết
29 thg 5, 2026
25 min read

Deep Dive: PostgreSQL Index — Vì Sao Index Tăng Tốc Query, Và Khi Nào Postgres Bỏ Qua Nó

Bạn có một bảng orders 50 triệu dòng. Câu SELECT * FROM orders WHERE customer_id = 12345 chạy mất 8 giây — EXPLAIN cho thấy một Seq Scan (sequential scan: quét tuần tự toàn bộ bảng từ đầu đến cuối). Bạn tạo một index trên customer_id, chạy lại, và query trả về trong 1 ms. Cảm giác như phép màu.

Phấn khích, bạn làm điều tương tự cho câu SELECT * FROM orders WHERE status = 'pending' — vốn cũng đang chậm. Bạn tạo index trên status, chạy EXPLAIN… và PostgreSQL vẫn chọn Seq Scan, phớt lờ hoàn toàn cái index bạn vừa tạo. Index nằm đó, tốn dung lượng đĩa, làm chậm mọi câu INSERT, nhưng không được dùng. Vì sao cùng một thao tác “tạo index” lại cho hai kết quả trái ngược?

Câu trả lời không nằm ở index — nó nằm ở cách index trỏ vào dữ liệu thật trên đĩa, và ở phép tính chi phí mà query planner thực hiện trước mỗi câu query. Bài viết này đi sâu vào B-tree index của PostgreSQL: từ cấu trúc cây, cách leaf node trỏ tới row qua ctid, sức mạnh duyệt cây theo cấp logarit, cho đến lý do random I/O khiến planner đôi khi quay lưng với index. Bài này nối tiếp Deep Dive về PostgreSQL Storage Internals (heap file, page, tuple, ctid) — nên ở đâu cần khái niệm storage tầng dưới, mình sẽ link sang đó thay vì giải thích lại.

1. Index là gì — và vì sao nó là “thuần dư thừa”

Một câu nói gọn: index làm cho query nhanh hơn. Nhưng để hiểu khi nào nó nhanh và khi nào không, ta cần hiểu chính xác index là gì ở mức vật lý.

Index là một cấu trúc dữ liệu riêng biệt, nằm trong file riêng trên đĩa, lưu một bản sao của (các) cột được lập chỉ mục, đã được sắp xếp, kèm theo con trỏ tới row tương ứng trong bảng. Hãy để ý ba chi tiết:

  1. Index không thay đổi dữ liệu bảng. Tạo index không đụng gì tới heap (vùng chứa dữ liệu thật của bảng). Nó chỉ tạo thêm một cấu trúc mới trỏ về bảng. Bạn có thể DROP INDEX bất cứ lúc nào mà không mất dữ liệu.
  2. Index chiếm không gian đĩa riêng. Nó là một relation riêng (relfilenode riêng — xem Storage Internals), có FSM/VM forks riêng, và lớn lên theo số row của bảng.
  3. Index là sự dư thừa thuần túy. Mọi giá trị trong index đều đã có sẵn trong bảng. Index không thêm thông tin mới — nó chỉ sắp xếp lại một bản sao để tra cứu nhanh.

Chính vì là dư thừa, index có cái giá của nó: mỗi INSERT/UPDATE/DELETE chạm tới cột được index đều phải cập nhật cả index, và mỗi index “ăn” thêm đĩa lẫn shared buffer. Đây là đánh đổi cốt lõi — đọc nhanh hơn, đổi lấy ghi chậm hơn và tốn storage hơn.

Vậy quyết định tạo index dựa trên cái gì? Câu trả lời quan trọng nhất của toàn bộ chủ đề này:

Thông tin quan trọng nhất để lập chỉ mục không phải là cấu trúc bảng, mà là cách ứng dụng của bạn truy vấn dữ liệu — những cột nào xuất hiện trong WHERE, JOIN, ORDER BY, và với độ chọn lọc (selectivity) ra sao.

Một index được thiết kế theo cấu trúc bảng (ví dụ “cứ index mọi khóa ngoại”) thường lãng phí; một index được thiết kế theo query pattern thực tế mới tạo ra khác biệt. Ta sẽ quay lại nguyên tắc này ở mục 8.

PostgreSQL có nhiều loại index khác nhau — Hash, GiST, GIN, BRIN, SP-GiST — mỗi loại tối ưu cho một kiểu truy vấn riêng. Bài này tập trung hoàn toàn vào B-tree, loại index mặc định (CREATE INDEX không chỉ định loại sẽ tạo B-tree) và chiếm đại đa số index trong thực tế.

2. Cấu trúc B-tree: internal node định tuyến, leaf node giữ (key, ctid)

B-tree là một cấu trúc cây mà mọi đường đi từ gốc xuống lá đều có cùng độ dài. “Cân bằng” ở đây nghĩa là cây không bao giờ bị lệch một bên; thêm/xóa phần tử luôn giữ cho mọi lá ở cùng một độ sâu. Đây là tính chất nền tảng cho hiệu năng mà ta sẽ thấy ở mục 4.

Chính xác hơn, index của PostgreSQL là một biến thể B+ tree: toàn bộ dữ liệu (key + con trỏ) nằm ở leaf node (nút lá), còn các node bên trên chỉ chứa separator key (khóa phân tách) để định tuyến. PostgreSQL hiện thực phiên bản Lehman & Yao B-link tree — một biến thể thêm con trỏ “right-link” nối các node cùng tầng, cho phép nhiều transaction đọc/ghi đồng thời mà hạn chế khóa (lock). Bạn không cần nhớ tên này, chỉ cần nắm ba tầng vai trò:

Một chi tiết quan trọng: các leaf node được nối với nhau thành một danh sách liên kết hai chiều (doubly-linked list). Mỗi leaf giữ con trỏ tới leaf bên trái và bên phải. Nhờ đó, sau khi tra cứu xuống đúng leaf chứa key bắt đầu, PostgreSQL có thể đi ngang dọc chuỗi leaf để đọc một dải giá trị liên tiếp (WHERE created_at BETWEEN ... AND ..., hoặc ORDER BY) mà không phải quay lên gốc — và đi được cả hai chiều, nên ORDER BY ... DESC cũng tận dụng được cùng một index.

Yếu tố làm B-tree mạnh là fanout (số con tối đa của một node) cực lớn. Mỗi node là một page 8 KB; một entry trong internal node chỉ gồm key + con trỏ (vài chục byte), nên một node chứa được hàng trăm entry. Fanout hàng trăm là chìa khóa cho mục 4.

3. Leaf node trỏ tới row trên heap thế nào — ctid

Đây là cơ chế cốt lõi của index trong PostgreSQL, và cũng là gốc rễ của vấn đề random I/O ở mục 6.

Trong PostgreSQL, mỗi entry ở leaf node là một cặp (giá trị key, ctid). ctid là địa chỉ vật lý của row trong heap, gồm hai phần: (block_number, item_offset).

Khi cần lấy row thật từ một entry leaf, PostgreSQL làm đúng hai bước:

  1. Dựa vào block_number, nó biết phải nạp page (block) số mấy từ heap file lên shared buffer (đơn vị nạp là cả một page 8 KB — xem Storage Internals).
  2. Trong page đó, nó dùng item_offset để tra vào line pointer array — line_pointer_array[item_offset] cho ra một con trỏ, và con trỏ này mới trỏ tới tuple (row vật lý) cần tìm.

Vì sao phải qua một tầng gián tiếp là line pointer thay vì trỏ thẳng vào offset của tuple? Vì khi VACUUM dọn dead tuple và dồn lại page (defragmentation), các tuple bị dịch chuyển vị trí trong page. Line pointer cho phép tuple di chuyển bên trong page mà index không cần cập nhật — chỉ cần line pointer được cập nhật. Chi tiết cơ chế này nằm ở Storage Internals.

Cách tổ chức này được gọi là heap-organized table: dữ liệu row sống trong heap, và mọi index — kể cả index trên primary key — đều là cấu trúc riêng bên ngoài, trỏ vào heap qua ctid. Không có index nào “đặc biệt” hơn index nào; tất cả đều bình đẳng và đều phải fetch heap để lấy row.

Mô hình heap + ctid này có một hệ quả lớn: vì row không được sắp xếp vật lý theo bất kỳ index nào, các entry liên tiếp trên leaf có thể trỏ tới những row nằm rải rác khắp heap. Đây chính là mầm mống của random I/O — ta sẽ thấy ở mục 6.

4. Sức mạnh thứ nhất: duyệt cây và khả năng mở rộng logarit

Việc duyệt cây từ gốc xuống lá là thao tác cực kỳ hiệu quả — hiệu quả đến mức Markus Winand (tác giả Use The Index, Luke) gọi nó là sức mạnh thứ nhất của việc lập chỉ mục (the first power of indexing). Nó hoạt động gần như tức thời, kể cả trên tập dữ liệu khổng lồ. Hai lý do:

Thứ nhất, cây cân bằng. Vì mọi leaf ở cùng độ sâu, mọi key đều cách gốc đúng cùng một số bước. Không có key nào “may mắn” gần gốc hay “xui xẻo” tận đáy sâu. Hiệu năng tra cứu là đồng đều và dự đoán được.

Thứ hai, độ sâu tăng theo cấp logarit. Đây là phần đáng kinh ngạc. Nhờ fanout hàng trăm (mục 2), số leaf mà cây “với tới” tăng theo lũy thừa của độ sâu, nên độ sâu tăng cực kỳ chậm so với số lượng record:

Với fanout vài trăm, độ sâu cây ≈ log_fanout(số_row). Trong thực tế, các index với hàng triệu record chỉ có độ sâu khoảng bốn hoặc năm. Một index trên một tỉ row vẫn chỉ cần ~5 tầng. Độ sâu bằng sáu hầu như không bao giờ thấy. Nghĩa là: tra cứu một key trong bảng một tỉ dòng chỉ tốn ~5 lần đọc node — và các node trên cao (root, internal tầng đầu) gần như luôn nằm sẵn trong shared buffer. Đó là lý do “tìm theo index” cho cảm giác tức thời.

5. Đơn vị đọc là page, không phải row — correlation và CLUSTER

Mục 4 mới chỉ là một nửa câu chuyện — nửa “tìm thấy entry trong index”. Nửa còn lại, thường tốn kém hơn nhiều, là lấy row thật từ heap. Và ở đây có một sự thật cốt lõi:

PostgreSQL đọc theo page, không theo row. Khi cần một row, nó nạp cả page 8 KB chứa row đó lên RAM — không bao giờ chỉ một dòng.

Hệ quả trực tiếp: nếu nhiều row khớp cùng nằm trên một page, chúng được lấy trong cùng một lần đọc page. Ngược lại, nếu mỗi row khớp nằm trên một page khác nhau, mỗi row tốn một lần đọc page riêng. Cùng số row khớp, nhưng chi phí I/O có thể chênh nhau cả chục lần — chỉ vì thứ tự vật lý của dữ liệu trên heap.

Mức độ “row được sắp vật lý trùng với thứ tự index” được PostgreSQL đo bằng một chỉ số gọi là correlation (tương quan), nằm trong pg_stats:

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders';

correlation chạy từ -1 đến 1:

Ví dụ kinh điển của correlation — và là tâm điểm của một cuộc tranh cãi quen thuộc khi thiết kế bảng — là chọn BIGSERIAL hay UUID làm primary key. Trên bề mặt, cuộc tranh cãi xoay quanh tính phân tán (UUID sinh được ở client, không cần round-trip tới database xin ID) và riêng tư (ID liên tiếp lộ thông tin business — đơn hàng số 7000 hôm nay tiết lộ doanh nghiệp xử lý khoảng 7000 đơn/ngày). Nhưng dưới tầng storage, sự khác biệt sống còn lại nằm ở correlation:

Tác hại của correlation thấp ở UUID v4 không nằm ở range query trên id (gần như không ai làm vậy) — mà ở write amplification trên chính index. Mỗi INSERT UUID v4 chạm vào một leaf page ngẫu nhiên trong B-tree thay vì luôn rơi vào page ngoài cùng bên phải. Với bảng lớn, B-tree không vừa shared buffer → mỗi INSERT phải nạp một page lạ từ disk lên, sửa, ghi lại. Throughput INSERT của bảng UUID v4 có thể chậm hơn cả chục lần so với BIGSERIAL ở cùng quy mô.

Đây cũng là lý do UUID v7 (và ULID) được sinh ra: 48 bit đầu là Unix timestamp millisecond, phần còn lại mới là random. UUID v7 giữ được ưu điểm của UUID (sinh ở client, không lộ count) đồng thời thừa hưởng correlation cao của BIGSERIAL — UUID v7 sinh sau luôn lớn hơn UUID v7 sinh trước theo thứ tự sort, nên INSERT lại rơi vào leaf ngoài cùng bên phải như BIGSERIAL. Với project mới, UUID v7 thường là lựa chọn mặc định tốt nhất khi bạn cần tính phân tán của UUID mà không muốn trả giá correlation.

CLUSTER — ép thứ tự vật lý theo một index

Khi correlation thấp làm một range query quan trọng trở nên chậm, bạn có thể sắp xếp lại heap theo một index bằng lệnh CLUSTER:

CLUSTER orders USING idx_orders_created_at;

Sau lệnh này, heap được ghi lại sao cho thứ tự vật lý của row trùng với thứ tự của idx_orders_created_at → correlation của created_at tiến về 1. Hai lưu ý quan trọng:

6. Random I/O — gót chân Achilles của index

Giờ ta ghép hai mảnh — ctid (mục 3) và “đọc theo page” (mục 5) — để hiểu vì sao index đôi khi bị bỏ qua.

Sau khi duyệt cây tới đúng vùng leaf, PostgreSQL có một dãy entry liên tiếp, mỗi entry là một ctid. Vấn đề: các ctid liên tiếp trên leaf có thể trỏ tới các page nằm rải rác khắp heap (đúng như mô hình heap + correlation thấp ở trên). Mỗi page như vậy là một lần random access — ổ đĩa (hoặc lớp cache phía dưới) phải “nhảy” tới một vị trí ngẫu nhiên để đọc.

Đây là điểm cần phân biệt rõ:

Nhưng vì sao “nhảy” lại đắt hơn? Cần bóc tách thêm một lớp: phía dưới PostgreSQL còn có hệ điều hành và phần cứng đĩa, và lợi thế sequential thực ra đến từ chính chúng.

Khi PostgreSQL muốn đọc một page, nó tra shared buffer trước. Nếu miss, nó gọi read() xuống OS — và OS lại tra page cache. Chỉ khi page cache cũng miss thì mới thực sự xuống đĩa. Hai tầng cache xếp chồng nghĩa là: nhiều “lần đọc đĩa” trong code thực ra chỉ là một lần memcpy từ RAM.

Mấu chốt: kernel chủ động làm việc khi pattern truy cập có tính tuần tự. Khi OS thấy ứng dụng đọc block N, rồi N+1, rồi N+2…, nó kích hoạt read-ahead: nạp sẵn block N+3, N+4, … vào page cache trước cả khi PostgreSQL hỏi tới. Đến khi read() cho block tiếp theo được gọi, dữ liệu đã chờ sẵn — không round trip xuống đĩa, chi phí gần bằng một lần memcpy. Random pattern thì không có “block tiếp theo” để đoán; OS không prefetch, mỗi page là một lần xuống đĩa thật.

Ngay cả khi đã xuống tới phần cứng, chênh lệch vẫn lớn. Trên HDD, mỗi random read phải chịu seek time cộng rotational latency; cộng dồn khoảng 5–10 ms mỗi lần truy cập, bất kể chỉ đọc 8 KB. Sequential thì sau khi đầu đọc đã ở đúng track, các page liền nhau “trôi” qua đầu đọc gần như tức thì — chênh lệch dễ tới hai bậc. Trên SSD, không còn seek vật lý nhưng vẫn đắt hơn: mỗi access là một I/O request riêng đi qua block layer của kernel (system call, queue, driver, controller — gọi là per-IO overhead), trong khi đọc tuần tự có thể được gộp thành request lớn. Random pattern cũng làm hỏng tính cục bộ của page cache: mỗi page mới nạp có thể đẩy một page hot khác ra, kéo theo cache miss dây chuyền cho các query khác.

PostgreSQL mã hóa sự chênh lệch này bằng hai tham số chi phí: seq_page_cost (mặc định 1.0) và random_page_cost (mặc định 4.0). Nghĩa là theo ước lượng mặc định, đọc một page ngẫu nhiên “đắt” gấp bốn lần đọc một page tuần tự. Tỷ số 4× không phải con số tùy chọn — nó là mã hóa định lượng của tất cả những thứ vừa kể: read-ahead bị mất, per-IO overhead, tính cục bộ cache bị phá vỡ, và (trên HDD) seek/rotational latency. Trên SSD nhanh người ta thường tinh chỉnh random_page_cost xuống 1.5–2.0, nhưng bản chất “random đắt hơn sequential” không biến mất hoàn toàn.

Bây giờ là mấu chốt: khi số row khớp đủ lớn, tổng chi phí random I/O của Index Scan vượt qua chi phí đọc tuần tự toàn bộ bảng. Lúc đó, đọc tuần tự cả bảng (mỗi page đúng một lần, theo thứ tự, với read-ahead) lại rẻ hơn là nhảy lung tung theo ctid. Đây chính xác là khoảnh khắc planner chọn Seq Scan thay vì dùng index — và là lời giải cho câu chuyện status = 'pending' ở đầu bài.

7. Planner quyết định thế nào: Seq Scan vs Index Scan vs Bitmap Heap Scan

Query planner của PostgreSQL là một cost-based optimizer (bộ tối ưu dựa trên chi phí): với mỗi cách thực thi khả dĩ, nó ước lượng một con số “chi phí” và chọn cách rẻ nhất. Hai đầu vào quyết định:

  1. Selectivity (độ chọn lọc): ước lượng bao nhiêu phần trăm row sẽ khớp điều kiện. Lấy từ thống kê trong pg_statistic, được cập nhật bởi ANALYZE (và autovacuum). Selectivity sai (thống kê cũ) là nguyên nhân số một của việc chọn nhầm scan.
  2. Chi phí mỗi loại scan: tính từ seq_page_cost, random_page_cost, cpu_tuple_cost, số page, số row ước lượng, và correlation.

PostgreSQL có ba chiến lược chính để đọc dữ liệu theo điều kiện:

Index Scan

Duyệt index, và với mỗi entry khớp, lập tức fetch row từ heap theo ctid. Vì fetch xen kẽ với duyệt index, mỗi row khớp thường là một random page read. Tốt nhất khi: số row khớp ít (selectivity cao), hoặc correlation cao (các row khớp gom cụm). Đây là kịch bản customer_id = 12345 — chỉ vài chục đơn, fetch vài page là xong.

Bitmap Heap Scan

Đây là lời giải thông minh của PostgreSQL cho vùng “selectivity trung bình”. Nó tách làm hai pha:

  1. Bitmap Index Scan: duyệt index, gom tất cả ctid khớp vào một bitmap (bản đồ bit) đánh dấu những page nào cần đọc — chưa fetch row nào.
  2. Bitmap Heap Scan: đọc các page đó theo thứ tự vật lý tăng dần, mỗi page đúng một lần.

Mẹo ở đây là biến random I/O thành gần-sequential I/O: thay vì nhảy tới page 7, rồi page 2, rồi page 9 (theo thứ tự index), nó sắp lại thành page 2, 7, 9 và đọc xuôi. Nếu nhiều row khớp nằm chung page, page đó cũng chỉ đọc một lần. Tốt nhất khi: selectivity trung bình — quá nhiều cho Index Scan, nhưng chưa đủ nhiều để phải quét cả bảng. Trong EXPLAIN bạn sẽ thấy cặp Bitmap Heap Scan + Bitmap Index Scan.

Seq Scan

Bỏ qua index, đọc toàn bộ heap tuần tự từ đầu đến cuối, lọc từng row. Nghe có vẻ “ngu ngốc” nhưng với sequential I/O + read-ahead, nó cực nhanh trên mỗi page. Tốt nhất khi: số row khớp lớn (selectivity thấp) — như status = 'pending' chiếm 40% bảng. Lúc đó dù sao bạn cũng phải chạm gần hết các page, nên đọc tuần tự một lần rẻ hơn nhiều so với hàng triệu lần random fetch.

Đọc EXPLAIN để thấy quyết định

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 12345;
-- Index Scan using idx_orders_customer_id ...
--   Buffers: shared hit=4 read=3
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';
-- Seq Scan on orders ...
--   Filter: (status = 'pending')
--   Rows Removed by Filter: ...

Với customer_id (cardinality cao, ít row khớp) → Index Scan. Với status (cardinality thấp, 40% row khớp) → Seq Scan. Cùng một thao tác CREATE INDEX, nhưng planner chọn khác nhau vì selectivity khác nhau. Index trên status không sai về mặt kỹ thuật — nó chỉ vô dụng cho query này, và planner đủ thông minh để không dùng nó.

Bạn có thể tạm thời ép planner thử dùng index để so sánh, bằng SET enable_seqscan = off; rồi chạy lại EXPLAIN ANALYZE. Nếu thời gian thực tế tệ hơn, planner đã đúng. Đừng để enable_seqscan = off trong production — nó chỉ là công cụ chẩn đoán.

8. Cardinality, selectivity, và nghệ thuật chọn cột để index

Giờ ta khép lại nguyên tắc ở mục 1: index theo cách ứng dụng query, và ưu tiên cột có độ chọn lọc cao.

Cardinality (lực lượng) là số lượng giá trị phân biệt trong một cột. Nó quyết định selectivity:

Multi-column index và quy tắc leftmost prefix

Một index nhiều cột (a, b, c) sắp xếp các entry theo a, rồi b, rồi c. Vì thứ tự đó, index dùng được cho điều kiện trên tiền tố trái liên tục (leftmost prefix): (a), (a, b), (a, b, c) — nhưng không dùng hiệu quả cho riêng (b) hay (c). Quy tắc thực dụng:

Partial, expression, và covering index

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Index này nhỏ xíu (chỉ chứa đơn pending) và biến một cột cardinality thấp thành index hữu ích cho đúng truy vấn cần nó.

CREATE INDEX idx_users_lower_email ON users (lower(email));
-- phục vụ: WHERE lower(email) = 'a@b.com'
CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (status, total);

Khi mọi cột query cần đều nằm trong index, planner có thể dùng Index-Only Scan — bỏ hẳn bước fetch heap. Nó dựa vào Visibility Map để biết page nào toàn live tuple mà bỏ qua việc kiểm tra visibility ở heap (cơ chế VM được mô tả trong Storage Internals).

Hãy để query pattern dẫn đường

Trong thực tế, bạn nhìn vào code truy vấn của ứng dụng để quyết định index. Ví dụ một repository điển hình với thư viện pg trong Node.js:

import { Pool } from 'pg'
 
const pool = new Pool()
 
async function findRecentOrdersForCustomer(customerId: number, limit: number) {
  const { rows } = await pool.query(
    `SELECT id, status, total, created_at
     FROM orders
     WHERE customer_id = $1
     ORDER BY created_at DESC
     LIMIT $2`,
    [customerId, limit]
  )
  return rows
}

Query này lọc theo customer_id (bằng) rồi sắp theo created_at (dải/thứ tự). Index phục vụ nó tốt nhất là multi-column, đặt cột = trước, cột ORDER BY sau:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Với index này, PostgreSQL nhảy thẳng tới nhánh của customer_id, rồi vì các entry đã được sắp theo created_at DESC ngay trong leaf, nó đọc LIMIT dòng đầu tiên là xong — không cần sort, không cần quét thừa. Index được thiết kế theo query, chứ không theo cấu trúc bảng.

9. Index cũng bloat — một chút về bảo trì

Index không phải “tạo xong là quên”. Giống như bảng, index B-tree cũng bloat (phình to vì không gian lãng phí). Nhưng index có một mầm bloat riêng của cấu trúc B-tree, không dính tới MVCC, và phải mổ xẻ trước khi nói tới VACUUM.

Cơ chế page split

Mỗi leaf page B-tree là 8 KB cố định. Khi insert một row mới, PostgreSQL phải chèn entry (key, ctid) vào đúng leaf theo thứ tự sắp xếp. Nếu leaf còn chỗ, chèn xong là kết thúc.

Khi leaf đã đầy nhưng vẫn phải chèn thêm: PostgreSQL cấp phát một leaf page mới, di chuyển khoảng nửa số entry sang page mới, để lại nửa kia ở page cũ — sau đó cập nhật con trỏ right-link để page mới chen vào giữa, và đẩy một separator key mới lên parent để định tuyến tới page mới. Nếu parent cũng đầy thì parent split tiếp, và quá trình có thể lan lên đến root (lúc đó cây cao thêm một tầng). Đây gọi là page split.

Hậu quả về dung lượng: hai page chỉ đầy ~50% thay vì một page đầy 100%. Index file mở rộng thêm 8 KB. Hai page nửa rỗng chỉ được lấp lại nếu insert tiếp theo rơi đúng vào dải key chúng đang giữ — còn nếu key insert sau cứ rơi vào vị trí khác (rất phổ biến với key UUID, hay customer_id ngẫu nhiên), hai leaf nửa rỗng kia có thể đứng yên mãi mãi với tỷ lệ lấp ~50%.

Quan trọng: index B-tree không bao giờ tự co lại. VACUUM chỉ dọn dead entries (xem dưới); nó không hợp nhất hai leaf nửa rỗng cạnh nhau lại thành một. Theo thời gian, số leaf nửa rỗng tích tụ → tổng số page tăng → cây có thể cao thêm một tầng (mỗi tra cứu cộng thêm một lần I/O) → page utilization tụt từ ~90% lúc mới tạo xuống có khi 40–50%. Đây mới là phần “index relation cũng phình to” — không liên quan tới dead tuple.

Vì lý do đó, B-tree index có fillfactor riêng (mặc định 90 cho index, khác với 100 của heap): khi tạo, chừa sẵn ~10% chỗ trống ở mỗi leaf để insert/update kế tiếp có chỗ chèn mà không cần split. Đánh đổi: file ban đầu lớn hơn một chút, nhưng split xảy ra ít hơn nhiều — đặc biệt hữu ích cho bảng nhiều ghi vào giữa dải key.

Dead entries và bảo trì

Kênh bloat thứ hai: khi row bị DELETE/UPDATE, entry index tương ứng trở thành dead nhưng chỉ được dọn khi VACUUM chạy. (Cơ chế dead tuple/MVCC được mổ xẻ trong Deep Dive về Table Bloat.)

Index phình to vì cả hai lý do (page split + dead entries) đều dẫn tới cùng hệ quả: cây cao hơn cần thiết, nhiều page hơn phải nạp, cache kém hiệu quả hơn. Vài thao tác bảo trì cốt lõi:

-- Tìm index không bao giờ được dùng (ứng viên để xóa)
SELECT relname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
 
-- Dựng lại index để thu hồi không gian bloat, không khóa ghi
REINDEX INDEX CONCURRENTLY idx_orders_customer_created;

REINDEX ... CONCURRENTLY dựng lại index mà không lấy khóa chặn ghi (tốn thời gian hơn nhưng an toàn cho production). Và đừng quên: index không dùng tới là gánh nặng thuần túy — nó làm chậm mọi INSERT/UPDATE và tốn đĩa mà chẳng tăng tốc query nào. pg_stat_user_indexes với idx_scan = 0 là nơi đầu tiên để dọn dẹp.

Kết luận

Quay lại câu hỏi mở đầu. Vì sao index trên customer_id biến query 8 giây thành 1 ms, còn index trên status bị phớt lờ?

Những điều cốt lõi cần mang theo:

Index không phải nút “làm cho nhanh”. Nó là một đánh đổi mà bạn thiết kế — và planner, với phép tính chi phí của nó, sẽ là người quyết định cuối cùng có dùng hay không.

Liên quan

Deep Dive: PostgreSQL Partitioning — Khi 1 Bảng Lớn Trở Thành Nhiều Bảng Nhỏ
Database
26 thg 5, 2026
Deep Dive: PostgreSQL Storage Internals — Từ Heap File Trên Disk Đến Shared Buffer
Database
26 thg 5, 2026
Amazon Aurora: Toàn bộ tính năng bạn cần biết
Devops, AWS, Database, SAA
4 thg 5, 2026
Connection Pool: Tại sao mở kết nối database mỗi request là một sự lãng phí
Backend, Database, System Design
4 thg 5, 2026
RDS Read Replica vs Multi-AZ: Cùng là "bản sao", nhưng đừng nhầm lẫn!
Devops, AWS, Database
3 thg 5, 2026