Postgresql – to use full text search or not

full-text-searchpostgresql

In my web app, I have a web page for admins that displays the following fields: 

user id (numeric)
device id(numeric)
device description (text)
cell phone number (text)
user name (string ... but pulled in dynamically from another database)

The list of records can be quite large… into the thousands… and so the users have requested a search. It'd be nice if I could just provide a generic search box where they can specify either a user_id or a device description etc and the system will just search all fields and display matching records.

I've never built a full text search and am just researching how to do it now.
Based on my reading, I understand that a "document" / (tsvector in postgresql) can just be several different columns of data that are concatenated together.
Ignoring the fact that user name is from a different database altogether, what I'm wondering is if it makes sense for me to implement a full text search, or to just search the individual fields.
What factors should I be considering to make this decision?

Also, I don't really know how to design the system so that I could provide searching on the fields of data that are pulled in from another database, as is the case with the user names.

Any comments would be appreciated.

Thanks.

Best Answer

For website search you should use an actual search tool like Solr. Full text search works in-theory, but performance slows when your data set starts to get big. Search tools like Solr, Endeca, and Elastic Search are designed to solve problems like this, and will perform much better than full text search.

Feel free to experiment with full text search if you want, but I don't think it is a valid long-term solution for a website.

Related Question