Indexing for full-text search

PostgreSQL includes a capable full-text search (FTS) package, available as a core feature starting in 8.3. Both GIN and GiST indexes can be used to accelerate text searches done that way. The basic idea is that GIN is better suited for relatively static data, while GiST performs better with frequently updated, dynamic text. The trade-offs involved in choosing between those index types are heavily documented in the PostgreSQL manual chapter devoted to full-text search.

That documentation can also be considered a tutorial on the relative strengths and weaknesses of GIN vs. GiST, which can be useful when trying to decide which of them to use for other applications. For example, the hstore key/value implementation available as a contrib module can be handy for efficiently storing that sort of data when it doesn't need to have full SQL semantics, an approach popularized by the NoSQL moment recently. There are both GIN and GiST implementations of hstore...