What does HackerNews think of zombodb?
Making Postgres and Elasticsearch work together like it's 2022
Quite deep Postgres/Elasticsearch integration
This deserves mention, as it solves that problem: https://github.com/zombodb/zombodb
From the README:
> ZomboDB brings powerful text-search and analytics features to Postgres by using Elasticsearch as an index type. Its comprehensive query language and SQL functions enable new and creative ways to query your relational data.
> From a technical perspective, ZomboDB is a 100% native Postgres extension that implements Postgres' Index Access Method API. As a native Postgres index type, ZomboDB allows you to CREATE INDEX ... USING zombodb on your existing Postgres tables. At that point, ZomboDB takes over and fully manages the remote Elasticsearch index and guarantees transactionally-correct text-search query results.
I find other things also hard in search engines: dealing with the plethora of human languages and all the requirements we may have to processing them. A mature solution like ES therefor is almost a must in the more demanding cases.
ZomboDB is a Postgres extension that enables efficient full-text searching via the use of indexes backed by Elasticsearch. (From the project's website[0]).
Lots more info in readme at the project's github[1].
https://www.elastic.co/guide/en/elasticsearch/reference/current/dense-vector.html
https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-script-score-query.html#vector-functions
https://github.com/zombodb/zombodb
https://github.com/zombodb/zombodb
It basically gives you a new kind of index (create index .. using zombodb(..) ..)
Curious there's no mention of zombodb[0] though, which gives you the full power of elasticsearch from within postgres (with consistency no, less!). You have to be willing to tolerate slow writes, of course, so using postgres' built-in search functionality still makes sense for a lot of cases.
I ask because where I work we sync postgres to a secondary store for search, but the way it's done in a piecemeal, application-specific way gives me the heebie jeebies. It almost certainly will result in that secondary store drifting. Unfortunately we can't use something like zombodb [1] as we're on amazon RDS. It seems like you know your stuff, and seeing non-deterministic consistency irritates the heck out of me!
We developed pgx so that we could rewrite "ZomboDB" (https://github.com/zombodb/zombodb) in Rust. ZDB is a custom "Index Access Method" for Postgres. Think btree index, but stored in Elasticsearch.
So that's definitely a thing.
Other ideas might be custom data analytics/transformation/processing engines that you'd prefer to run in the database instead of externally.
Custom data types to represent, for example, street addresses or genetic information.
The only limit is yourself! ;)
From the project's github page[1]:
ZomboDB brings powerful text-search and analytics features to Postgres by using Elasticsearch as an index type. Its comprehensive query language and SQL functions enable new and creative ways to query your relational data.
From a technical perspective, ZomboDB is a 100% native Postgres extension that implements Postgres' Index Access Method API. As a native Postgres index type, ZomboDB allows you to CREATE INDEX ... USING zombodb on your existing Postgres tables. At that point, ZomboDB takes over and fully manages the remote Elasticsearch index and guarantees transactionally-correct text-search query results.
Also:
> Squeezing the query language into some sql-like shape will provide marginal gain at best.
... is disingenuous at best. In both examples I raised (and in the ones you'll find if you query the others) the engines have functionality to accept user input pretty much as is - meaning as your typical mom would type it in a search field.
If you need extra criteria from there and the dialect's full text syntactic features, you can use regular SQL conditions (and joins, and aggregates, etc.) on the subset of records found. The most discriminating criteria/index will be the one related to the search query in most cases. And when not, lucky you - your query planner did not hammer everything with the same sledgehammer, thus demonstrating why you should be using SQL.
Doesn't seem to be a whole lot of people trying it out, though the project is 'relatively' active.
"ZomboDB is a Postgres extension that enables efficient full-text searching via the use of indexes backed by Elasticsearch. In order to achieve this, ZomboDB implements Postgres' Access Method API.
In practical terms, a ZomboDB index appears to Postgres as no different than a standard btree index. As such, standard SQL commands are fully supported, including SELECT, BEGIN, COMMIT, ABORT, INSERT, UPDATE, DELETE, COPY, and VACUUM."