11 ms·

Do you have FULLTEXT indexes on those rows? I find it hard to believe that searching something like e.g. a fragment of text in Youtube comments can be as fast i

• qaq 9y ago
  It will not be as fast but it will be fast enough for the wast majority of use cases.
• kuschku 9y ago
  I'm doing fulltext search on 270 million rows in postgres (with textvector indices) in below 65ms on commodity hardware (sub 8€/mo). And it works better than ElasticSearch, just due to the lower additional overhead.
  • ngrilly 9y ago
    How do you sort the result? Do you use ts_rank? What is the size of your database on disk?
    • kuschku 9y ago
      I use ts_rank combined with many other factors (as I also rank by time, by message type, etc). 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.
      • ngrilly 9y ago
        Thanks for following up :-) 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 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.
• qaq 9y ago
  I can assure you that Pinterest's dataset is vastly bigger than 100GB :) at certain scale RDBMS will obviously experience issues and might no longer be the optimal solution. For PG (hard to generalize) but beyond 10-20TB things become painful. Now the thing is that "limit" is constantly shifting so if you are starting with 100GB datasets and it is growing at 200GB a year you can basically stay on single instance RDBMS forever.
  • firasd 9y ago
    Sorry, I'm just not buying that it's about the file size of data. 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.
    • njs12345 9y ago
      If you wanted to, you could even use a trigger to compute that field, and then backfill it - no changes to app code required!
    • qaq 9y ago
      I am not selling anything to you :) You can scale your server to even 100 vCpu or whatever blackbox name the provider is using and still have same speed mainly because MySQL does not have parallel query :). BTW you none ever mentioned MySQL but you keep brining it up.
    • merb 9y ago
      the problem is that you are probably not using the correct index. considering that when you use a '%' at the beginning of a string you need to use at least a GIN (or was it gist?) index with string ops as far as I remember but actually using % at the beginning is not recommended. I think using a tsquery might be more recommended for contain searches. at least on postgresql
• brightball 9y ago
  PG is something of a different beast too. With MySQL the standard mode of operation for most non-basic functionality is to dump the data in another tool. 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.
  • ris 9y ago
    > Not putting the data somewhere else keeps you from having to maintain a second system, keep things in sync, etc. 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.
    • brightball 9y ago
      True, but it's transaction safe so should be impossible to get out of sync which is what I was really getting at.