Notes

Appearance

PostgreSQL — Index Maintenance ​

1. Unused Indexes ​

Index không chỉ tốn disk — mỗi INSERT/UPDATE/DELETE phải maintain tất cả indexes trên bảng đó, kể cả index không ai dùng đến.

INSERT 1 row vào bảng có 5 indexes:
→ ghi vào heap
→ cập nhật index 1
→ cập nhật index 2
→ cập nhật index 3
→ cập nhật index 4
→ cập nhật index 5  ← nếu index 5 không ai query thì đây là pure overhead

Unused index = write chậm hơn + tốn disk + tốn RAM (shared_buffers cache index pages) — không mang lại lợi ích gì.

sql
-- Tìm unused indexes (idx_scan = 0 kể từ lần cuối pg_stat reset)
SELECT schemaname,
       relname AS tablename,
       indexrelname AS indexname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)  -- loại trừ PK/unique constraint
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan được reset khi:

pg_stat_reset() được gọi thủ công → stats_reset có giá trị
PostgreSQL restart → stats_reset vẫn NULL (không reset stats)

-- Xem stats được collect từ khi nào
SELECT pg_stat_get_db_stat_reset_time(oid) AS stats_reset
FROM pg_database
WHERE datname = current_database();

stats_reset = NULL → chưa bao giờ reset → idx_scan đáng tin cậy nhất
stats_reset = timestamp → chỉ đếm từ thời điểm đó

Lưu ý trước khi drop: Index có idx_scan = 0 có thể là do server vừa restart, hoặc index chỉ được dùng theo mùa vụ (báo cáo cuối tháng, cuối năm). Hãy quan sát ít nhất vài tuần trước khi drop.

sql
-- Drop index không lock (CONCURRENTLY)
DROP INDEX CONCURRENTLY idx_name;

2. Index Bloat ​

Index pages tích lũy dead entries sau DELETE/UPDATE — tương tự table bloat. Tuy nhiên, VACUUM xử lý index bloat khác table bloat:

Table bloat: VACUUM dọn dead rows → đánh dấu free space → tái sử dụng được
Index bloat: VACUUM dọn dead entries → nhưng KHÔNG compact lại pages
             → page vẫn half-empty, vẫn chiếm disk, vẫn phải scan

Nguyên nhân index bloat:

1. Page split:
   Leaf node đầy → tách thành 2 node, mỗi node chỉ đầy 50%
   → 2 page chứa lượng data của 1 page → index phình to

2. Dead entries:
   DELETE/UPDATE row → index entry cũ trở thành dead
   → vẫn chiếm chỗ trong index page cho đến khi VACUUM dọn

Muốn loại bỏ index bloat hoàn toàn → phải rebuild index (REINDEX CONCURRENTLY).

Khi nào index bloat đáng lo ngại:

  • Range queries chậm dần dù data không tăng
  • Index size lớn bất thường so với table size
  • Bảng có tỷ lệ DELETE/UPDATE cao (orders, jobs, events)

Kiểm tra index bloat ​

Cách 1 — pgstatindex (chính xác, dùng cho B-tree):

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  psi.indexrelname,
  pg_size_pretty(pg_relation_size(psi.indexrelid)) AS index_size,
  round((pgstatindex(psi.indexrelid)).avg_leaf_density::numeric, 2) AS avg_leaf_density,
  round((pgstatindex(psi.indexrelid)).leaf_fragmentation::numeric, 2) AS fragmentation
FROM pg_stat_user_indexes psi
JOIN pg_class pc ON pc.oid = psi.indexrelid
WHERE pc.relam = (SELECT oid FROM pg_am WHERE amname = 'btree')
ORDER BY pg_relation_size(psi.indexrelid) DESC;

Đọc kết quả:

avg_leaf_density:
> 90%  → tốt, không cần làm gì
70-90% → chấp nhận được
< 70%  → bloat đáng kể → cần REINDEX

fragmentation:
< 10%  → tốt
> 30%  → nên REINDEX

Ví dụ thực tế:

products_sku_key trước REINDEX:
→ index_size = 27MB, avg_leaf_density = 51.59%, fragmentation = 10.81%
→ thực tế chỉ cần ~14MB → 13MB bị lãng phí = bloat

Sau REINDEX:
→ index_size = 15MB, avg_leaf_density = 90.03%, fragmentation = 0%
→ không còn bloat, giảm 44% disk space

Lưu ý: pgstatindex chỉ dùng cho B-tree index:

GIN index  → không dùng được pgstatindex
Hash index → không dùng được pgstatindex
→ chỉ filter B-tree bằng pg_am

Cách 2 — pgstattuple_approx (nhanh hơn, dùng cho table):

sql
SELECT
  relname,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  round((pgstattuple_approx(relid)).dead_tuple_percent::numeric, 2) AS dead_tuple_pct,
  round((pgstattuple_approx(relid)).approx_free_percent::numeric, 2) AS free_pct
FROM pg_stat_user_tables
WHERE relname IN ('users', 'products');

Lưu ý: pgstattuple_approx có giới hạn:

Partitioned table → không hỗ trợ, phải query từng partition:
  WHERE relname LIKE 'orders%'
  AND relname != 'orders'  -- bỏ qua parent table

Index → không hỗ trợ, dùng pgstatindex thay thế

Cách 3 — Xem index size so với table size:

sql
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       pg_size_pretty(pg_relation_size(indrelid))   AS table_size,
       round(pg_relation_size(indexrelid) * 100.0
             / nullif(pg_relation_size(indrelid), 0), 1) AS index_pct
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

3. Fragmentation ​

Fragmentation = leaf nodes không nằm liên tục trên disk:

Không phân mảnh (fragmentation = 0%):
[leaf1: id=1-100] [leaf2: id=101-200] [leaf3: id=201-300]
→ tuần tự, liền kề → đọc range query tuần tự → nhanh

Phân mảnh (fragmentation cao):
[leaf1: id=1-100] [other] [leaf3: id=201-300] [other] [leaf2: id=101-200]
→ leaf nodes rải rác → random I/O → chậm hơn

Nguyên nhân: page split tạo page mới ở vị trí trống bất kỳ trên disk, không nhất thiết liền kề leaf node cũ.

4. REINDEX CONCURRENTLY ​

REINDEX thông thường lock cả bảng trong suốt quá trình rebuild — không thể dùng trên production.

REINDEX CONCURRENTLY build index mới song song với traffic thật:

Phase 1: tạo index mới (trạng thái "invalid")
         → vừa build vừa track thay đổi mới từ live traffic
Phase 2: catch up với các thay đổi trong lúc build
Phase 3: swap index cũ → index mới
Phase 4: xóa index cũ

Không block read/write, nhưng chạy lâu hơn và tốn thêm disk tạm thời (2 index cùng tồn tại song song).

sql
-- Rebuild 1 index
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- Rebuild tất cả indexes của 1 bảng
REINDEX TABLE CONCURRENTLY orders;

Khi nào cần REINDEX:

  • avg_leaf_density < 70% → bloat nặng
  • fragmentation > 30% → phân mảnh nhiều
  • Index bị corrupt (hiếm gặp, thường xảy ra sau crash không sạch)

Không chạy được trong transaction block — REINDEX CONCURRENTLY không thể được wrap trong BEGIN/COMMIT.

5. Routine checklist ​

sql
-- 1. Unused indexes (chạy sau vài tuần uptime)
SELECT relname AS tablename,
       indexrelname AS indexname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY pg_relation_size(indexrelid) DESC;

-- 2. Index bloat (B-tree only)
SELECT
  psi.indexrelname,
  pg_size_pretty(pg_relation_size(psi.indexrelid)) AS index_size,
  round((pgstatindex(psi.indexrelid)).avg_leaf_density::numeric, 2) AS avg_leaf_density,
  round((pgstatindex(psi.indexrelid)).leaf_fragmentation::numeric, 2) AS fragmentation
FROM pg_stat_user_indexes psi
JOIN pg_class pc ON pc.oid = psi.indexrelid
WHERE pc.relam = (SELECT oid FROM pg_am WHERE amname = 'btree')
ORDER BY pg_relation_size(psi.indexrelid) DESC;

-- 3. Table bloat (non-partitioned)
SELECT
  relname,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  round((pgstattuple_approx(relid)).dead_tuple_percent::numeric, 2) AS dead_tuple_pct,
  round((pgstattuple_approx(relid)).approx_free_percent::numeric, 2) AS free_pct
FROM pg_stat_user_tables
WHERE relname NOT IN (
  SELECT relname FROM pg_partitioned_table
  JOIN pg_class ON pg_class.oid = pg_partitioned_table.partrelid
);

-- 4. Rebuild nếu cần
REINDEX INDEX CONCURRENTLY idx_name;

Personal notes by thanhlt