Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres Full-Text Search: A search engine in a database

blog.crunchydata.com

Post reply on HN
Print view View original

61–70 of 141 posts

Re: Postgres Full-Text Search: A search engine in a database

#61

Post by ezekg »

snack-boye wrote: post #38
ezekg wrote: post #13

Earlier quoted context omitted.

I have a table with over a billion rows and most full-text searches still respond in around a few milliseconds. I think this will depend on a lot of factors, such as proper indexing, and filtering down the dataset as much as possible before performing the full-text ops. I've spent a considerable amount of time on optimizing these queries, thanks to tools like PgMustard [0]. Granted, I do still have a couple slow quer…

Silly question, I'm using pg right now and most of my queries are something like this (in english) Find me some results in my area that contain these categoryIds and are slotted to start between now and next 10 days. Since its already quite a filtered set of data, would that mean I should have little issues adding pg text search because with correct indexing and all, it will usually be applied to a small set of data?…

I'm not a DBA, so I can't say for certain simply due to a gap in my knowledge. But in my experience, it depends on a lot of factors. Sometimes pg will use an index before performing the search ops, other times a subquery is needed. Check out pgmustard and dig into your slow query plans. :)

Re: Postgres Full-Text Search: A search engine in a database

#62

Post by thom »

We get really nice results with gist indexes (gist_trgm_ops) searching across multiple entity types to do top X queries. It’s very useful to be able to make a stab at a difficult-to-spell foreign football player’s name, possibly with lots of diacritics, and get quick results back. I’m always surprised when I find a search engine on any site that is so unkind as to make you spell things exactly.

Re: Postgres Full-Text Search: A search engine in a database

#63

Post by simonw »

mjewkes wrote: post #19
rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.

Exact phrase searching works in PostgreSQL full-text search - here's an example: https://simonwillison.net/search/?q=%22nosql+database%22

I'm using search_type=websearch https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00...

That's using websearch_to_tsquery() which was added in PostgreSQL 11: https://www.postgresql.org/docs/11/textsearch-controls.html#...

Re: Postgres Full-Text Search: A search engine in a database

#64

Post by rattray »

mjewkes wrote: post #19
rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.

Pardon my ignorance – what is exact phrase matching and why doesn't it work with tsvector?

Re: Postgres Full-Text Search: A search engine in a database

#66

Post by rattray »

SigmundA wrote: post #44
sandGorgon wrote: post #30

Earlier quoted context omitted.

it doesnt do TF-IDF or BM-25 - the current state of art in search relevance algorithms. that's where it cant be used for anything serious.

That why this really needs to get merged: https://github.com/postgrespro/rum

TF/IDF is listed as a TODO on that repo, and I don't see a PR which promises to provide it.

Re: Postgres Full-Text Search: A search engine in a database

#67

Post by iav »

rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

I moved from ElasticSearch to PG FTS in production, and here are the things I had to give up:

1. PostgreSQL has a cap on column length, and the search index has to be stored in a column. The length of the column is indeterminate - it is storing every word in the document and where it's located, so a short document with very many unique words (numbers are treated as words too) can easily burst the cap. This means you have to truncate each document before indexing it, and pray that your cap is set low enough. You can use multiple columns but that slows down search and makes ranking a lot more complicated. I truncate documents at 4MB.

2. PostgreSQL supports custom dictionaries for specific languages, stemmers, and other nice tricks, but none of those are supported by AWS because the dictionary gets stored as a file on the filesystem (it's not a config setting). You can still have custom rules like whether or not numbers count as words.

Re: Postgres Full-Text Search: A search engine in a database

#68

Post by jjice »

bityard wrote: post #16

I know Postgres and SQLite have mostly different purposes but FWIW, SQLite also has a surprisingly capable full-text search extension built right in: https://www.sqlite.org/fts5.html

It's very impressive, especially considering the SQLite version you're already using probably has it enabled already. I use it for a small site I run and it works fantastic. Little finicky with deletes and updates due to virtual tables in SQLite, but definitely impressive and has its uses.

Re: Postgres Full-Text Search: A search engine in a database

#69

Post by FpUser »

mixmastamyk wrote: post #32
quietbritishjim wrote: post #23

Earlier quoted context omitted.

Do you see any particular reasons to use or not use ZomboDB [1]? It claims to lets you use ElasticSearch from PG seamlessly e.g. it manages coherency of which results ought to be returned according to the current transaction. (I've never quite ended up needing to use ES but it's always seemed to me I'd be likely to need ZomboDB if I did.) [1] https://github.com/zombodb/zombodb

You can do anything, anything at all, at https://zombo.com/ "The only limit, is yourself…"

This one is my favorite for many many years. Good for relaxing.

Re: Postgres Full-Text Search: A search engine in a database

#70

Post by lettergram »

rattray wrote: post #14
lettergram wrote: post #6

I actually built a search engine back in 2018 using postgresql https://austingwalters.com/fast-full-text-search-in-postgres... Worked quite well and still use it daily. Basically doing weighted searches on vectors is slower than my approach, but definitely good enough. Currently, I can search around 50m HN & Reddit comments in 200ms on the postgresql running on my machine.

Nice – looks like the ~same approach recommended here of adding a generated `tsvector` column with a GIN index and querying it with `col @@ @@ to_tsquery('english', query)`.

Yeah my internal approach was creating custom vectors which are quicker to search.
Post reply on HN