11 ms·

Full text search over Postgres: Elasticsearch vs. alternatives

319 points by philippemnoel 2y ago

ashconnor 2y ago
Facets are possible in Postgres but it looks complex: https://web.archive.org/web/20200815141031/https://roamanalytics.com/2019/04/16/faceted-search-with-postgres-using-tsvector/ https://web.archive.org/web/20200815141031/https://roamanaly... pg_search [0] not to be confused with the ruby gem of the same name [1]: [0] - https://github.com/paradedb/paradedb/tree/dev/pg_search#overview https://github.com/paradedb/paradedb/tree/dev/pg_search#over... [1] - https://github.com/Casecommons/pg_search https://github.com/Casecommons/pg_search
- simonw 2y ago
  I implemented faceted search on my own blog entirely on top of PostgreSQL. It works well, but that's because my blog has only 11,348 documents total. Notes on how I built that here: https://simonwillison.net/2017/Oct/5/django-postgresql-faceted-search/ https://simonwillison.net/2017/Oct/5/django-postgresql-facet...
  - przemub 2y ago
    That's a lot of posts for a blog :)
- bdzr 2y ago
  Faceting over even small result sets and producing the relevant counts is really hard for postgres, and caching tends to be impossible due to the combinatorics of the problem. There's some discussion around supporting better indexing options here that is relevant: https://www.postgresql.org/message-id/CAJqPDh9o%3DoxRP%3DxZhbCzzAs4Vr5hNUkYLMspGee%2B%3DmgbqG3tAg%40mail.gmail.com https://www.postgresql.org/message-id/CAJqPDh9o%3DoxRP%3DxZh.... Practically, https://github.com/cybertec-postgresql/pgfaceting https://github.com/cybertec-postgresql/pgfaceting is the best bet for people who must use postgres even though it still uses a manual maintenance step.
radpanda 2y ago
> Because Elasticsearch is not a reliable data store, organizations that use Postgres typically extract, transform, and load (ETL) data from Postgres to Elasticsearch I’ll admit haven’t kept up with this but is it still the case that Elasticsearch is “not a reliable data store”? I remember there used to be a line in the Elasticsearch docs saying that Elasticseach shouldn’t be your primary data store or something to that effect. At some point they removed that verbiage, seemingly indicating more confidence in their reliability but I still hear people sticking with the previous guidance.
- wordofx 2y ago
  ES is just unreliable. Can be running smoothly for a year and boom it falls over and you’re left scratching your head.
  - cyberes 2y ago
    That exact scenario just happened to me a few days ago.
    - agumonkey 2y ago
      What would you consider as replacement
      - alexpeng 2y ago
        SingleStore is pretty good at ES use cases. Low latency, scalability, real time ingest, full text search + millisecond query times.
        agumonkey 2y ago
        Thanks a lot
        papadany 2y ago
        Yes, SingleStore seems to be really good.
simonw 2y ago
The BM25 thing is actually a really big deal. BM25 is similar to TF/IDF. In both cases, the key idea is to consider statistics of the overall corpus as part of relevance calculations. If the user searches for "charities in new orleans" in a corpus where "new orleans" is only represented in a few documents, those should clearly rank highly. If the corpus has "new orleans" in almost every document then the term "charity" is more important. PostgreSQL FTS cannot do this, because it doesn't maintain statistics for word frequencies across the entire corpus. This severely limits what it can implement in terms of relevance scoring - each result is scored based purely on if the search terms are present or not. For comparison, SQLite FTS (which a lot of people are unaware of) actually does implement full index statistics, and SQLite FTS5 implements BM25 out of the box.
- beardedetim 2y ago
  BM25 is definitely a big deal when you're doing FTS. It's one of the reasons I've switched to Arango as my DB for the project I'm working on that needs FTS. The fact it also comes with graphs means I don't need to bang my head against CTEs either. Not saying Arango can replace Postgres but for my needs, it's a much better fit AND it offers the FTS that I need out of the box.
  - philippemnoel 2y ago
    Arango is sweet! We've actually talked to many people who switched from Postgres to Arango because of its better FTS. This was one of the reasons for creating ParadeDB in the first place. Many of the users who made this switch wished they could have stayed in Postgres without compromising on FTS.
- packetlost 2y ago
  I wish SQLite FTS had a nicer to use API. Last time I looked (a few months ago) it was very confusing and not very user friendly.
  - simonw 2y ago
    I've built a bunch of Python (and CLI) code to try and help make that work better: https://sqlite-utils.datasette.io/en/stable/python-api.html#full-text-search https://sqlite-utils.datasette.io/en/stable/python-api.html#... import sqlite_utils db = sqlite_utils.Database("news.db") db["articles"].enable_fts(["headline", "body"]) results = list(db["articles"].search("softball")) Or with the CLI tool: https://sqlite-utils.datasette.io/en/stable/cli.html#configuring-full-text-search https://sqlite-utils.datasette.io/en/stable/cli.html#configu... sqlite-utils enable-fts news.db articles headline body sqlite-utils search news.db articles softball
deleted 2y ago
[deleted]
samsk 2y ago
For internet user facing full-text search I would always prefer to use a separate tool and not a SQL database, because - the fulltext tool, can and should hold only 'active' data - as it has only active data, data size is usually much much smaller - as data size is smaller, it better fits in RAM - as data size is smaller, it can be probably run on poorer HW the full ACID db - as the indexed data are mostly read-only, the VM where it runs can be relatively easily cloned (never seen a corruption till now) - as FTS tools are usually schema-less, there is no outage during schema changes (compared to doing changes in ACID db) - as the indexed data are mostly read-only, the can be easily backup-ed - as the backups are smaller, restoring a backup can be very fast - and there is no such thing as database upgrade outage, you just spin a new version, feed it with new data and than change the backends - functionality and extensibility There is probably more, but if one doesn't needs to do a fulltext search on whole database (and you usually don't), than its IMHO better to use separate tool, that doesn't comes with all the ACID constraints. Probably only downside is that you need to format data for the FTS and index them, but if you want run a serious full-text search, you will have to take almost the same steps in the database. On a 15y old side project, I use SOLR for full-text search, serving 20-30k/request per day on a cheap VM, and PostgreSQL is used as primary data source. The PostgreSQL has had several longer outages - during major upgrades, because of disk corruption, because of failed schema migrations, because of 'problems' between the chair and keyboard etc... During that outages the full-text search always worked - it didn't had most recent data, but most users probably never noticed.
- whalesalad 2y ago
  playing devils avocate > the fulltext tool, can and should hold only 'active' data very possible with postgres, too. instead of augmenting your primary table to support search, you would have a secondary/ephemeral table serving search duties > as data size is smaller, it better fits in RAM likewise, a standalone table for search helps here, containing only the relevant fields and attributes. this can be further optimized by using partial indexes. > as FTS tools are usually schema-less, there is no outage during schema changes (compared to doing changes in ACID db) postgresql can be used in this manner by using json/jsonb fields. instead of defining every field, just define one field and drop whatever you want in it. > as the indexed data are mostly read-only, the can be easily backup-ed same for postgres. the search table can be exported very easily as parquet, csv, etc. > as the backups are smaller, restoring a backup can be very fast tbh regardless of underlying mechanism, if your search index is based on upstream data it is likely easier to just rebuild it versus restoring a backup of throwaway data. > The PostgreSQL has had several longer outages - during major upgrades, because of disk corruption, because of failed schema migrations, because of 'problems' between the chair and keyboard etc... to be fair, these same issues can happen with elasticsearch or any other tool. how big was your data in solr?
corytheboyd 2y ago
> We’ve talked to many companies who have tried and regretted their decision to use Elasticsearch as their primary data store oh no…
haolez 2y ago
Are there success cases of using ElasticSearch/OpenSearch as the primary data store in production? Just curious.
- j0ba 2y ago
  I've been using it for a small real estate app for like ~8 years. Zero problems, works like a dream. I use MySQL for some app-user data, but most of the bulk data is on ES. Solo dev running a 3 node cluster on Hetzner.
amai 2y ago
Alternatives to both are https://www.meilisearch.com/ https://www.meilisearch.com/ https://typesense.org/ https://typesense.org/ and maybe https://github.com/Sygil-Dev/whoosh-reloaded https://github.com/Sygil-Dev/whoosh-reloaded
- emmanueloga_ 2y ago
  Manticore is seldom mentioned around here but it seems to be a really good alternative [1]. The fact that is the search engine Craigslist uses gives them a lot of credibility. This may be a bit silly, but I wonder if the design of their blog and website makes them look a bit "old fashioned" or something. -- 1: https://manticoresearch.com/blog/manticore-alternative-to-elasticsearch/ https://manticoresearch.com/blog/manticore-alternative-to-el...
  - pqdbr 2y ago
    Funny you mentioned because I also get that vibe from the Manticore website. We do judge books by their covers afterall.
    - philippemnoel 2y ago
      Manticore is primarily oriented towards the MySQL world
      - emmanueloga_ 2y ago
        It uses the MySQL protocol and has some extra features for MySQL users like the Federated engine [1], so it should definitely be nice for MySQL users. But it also has an HTTP API [2] so sending data to manticore should be no different than sending it to Elastic, Meili or whatnot. -- 1: https://manual.manticoresearch.com/Extensions/FEDERATED https://manual.manticoresearch.com/Extensions/FEDERATED 2: https://manual.manticoresearch.com/Connecting_to_the_server/HTTP https://manual.manticoresearch.com/Connecting_to_the_server/...
- arkh 2y ago
j45 2y ago
Solr still seems to be an option
- cortesoft 2y ago
  Solr and ElasticSearch are both based on Lucene.
  - j45 2y ago
    I remember Lucene, forgot that Solr is a derivative
- 4RealFreedom 2y ago
  Just upgraded an old system to solr 9. I've been very impressed with it.
alanwli 2y ago
Always great to see Postgres-based alternatives. One clarification question - the blog post lists "lack of ACID transactions and MVCC can lead to data inconsistencies and loss, while its lack of relational properties and real-time consistency makes many database queries challenging" as the bad for ElasticSearch. What is pg_bm25's consistency model? It had been mentioned previously as offering "weak consistency" [0], which I interpret to have the same problems with transactions, MVCC, etc? [0]: https://news.ycombinator.com/item?id=37864089 https://news.ycombinator.com/item?id=37864089
- retakeming 2y ago
  Good question. That was from a very old version of pg_bm25 (since renamed to pg_search). BM25 indexes are now strongly consistent.
  - alanwli 2y ago
    Nice! I've seen other extensions that don't have transactional semantics, which runs counter to the norm for PG. So since it was previously weakly consistent due to performance reasons, how does strong consistency affect transactional inserts/updates latency?
    - philippemnoel 2y ago
      It adds a small overhead to transactions. The exact number depends on how much you're inserting and indexing, but is in the milliseconds. We have adopted strong consistency because we've observed most of our customers run ParadeDB pg_search in a separate instance from their primary Postgres, to tune the instance differently and pick more optimized hardware. The data is sent from the primary Postgres to the ParadeDB instance via logical replication, which is Zero-ETL. In this model, there are no transactions powering the app executed against the ParadeDB instance, only search queries, so this is a non-issue. We had previously adopted weak consistency because we expected our customers to run pg_search in their primary Postgres database, but observed this is not what they wanted. This is specifically for our mid-market/enterprise customers. We may bring back weak consistency as an optional feature eventually, as it would enable faster ingestion.
coding123 2y ago
The analytical part is what usually makes the Elasticsearch (or opensearch) the choice - it's not that you have to search just the text, its that with that text you need to show 5 different categories some of the top values and the number of occurrences and permutations thereof if the user decides to clicky clicky and filter it more.
- pqdbr 2y ago
  How does Elasticsearch (or the ELK stack) would help implement what you described?
  - paulmd 2y ago
    ELK stack is unrelated, this is the analytical/OLAP use-case not the logging stack stuff. Although you can say that logging is a specific use-case of the analytical/OLAP. Search for the discussion around "facets". Basically the way to think of this is, elastic/solr are MapReduce with weak database semantics layered on top. The ingest stage is the "map", you ingest JSON documents and optionally perform some transformation on them. The query is the "reduce", and you are performing some analytical summation or search on top of this. The point of having the "map" step is that on ingest, you can augment the document with various things like tokenizations or transforms, and then write those into the stored representation, like a generated column. This happens outside your relational DB and thus doesn't incur RDBMS storage space for the transformed (and duplicated!) data, or the generation expense at query time. Pushing that stuff inside your relational store is wasteful. It's an OLAP store. You have your online/OLTP store, you extract your OLAP measurements as a JSON document and send it to the OLAP store. The OLAP store performs some additional augmentations, then searches on it. What facets let you do is have "groups within groups". So you have company with multiple employees inside it, you could do "select * from sales where company = 'dell' group by product facet by employee". But it's actually a whole separate layer that runs on top of grouping (and actually behaves slightly differently!), and because of the way this is done (inverted indexes) this is actually incredibly efficient for many many groups. It's built on a "full-scan every time" idiom rather than maintaining indexes etc... but it actually makes full-scans super performant, if you stay on the happy path. And because you're full-scanning every time, you can actually build the information an index would have contained as you go... hence "inverted index". You are collecting pointers to the rows that fit a particular filter, like an index, and aggregating on them as it's built. And the really clever bit is that it can actually build little trees of these counts, representing the different slices through OLAP hyperspace you could take when navigating your object's dimensions. So you can know that if you filter by employee there are 8 different types of products, and if we filter by "EUV tool" then there is only 1 company in the category. Which is helpful for hinting that sidebar product navigation tree in stores etc. A lot of times if you look carefully at the HTML/JS you can see evidence of this, it will often explicitly mention facets and sometimes expose Lucene syntax directly.
killme2008 2y ago
I believe the primary issue with using full-text search in PostgreSQL is its performance and scalability limitations. Elasticsearch/OpenSearch offers more than just search functionality; they come with extensive ecosystems, including ELK for logging and processors for ETL pipelines. These platforms are powerful and provide out-of-the-box solutions for developers. However, as the article mentioned, scaling them up or out can be costly. ParadeDB looks like is based on tantivy(https://github.com/quickwit-oss/tantivy https://github.com/quickwit-oss/tantivy) which is an impressive project. We leverage it to implement full-text indexing for GreptimeDB too (https://github.com/GreptimeTeam/greptimedb https://github.com/GreptimeTeam/greptimedb). Nevertheless, building full-text indexes with Tantivy is still resource-intensive. This is why Grafana Loki, which only indexes tags instead of building full-text indexes, is popular in the observability space. In GreptimeDB, we offer users the flexibility to choose whether to build full-text indexes for text fields, while always creating inverted indexes for tags.
deleted 2y ago
[deleted]
izietto 2y ago
Is this distributed as a PostgreSQL extension as well?
- Ulti 2y ago
  yeah looks like how they distribute too, basically pre installed and configured pg_search pg_datalake and pgvector
  - philippemnoel 2y ago
    Correct. If you run your own Postgres, you can install pg_search directly, otherwise we suggest our users integrate with their existing Postgres deployments (say AWS RDS) via logical replication: https://docs.paradedb.com/replication/pg_search https://docs.paradedb.com/replication/pg_search
jillesvangurp 2y ago
If you care about search quality, postgres ends up being fairly limited. I've done stuff with it; it's alright for really simple stuff. But it's the wrong tool for the job generally. If the order of the search results matters to you, you might want something that gives you some more tools to control that. And if you are not measuring search quality to begin with, you probably don't care enough to even know that you are missing the tools to do a better job. I consult clients on this stuff professionally and I've seen companies do all sorts of silly shit. Mostly it's because they simply lack the in house expertise which is usually how they end up talking to me. I've actually had to sit clients down and explain them their own business model. Usually I come in for some technical problem and then end up talking to product managers or senior managers about stuff like this because usually the real problem is at that level. The technical issues are just a symptom. Here's a discussion I had with a client fairly recently (paraphrasing/exaggerating, obviously): "Me: So your business model is that your users find shit on your web site (it has a very prominent search box at the top) and then some transaction happens that causes you to make money? Customer: yes. Me: so you make more money if your search works better and users find stuff they want. Customer: yes, we want to make more money! Me: congratulations, you are a search company! Customer: LOL whut?! Me: So, why aren't you doing the kinds of things that other search companies do to ensure you maximize profit? Like measuring how good your search is or generally giving a shit whether users can actually find what they are looking for. Customer: uhhhhh ???? Me: where's your search team? Customer: oh we don't have one, you do it! Me: how did you end up with what you currently have. Customer: oh that guy (some poor, overworked dev) over there picked solution X 3 years ago and we never gave it a second thought." Honestly, some companies can't be helped and this was an example of a company that was kind of hopelessly flailing around and doing very sub optimal things at all levels in the company. And wasting lots of time and money in the process. Not realizing your revenue and competitiveness are literally defined by your search quality is never a good sign. You take different decisions if you do.
- porsager 2y ago
  I certainly get the sentiment of your comment, but I'd be much more interested to hear your take on ParadeDB which the post is about?
glintik 2y ago
Postgres has very limited features related to FTS, so it is useful only for very simple cases or for very limited budget. Elasticsearch is stable enough(but good devops and devs needed) and has numerous of features. Anyway Elasticsearch is not a "plug and play" solution and requires much work to be done for good search quality. Why I know this all? I'm running ecommerce search startup, based on Elasticsearch as primary storage.
sgift 2y ago
Solr. The correct answer is Solr. All the search features of Elasticsearch (both are built on Lucene, which provides the actual search implementation) without the problems that Elastic (the company) or Elasticsearch (the product) brings with it. 99% of companies using Elasticsearch would be far better of with Solr.
- dig1 2y ago
  Solr will work perfectly if you want to do a standard search (db, shop) or need a customized approach, like custom transformers, ranking, etc. However, if you aim for "something that just works good enough" and has much better scaling capability and tooling, you can't beat ES/OpenSearch here (Solr scaling is much more manual process than ES/OS). And if you want to stay away from ElasticSearch drama, stick to OpenSearch - they are shipping excellent stuff these days.
- maxloh 2y ago
  Never heard of it before. I find that Apache often does not do well at promoting their products."
  - lastofthemojito 2y ago
    Solr is seen (probably unfairly) as passé by many people. I used Solr back in 2008-2009 or so and it did a great job, but people didn't like that it used XML rather than JSON. Then we had a requirement for something like Elasticsearch's percolate query functionality, which Solr didn't support. So we switched, and subsequent projects I've joined have all used ES. As I understand, Solr now has a JSON REST API and it's improved in other ways over the years, but ES has quite a bit more mindshare these days: https://books.google.com/ngrams/graph?content=solr%2Celasticsearch&year_start=2000&year_end=2022&corpus=en&smoothing=3&case_insensitive=false https://books.google.com/ngrams/graph?content=solr%2Celastic... So now in addition to the "does it do the job?" question, teams might also ask "can we hire and retain people to work with this technology rather than the more popular alternative?".
sideway 2y ago
Semi-related: I guess some companies have already started augmenting their search with the use of LLMs. If you've worked on a similar project, what is your (very) high-level architecture? Did you see a noticeable difference in relevance and query time?
- cpursley 2y ago
  I haven't used LLMs but am using a hybrid approach with pg_vector: https://github.com/agoodway/vecto https://github.com/agoodway/vecto (some useful links in the README). One way that comes to mind is using LLMs is to re-write the query before shoving it into the search engine (known as "query rewriting"). More full-text ideas here: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#search https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
Sytten 2y ago
AWS RDS support is key IMO, I have done a lot of consulting on infrastructure and most clients I had don't want to have third party services and don't want to manage their DBs.
- philippemnoel 2y ago
  Agreed! But for most of our customers, they want to separate their OLTP Postgres from their Search Postgres. This is because query patterns differ, and you want to tune Postgres and choose hardware differently. This also provides higher reliability and ensures search queries can't slow down transactions. ParadeDB implements this by ingesting logical replication (WALs) from the primary Postgres. This gives a "closely coupled" solution, where you have the feel of a single Postgres database (ParadeDB is essentially an extra read replica in your Postgres HA setup). More here: https://docs.paradedb.com/replication/pg_search https://docs.paradedb.com/replication/pg_search
dewey 2y ago
I've had good experiences with both Postgres + ES and also Postgres + Meilisearch. The latter one I only used for small side projects but it was very easy to get running (With a RoR app).
demilich 2y ago
https://github.com/infiniflow/infinity https://github.com/infiniflow/infinity, dense vector + sparse vector + fulltext search(BM25) + late interact reranker(Colbert)
deleted 2y ago
[deleted]

319 points by philippemnoel 2y ago

Facets are possible in Postgres but it looks complex: https://web.archive.org/web/20200815141031/https://roamanalytics.com/2019/04/16/faceted-search-with-postgres-using-tsvector/ https://web.archive.org/web/20200815141031/https://roamanaly... pg_search [0] not to be confused with the ruby gem of the same name [1]: [0] - https://github.com/paradedb/paradedb/tree/dev/pg_search#overview https://github.com/paradedb/paradedb/tree/dev/pg_search#over... [1] - https://github.com/Casecommons/pg_search https://github.com/Casecommons/pg_search

simonw 2y ago
I implemented faceted search on my own blog entirely on top of PostgreSQL. It works well, but that's because my blog has only 11,348 documents total. Notes on how I built that here: https://simonwillison.net/2017/Oct/5/django-postgresql-faceted-search/ https://simonwillison.net/2017/Oct/5/django-postgresql-facet...
- przemub 2y ago
  That's a lot of posts for a blog :)
bdzr 2y ago
Faceting over even small result sets and producing the relevant counts is really hard for postgres, and caching tends to be impossible due to the combinatorics of the problem. There's some discussion around supporting better indexing options here that is relevant: https://www.postgresql.org/message-id/CAJqPDh9o%3DoxRP%3DxZhbCzzAs4Vr5hNUkYLMspGee%2B%3DmgbqG3tAg%40mail.gmail.com https://www.postgresql.org/message-id/CAJqPDh9o%3DoxRP%3DxZh.... Practically, https://github.com/cybertec-postgresql/pgfaceting https://github.com/cybertec-postgresql/pgfaceting is the best bet for people who must use postgres even though it still uses a manual maintenance step.

radpanda 2y ago

> Because Elasticsearch is not a reliable data store, organizations that use Postgres typically extract, transform, and load (ETL) data from Postgres to Elasticsearch I’ll admit haven’t kept up with this but is it still the case that Elasticsearch is “not a reliable data store”? I remember there used to be a line in the Elasticsearch docs saying that Elasticseach shouldn’t be your primary data store or something to that effect. At some point they removed that verbiage, seemingly indicating more confidence in their reliability but I still hear people sticking with the previous guidance.

wordofx 2y ago
ES is just unreliable. Can be running smoothly for a year and boom it falls over and you’re left scratching your head.
- cyberes 2y ago
  That exact scenario just happened to me a few days ago.
  - agumonkey 2y ago
    What would you consider as replacement
    - alexpeng 2y ago
      SingleStore is pretty good at ES use cases. Low latency, scalability, real time ingest, full text search + millisecond query times.
      - agumonkey 2y ago
        Thanks a lot
      - papadany 2y ago
        Yes, SingleStore seems to be really good.

simonw 2y ago

The BM25 thing is actually a really big deal. BM25 is similar to TF/IDF. In both cases, the key idea is to consider statistics of the overall corpus as part of relevance calculations. If the user searches for "charities in new orleans" in a corpus where "new orleans" is only represented in a few documents, those should clearly rank highly. If the corpus has "new orleans" in almost every document then the term "charity" is more important. PostgreSQL FTS cannot do this, because it doesn't maintain statistics for word frequencies across the entire corpus. This severely limits what it can implement in terms of relevance scoring - each result is scored based purely on if the search terms are present or not. For comparison, SQLite FTS (which a lot of people are unaware of) actually does implement full index statistics, and SQLite FTS5 implements BM25 out of the box.

beardedetim 2y ago
BM25 is definitely a big deal when you're doing FTS. It's one of the reasons I've switched to Arango as my DB for the project I'm working on that needs FTS. The fact it also comes with graphs means I don't need to bang my head against CTEs either. Not saying Arango can replace Postgres but for my needs, it's a much better fit AND it offers the FTS that I need out of the box.
- philippemnoel 2y ago
  Arango is sweet! We've actually talked to many people who switched from Postgres to Arango because of its better FTS. This was one of the reasons for creating ParadeDB in the first place. Many of the users who made this switch wished they could have stayed in Postgres without compromising on FTS.
packetlost 2y ago
I wish SQLite FTS had a nicer to use API. Last time I looked (a few months ago) it was very confusing and not very user friendly.
- simonw 2y ago
  I've built a bunch of Python (and CLI) code to try and help make that work better: https://sqlite-utils.datasette.io/en/stable/python-api.html#full-text-search https://sqlite-utils.datasette.io/en/stable/python-api.html#... import sqlite_utils db = sqlite_utils.Database("news.db") db["articles"].enable_fts(["headline", "body"]) results = list(db["articles"].search("softball")) Or with the CLI tool: https://sqlite-utils.datasette.io/en/stable/cli.html#configuring-full-text-search https://sqlite-utils.datasette.io/en/stable/cli.html#configu... sqlite-utils enable-fts news.db articles headline body sqlite-utils search news.db articles softball

deleted 2y ago

[deleted]

samsk 2y ago

For internet user facing full-text search I would always prefer to use a separate tool and not a SQL database, because - the fulltext tool, can and should hold only 'active' data - as it has only active data, data size is usually much much smaller - as data size is smaller, it better fits in RAM - as data size is smaller, it can be probably run on poorer HW the full ACID db - as the indexed data are mostly read-only, the VM where it runs can be relatively easily cloned (never seen a corruption till now) - as FTS tools are usually schema-less, there is no outage during schema changes (compared to doing changes in ACID db) - as the indexed data are mostly read-only, the can be easily backup-ed - as the backups are smaller, restoring a backup can be very fast - and there is no such thing as database upgrade outage, you just spin a new version, feed it with new data and than change the backends - functionality and extensibility There is probably more, but if one doesn't needs to do a fulltext search on whole database (and you usually don't), than its IMHO better to use separate tool, that doesn't comes with all the ACID constraints. Probably only downside is that you need to format data for the FTS and index them, but if you want run a serious full-text search, you will have to take almost the same steps in the database. On a 15y old side project, I use SOLR for full-text search, serving 20-30k/request per day on a cheap VM, and PostgreSQL is used as primary data source. The PostgreSQL has had several longer outages - during major upgrades, because of disk corruption, because of failed schema migrations, because of 'problems' between the chair and keyboard etc... During that outages the full-text search always worked - it didn't had most recent data, but most users probably never noticed.

whalesalad 2y ago
playing devils avocate > the fulltext tool, can and should hold only 'active' data very possible with postgres, too. instead of augmenting your primary table to support search, you would have a secondary/ephemeral table serving search duties > as data size is smaller, it better fits in RAM likewise, a standalone table for search helps here, containing only the relevant fields and attributes. this can be further optimized by using partial indexes. > as FTS tools are usually schema-less, there is no outage during schema changes (compared to doing changes in ACID db) postgresql can be used in this manner by using json/jsonb fields. instead of defining every field, just define one field and drop whatever you want in it. > as the indexed data are mostly read-only, the can be easily backup-ed same for postgres. the search table can be exported very easily as parquet, csv, etc. > as the backups are smaller, restoring a backup can be very fast tbh regardless of underlying mechanism, if your search index is based on upstream data it is likely easier to just rebuild it versus restoring a backup of throwaway data. > The PostgreSQL has had several longer outages - during major upgrades, because of disk corruption, because of failed schema migrations, because of 'problems' between the chair and keyboard etc... to be fair, these same issues can happen with elasticsearch or any other tool. how big was your data in solr?

corytheboyd 2y ago

> We’ve talked to many companies who have tried and regretted their decision to use Elasticsearch as their primary data store oh no…

haolez 2y ago

Are there success cases of using ElasticSearch/OpenSearch as the primary data store in production? Just curious.

j0ba 2y ago
I've been using it for a small real estate app for like ~8 years. Zero problems, works like a dream. I use MySQL for some app-user data, but most of the bulk data is on ES. Solo dev running a 3 node cluster on Hetzner.

amai 2y ago

Alternatives to both are https://www.meilisearch.com/ https://www.meilisearch.com/ https://typesense.org/ https://typesense.org/ and maybe https://github.com/Sygil-Dev/whoosh-reloaded https://github.com/Sygil-Dev/whoosh-reloaded

emmanueloga_ 2y ago
Manticore is seldom mentioned around here but it seems to be a really good alternative [1]. The fact that is the search engine Craigslist uses gives them a lot of credibility. This may be a bit silly, but I wonder if the design of their blog and website makes them look a bit "old fashioned" or something. -- 1: https://manticoresearch.com/blog/manticore-alternative-to-elasticsearch/ https://manticoresearch.com/blog/manticore-alternative-to-el...
- pqdbr 2y ago
  Funny you mentioned because I also get that vibe from the Manticore website. We do judge books by their covers afterall.
  - philippemnoel 2y ago
    Manticore is primarily oriented towards the MySQL world
    - emmanueloga_ 2y ago
      It uses the MySQL protocol and has some extra features for MySQL users like the Federated engine [1], so it should definitely be nice for MySQL users. But it also has an HTTP API [2] so sending data to manticore should be no different than sending it to Elastic, Meili or whatnot. -- 1: https://manual.manticoresearch.com/Extensions/FEDERATED https://manual.manticoresearch.com/Extensions/FEDERATED 2: https://manual.manticoresearch.com/Connecting_to_the_server/HTTP https://manual.manticoresearch.com/Connecting_to_the_server/...
arkh 2y ago

j45 2y ago

Solr still seems to be an option

cortesoft 2y ago
Solr and ElasticSearch are both based on Lucene.
- j45 2y ago
  I remember Lucene, forgot that Solr is a derivative
4RealFreedom 2y ago
Just upgraded an old system to solr 9. I've been very impressed with it.

alanwli 2y ago

Always great to see Postgres-based alternatives. One clarification question - the blog post lists "lack of ACID transactions and MVCC can lead to data inconsistencies and loss, while its lack of relational properties and real-time consistency makes many database queries challenging" as the bad for ElasticSearch. What is pg_bm25's consistency model? It had been mentioned previously as offering "weak consistency" [0], which I interpret to have the same problems with transactions, MVCC, etc? [0]: https://news.ycombinator.com/item?id=37864089 https://news.ycombinator.com/item?id=37864089

retakeming 2y ago
Good question. That was from a very old version of pg_bm25 (since renamed to pg_search). BM25 indexes are now strongly consistent.
- alanwli 2y ago
  Nice! I've seen other extensions that don't have transactional semantics, which runs counter to the norm for PG. So since it was previously weakly consistent due to performance reasons, how does strong consistency affect transactional inserts/updates latency?
  - philippemnoel 2y ago
    It adds a small overhead to transactions. The exact number depends on how much you're inserting and indexing, but is in the milliseconds. We have adopted strong consistency because we've observed most of our customers run ParadeDB pg_search in a separate instance from their primary Postgres, to tune the instance differently and pick more optimized hardware. The data is sent from the primary Postgres to the ParadeDB instance via logical replication, which is Zero-ETL. In this model, there are no transactions powering the app executed against the ParadeDB instance, only search queries, so this is a non-issue. We had previously adopted weak consistency because we expected our customers to run pg_search in their primary Postgres database, but observed this is not what they wanted. This is specifically for our mid-market/enterprise customers. We may bring back weak consistency as an optional feature eventually, as it would enable faster ingestion.

coding123 2y ago

The analytical part is what usually makes the Elasticsearch (or opensearch) the choice - it's not that you have to search just the text, its that with that text you need to show 5 different categories some of the top values and the number of occurrences and permutations thereof if the user decides to clicky clicky and filter it more.

pqdbr 2y ago
How does Elasticsearch (or the ELK stack) would help implement what you described?
- paulmd 2y ago
  ELK stack is unrelated, this is the analytical/OLAP use-case not the logging stack stuff. Although you can say that logging is a specific use-case of the analytical/OLAP. Search for the discussion around "facets". Basically the way to think of this is, elastic/solr are MapReduce with weak database semantics layered on top. The ingest stage is the "map", you ingest JSON documents and optionally perform some transformation on them. The query is the "reduce", and you are performing some analytical summation or search on top of this. The point of having the "map" step is that on ingest, you can augment the document with various things like tokenizations or transforms, and then write those into the stored representation, like a generated column. This happens outside your relational DB and thus doesn't incur RDBMS storage space for the transformed (and duplicated!) data, or the generation expense at query time. Pushing that stuff inside your relational store is wasteful. It's an OLAP store. You have your online/OLTP store, you extract your OLAP measurements as a JSON document and send it to the OLAP store. The OLAP store performs some additional augmentations, then searches on it. What facets let you do is have "groups within groups". So you have company with multiple employees inside it, you could do "select * from sales where company = 'dell' group by product facet by employee". But it's actually a whole separate layer that runs on top of grouping (and actually behaves slightly differently!), and because of the way this is done (inverted indexes) this is actually incredibly efficient for many many groups. It's built on a "full-scan every time" idiom rather than maintaining indexes etc... but it actually makes full-scans super performant, if you stay on the happy path. And because you're full-scanning every time, you can actually build the information an index would have contained as you go... hence "inverted index". You are collecting pointers to the rows that fit a particular filter, like an index, and aggregating on them as it's built. And the really clever bit is that it can actually build little trees of these counts, representing the different slices through OLAP hyperspace you could take when navigating your object's dimensions. So you can know that if you filter by employee there are 8 different types of products, and if we filter by "EUV tool" then there is only 1 company in the category. Which is helpful for hinting that sidebar product navigation tree in stores etc. A lot of times if you look carefully at the HTML/JS you can see evidence of this, it will often explicitly mention facets and sometimes expose Lucene syntax directly.

killme2008 2y ago

I believe the primary issue with using full-text search in PostgreSQL is its performance and scalability limitations. Elasticsearch/OpenSearch offers more than just search functionality; they come with extensive ecosystems, including ELK for logging and processors for ETL pipelines. These platforms are powerful and provide out-of-the-box solutions for developers. However, as the article mentioned, scaling them up or out can be costly. ParadeDB looks like is based on tantivy(https://github.com/quickwit-oss/tantivy https://github.com/quickwit-oss/tantivy) which is an impressive project. We leverage it to implement full-text indexing for GreptimeDB too (https://github.com/GreptimeTeam/greptimedb https://github.com/GreptimeTeam/greptimedb). Nevertheless, building full-text indexes with Tantivy is still resource-intensive. This is why Grafana Loki, which only indexes tags instead of building full-text indexes, is popular in the observability space. In GreptimeDB, we offer users the flexibility to choose whether to build full-text indexes for text fields, while always creating inverted indexes for tags.

deleted 2y ago

[deleted]

izietto 2y ago

Is this distributed as a PostgreSQL extension as well?

Ulti 2y ago
yeah looks like how they distribute too, basically pre installed and configured pg_search pg_datalake and pgvector
- philippemnoel 2y ago
  Correct. If you run your own Postgres, you can install pg_search directly, otherwise we suggest our users integrate with their existing Postgres deployments (say AWS RDS) via logical replication: https://docs.paradedb.com/replication/pg_search https://docs.paradedb.com/replication/pg_search

jillesvangurp 2y ago

If you care about search quality, postgres ends up being fairly limited. I've done stuff with it; it's alright for really simple stuff. But it's the wrong tool for the job generally. If the order of the search results matters to you, you might want something that gives you some more tools to control that. And if you are not measuring search quality to begin with, you probably don't care enough to even know that you are missing the tools to do a better job. I consult clients on this stuff professionally and I've seen companies do all sorts of silly shit. Mostly it's because they simply lack the in house expertise which is usually how they end up talking to me. I've actually had to sit clients down and explain them their own business model. Usually I come in for some technical problem and then end up talking to product managers or senior managers about stuff like this because usually the real problem is at that level. The technical issues are just a symptom. Here's a discussion I had with a client fairly recently (paraphrasing/exaggerating, obviously): "Me: So your business model is that your users find shit on your web site (it has a very prominent search box at the top) and then some transaction happens that causes you to make money? Customer: yes. Me: so you make more money if your search works better and users find stuff they want. Customer: yes, we want to make more money! Me: congratulations, you are a search company! Customer: LOL whut?! Me: So, why aren't you doing the kinds of things that other search companies do to ensure you maximize profit? Like measuring how good your search is or generally giving a shit whether users can actually find what they are looking for. Customer: uhhhhh ???? Me: where's your search team? Customer: oh we don't have one, you do it! Me: how did you end up with what you currently have. Customer: oh that guy (some poor, overworked dev) over there picked solution X 3 years ago and we never gave it a second thought." Honestly, some companies can't be helped and this was an example of a company that was kind of hopelessly flailing around and doing very sub optimal things at all levels in the company. And wasting lots of time and money in the process. Not realizing your revenue and competitiveness are literally defined by your search quality is never a good sign. You take different decisions if you do.

porsager 2y ago
I certainly get the sentiment of your comment, but I'd be much more interested to hear your take on ParadeDB which the post is about?

glintik 2y ago

Postgres has very limited features related to FTS, so it is useful only for very simple cases or for very limited budget. Elasticsearch is stable enough(but good devops and devs needed) and has numerous of features. Anyway Elasticsearch is not a "plug and play" solution and requires much work to be done for good search quality. Why I know this all? I'm running ecommerce search startup, based on Elasticsearch as primary storage.

sgift 2y ago

Solr. The correct answer is Solr. All the search features of Elasticsearch (both are built on Lucene, which provides the actual search implementation) without the problems that Elastic (the company) or Elasticsearch (the product) brings with it. 99% of companies using Elasticsearch would be far better of with Solr.

dig1 2y ago
Solr will work perfectly if you want to do a standard search (db, shop) or need a customized approach, like custom transformers, ranking, etc. However, if you aim for "something that just works good enough" and has much better scaling capability and tooling, you can't beat ES/OpenSearch here (Solr scaling is much more manual process than ES/OS). And if you want to stay away from ElasticSearch drama, stick to OpenSearch - they are shipping excellent stuff these days.
maxloh 2y ago
Never heard of it before. I find that Apache often does not do well at promoting their products."
- lastofthemojito 2y ago
  Solr is seen (probably unfairly) as passé by many people. I used Solr back in 2008-2009 or so and it did a great job, but people didn't like that it used XML rather than JSON. Then we had a requirement for something like Elasticsearch's percolate query functionality, which Solr didn't support. So we switched, and subsequent projects I've joined have all used ES. As I understand, Solr now has a JSON REST API and it's improved in other ways over the years, but ES has quite a bit more mindshare these days: https://books.google.com/ngrams/graph?content=solr%2Celasticsearch&year_start=2000&year_end=2022&corpus=en&smoothing=3&case_insensitive=false https://books.google.com/ngrams/graph?content=solr%2Celastic... So now in addition to the "does it do the job?" question, teams might also ask "can we hire and retain people to work with this technology rather than the more popular alternative?".

sideway 2y ago

Semi-related: I guess some companies have already started augmenting their search with the use of LLMs. If you've worked on a similar project, what is your (very) high-level architecture? Did you see a noticeable difference in relevance and query time?

cpursley 2y ago
I haven't used LLMs but am using a hybrid approach with pg_vector: https://github.com/agoodway/vecto https://github.com/agoodway/vecto (some useful links in the README). One way that comes to mind is using LLMs is to re-write the query before shoving it into the search engine (known as "query rewriting"). More full-text ideas here: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#search https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

Sytten 2y ago

AWS RDS support is key IMO, I have done a lot of consulting on infrastructure and most clients I had don't want to have third party services and don't want to manage their DBs.

philippemnoel 2y ago
Agreed! But for most of our customers, they want to separate their OLTP Postgres from their Search Postgres. This is because query patterns differ, and you want to tune Postgres and choose hardware differently. This also provides higher reliability and ensures search queries can't slow down transactions. ParadeDB implements this by ingesting logical replication (WALs) from the primary Postgres. This gives a "closely coupled" solution, where you have the feel of a single Postgres database (ParadeDB is essentially an extra read replica in your Postgres HA setup). More here: https://docs.paradedb.com/replication/pg_search https://docs.paradedb.com/replication/pg_search

dewey 2y ago

I've had good experiences with both Postgres + ES and also Postgres + Meilisearch. The latter one I only used for small side projects but it was very easy to get running (With a RoR app).

demilich 2y ago

https://github.com/infiniflow/infinity https://github.com/infiniflow/infinity, dense vector + sparse vector + fulltext search(BM25) + late interact reranker(Colbert)

deleted 2y ago

[deleted]