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

11–20 of 141 posts

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

#12

Post by ocimbote »

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…

This is anecdote, not proper feedback, since I wasn't directly involved in the topic.

My company relied on PG as its search engine and everything went well from POC to production. After a few years of production and new clients requiring volumes of data an order of magnitude above our comfort zone, things went south pretty fast.

Not many months later but many sweaty weeks of engineering after, we switched to ES and we're not looking back.

tl;dr; even with great DB engineers (which we had), I'd suggest that scale is a strong limiting factor on this feature.

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

#13

Post by ezekg »

kayodelycaon wrote: post #5
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…

If I recall correctly, Postgres search doesn't scale well. Not sure where it falls apart but it isn't optimized in the same way something like Solr is.

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 queries (1-10s query time), but that's likely due to very infrequent access i.e. cold cache.

I will say, if you use open source libraries like pg_search, you are unlikely to ever have performant full-text search. Most full-text queries need to be written by hand to actually utilize indexes, instead of the query-soup that these types of libraries output. (No offense to the maintainers -- it's just how it be when you create a "general" solution.)

[0]: https://pgmustard.com

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

#14

Post by rattray »

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)`.

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

#18

Post by fizx »

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…

Hi, I started an Elasticsearch hosting company, since sold, and have built products on PG's search and SQLite FTS search.

There are in my mind two reasons to not use PG's search.

1. Elasticsearch allows you to build sophisticated linguistic and feature scoring pipelines to optimize your search quality. This is not a typical use case in PG.

2. Your primary database is usually your scaling bottleneck even without adding a relatively expensive search workload into the mix. A full-text search tends to be around as expensive as a 5% table scan of the related table. Most DBAs don't like large scan workloads.

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

#19

Post by mjewkes »

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.

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

#20

Post by theandrewbailey »

> You could also look into enabling extensions such as unaccent (remove diacritic signs from lexemes) or pg_trgm (for fuzzy search).

Trigrams (pg_trgm) are practically needed for usable search when it comes to misspellings and compound words (e.g. a search for "down loads" won't return "downloads").

I also recommend using websearch_to_tsquery instead of using the cryptic syntax of to_tsquery.

Post reply on HN