8 ms·
I can deeply vouch for the performance and joy of implementing Full-Text Search with PostgreSQL and Django. I just finished a a project where we chose Postgres
by agconti 9y ago
I can deeply vouch for the performance and joy of implementing Full-Text Search with PostgreSQL and Django.
I just finished a a project where we chose Postgres's FTS over using Elastic Search. At the beginning, I was worried about what performance we'd see since we choose to not use ES. But after slight performance tweaking, we had even our least performing queries under 50ms.
- kayhi 9y agoAny tips on what is worth tweaking?
- agconti 9y agoAbsolutely. - Using a `SearchVectorField` is a must after 500K rows. - Make keeping this field up to date easy for yourself by populating it using `SeachVector` with a Django pre_save signal or PostgreSQL trigger. This reduces CPU utilization significantly as the parsing and tokenization of the field your searching on is done a head of time. - Adding a GIN Index on your `SearchVectorField` column will improve performance dramatically. - You should specify your language configuration for postgres FTS parser. The default parser doesn't do much. It just removes spaces and normalize case. Specifying a language lets the parser make heavier optimizations that noticeably improve performance and the quality of results. If you need support for more then one langue, Django already makes it easy for this configuration to be dynamic.
- lobster_johnson 9y agoWhy not just have a GIN index on the expression to_tsvector(body, 'english') or whatever? Then you don't need to maintain a separate column.
- agconti 9y agoThe Postgres docs suggest using another column. My guess is that an expression index would be too large if it held the tokenized value of all of your FTS documents. These things can and often are entire written documents. Imagine the index size for 2 Million rows of tokenized documents at 2,000 words each. You might be able to get away with if if you were indexing a less then large amount of very small documents. I like your expression index idea a lot.
- lobster_johnson 9y agoBut if you're maintaining a separate ts_vector column, which you then index, you're creating the exact same amount of index data. Unless you're saying that you would populate the field only on some rows and not all of them, and control this from the app. But you could do that with an expression index, too, assuming the rule is a simple, pure function: CREATE INDEX index_posts_on_body ON posts (to_tsvector(body, 'english')) WHERE published = true; or similar.
- ddebernardy 9y agoNot the OP, and I haven't used PG regularly for a few years, but back then I vaguely recollect the query optimizer not behaving consistently based on whether you had a full column with a GIN (or GIST) index and a (potentially partial) index on an expression for some reason. In a nutshell it preferred using the full column with an index rather than the expression index. Even more importantly in some circumstances, having the full column allows the optimizer to pick another index when it's totally relevant, and filter the relevant rows without needing to recompute the TSV one by one for the subset.
- pauloxnet 9y agoIf your "document" is based also on columns on other table, as in the example on my article, you can't have a GIN index on your expression, but you can have a GIN index on your specified column.
- pauloxnet 9y agoVery good tips ;-)
- PhilipA 9y agoYou need to look at other stuff than performance - relevancy is probably the biggest thing when implementing search. Is it more relevant than what you experienced with ES?
- RasputinsBro 9y agoI second this. You can't let your queries take unusable amounts of time, but below a certain threshold relevancy is infinitely more important. I'm putting together a product which has a search feature and that uses Django + MySQL and I'm struggling with relevancy. I'd happily accept 500ms queries if that guaranteed me the relevant hit would be on the first page. That's FAR more usable than 50ms queries and then the relevant hit is on page 5.
- brightball 9y agoFull text search in MySQL isn’t in the same ballpark as PG. Thats not a dig at MySQL, just praise for the quality of what you get from the PG implementation.
- orf 9y agoWhy mysql? Search in pg is waaay better.
- pauloxnet 9y agoYes I think you need both of them of course and I found it on my project with Django and PostgreSQL.
- agconti 9y agoI'd argue that relevancy is more your application application's design then the underlying system retrieving the results. For example, putting the same dataset in Postgres or ES wouldn't make one deliver more relevant results given equal configurations. You could lean on the relevancy strategies built in to ES, but in my experience you're better off understanding what relevancy means for your dataset and implementing a strategy yourself. Your millage may vary though, I'd never advocate reimplenting something that's already provided by your chosen tool. The options and tools for configuring and tweaking relevancy between ES and PostgreSQL's FTS are surprisingly similar for many application use cases. If you're interested you can check out Postgres' search rank and query weighting configurations.
- holmberd 9y agoHaving implemented this for a client in the past I have to agree that it is a cheaper option than ElasticSearch, especially for smaller projects with a lower number of records to index. ElastiSearch easily gets expensive and the search suggestion is pretty bad.
- pauloxnet 9y agoThanks for your feedback, I obviously agree with you, but I'm starting to plan to use PG FTS with Django also in some bigger project. I hope to write another article about it in new future.
- holmberd 9y agoDepends on the data right. I've seen good performance on larger tables after using GIN indexing with records that rarely needs to be updated and simple queries. I'm not a expert in PostgreSQL by any means, but reducing cost and learning something never hurts.
- pauloxnet 9y agoI totally agree with you
- threeseed 9y agoWhat does this mean ? ElasticSearch starts off as a small Java application that wraps the Lucene library. Obviously heap will increase with usage and number of documents but I am still confused how it is in any way "expensive".
- pauloxnet 9y agoThe stack I proposed in my article is pretty simple: Django + PostgreSQL (DB + FTS). In other project I used Elastic for the search function: Django + PostgreSQL (DB) + Haystack + ES (FTS). Is obvious that the second solution is more expensive.
- pauloxnet 9y agoThanks, I'm happy to read similar experience from other developers.
- brightball 9y agoI can report the same with both Rails and Elixir. People reach for outside search tools far too quickly.
- pauloxnet 9y agoYou are right