1

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.

  • You won't have any problems doing this with plain SQL if you're looking at record counts in the thousands. – swasheck Nov 22 '13 at 20:31
  • @swasheck, so you mean I should try it without implement full text search? – dot Nov 26 '13 at 21:20
  • we'd need more details about the structure and what exactly it is that you're searching for. – swasheck Nov 26 '13 at 22:27
2

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.

  • 1
    Hey I found this question using this site which uses SQL Server full text search! Man, it was fast. – Neil McGuigan Nov 13 '13 at 19:01
  • Nice! I guess I can't argue with that. My bias comes from the fact that I have worked on a few (e-commerce) sites which used full-text search, with sub-par results. – Aaron Nov 13 '13 at 19:18
  • I can't say I agree with the strength of that opinion, but agree that in-DB FTS is not going to be perfect for all applications. Especially PostgreSQL's, as it doesn't work well for multiple-table search. – Craig Ringer Dec 23 '13 at 4:38
1

In general in-database full text search is quite sufficient, and not inherently slower than an external search solution given the same available resources. There's generally no reason to look further afield until you hit the limitations of built-in search.

In your case your requirements clearly exceed the capabilities of Pg's full text search, in that you're using data from multiple sources, and Pg's FTS really works best for fast searches of single tables.

If you weren't pulling info in from other DBs then Pg's regular FTS would be a perfectly sensible solution. It might still be usable, but you'd have to generate a materialized view that you update with external jobs and with triggers. At that point it's rarely much simpler to use Pg FTS than to go for something full-on like Solr.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.