Date: 2015-09-18
Time: 21:00 - 21:50
Room: Houston Ballroom A
Level: Beginner
What options are available in Postgres for searching text? What is pg_trgm, and how is it different from Full Text search with ts_vector? Can you combine these methods then? Index the columns? Most importantly, how does it all perform?
In this talk, we will explore the tools Postgres provides for searching text in a single column and across columns. We’ll build a context for understanding by looking at a common use case that many struggle to get right: address lookups. Property data in the US is fun because addresses are frequently mis-entered or altered by “standardizers” that can bend and distort. User facing search is expected to account for typos and word fragments, while probing data that may reside in any of the city, state, zip or postal address columns.
As we progress, we’ll see what combination of indexing, column typing, and feature usage delivers the fastest result to a user. Then we’ll see how much fun can be had using the same features to try detecting possible duplicate addresses in our data!
This talk is intended for anybody - beginner or above - who wants to search through text in Postgres.