49 ms·

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

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 able to say, "keep in mind we'll need to budget a 3-mo transition to ES once we need X, Y, or Z".

kayodelycaon 5y ago
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.
- ezekg 5y ago
  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 https://pgmustard.com
  - kayodelycaon 5y ago
    Oh cool. I was right and wrong. Thanks!
  - snack-boye 5y ago
    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? Thanks
    - ezekg 5y ago
      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. :)
    - john-shaffer 5y ago
      You might be just fine adding an unindexed tsvector column, since you've already filtered down the results. The GIN indexes for FTS don't really work in conjunction with other indices, which is why https://github.com/postgrespro/rum https://github.com/postgrespro/rum exists. Luckily, it sounds like you can use your existing indices to filter and let postgres scan for matches on the tsvector. The GIN tsvector indices are quite expensive to build, so don't add one if postgres can't make use of it!
      - ezekg 5y ago
        Great read on the pitfalls of GIN indexes: https://iamsafts.com/posts/postgres-gin-performance/ https://iamsafts.com/posts/postgres-gin-performance/ (I'm not the author, but this did happen to me.)
amichal 5y ago
Postgres was not good at (for us) - IDF and other corpus based relevancy measures. had to hand roll - thesaurus and missspelling - again possible of course with preprocessing and by adding config files - non Latin alphabet languages. E.g. Arabic - needed filesystem access (we used aws rds so couldn’t do it) to add a dictionary based stemmed/word breaker We used es or solr for those cases. For English FTS with 100k documents doing it in PG is super easy and one less dependency
grncdr 5y ago
My experience has been that sorting by relevance ranking is quite expensive. I looked into this a bit and found https://github.com/postgrespro/rum https://github.com/postgrespro/rum (and some earlier slide decks about it) that explains why the GIN index type can't support searching and ranking itself (meaning you need to do heap scans for ranking). This is especially problematic if your users routinely do searches that match a lot of documents and you only want to show the top X results. Edit: if any of the Crunchy Data people are reading this: support for RUM indexes would be super cool to have in your managed service.
- craigkerstiens 5y ago
  Actually thats a great suggestion. We need to take a deeper look at the code itself and ability to support the extension, but we'll definitely take and evaluate it at some of our upcoming roadmap planning for Crunchy Bridge (https://www.crunchybridge.com https://www.crunchybridge.com).
  - grncdr 5y ago
    Cool cool If I could have your other ear for a moment: support for any EU-based cloud provider would be super nice. Ever since the Privacy Shield fig leaf was removed, questions about whether we store any data under US jurisdiction have become a lot more frequent.
    - craigkerstiens 5y ago
      Do you have a preferred one? We're currently on the big 3 US ones, in process of working on our 4th provider, our goal is very much to deliver the best Postgres experience whether on bare metal/on-premise or in the cloud, self hosted or fully managed. Edit: Always feel free to reach out directly, I'm always happy to spend time with anyone that has questions and usually pretty easy to track me down.
      - james_in_the_uk 5y ago
        Dublin usually works well from a regulatory perspective.
xcambar 5y ago
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.
- dante_dev 5y ago
  can you tell us the scale you're talking about? getting good enough results with ~1 billion rows
  - lacksconfidence 5y ago
    Can you tell us about your scoring function? Selecting 40M results from a dataset of ~1B and returning the top 10 based on some trivial scoring function is easy, any reasonable search system will handle that. The problem is when you have to run your scoring function on all 40M matching docs to decide which 10 are the most relevant. It's even more of a problem when your scoring function captures some of the complexities of the real word, rather than something trivial like tf/idf or bm25.
fizx 5y ago
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.
- quietbritishjim 5y ago
  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 https://github.com/zombodb/zombodb
  - mixmastamyk 5y ago
    You can do anything, anything at all, at https://zombo.com/ https://zombo.com/ "The only limit, is yourself…"
    - habibur 5y ago
      It's still around?
      - mixmastamyk 5y ago
        Klicken-Sie Link.
      - mumblemumble 5y ago
        That seems like one for the philosophers. If you completely rewrite a Flash site in HTML5, but it looks the same and has the same URL, is it still the same site?
mjewkes 5y ago
Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.
- e12e 5y ago
  Even if using ILIKE over the result of an imprecise query?
  - deleted 5y ago
    [deleted]
  - mjewkes 5y ago
    ILIKE itself is a linear scan. The only way to index them are trigram indicies, which are very inefficient (and sometimes not usable) if you're searching document-length content. Whether or not it works in your specific situation depends on your use case.
- simonw 5y ago
  Exact phrase searching works in PostgreSQL full-text search - here's an example: https://simonwillison.net/search/?q=%22nosql+database%22 https://simonwillison.net/search/?q=%22nosql+database%22 I'm using search_type=websearch https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00d4c95c88c8371cfc17453b0726c23/blog/views.py#L492-L494 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#TEXTSEARCH-PARSING-QUERIES https://www.postgresql.org/docs/11/textsearch-controls.html#...
  - SahAssar 5y ago
    I had some issues with this recently as I couldn't get a FTS query to find something looking like a path or url in a query. As an example from your site: https://simonwillison.net/2020/Jan/6/sitemap-xml/ https://simonwillison.net/2020/Jan/6/sitemap-xml/ contains the exact text https://www.niche-museums.com/ https://www.niche-museums.com/, but I cannot find a way to search for that phrase exactly (trying https://simonwillison.net/search/?q=%22www.niche-museums.com%22 https://simonwillison.net/search/?q=%22www.niche-museums.com... works though). I tried both in my own psql setup and on your site, and it seems like exact phrase searching is limited to the language used, even if it would be an exact string match. Are there any workarounds for that?
gk1 5y ago
Semantic search using text embeddings. With Open Distro for Elasticsearch you can store your text embeddings and then perform a nearest-neighbor search[1] to find most similar documents using cosine similarity[2]. Elasticsearch (vanilla) will get this feature with 8.0. If migrating to ES makes you groan you can use a managed service like Pinecone[3] (disclaimer: I work there) just for storing and searching through text embeddings in-memory through an API while keeping the rest of your data in PG. [1] Nearest-neighbor searches in Open Distro: https://opendistro.github.io/for-elasticsearch-docs/docs/knn/ https://opendistro.github.io/for-elasticsearch-docs/docs/knn... [2] More on how semantic similarity is measured: https://www.pinecone.io/learn/semantic-search/ https://www.pinecone.io/learn/semantic-search/ [3] https://www.pinecone.io https://www.pinecone.io
sandGorgon 5y ago
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.
- SigmundA 5y ago
  That why this really needs to get merged: https://github.com/postgrespro/rum https://github.com/postgrespro/rum
  - rattray 5y ago
    TF/IDF is listed as a TODO on that repo, and I don't see a PR which promises to provide it.
    - SigmundA 5y ago
      It lays the groundwork by storing the needed metadata in the index, yes it needs more work and I wish there was more interest to do it. I really think the is a neglected area and if PG was able to merge in TF-IDF and BM25 there would be little reason to use a separate search db / engine and many advantages with it being integrated.
Thaxll 5y ago
PG is average at best for text search, it's not even good.
nextaccountic 5y ago
If your search needs outgrow Postgres' native search engine, you can use Postgres search with an ElasticSearch backend, using Zombo https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb It basically gives you a new kind of index (create index .. using zombodb(..) ..)
matsemann 5y ago
I'm not well versed on modern pg for this use, but when I managed a Solr instance ~5 years ago, it was the ranking of the results that was the killer feature. Finding results fast most systems can do. Knowing which results to present is harder. Our case was a domain specific knowledge base, with certain terms occurring often in many articles. Searching for a term could bring up thousands of results, but few of them were actually relevant to show in context of the search, they just happened to use the term.
brightball 5y ago
Streaming data ingestion is the biggest. If you’re constantly writing data to be searched, this is where ES really outshines everything.
iav 5y ago
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.
jka 5y ago
"Faceted search"[1] (aka aggregates in Elasticsearch) tends to be a popular one, to provide user-facing content navigation. That said, simonw has been on the case[2] demonstrating an implementation of that using Django and PostgreSQL. [1] - https://en.wikipedia.org/wiki/Faceted_search https://en.wikipedia.org/wiki/Faceted_search [2] - https://simonwillison.net/2017/Oct/5/django-postgresql-faceted-search/ https://simonwillison.net/2017/Oct/5/django-postgresql-facet...
- craigds 5y ago
  This is the key one for us that makes Postgres a non-starter for FTS (We use postgres for everything else) We begrudgingly use Solr instead (we started before ES was really a thing and haven't found a need to switch yet) When you get more than about two different types of filters (e.g. types of filters could be 'tags', 'categories', 'geotags', 'media type', 'author' etc), the combinatorial explosion of Postgres queries required to provide facet counts gets unmanageable. For example, when I do a query filtered by `?tag=abc&category=category1`, I need to do these queries: - `... where tag = 'abc' and category_id = 1` (the current results) - `count(*) ... where tag = 'def' and category_id = 1` (for each other tag present in the results) - `count(*) ... where tag = 'abc' and category_id = 2` (for each other category present in the results) - `count(*) ... where category_id = 1` - `count(*) ... where tag = 'abc'` - `count(*)` There are certainly smarter ways to do this than lots of tiny queries, but all this complexity still ends up somewhere and isn't likely to be great for performance. Whereas solr/elasticsearch have faceting built in and handle this with ease.
  - kalev 5y ago
    This is exactly the issue I’m currently facing. We do a bunch of count queries to calculate facets and am looking for something that can do this out of the box. I’m glad I came to the same conclusion myself, either solr or elasticsearch might be the way to go. Starting this from scratch, which of the two would you recommend and why?
    - Ueland 5y ago
_009 5y ago
If you are looking to do semantic search (Cosine similarity) + filtering (SQL) on data that can be represented as vectors (audio, text, video, bio) I suggest, https://github.com/ankane/pgvector https://github.com/ankane/pgvector
some_developer 5y ago
Anectodal note: A few years ago we added yet-another part to our product and, whilst ES worked "okay", we got a bit weary of ES due to "some issues" (some bug in the architecture keeping things not perfect in sync, certain queries with "joins" of types taking long, demand on HW due to the size of database, no proper multi-node setup due to $$$ and time constraint, etc.; small things piling up over time). Bright idea: let's see how far Postgres, which is our primary datastore, can take us! Unfortunately, the feature never made it fully into production. We thought that on paper, the basic requirements were ideal: - although the table has multiple hundreds of millions of entries, natural segmentation by customer IDs made possible individual results much smaller - no weighted search result needed: datetime based is perfect enough for this use-case, we thought it would be easy to come up with the "perfect index [tm]" Alas, we didn't even get that far: - we identified ("only") 2 columns necessary for the search => "yay, easy" - one of those columns was multi-language; though we didn't have specific requirements and did not have to deal with language specific behaviour in ES, we had to decide on one for the TS vectorization (details elude me why "simple" wasn't appropriate for this one column; it was certainly for the other one) - unsure which one, or both, we would need, for one of the columns we created both indices (difference being the "language") - we started out with a GIN index (see https://www.postgresql.org/docs/9.6/textsearch-indexes.html https://www.postgresql.org/docs/9.6/textsearch-indexes.html ) - creating a single index took > 15 hours But once the second index was done, and had not even rolled out the feature in the app itself (which at this point was still an ever changing MVP), unrelated we suddenly got hit by lot of customer complains that totally different operations on this table (INSERTs and UPDATEs) started to be getting slow (like 5-15 seconds slow, something which usually takes tiny ms). Backend developer eyes were wide open O_O But since we knew that second index just finished, after checking the Posgres logs we decided to drop the FTS indices and, lo' and behold, "performance problem solved". Communication lines were very short back then (still are today, actually) and it was promptly decided we just cut the search functionality from this new part of the product and be done with it. This also solved the problem, basically (guess there's some "business lesson" to be learned here too, not just technical ones). Since no one within the company counter argued this decision, we did not spend more time analyzing the details of the performance issue though I would have loved to dig into this and get an expert on board to dissect this. -- A year later or so I had a bit free time and analyzed one annoying recurring slow UPDATE query problem on a completely different table, but also involving FTS on a single column there also using a GIN index. That's when I stumble over https://www.postgresql.org/docs/9.6/gin-implementation.html https://www.postgresql.org/docs/9.6/gin-implementation.html > Updating a GIN index tends to be slow because of the intrinsic nature of inverted indexes: inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed item). As of PostgreSQL 8.4, GIN is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. When the table is vacuumed or autoanalyzed, or when gin_clean_pending_list function is called, or if the pending list becomes larger than gin_pending_list_limit, the entries are moved to the main GIN data structure using the same bulk insert techniques used during initial index creation. This greatly improves GIN index update speed, even counting the additional vacuum overhead. Moreover the overhead work can be done by a background process instead of in foreground query processing. In this particular case I was able to solve the occasional slow UPDATE queries with "FASTUPDATE=OFF" on that table and, thinking back about the other issue, it might have solved or minimized the impact. Back to the original story: yep, this one table can have "peaks" of inserts but it's far from "facebook scale" or whatever, basically 1.5k inserts / second were the absolute rare peak I measured and usually it's in the <500 area. But I guess it was enough for this scenario to add latency within the database. -- Turning back my memory further, I was always "pro" trying to minimize / get rid of ES after learning about http://rachbelaid.com/postgres-full-text-search-is-good-enough/ http://rachbelaid.com/postgres-full-text-search-is-good-enou... even before we used any FTS feature. At also mentions the GIN/GiST issue but alas, in our case: ElasticSearch is good enough and, besides the thwarts we've with it, actually easier to reason about (so far).
nostrademons 5y ago
Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated and subtle problem, and frequently depends on signals that are not in the document itself. Google's big insight is that how other people talk about a website is more important than how the website talks about itself, and its ranking algorithm weights accordingly. ES has the basic building blocks to construct such a ranking algorithm. In terms of fundamental infrastructure I found ES to be just as good as Google, and better in some ways. But its out-of-the-box ranking function sucks. Expect to put a domain expert just on search ranking and evaluation to get decent results, and they're going to have to delve pretty deeply into advanced features of ES to get there. AFAICT Postgres search only lets you tweak the ranking algorithm by assigning different weights to fields, assuming that the final document score is a linear combination of individual fields. This is usually not what you want - it's pretty common to have non-linear terms from different signals.
- SOLAR_FIELDS 5y ago
  Nice insightful comment. Can you give a bit additional insight related to when it makes sense to use ES vs Postgres? My takeaway from your comment is that unless your product depends on search as a central component that inbuilt search is good enough. Is that an incorrect takeaway?
  - nostrademons 5y ago
    That's probably a decent takeaway. If full-text search is a "nice to have" (a bonus feature for advanced users, for example) you can probably rely on built-in Postgres search. If search is a fundamental way that users interact with your product, you want to spend the time and money to get it right, and that probably means ElasticSearch and a custom ranking function.
- __jem 5y ago
  I built a big feature with percolate as well and it really sold me on the possibilities of ES for product architecture.
animeshjain 5y ago
From what I know, full text search in Postgres (and MySQL) does not have faceted search. So it only supports returning full text results from the entire index. Actually, it is possible, but doing a search on a particular segment of rows is a very slow operation - say text search for all employees with name matching 'x', in organization id 'y'. It is not able to utilise the index on organization id in this case, and it results in a full scan.
manigandham 5y ago
It can't go that far. Postgres is limited by dictionaries, stemming/processing, query semantics (like fuzzy searching), and the biggest issue of all being a lack of modern relevance algorithms. It's good for limited scenarios where you just need more than a SQL LIKE statement, and chaining some functions together can get you decent results [1] without adding another datastore. However search tech is pretty mature with Lucene at the core and there are many better options [2] from in-process libraries to simple standalone servers to full distributed systems like Elastic. There are also other databases (relational like MemSQL, or documentstores like MongoDB/RavenDB) that are adding search as native querying functions with most of the abilities of ES. If search is a core or complex part of your application (like patterns in raw image data or similarities in audio waveforms) then that's where ES will excel. 1. https://stackoverflow.com/questions/46122175/fulltext-search-combined-with-fuzzysearch-in-postgresql/51433877#51433877 https://stackoverflow.com/questions/46122175/fulltext-search... 2. https://gist.github.com/manigandham/58320ddb24fed654b57b4ba22aceae25 https://gist.github.com/manigandham/58320ddb24fed654b57b4ba2...

kayodelycaon 5y ago

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.

ezekg 5y ago
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 https://pgmustard.com
- kayodelycaon 5y ago
  Oh cool. I was right and wrong. Thanks!
- snack-boye 5y ago
  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? Thanks
  - ezekg 5y ago
    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. :)
  - john-shaffer 5y ago
    You might be just fine adding an unindexed tsvector column, since you've already filtered down the results. The GIN indexes for FTS don't really work in conjunction with other indices, which is why https://github.com/postgrespro/rum https://github.com/postgrespro/rum exists. Luckily, it sounds like you can use your existing indices to filter and let postgres scan for matches on the tsvector. The GIN tsvector indices are quite expensive to build, so don't add one if postgres can't make use of it!
    - ezekg 5y ago
      Great read on the pitfalls of GIN indexes: https://iamsafts.com/posts/postgres-gin-performance/ https://iamsafts.com/posts/postgres-gin-performance/ (I'm not the author, but this did happen to me.)

amichal 5y ago

Postgres was not good at (for us) - IDF and other corpus based relevancy measures. had to hand roll - thesaurus and missspelling - again possible of course with preprocessing and by adding config files - non Latin alphabet languages. E.g. Arabic - needed filesystem access (we used aws rds so couldn’t do it) to add a dictionary based stemmed/word breaker We used es or solr for those cases. For English FTS with 100k documents doing it in PG is super easy and one less dependency

grncdr 5y ago

My experience has been that sorting by relevance ranking is quite expensive. I looked into this a bit and found https://github.com/postgrespro/rum https://github.com/postgrespro/rum (and some earlier slide decks about it) that explains why the GIN index type can't support searching and ranking itself (meaning you need to do heap scans for ranking). This is especially problematic if your users routinely do searches that match a lot of documents and you only want to show the top X results. Edit: if any of the Crunchy Data people are reading this: support for RUM indexes would be super cool to have in your managed service.

craigkerstiens 5y ago
Actually thats a great suggestion. We need to take a deeper look at the code itself and ability to support the extension, but we'll definitely take and evaluate it at some of our upcoming roadmap planning for Crunchy Bridge (https://www.crunchybridge.com https://www.crunchybridge.com).
- grncdr 5y ago
  Cool cool If I could have your other ear for a moment: support for any EU-based cloud provider would be super nice. Ever since the Privacy Shield fig leaf was removed, questions about whether we store any data under US jurisdiction have become a lot more frequent.
  - craigkerstiens 5y ago
    Do you have a preferred one? We're currently on the big 3 US ones, in process of working on our 4th provider, our goal is very much to deliver the best Postgres experience whether on bare metal/on-premise or in the cloud, self hosted or fully managed. Edit: Always feel free to reach out directly, I'm always happy to spend time with anyone that has questions and usually pretty easy to track me down.
    - james_in_the_uk 5y ago
      Dublin usually works well from a regulatory perspective.

xcambar 5y ago

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.

dante_dev 5y ago
can you tell us the scale you're talking about? getting good enough results with ~1 billion rows
- lacksconfidence 5y ago
  Can you tell us about your scoring function? Selecting 40M results from a dataset of ~1B and returning the top 10 based on some trivial scoring function is easy, any reasonable search system will handle that. The problem is when you have to run your scoring function on all 40M matching docs to decide which 10 are the most relevant. It's even more of a problem when your scoring function captures some of the complexities of the real word, rather than something trivial like tf/idf or bm25.

fizx 5y ago

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.

quietbritishjim 5y ago
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 https://github.com/zombodb/zombodb
- mixmastamyk 5y ago
  You can do anything, anything at all, at https://zombo.com/ https://zombo.com/ "The only limit, is yourself…"
  - habibur 5y ago
    It's still around?
    - mixmastamyk 5y ago
      Klicken-Sie Link.
    - mumblemumble 5y ago
      That seems like one for the philosophers. If you completely rewrite a Flash site in HTML5, but it looks the same and has the same URL, is it still the same site?

mjewkes 5y ago

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

e12e 5y ago
Even if using ILIKE over the result of an imprecise query?
- deleted 5y ago
  [deleted]
- mjewkes 5y ago
  ILIKE itself is a linear scan. The only way to index them are trigram indicies, which are very inefficient (and sometimes not usable) if you're searching document-length content. Whether or not it works in your specific situation depends on your use case.
simonw 5y ago
Exact phrase searching works in PostgreSQL full-text search - here's an example: https://simonwillison.net/search/?q=%22nosql+database%22 https://simonwillison.net/search/?q=%22nosql+database%22 I'm using search_type=websearch https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00d4c95c88c8371cfc17453b0726c23/blog/views.py#L492-L494 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#TEXTSEARCH-PARSING-QUERIES https://www.postgresql.org/docs/11/textsearch-controls.html#...
- SahAssar 5y ago
  I had some issues with this recently as I couldn't get a FTS query to find something looking like a path or url in a query. As an example from your site: https://simonwillison.net/2020/Jan/6/sitemap-xml/ https://simonwillison.net/2020/Jan/6/sitemap-xml/ contains the exact text https://www.niche-museums.com/ https://www.niche-museums.com/, but I cannot find a way to search for that phrase exactly (trying https://simonwillison.net/search/?q=%22www.niche-museums.com%22 https://simonwillison.net/search/?q=%22www.niche-museums.com... works though). I tried both in my own psql setup and on your site, and it seems like exact phrase searching is limited to the language used, even if it would be an exact string match. Are there any workarounds for that?

gk1 5y ago

Semantic search using text embeddings. With Open Distro for Elasticsearch you can store your text embeddings and then perform a nearest-neighbor search[1] to find most similar documents using cosine similarity[2]. Elasticsearch (vanilla) will get this feature with 8.0. If migrating to ES makes you groan you can use a managed service like Pinecone[3] (disclaimer: I work there) just for storing and searching through text embeddings in-memory through an API while keeping the rest of your data in PG. [1] Nearest-neighbor searches in Open Distro: https://opendistro.github.io/for-elasticsearch-docs/docs/knn/ https://opendistro.github.io/for-elasticsearch-docs/docs/knn... [2] More on how semantic similarity is measured: https://www.pinecone.io/learn/semantic-search/ https://www.pinecone.io/learn/semantic-search/ [3] https://www.pinecone.io https://www.pinecone.io

sandGorgon 5y ago

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.

SigmundA 5y ago
That why this really needs to get merged: https://github.com/postgrespro/rum https://github.com/postgrespro/rum
- rattray 5y ago
  TF/IDF is listed as a TODO on that repo, and I don't see a PR which promises to provide it.
  - SigmundA 5y ago
    It lays the groundwork by storing the needed metadata in the index, yes it needs more work and I wish there was more interest to do it. I really think the is a neglected area and if PG was able to merge in TF-IDF and BM25 there would be little reason to use a separate search db / engine and many advantages with it being integrated.

Thaxll 5y ago

PG is average at best for text search, it's not even good.

nextaccountic 5y ago

If your search needs outgrow Postgres' native search engine, you can use Postgres search with an ElasticSearch backend, using Zombo https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb It basically gives you a new kind of index (create index .. using zombodb(..) ..)

matsemann 5y ago

I'm not well versed on modern pg for this use, but when I managed a Solr instance ~5 years ago, it was the ranking of the results that was the killer feature. Finding results fast most systems can do. Knowing which results to present is harder. Our case was a domain specific knowledge base, with certain terms occurring often in many articles. Searching for a term could bring up thousands of results, but few of them were actually relevant to show in context of the search, they just happened to use the term.

brightball 5y ago

Streaming data ingestion is the biggest. If you’re constantly writing data to be searched, this is where ES really outshines everything.

iav 5y ago

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.

jka 5y ago

"Faceted search"[1] (aka aggregates in Elasticsearch) tends to be a popular one, to provide user-facing content navigation. That said, simonw has been on the case[2] demonstrating an implementation of that using Django and PostgreSQL. [1] - https://en.wikipedia.org/wiki/Faceted_search https://en.wikipedia.org/wiki/Faceted_search [2] - https://simonwillison.net/2017/Oct/5/django-postgresql-faceted-search/ https://simonwillison.net/2017/Oct/5/django-postgresql-facet...

craigds 5y ago
This is the key one for us that makes Postgres a non-starter for FTS (We use postgres for everything else) We begrudgingly use Solr instead (we started before ES was really a thing and haven't found a need to switch yet) When you get more than about two different types of filters (e.g. types of filters could be 'tags', 'categories', 'geotags', 'media type', 'author' etc), the combinatorial explosion of Postgres queries required to provide facet counts gets unmanageable. For example, when I do a query filtered by `?tag=abc&category=category1`, I need to do these queries: - `... where tag = 'abc' and category_id = 1` (the current results) - `count(*) ... where tag = 'def' and category_id = 1` (for each other tag present in the results) - `count(*) ... where tag = 'abc' and category_id = 2` (for each other category present in the results) - `count(*) ... where category_id = 1` - `count(*) ... where tag = 'abc'` - `count(*)` There are certainly smarter ways to do this than lots of tiny queries, but all this complexity still ends up somewhere and isn't likely to be great for performance. Whereas solr/elasticsearch have faceting built in and handle this with ease.
- kalev 5y ago
  This is exactly the issue I’m currently facing. We do a bunch of count queries to calculate facets and am looking for something that can do this out of the box. I’m glad I came to the same conclusion myself, either solr or elasticsearch might be the way to go. Starting this from scratch, which of the two would you recommend and why?
  - Ueland 5y ago

_009 5y ago

If you are looking to do semantic search (Cosine similarity) + filtering (SQL) on data that can be represented as vectors (audio, text, video, bio) I suggest, https://github.com/ankane/pgvector https://github.com/ankane/pgvector

some_developer 5y ago

Anectodal note: A few years ago we added yet-another part to our product and, whilst ES worked "okay", we got a bit weary of ES due to "some issues" (some bug in the architecture keeping things not perfect in sync, certain queries with "joins" of types taking long, demand on HW due to the size of database, no proper multi-node setup due to $$$ and time constraint, etc.; small things piling up over time). Bright idea: let's see how far Postgres, which is our primary datastore, can take us! Unfortunately, the feature never made it fully into production. We thought that on paper, the basic requirements were ideal: - although the table has multiple hundreds of millions of entries, natural segmentation by customer IDs made possible individual results much smaller - no weighted search result needed: datetime based is perfect enough for this use-case, we thought it would be easy to come up with the "perfect index [tm]" Alas, we didn't even get that far: - we identified ("only") 2 columns necessary for the search => "yay, easy" - one of those columns was multi-language; though we didn't have specific requirements and did not have to deal with language specific behaviour in ES, we had to decide on one for the TS vectorization (details elude me why "simple" wasn't appropriate for this one column; it was certainly for the other one) - unsure which one, or both, we would need, for one of the columns we created both indices (difference being the "language") - we started out with a GIN index (see https://www.postgresql.org/docs/9.6/textsearch-indexes.html https://www.postgresql.org/docs/9.6/textsearch-indexes.html ) - creating a single index took > 15 hours But once the second index was done, and had not even rolled out the feature in the app itself (which at this point was still an ever changing MVP), unrelated we suddenly got hit by lot of customer complains that totally different operations on this table (INSERTs and UPDATEs) started to be getting slow (like 5-15 seconds slow, something which usually takes tiny ms). Backend developer eyes were wide open O_O But since we knew that second index just finished, after checking the Posgres logs we decided to drop the FTS indices and, lo' and behold, "performance problem solved". Communication lines were very short back then (still are today, actually) and it was promptly decided we just cut the search functionality from this new part of the product and be done with it. This also solved the problem, basically (guess there's some "business lesson" to be learned here too, not just technical ones). Since no one within the company counter argued this decision, we did not spend more time analyzing the details of the performance issue though I would have loved to dig into this and get an expert on board to dissect this. -- A year later or so I had a bit free time and analyzed one annoying recurring slow UPDATE query problem on a completely different table, but also involving FTS on a single column there also using a GIN index. That's when I stumble over https://www.postgresql.org/docs/9.6/gin-implementation.html https://www.postgresql.org/docs/9.6/gin-implementation.html > Updating a GIN index tends to be slow because of the intrinsic nature of inverted indexes: inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed item). As of PostgreSQL 8.4, GIN is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. When the table is vacuumed or autoanalyzed, or when gin_clean_pending_list function is called, or if the pending list becomes larger than gin_pending_list_limit, the entries are moved to the main GIN data structure using the same bulk insert techniques used during initial index creation. This greatly improves GIN index update speed, even counting the additional vacuum overhead. Moreover the overhead work can be done by a background process instead of in foreground query processing. In this particular case I was able to solve the occasional slow UPDATE queries with "FASTUPDATE=OFF" on that table and, thinking back about the other issue, it might have solved or minimized the impact. Back to the original story: yep, this one table can have "peaks" of inserts but it's far from "facebook scale" or whatever, basically 1.5k inserts / second were the absolute rare peak I measured and usually it's in the <500 area. But I guess it was enough for this scenario to add latency within the database. -- Turning back my memory further, I was always "pro" trying to minimize / get rid of ES after learning about http://rachbelaid.com/postgres-full-text-search-is-good-enough/ http://rachbelaid.com/postgres-full-text-search-is-good-enou... even before we used any FTS feature. At also mentions the GIN/GiST issue but alas, in our case: ElasticSearch is good enough and, besides the thwarts we've with it, actually easier to reason about (so far).

nostrademons 5y ago

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated and subtle problem, and frequently depends on signals that are not in the document itself. Google's big insight is that how other people talk about a website is more important than how the website talks about itself, and its ranking algorithm weights accordingly. ES has the basic building blocks to construct such a ranking algorithm. In terms of fundamental infrastructure I found ES to be just as good as Google, and better in some ways. But its out-of-the-box ranking function sucks. Expect to put a domain expert just on search ranking and evaluation to get decent results, and they're going to have to delve pretty deeply into advanced features of ES to get there. AFAICT Postgres search only lets you tweak the ranking algorithm by assigning different weights to fields, assuming that the final document score is a linear combination of individual fields. This is usually not what you want - it's pretty common to have non-linear terms from different signals.

SOLAR_FIELDS 5y ago
Nice insightful comment. Can you give a bit additional insight related to when it makes sense to use ES vs Postgres? My takeaway from your comment is that unless your product depends on search as a central component that inbuilt search is good enough. Is that an incorrect takeaway?
- nostrademons 5y ago
  That's probably a decent takeaway. If full-text search is a "nice to have" (a bonus feature for advanced users, for example) you can probably rely on built-in Postgres search. If search is a fundamental way that users interact with your product, you want to spend the time and money to get it right, and that probably means ElasticSearch and a custom ranking function.
__jem 5y ago
I built a big feature with percolate as well and it really sold me on the possibilities of ES for product architecture.

animeshjain 5y ago

From what I know, full text search in Postgres (and MySQL) does not have faceted search. So it only supports returning full text results from the entire index. Actually, it is possible, but doing a search on a particular segment of rows is a very slow operation - say text search for all employees with name matching 'x', in organization id 'y'. It is not able to utilise the index on organization id in this case, and it results in a full scan.

manigandham 5y ago

It can't go that far. Postgres is limited by dictionaries, stemming/processing, query semantics (like fuzzy searching), and the biggest issue of all being a lack of modern relevance algorithms. It's good for limited scenarios where you just need more than a SQL LIKE statement, and chaining some functions together can get you decent results [1] without adding another datastore. However search tech is pretty mature with Lucene at the core and there are many better options [2] from in-process libraries to simple standalone servers to full distributed systems like Elastic. There are also other databases (relational like MemSQL, or documentstores like MongoDB/RavenDB) that are adding search as native querying functions with most of the abilities of ES. If search is a core or complex part of your application (like patterns in raw image data or similarities in audio waveforms) then that's where ES will excel. 1. https://stackoverflow.com/questions/46122175/fulltext-search-combined-with-fuzzysearch-in-postgresql/51433877#51433877 https://stackoverflow.com/questions/46122175/fulltext-search... 2. https://gist.github.com/manigandham/58320ddb24fed654b57b4ba22aceae25 https://gist.github.com/manigandham/58320ddb24fed654b57b4ba2...