9 ms·
Full-Text Search in Django with PostgreSQL
- steve-chavez 9y agoNot to discredit Django but if you're already using PostgreSQL you could use http://postgrest.com http://postgrest.com which is a more tightly knit component for fts. With PostgREST you can avoid the abstractions overhead and just use plain SQL and REST calls, basically to do a fts using `to_tsquery` you would do: CREATE TABLE blog_entry( headline text, body_text tsvector ); GET localhost:3000/blog_entry?body_text=fts.Cheese&select=headline
- pauloxnet 9y agoThanks for your link. I read about Postgrest months ago and I think is a good software. I think Django is good for some problem and postgrest for other.
- kuschku 9y agoConsidering your nickname, @OP, I assume you’re the author of the website: The site is currently broken on screens with a width of 1024px or less. This screenshot was taken on my secondary monitor, which is 1280x1024 in portrait mode: https://i.imgur.com/n3LGCKM.png https://i.imgur.com/n3LGCKM.png
- StavrosK 9y agoIt does something differently funky for me: https://i.imgur.com/gnXgBK5.png https://i.imgur.com/gnXgBK5.png Also I'm not a fan of all the visual noise, such as the orange paragraph markers next to every title. EDIT: Replaced "fucking" with the intended "funky", I have no idea how that typo happened.
- pauloxnet 9y agoThanks for your report, which browser are you using to take the screenshot ? I used the default markdown plugin to generate paragraph markers and I found convenient to link directly to a paragraph.
- StavrosK 9y agoI'm using Firefox 57 on Ubuntu. You may want to hide the markers until someone hovers over the title in question, like in the Django docs[1]. [1]: https://docs.djangoproject.com/en/2.0/ https://docs.djangoproject.com/en/2.0/
- pauloxnet 9y agoI updated markers as you suggested, thanks again http://www.paulox.net/2017/12/22/full-text-search-in-django-with-postgresql/#thank-you http://www.paulox.net/2017/12/22/full-text-search-in-django-...
- pauloxnet 9y agoI'm using Firefox 57 on Ubuntu 17.10. Thanks for your suggestion about the paragraph markers. I'll update it. ;-)
- TJSomething 9y agoI'm seeing the same thing in Firefox 57.0.3 on Debian, if my viewport is more than or equal to 1320 pixels wide. It goes away for the text on screen if I force it to repaint, but comes back if I scroll or resize the viewport.
- pauloxnet 9y agoThanks for the report.
- aurelian15 9y agoSame problem here. Seems to be a font rendering issue, the strange artefacts disappear at certain zoom levels. My platform is Fedora 27 with Firefox 57.0.1.
- pauloxnet 9y agoThanks for the report. I'm thinking to change font.
- rahimnathwani 9y agoWorks fine on mobile. Until it's fixed you can probably just narrow your browser window until the hero image moves from the side to the top.
- pauloxnet 9y agoThanks for the report, I'm going to check this issue. Can I ask which browser are you using ?
- yeukhon 9y agoA small visual complain. The Django logo is really too big. I am on a big screen, not sure if that's relevant. But it's preferable to scale back. Firefox 57 on Mac.
- pauloxnet 9y agoThanks for your feedback. The django logo is an svg and i tested only on a HD monitor and i like it. Maybe in a bigger screen it's too big but I haven't worked a lot on the design of my blog. I hope you enjoyed the content more than the logo. ;-)
- yeukhon 9y agoYes! Sorry if I didn't mention it :-) thank you!
- sus_007 9y agoSeems like OP is using one of the Pelican themes, most of them last maintained 5 years ago.
- pauloxnet 9y agoYes, I used pelican with Flex theme for my blog. http://www.paulox.net/about/#blog http://www.paulox.net/about/#blog The Flex theme is not so old, but can be better, but I'm going to update it based on many report I received here. BTW pelican site is quite fast https://developers.google.com/speed/pagespeed/insights/?url=www.paulox.net&tab=desktop https://developers.google.com/speed/pagespeed/insights/?url=...
- pauloxnet 9y agoThanks for your and other report I found the issue and fixed it. I think now the layout is working correctly on low-width window https://i.imgur.com/BCaPTEt.png https://i.imgur.com/BCaPTEt.png
- agconti 9y agoI 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.
- sandGorgon 9y agoDoes anyone know how to play with relevance and spell corrections in FTS ? In elasticsearch, relevance is Tf-IDF or BM25. I don't see anything similar in FTS. Not sure how I can results around ranking as well as spell correction/suggestions simultaneously.
- pauloxnet 9y agoI used Elastic in past project as I reported in my article, but I never used relevance, can you add a link to the elastic official documentation about it ?
- softwaredoug 9y agoI might shamelessly plug my own book, Relevant Search (http://Manning.com/books/relevant-search http://Manning.com/books/relevant-search) which introduces relevance on Elasticsearch. Ping me (http://o19s.com/doug http://o19s.com/doug) if you’d like a discount code!
- sandGorgon 9y agohere - https://www.elastic.co/guide/en/elasticsearch/reference/current/index-modules-similarity.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... similarity/relevance is the mathematical difference between search and database query. Its an inexact match.
- pauloxnet 9y agoThanks. I'll read it.
- burntsushi 9y agoIf you want to reason about these things from first principles, then I recommend the IR book, which is free online: https://nlp.stanford.edu/IR-book/ https://nlp.stanford.edu/IR-book/
- rpedela 9y ago
- rpedela 9y agoCan you expand on the difficulty with synchronization? With ES I understand because you have to build it from scratch. However Solr has the DataImportHandler [1] which is made to sync data from a relational database. 1. https://lucene.apache.org/solr/guide/7_2/uploading-structured-data-store-data-with-the-data-import-handler.html#dataimporthandler-commands https://lucene.apache.org/solr/guide/7_2/uploading-structure...
- yeukhon 9y agoHave worked with ES... If you want the out-of-the-box distributed experience, then you can't go wrong with Elasticsearch because of the built-in sharding mechanism. However, figuring out how many shards you need for each index, where should each shard sits, and how to balance out the cluster has always been a trial and error exercise. Indices continue to grow like there is infinite space, and performance will degrade. What I find useful in the end is to have multiple ES clusters, which seems obvious right? One of the most frequently asked questions is "how many nodes do I need", and the answer is always "it depends" which is true, but gives me a chill. With Cassandra, from my experience, adding more nodes (scale horizontally) so data spread more thinly, and revisit some data structure change, would be an acceptable answer. The other thing is a lot of folks I have worked with just dump stuff into ES because ES feels like a dumping ground for "json"-like documents (reminds of me MongoDB). Please compress, understand mappings and different fields to make the document smaller... I am still very convinced SQL databases are robust enough to take any lazy dumping.
- threeseed 9y agoWhy not just run ElasticSearch on a single instance then ? That way you're not having to completely redo your search infrastructure at some point in time. You can seamlessly scale from 1 document to billions without any change to your code, backup approach, monitoring etc.
- yeukhon 9y ago> Why not just run ElasticSearch on a single instance then ? Sorry, I am not sure why you brought up a single instance. If you run on a single instance, you lose half of the benefits of ES which is trying to be highly available. If we have an index which does not need any backups, single instance is fine, but for most people single instance is not an option. Furthermore, if one is already struggling with 3-4 nodes, how helpful is single instance?
- threeseed 9y agoI brought up single instance since you suggested using a SQL database most of which don't support a distributed deployment in the same way ElasticSearch does. My point was merely that ElasticSearch scales from very small use cases to very large ones. You don't have to setup a cluster. And that using your SQL database isn't going to be significantly easier to manage.
- nicolashahn 9y agoWe use Flask with SQLAlchemy, this guide on sqlalchemy-searchable: https://sqlalchemy-searchable.readthedocs.io/en/latest/installation.html#quickstart https://sqlalchemy-searchable.readthedocs.io/en/latest/insta... had us easily swap out Algolia for search with Postgres FTS.
- tinyhouse 9y agoNot familiar with SQLAlchemy. May I ask where do you host the search index and also why you decided to leave Algolia? Thanks.
- nicolashahn 9y agoSearch index is created by adding a automatically populated search vector column to each searchable table in Postgres and appropriate SQLAlchemy models. Left Algolia partly because our old implementation was poorly done, overly complicated, and becoming difficult to maintain, but also because Postgres FTS could give us equivalent search capabilities (we didn't need all the features Algolia had to offer) for free.
- pauloxnet 9y agoI think everyone that use PostgreSQL FTS had similar stories and motivation to switch from external software/service.
- pauloxnet 9y agoThanks for the link
- hitekker 9y agoDoes the Django postgres module handle partial word lookups?
- 11235813213455 9y agoI'll take the occasion to show my lib https://github.com/caub/pg-tsquery https://github.com/caub/pg-tsquery for making valid postgres text searches
- penetrarthur 9y agoWhy hasn't anyone brought up Django Haystack? http://django-haystack.readthedocs.io/en/master/tutorial.html http://django-haystack.readthedocs.io/en/master/tutorial.htm...
- pauloxnet 9y agoI used haystack in different project to connect to Elastisearch as I wrote in my article, but in this article I wrote about Django Full-Text Search with PostgreSQL and you don't need haystack to use it, because there's a 'contrib' module in Django code https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/search/ https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/s...
- penetrarthur 9y agoOut of curiosity, which one of them is faster for doing complex search?
- pauloxnet 9y agoI'm sorry but "complex search" is too generic. I can say that in my project the PG FTS is quite fast but a lot less complex than ES
- pauloxnet 9y agoBTW I migrated the djangoproject.com from ES to PG FTS and it works great https://github.com/django/djangoproject.com/commit/1be9a483fe876d36de35c5ece1000f16eadb0f13 https://github.com/django/djangoproject.com/commit/1be9a483f... I'll write another article about it and, if they vote it, I'll have a talk about it in the next PyCon Nove conference http://www.paulox.net/talks/#pycon-nove http://www.paulox.net/talks/#pycon-nove