What you retain by staying with Postgres rather than going to a more exotic database is priceless. There is a threshold of data size or product need that makes a more specialized database the right choice. It's just well above 100 GB and your application should have some very specific needs to justify it.
As for the other stuff I mentioned (recommendations, etc.) I'm not just basing it on my personal experience--here's a write-up from Pinterest about having to dump all their MySQL data to Hadoop to drive various types of analysis. I doubt they would do it it if just putting the SQL DBs in RAM was adequate! https://medium.com/@Pinterest_Engineering/tracker-ingesting-...
Full text with Postgres is pretty fantastic and configurable. Not putting the data somewhere else keeps you from having to maintain a second system, keep things in sync, etc.
People jump to putting data in a search tool because it's a search tool waaaaay too quickly IMHO. If the use case justifies it, go for it...but don't add uneccessary complexity unless you have to.
To be fair there is some amount of "keeping things in sync" one has to do with postgres, even if it's just setting up the right triggers to update the FTS index on updates.
Right now I'm working on an app that works with tweets. I want to find all tweets that link to iTunes.
When I was using:
> select * from `tweets` where `url` not like '%twitter.com%' and `url` like '%itunes.apple.com%'
I could scale my server up to 16 CPUs, it would still take several minutes to search a few million tweets.
Yesterday I added another field to the database, `is_audio_url` where I pre-compute whether the URL is an itunes link (by string matching in the app code) when I insert the record into the database. So I can do:
> select * from `tweets` where `is_audio_url` = 1
And now it's blazing fast. It is just my most recent of many experiences that MySQL really struggles with text matching.
> It is just my most recent of many experiences that MySQL
1) You're using MySQL not Postgres; given that this is a discussion about whether Postgres can compete with Elasticsearch, that's not super relevant. :)
> select * from `tweets` where `url` not like '%twitter.com%' and `url` like '%itunes.apple.com%'
2) That's not how you query a full text index; that's going to be glacially slow.
You need a FULLTEXT index and to use a MATCH...AGAINST query. Check out the docs[1].
[1]: https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
You can use trgm module to index this kind of queries.
- Full text index - Extract the domain name to another column index it - Change mysql defaults - Change engine types. Maybe go in memory - Create lookup table of domains
And it works better than ElasticSearch, just due to the lower additional overhead.
What is the size of your database on disk?
The size of the database is a few dozen gigabytes by now, but that isn’t relevant with tsvector, only the row count has an effect on search speed.
I was asking because ranking can be slow in PostgreSQL. PostgreSQL can use a GIN or GiST index for filtering, but not for ranking, because the index doesn't contain the positional information needed for ranking.
This is not an issue when your query is highly selective and returns a low number of matching rows. But when the query returns a large number of matching rows, PostgreSQL has to fetch the ts_vector from heap for each matching row, and this can be really slow.
People are working on this but it's not in PostgreSQL core yet: https://github.com/postgrespro/rum.
This is why I'm a bit surprised by the numbers you shared: fulltext search on 270 million rows in below 65ms on commodity hardware (sub 8€/mo).
A few questions, if I may:
- What is the average number of rows returned by your queries? Is there a LIMIT?
- Is the ts_vector stored in the table?
- Do you use a GIN or GiST index on the ts_vector?
Cheers.