10 ms·
Postgres full-text search: A search engine in a database (2021)
- jfbaro 4y agoI have seen this presentation from a company that used PG Full Text search for a pretty complex use case. Interesting -> http://matheusoliveira.s3-website-us-east-1.amazonaws.com/presentations/pgconfeu-20191018/#/ http://matheusoliveira.s3-website-us-east-1.amazonaws.com/pr... (updated thanks to ddevault)
- ddevault 4y agoPlease don't use URL shorteners. http://matheusoliveira.s3-website-us-east-1.amazonaws.com/presentations/pgconfeu-20191018/#/ http://matheusoliveira.s3-website-us-east-1.amazonaws.com/pr...
- jfbaro 4y agoUpdated! Thanks
- RowanH 4y agoGood link thinks. Have to say we're moving to pgSearch after implementing SOLR alongside a PG/Rails backend. Makes the stack simpler, less components to worry about (less headaches for gem versioning dependencies with SOLR). Lot to be said for it after reading through what's available now...
- sandGorgon 4y agoare you planning to do relevance ? this is one of the blockers for us - TFIDF vs BM25 or something. From what i know - PG fulltext seaarch does not implement relevance.
- rocmcd 4y agoThis has been my understanding of the state of Postgres full-text search. It's great if your search requirements are fairly vanilla, but I haven't seen any solutions for more advanced search needs, such boosting, relevance, scoring, etc.
- mamcx 4y agoAn alternative is to just layer the FTS on top of vanilla sql to get the extra stuff (this is what I do for my eCommerce backend), so is pretty simple to have something alike: SELECT .. -- Get the fts IN ( FTS QUERY) ORDER BY -- The relevance is hardcoded? mayber in another table that store th rankings? ( Products, Inventory, Invoices,.. ) I found is much easier and predictable if I code the "rankings" based on the business logic instead of let the FTS engine guess it. You can store that stuff as normal columns or use the "sources" (ie: products, inventory) as ways to know what could be more important to pull first. This have the nice property that our search results are ver good and better: Never return non-sensical stuff! (like searching for a apple in the store and get and blog post!)
- sandGorgon 4y agoi think we are talking a little bit different. it doesnt matter what the variables are (your business logic) or some other variables. Given a certain variable, tfidf/bm-25 will order by relevance and not by match. So it answers the question, what if the name match was off by two characters and the inventory number is less than 200. tf-idf does not tell you what to order by...but it takes care of all the edge cases of ordering. now if ur not using text match anywhere and only using business variables...then this entire thread is not for u. But FTS and lucene attack full text search primarily, and that's where the relevance vs ordering discussion comes from
- SahAssar 4y agoWhy does ts_rank not work for you? https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING https://www.postgresql.org/docs/current/textsearch-controls....
- adhoc_slime 4y agoVery convenient for me that this article swings around, I'm weighing pros and cons of PGSQL and elastic search for full-text search for the business's use-case.
- avereveard 4y agoAlso consider full ngram search, so you can do client side stemming and use a single index for all languages. It's not out of the box, but installing the ngram extension is literally one line.
- jonatron 4y agoLast time I looked, Postgres full text search doesn't really do CJK without third party parsers, which can be a bit of a deal breaker.
- zkirill 4y agoI love Postgres but lack of CJK full-text search is the main reason why I am going with Lucene/Solr for in-app search. It's possible to make Postgres do amazing things (e.g. job queue with SKIP LOCKED!) but at what point do you draw the line and use the right tool for the job?
- blowski 4y agoAny idea how well CJK works in general with Postgres? Or is it just on full text where it's lacking?
- mistrial9 4y agothe Japanese national phone company NTT is a major postgresql contributor
- zkirill 4y agoIn general, we have absolutely no problem with CJK in Postgres. Search is very application specific and some of our users want to do things like kana + kanji search. [1] I don't even know where you would start with that in Postgres. We also need stuff like language detection and analysis of mixed language data. A big advantage of going with a dedicated search tool is that it teaches you what you don't know about search, and it turned out that we knew pretty much nothing. [1] https://www.elastic.co/blog/implementing-japanese-autocomplete-suggestions-in-elasticsearch https://www.elastic.co/blog/implementing-japanese-autocomple...
- deleted 4y ago[deleted]
- chucky_z 4y agoWhen the feature doesn't exist! For existing workflows/functionality; I think using a new tool for a feature that doesn't (and won't for some time) exist is a _perfectly_ good reason to introduce the tool. On top of that you're looking at using something that is mature and well-used, Lucene/Solr. If you wanted to be cheeky you could go ask your CTO if they would foot the bill for someone like 2nd Quadrant to write CJK full-text search into pgsql. ;) I've done this for some features/issues in random OSS projects and if you feel someone else has the same problem I always feel paying money to have it implemented is well worth it.
- lettergram 4y agoI’ve written some tips on this before: https://austingwalters.com/fast-full-text-search-in-postgresql/ https://austingwalters.com/fast-full-text-search-in-postgres... Basically, it’s easy to create triggers that’ll update an index for you on entry. Then you can create custom indices that have key words, etc.
- Latty 4y agoIs there a benefit to using a trigger over a generated stored column?
- OJFord 4y agoNot sure it would apply all that often for search indices, but joins are the usual reason I can't use them when I want to; triggered functions (obviously) allow any query.
- lettergram 4y agoYou can technically do way more robust things in a trigger. If you don’t need it then :shrug: but I often create fairly robust updates
- SnowHill9902 4y agoTRIGGER can run on UPDATE.
- Latty 4y agoNot sure I follow, generated columns are updated on UPDATE, so that's just the same?
- iav 4y agoGenerated columns are not supported by older PG versions. For example if you used AWS AuroraDB with Postgres compatibility, they didn’t support generated columns till a few years ago. In my experience using PG FTS, there are often some preprocessing tasks you might like to do before storing the index. One common thing is the generated index has to fit inside the column length limit. So if your user provides a long text field with a lot of unique words, then the index will be very long and might lead to a database error. So here you can check for that on the fly, and truncate the result.
- puika 4y agoMandatory mention of the RUM extension (https://github.com/postgrespro/rum https://github.com/postgrespro/rum) if this caught your eye. Lots of tutorials and conference presentations out there showcasing the advantages in terms of ranking, timestamps...
- itsthecourier 4y agowe built a listings site similar to ebay in latin america after buying the operation from an european company, https://corotos.com https://corotos.com we made use of bson in the listings table for metadata and FTS inside postgres. spanish dictionaries, weighting, white-listings of words and including metadata inside the tsvectors have been awesome. really quick search, you never have unsync documents in another part of the stack and you save a lot in storage glad to help anybody going this path, no regrets.
- mixcocam 4y agoI think SQLite is even more batteries included! https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html
- shakezula 4y agoI used SQLite’s built in fuzzy search and it is surprisingly powerful. I used it to for full text search over a magic the gathering card database and it was amazing.
- cduzz 4y agoI've often wondered why someone doesn't reimplement elasticsearch using sqlite as the back-end datastore, instead of lucene. Or did I just describe foundationDB? I guess the real problem is that such a beast wouldn't have kibana, and people want kibana and put up with elasticsearch to get it.
- hiram112 4y agoAs far as I can tell, Lucene is the library that powers the main search-related features of Elastic Search i.e. inverted indices, stemming, tokenization, etc. Elastic Search, Amazon Open Search, Solr, etc. just wrapped Lucene up in a much easier to use HTTP / Rest API and then added bells and whistles like a coherent DSL, scripting, authentication and authorization, jobs, bulk API, etc. Then they built additional tools like Logstah and Kibana, which integrate using these APIs.
- OJFord 4y agoGP did say 'reimplement' (not making it sound trivial, like 'add a sqlite backend plugin') in fairness - i.e. in your terms 'why not implement the ES API for sqlite'.
- cduzz 4y agoMore like "make a thing that's a bit like elasticsearch, but exposing sqlite query language to a sharded event store" -- not "make elasticsearch but use lucene" because elasticsearch is lucene, and reimplementing it via sqlite wouldn't give elasticsearch. I'd call it elasticite and then (correctly!) be sued by both elastic and the sqlite orgs...
- ravirajx7 4y agoI am feeling real bad now as I used the like query as OP did (although in MySQL) for implementing a custom search over two fields ( Name & Description ) in one of my personal projects. I thought it worked pretty well though and I was so happy about it.
- giovannibonetti 4y agoDon't confuse full text search with word part search. If you have a list of names, you're probably looking for the latter one, where LIKE works fine and can be indexed with pg_trgrm.
- crtxcr 4y agoI was a bit disappointed by some limitations back then when I tried it for a project of mine. When searching phrases where ordering matters, phraseto_tsquery() does not quite work for larger documents, as the tsvector position values are quite limited: https://www.postgresql.org/docs/14/textsearch-limitations.html https://www.postgresql.org/docs/14/textsearch-limitations.ht... Here I had much better success with sqlite's FTS implemention.
- panabee 4y agoslightly off-topic, but if the goal is to store vast amounts of protein and gene nucleotide sequences and allow wildcard searches (e.g., %CTAAACGGG%), would postgres be the recommended approach? assume 200K proteins/genes with nucleotide sequences ranging from 20 base pairs to 2.3 million base pairs.
- giovannibonetti 4y agoPg_trgrm based indexes works well to make this type of LIKE %...% queries fast
- tveita 4y agoPostgreSQL will not perform well on genome data compared to specialized algorithms like BLAST and its alternatives. For exact search a suffix array should allow fast lookup, but DNA search usually requires approximate matches. I don't know what is state of the art these days, but this is probably a good place to start: https://en.wikipedia.org/wiki/BLAST_(biotechnology)#Alternatives_to_BLAST https://en.wikipedia.org/wiki/BLAST_(biotechnology)#Alternat...
- panabee 4y agothanks for the suggestion. the reason to create a BLAST alternative is to gain more granular control over matches. unless i'm mistaken, BLAST won't help on searches akin to this reg ex: GTC[ATGC]{3,10}AACCGT (i.e., GTC followed by any sequence of 3-10 nucleotides then ending with AACCGT.) another way of framing the goal is to enable reg ex searches over genome/protein data. do you know if anything allows this today?
- tveita 4y agoIn theory you can support this search with a suffix tree [1] or n-gram index [2], (but you will need n>3 because your alphabet is small.) I'm not sure what ready-made tools or libraries you might find for this - last time I checked there were a lot more papers than production-quality open source code. Or you could always just try a fast RE engine like Hyperscan and see if it is fast enough for your use case. [1] https://dl.acm.org/doi/10.1145/235809.235810 https://dl.acm.org/doi/10.1145/235809.235810 [2] https://swtch.com/~rsc/regexp/regexp4.html https://swtch.com/~rsc/regexp/regexp4.html
- phemartin 4y agomongodb has its own full-text implementation with Atlas Search. it has full Lucene capability. it's worth looking into. https://www.mongodb.com/docs/atlas/atlas-search/ https://www.mongodb.com/docs/atlas/atlas-search/
- cpursley 4y agoIf you're already running postgres, why would you set up mongo for search instead of elastic, etc?
- cpursley 4y agoHere's the approach I settled on that allows for fuzzy search (also some useful links at the bottom): https://gist.github.com/cpursley/e3586382c3a42c54ca7f5fef1665be7b https://gist.github.com/cpursley/e3586382c3a42c54ca7f5fef166... Downside is I haven't found a way to do weighting.
- qohen 4y agoFYI, this crunchydata.com blog-post was discussed 11 months ago on HN[0], in a thread awarded 601 points, with 137 comments. [0] https://news.ycombinator.com/item?id=27973497 https://news.ycombinator.com/item?id=27973497
- qohen 4y agoAmong other things in that earlier thread, there's some discussion of aspects of ZomboDB with its developer. ZomboDB is a Postgres extension that enables efficient full-text searching via the use of indexes backed by Elasticsearch. (From the project's website[0]). Lots more info in readme at the project's github[1]. [0] http://zombodb.com http://zombodb.com [1] https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
- simonw 4y agoThe hardest part of building any search engine is keeping the index up-to-date with changes made to the underlying data store. It's a solvable problem, but it's always a lot of work to build and to keep working as the database schema changes in the future. This is why I really like PostgreSQL FTS: it's good enough that for many projects I don't need to use an external search engine any more - and it's way easier to keep the search index up-to-date than if the index lives in a separate system. I wrote this tutorial on implementing faceted search with PostgreSQL and Django a while ago: https://simonwillison.net/2017/Oct/5/django-postgresql-faceted-search/ https://simonwillison.net/2017/Oct/5/django-postgresql-facet...
- nine_k 4y agoThat's interesting; I thought that computing the relevance of found texts, using a language and text structure models of sorts, is the hardest part.
- simonw 4y agoSure, that's where the hard computer science is - but if you use a tool like Elasticsearch other people have spent decades solving those problems for you already.
- soperj 4y agoHow hard is this on the database?
- brightball 4y agoIt’s just like any other index.
- canadiantim 4y agoHave you ever considered using PGroonga (https://pgroonga.github.io/ https://pgroonga.github.io/) to further extend FTS in postgres? They've got a drilldown feature that is basically their implementation of facets. Always wondered what your thoughts on that might be? I've got a django setup with postgres and facets implemented as you detailed in your blog (thank you for all the amazing info over the years btw!), now I'm looking to extend it with PGroonga and potentially considering Hasura to try and further extend what I can do while still using Postgres as my single source of truth without need for keeping any other service in sync.
- forinti 4y agoIt's much better to use elastic (or solr) and keep your text indices out of the database. They have more features and you keep the load separate. This is important if you suddenly have to index a ton of new documents or even recreate your indices. I used to keep text indices in Oracle and it would take days to sync them. There's even a Postgresql Foreign Data Wrapper for elastic, so you can integrate them easily. One project I witnessed used Postgresql solely as a middle man between the main database and Elastic.
- LAC-Tech 4y agoI really like the Postgres philosophy. It may not be the best full text search engine, or the best time series database, or the best document/"NoSQL" database. But if you use it you have all of the above, with having to wrangle micro services.
- kumarvvr 4y agoWith the right configuration, you can use it as either of them. Only thing is that data compression / formatting / structuring and corresponding indexing config must be good. A simple table with a PK and a document column is enough to make it into a great MongoDB alternative, upto a certain size.
- didip 4y agoI think PostgreSQL has enough features by now. They should try to focus on operational and scalability aspects now.
- threesquared 4y agoWhy would you use Postgres for this which doesn't horizontally scale when you could easily index documents in ES and use that for full text search?
- hhjinks 4y agoI worked on a project where we needed to search in a set of approximately 2,5M addresses, and return the adresses' approximate coordinates. We already used postgres for our other database needs, so slotting this in in our existing stack took us all of a couple days.
- elpatoisthebest 4y agoIf you only need modest search functionality and you don't want to manage another service/system, Postgres full-text search may be for you. http://rachbelaid.com/postgres-full-text-search-is-good-enough/ http://rachbelaid.com/postgres-full-text-search-is-good-enou... I read this article about 6 years ago and after an afternoon I added full-text search to the app I worked on at the time. 6 years later, it's still the only search they use. This small tool already built into postgres has been more than enough for us.
- bshipp 4y agoI've had similar luck with sqlite, of all things. Unless it's envisioned to handle huge user loads, sqlite does a pretty amazing job of FTS, all contained in a tiny binary that runs when I want and only when I want. I find it very useful for finding nuggets in titles and descriptions, spanning across thousands or millions of records. the index is often double or even triple the size of the database, but its worth it for a query that returns in a heartbeat.
- knighthack 4y agoFor static databases (especially where the content is not deleted, or rarely updated) consider using contentless tables or external content tables. The database size remains virtually the same, but you'll have immediate access to almost all FTS features.
- jwmoz 4y agoI use the trigram functionality for a movie name autocomplete feature, works pretty well.
- knighthack 4y agoI've since last year migrated virtually all serious content to SQLite, which handless most of my FTS needs flawlessly, or can be programmed to do it with little trouble. The only thing lacking is the lack of a default 'websearch' feature like in PostgreSQL, but you can always write functions to add search syntax and faceted searches.