I am implementing full text search in postgres.

I would like to search all posts in my system. The posts fulltext index is an amalgamation of the post title and post body.

I have two ways of achieving this:

  1. create a tsvector column in the posts table, trigger an update to it.
  2. create a second table (posts_search) with a post_id and tsvector column containing the index data.
  3. create a simple gin index ... (out of the question, cause my real world problem needs data in multiple tables for the index)

What is going to perform better, considering I sometimes need to filter down the search by other attributes in the table (like deleted_at is null and so on).

Is it a better approach to keep the tsvector column in the same table as the data (side effect select * now sucks) or a separate table (side effect, join required, index filtering is complicated)?

Edit
Report