終於在學完各種不同的 Index 之後，來到最後一篇關於 Index 的章節了！在了解不同 Index 的特性之後，是不是急著想要將資料庫加上 Index？

如果專案還沒有上線，想要隨意新增 /刪除 Index 是一件很容易的事，但是如果系統是已經在 Production 環境的狀態，加上 Index 可能會鎖住表格，導致應用程式卡住。

前面我們有用了很多次 CREATE INDEX 的指令，這個指令會將 Index 一次建立完成，但是在建立的同時，PostgreSQL 會鎖住表格，阻止任何寫入（INSERT / UPDATE / DELETE）操作，確保 Index 建立期間不會有資料變動。

但這如果是在 Production 環境，建立 Index 需要時間，可能就會導致應用程式請求被 Block 住、使用者無法更新或寫入資料，因此長時間影響運行的服務。

那在 Production 環境時要怎麼處理呢？CREATE INDEX 後面其實可以加上 CONCURRENTLY，這代表資料庫不會一口氣建立完 Index，避免因為鎖表而導致寫入（INSERT / UPDATE / DELETE）無法運作。

CREATE INDEX CONCURRENTLY my_index;

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done.

CREATE INDEX vs. CREATE INDEX CONCURRENTLY 差異

CONCURRENTLY 步驟有哪些

從 文件可以看到 CONCURRENTLY 的運作方式，步驟大致如下：

1. 新增 Index 但先標記為無效

   新增 Index，但是在 pg_index 裡面先設定 indisready = false & indisvalid = false，代表還不能用 。

2. 第一次等待

   等待修改這張 table 的 transaction 結束。

3. 第一次 scan + snapshot

   依據這個 snapshot 建立 Index，這時候 table 的資料還是開放修改。建立完之後改為 indisready = true ，代表 Index 準備好了！

4. 第二次等待

   等待修改這張 table 的 transaction 結束。

5. 第二次 scan + snapshot

   依據第二個 snapshot 更新現有的 Index，這時候 table 的資料還是開放修改，但是遇到修改 table 資料的請求，就直接更新 Index！（因為在第三步我們說 Index 準備好了 indisready = true）

6. 第三次等待

   等待所有早於第二次 scan 的 transaction 都結束，確保剛剛第二次 snapshot 更新的資料都是對的。

7. Index 標記為有效

   建立流程結束了，把這個 Index 標記為「有效」indisvalid = true，可以拿來搜尋使用了！

indisready & indisvalid 的定義也可以在文件裡面找到：

看到有這麼多個步驟，應該就可以知道為什麼它比 CREATE INDEX 還要花上更多時間了。

處理無效的 Index

你可能有注意到，如果是 CONCURRENTLY 的方式，有可能在建 Index 時建到一半失敗了，導致這個建一半的 Index 無法使用，又會佔空間。這時候可以把 Index 刪掉再重新下一次 CREATE INDEX，又或者可以使用 REINDEX 重新建立。REINDEX 也同樣可以選擇是否要使用 CONCURRENTLY。

• 手動刪除再重建

DROP INDEX my_index;
CREATE INDEX CONCURRENTLY my_index ON my_table (column);

• 使用 REINDEX 修復

REINDEX INDEX CONCURRENTLY my_index;

不確定有哪些 Index 無效的話，可以從 pg_index 裡面看到：

SELECT indexrelid::regclass, indisvalid FROM pg_index

如果 indisvalid = false，就表示那個 Index 需要修復或刪除了。

重點回顧

• CREATE INDEX CONCURRENTLY 適用不想阻塞寫入操作的狀況 eg. Production 環境
• CONCURRENTLY 的方式建立 Index 時間較長，失敗時需要手動刪除或使用 REINDEX修復

參考資料

https://www.postgresql.org/docs/current/sql-createindex.html
https://www.postgresql.org/docs/current/sql-reindex.html
https://www.mengqingzhong.com/2021/01/01/postgresql-create-index-concurrently/
https://developer.aliyun.com/article/590359