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

Post by dante_dev » Tue, Jul 27, 2021, 4:24 PM UTC

ocimbote wrote: post #12

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 w…

Post by quietbritishjim » Tue, Jul 27, 2021, 4:25 PM UTC

fizx wrote: post #18

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 addin…

Post by gk1 » Tue, Jul 27, 2021, 4:29 PM UTC

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…

Post by lacksconfidence » Tue, Jul 27, 2021, 4:42 PM UTC

dante_dev wrote: post #21

ocimbote wrote: post #12

Earlier quoted context omitted.

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 w…

can you tell us the scale you're talking about? getting good enough results with ~1 billion rows

Post by kayodelycaon » Tue, Jul 27, 2021, 4:53 PM UTC

ezekg wrote: post #13

kayodelycaon wrote: post #5

Earlier quoted context omitted.

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 quer…