Searching across multiple tables (best practices)

Question

I have property management application consisting of tables:

tenants
landlords
units
properties
vendors-contacts

Basically I want one search field to search them all rather than having to select which category I am searching. Would this be an acceptable solution (technology wise?)

Will searching across 5 tables be OK in the long run and not bog down the server? What's the best way of accomplishing this?

Using PostgreSQL

Answer 1

I would suggest using a specialized full-text indexing tool like Lucene for this. It will probably be easier to get up and running, and the result is faster and more featureful too. Postgres full text indexes will be useful if you also need structured search capability on top of this or transactionality of your search index is important.

If you do want to implement this in the database, something like the following scheme might work, assuming you use surrogate keys:

1. for each searchable table create a view that has the primary key column of that table, the name of the table and a concatenation of all the searchable fields in that table.
2. create a functional GIN or GiST index on the underlying over the to_tsvector() of the exact same concatenation.
3. create a UNION ALL over all the views to create the searchable view.

After that you can do the searches like this:

SELECT id, table_name, ts_rank_cd(body, query) AS rank
    FROM search_view, to_tsquery('search&words') query
    WHERE query @@ body
    ORDER BY rank DESC
    LIMIT 10;