Scalable searching algorithm SQL


So I have a list of users stored in a postgres database and i want to search them by username on my (Java) backend and present a truncated list to the user on the front end (like facebook user search). One could of course, in SQL, use

WHERE username = 'john smith';

But I want the search algorithm to be a bit more sophisticated. Beginning with near misses for example

"Michael" ~ "Micheal"

And potentially improving it to use context e.g geographical proximity.

This has been done so many times I feel like I would be reinventing the wheel and doing a bad job of it. Are there libraries that do this? Should this be handled on the backend (so in Java) or in the DB (Postgresql). How do I make this model scalable (i.e use a model in which complexity is easy to add down the road)?

Show source
| java   | search   | postgresql   | sql   2016-12-26 00:12 1 Answers

Answers ( 1 )

  1. 2016-12-26 01:12

    A sophisticated algorithm will not magically appear, you have to implement it. Your last question is whether you should do it in Java or in the database. In overwhelming majority of cases it's better to use the database for queries. Things like "Michael" ~ "Micheal" or spatial queries are standard features in many modern SQL databases. You just have to implement the appropriate SQL query.

    Another point is, however, if SQL database is a right tool for "sophisticated queries". You may also consider alternatives like Elasticsearch.

◀ Go back