要說時下最流行、最優雅、功能最強大的數據庫必然離不了PostgreSQL，很多企業將其基礎架構的數據庫都遷移成了PostgreSQL。本文我們就學習一個實踐案例，使用PostgreSQL構建起全新的支撐搜索系統，實現快速模糊搜索，即時呈現結果。

在Levels.fyi是一個在線招聘網站。其架構中要求可以實現處理月度千萬搜索，並且p99的查詢性能的目標不高于百分之二秒（20ms）。

業務類型

企業 3.5w+

城市 6200+

區域 300+

國家 180+

標題 100+

專業 40+

六個表中的總記錄數超過42000條記錄。 除此之外，另外還支持複合搜索類型，例如 Company x Title (Google Software Engineer)和Title x Company (Software Engineer Google/Software Engineer at Google)，從而産生數十萬條附加記錄。

以一個簡單搜索子爲例，搜索JPMorgan Chase。起始SQL查詢可能如下所示：

@set q = 'jp%'

SELECT c.name FROM company c WHERE c.name ILIKE $q

UNION SELECT c.name FROM city c WHERE c.name ILIKE $q

...

ORDER BY name

LIMIT 10;

該查詢對所有表執行順序掃描。爲了優化這一點，可以簡單地在搜索列上添加一個小寫的BTREE索引，並使用LIKE代替 ILIKE進行查詢。否則數據庫就不會走索引。

爲了提高查找效率，第一個優化就是創建索引：

CREATE INDEX company_name_lower_index ON company(LOWER(name));

CREATE INDEX city_name_lower_index ON city(LOWER(name));

...

注意：使用CREATE INDEX會對表鎖寫入和更新。如果不允許該鎖定可以CREATE INDEX CONCURRENTLY代替。

創建如此多的索引並聚合如此多的表並不是最優的。爲了更進一步優化，使用物化視圖，會創建一個單獨的基于磁盤存儲的實體，因此支持索引。當然要考慮其持續更新的問題。

CREATE MATERIALIZED VIEW search_view AS

ㅤㅤSELECT c.name FROM company c UNION

ㅤㅤSELECT c.name FROM city c UNION ...;

CREATE INDEX search_view_name_lower_index

ㅤㅤON search_view(LOWER(name));

之後搜索查詢語句爲：

@set q = 'jp%'

SELECT s.name FROM search_view s

WHERE LOWER(s.name) LIKE LOWER($q)

ORDER BY s.name;

爲了保持物化視圖同步，使用事件橋規則，即每隔一段時間調用一次lambda X分鍾刷新物化視圖。這意味著搜索結果最多可能會過時X分鍾，最終仍然會保持一致。

可以考慮使用定時任務，閑時實施INDEX ONLY SCAN通過在索引中包含所有選定的列可以減少堆查找的需要，但這種方法確實有其局限性。

這樣搜索確實有局限性。例如，搜索jp morgan%不會産生任何結果，因爲存儲的名稱是JPMorgan Chase。甚至是前綴+後綴查詢-%jp morgan%在這裏沒有幫助。 使用任意通配符擴展搜索%jp%morgan%會有幫助，但它仍然無法進行jp chase%這樣搜索。口音和語言是也是一個要考慮的問題，如果用戶搜索the sofy，可能真正想搜sofi，th是一個停用詞並且可能會被誤聽爲y。

通配符搜索很有幫助，但隨著查詢變得更加隨意，這些搜索開始崩潰。隨著用戶搜索的多樣性，還需要弄清楚如何對搜索結果進行排名。所以需要更進一步優化處理。

非前綴搜索會導致順序掃描。要有效地使用任意通配符進行搜索，可以使用三元組索引。

直接搜索姓名欄是非常具有挑戰性的。爲了改善搜索結果，轉而使用全文搜索tsvector，一種允許文本搜索的數據類型。

@set q = 'the:*&jp:*&chase:*'

SELECT

ㅤㅤs.name,

ㅤㅤto_tsvector('english', s.name),

ㅤㅤto_tsquery('english', $q),

ㅤㅤts_rank(

ㅤㅤㅤㅤto_tsvector('english', s.name),

ㅤㅤㅤㅤto_tsquery('english', $q))

FROM search_view s;

爲了消除name轉換的tsvector開銷到，嵌入tsvector作爲物化視圖中預先計算的實體。還使用廣義倒排索引（Trigram）來優化查詢的運行時間。經過這些更改，搜索查詢修改爲：

@set q = 'the:*&jp:*&chase:*'

SELECT s.name

FROM search_view s

ORDER BY ts_rank(

ㅤㅤs.search_vector,

ㅤㅤto_tsquery('english', $q)) DESC

LIMIT 10;

對于像這樣的查詢D E Shaw，新方法審查排名和搜索列：

還有其他方法可以將查詢轉換爲向量，例如websearch_to_tsquery，不同類型的排名方法，例如similarity, ts_rank_cd並突出顯示類似的幫助者 ts_headline這可能更適合某些用例

有幾家公司有不同的名稱，要麽是因爲業務重塑，要麽是因爲它們在不同名稱之間有很強的聯系：

Google → Alphabet

X→Facebook → Meta

JPMorgan Chase → JPMC

通過維護可能的替代公司名稱的完整列表。 爲了在搜索中考慮備用名稱，搜索列變成名稱及其別名的組合，並自動在搜索結果中考慮它們。

當尋找goo，很可能是在搜索Google但查詢則會返回：

雖然這些結果與每個詞位匹配具有相同的排名，但它並不能提供最佳的用戶體驗。 爲了解決這個問題，通過開發相關性算法similarity和ts_rank。問題是，但是需要確定哪個結果更有可能被點擊呢？一個簡單的指標是結果收到的點擊次數，這樣確保結果越受歡迎，排名就越高。可以使用的一些參數爲：

完全匹配（排名#1）

使用匹配詞位的頻率ts_rank

使用相似度得分similarity

記錄類型

搜索結果的受歡迎程度

結果別名與查詢之間的相似度

結果字符串長度的倒數

這種方法使得夠微調搜索排名並向用戶提供相關結果。

注意到用戶正在組合搜索類型，例如“Product Manager at Coinbase”或“Coinbase Product Manager”。這帶來了挑戰，因爲按公司搜索會將用戶引導至薪資頁面，之後用戶仍然需要導航至其職位。爲了消除該額外的步驟，新添加了對複合類型的支持，特別是“Company x Title”和“Title x Company”組合。

由于使用是物化視圖，因此使用公司和標題表交叉聯接的結果更新其定義非常簡單。還添加了一些額外的檢查，以避免引導用戶訪問不存在的頁面。

Levels.fyi的案例中通過遷移到PostgreSQL，他們獲得了先進的查詢功能和經濟高效、可擴展的解決方案來滿足我們的數據需求。PostgreSQL很好地滿足了其業務模式和規模，並且通過利用該解決方案，每月節省了數百至數千刀的潛在費用。

這個案例給了我們一個可以學習的案例，如何使用一個新的強大的工具重建我們的基礎架構。當然探索一個新的工具是要一定的探索的精神和風險在裏頭的，但是一旦成功則可以帶來架構優化、額外新增功能、後續可擴展性，以及直接的性能上和成本上的改善。