Hello,
I have a schema which tracks various pieces of information which would
need to be globally searchable. One approach I came up with to make all
of the data searchable is to create a view made of UNION ALL queries
that would integrate different tables into a common structure which
could be uniformly queried by using tsearch2 functions. This would work,
up to the point where it would be practically unavoidable (for
performance reasons) to create indexes on this view, which cannot be
done. I would like to avoid using a "hand-made" materialized view (via
triggers, etc.) because of administrative overhead and because it would
duplicate data, of which there is potentially a lot.
I think this looks like a fairly common problem with full text searches
on a large-ish schemas, so I'm wondering what are the best practices
here, specifically with using tsearch2?