PostgreSQL full text searching

Steps

1. Add a column to the table that will hold the text search data (of type TSVECTOR). You need one column for each different type of search you want to do (e.g. on several different single columns, across multiple columns, etc.)
2. Populate the column with the search data (you will probably want to VACUUM FULL ANALYZE after this step on a large table)
3. Create a GIST index on the new column (another VACUUM FULL ANALYZE after this)
4. Create an update trigger on the new column to update the search data on UPDATE or INSERT.

Single Column Example

For step 2, use the COALESCE function to turn NULL values into empty strings.

ALTER TABLE book
    ADD COLUMN title_fti TSVECTOR;

UPDATE book
    SET title_fti = TO_TSVECTOR('default', COALESCE(title, ));

CREATE INDEX book_title_fti_idx
    ON book
    USING GIST(title_fti);

CREATE TRIGGER book_title_fti_update
    BEFORE INSERT OR UPDATE
    ON book
    FOR EACH ROW EXECUTE PROCEDURE
    TSEARCH2(title_fti, title);

Multiple Column Example

For a multi-column text search, you simply concatenate the columns together for step 2.

ALTER TABLE book
    ADD COLUMN title_description_fti TSVECTOR;

UPDATE book
    SET title_description_fti = TO_TSVECTOR('default',
        COALESCE(title, ) || ' ' || COALESCE(description, ));

CREATE INDEX book_title_description_fti_idx
    ON book
    USING GIST(title_description_fti);

CREATE TRIGGER book_title_description_fti_update
    BEFORE INSERT OR UPDATE
    ON book
    FOR EACH ROW EXECUTE PROCEDURE
    TSEARCH2(title_description_fti, title, description);