Choosing a PostgreSQL text search method by Craig Ringer.
From the post:
(This article is written with reference to PostgreSQL 9.3. If you’re using a newer version please check to make sure any limitations described remain in place.)
PostgreSQL offers several tools for searching and pattern matching text. The challenge is choosing which to use for a job. There’s:
- LIKE and ILIKE SQL pattern matching;
- ~ and ~* operators for mostly-perl-compatible regular expressions;
- full text search with @@, to_tsvector and to_tsquery
- Use of an external search provider like Apache Lucene / Solr.
There’s also SIMILAR TO, but we don’t speak of that in polite company, and PostgreSQL turns it into a regular expression anyway.
If you are thinking about running a PostgreSQL backend and need text searching, this will be a useful post for you.
I really appreciated Craig’s closing paragraph:
At no point did I try to determine whether LIKE or full-text search is faster for a given query. That’s because it usually doesn’t matter; they have different semantics. Which goes faster, a car or a boat? In most cases it doesn’t matter because speed isn’t your main selection criteria, it’s “goes on water” or “goes on land”.
Something to keep in mind with the “web scale” chorus comes along.
Most of the data of interest to me (not all) isn’t of web scale.
How about you?