retakeming

What would you say is the most neutral oil then?

The segments themselves being immutable doesn't mean that Tantivy is incompatible with Postgres - it just means that Tantivy needs to be made compatible with Postgres' concurrency control mechanisms (MVCC) and storage format (block storage). This blog post explains the latter: https://www.paradedb.com/blog/block_storage_part_one

I'm one of the pg_search maintainers. Hello! A few thoughts.

First, both strategies - the one outlined by the Neon/ParadeDB article, and the one used here -- are presented as viable alternatives by the Postgres docs: https://www.postgresql.org/docs/current/textsearch-tables.ht....

Second - as the article correctly demonstrates, the problem with Postgres FTS isn't "how can I pick and optimize a single pre-defined query" it's "how do I bring Postgres to Elastic-level performance across a wide range of real-world boolean, fuzzy, faceted, relevance-ranked, etc. queries?"

`pg_search` is designed to solve the latter problem, and the benchmarks were made to reflect that. You can always cherry-pick a query and optimize it at the expense of data duplication and complexity. The Neon/ParadeDB benchmarks contained 12 queries in total, and the benchmarks could have:

- Created composite b-tree indexes for each of the queries with boolean predicates

- Extracted the all the text fields from JSONBs, stored and indexed them as a separate columns for queries against JSONB

But that's not realistic for many real-world use cases. `pg_search` doesn't require that - it's a simple index definition that works for a variety of "Elastic style" queries and Postgres types and doesn't ask the user to duplicate every text column.

Good question. That was from a very old version of pg_bm25 (since renamed to pg_search). BM25 indexes are now strongly consistent.

If you're a Postgres pg_search user, partial indexes (https://docs.paradedb.com/search/full-text/index#partial-bm2...) can solve.

pg_search (full text search Postgres extension) can be used with pgvector for hybrid search over Postgres tables. It comes with a helpful hybrid search function that uses relative score fusion. Whereas rank fusion considers just the order of the results, relative score fusion uses the actual metrics outputted by text/vector search.

Could you clarify which result you're referring to as the baseline and "number 1 place?"

I should clarify that our published Clickbench results are from our pg_analytics extension. New results with pg_lakehouse will be released. They're going to beat the old benchmarks because 1. No overhead from Postgres transactions/MVCC, since pg_analytics used the table access method whereas pg_lakehouse is just a foreign data wrapper 2. Uses the latest release of DataFusion.

The performance differences that exist between DataFusion and other OLAP engine are rapidly becoming commoditized. DataFusion is already a world-class query engine and will only improve. pg_lakehouse absorbs all those improvements into Postgres.

pg_house just wasn't as catchy!

In all seriousness though, I see your point. While it's true that we don't provide the storage or table format, our belief is that companies actually want to own the data in their S3. We called it pg_lakehouse because it's the missing glue for companies already using Postgres + S3 + Delta Lake/Iceberg to have a lakehouse without new infrastructure.

Thanks!

1. It's single node, but DataFusion parallelizes query execution across multiple cores. We do have plans for a distributed architecture, but we've found that you can get ~very~ far just by scaling up a single Postgres node.

2. The only information stored in Postgres are the options passed into the foreign data wrapper and the schema of the foreign table (this is standard for all Postgres foreign data wrappers).

Whereas pg_analytics stores the data in Postgres block storage, pg_lakehouse does not use Postgres storage at all.

This makes it a much simpler (and in our opinion, more elegant) extension. We learned that many of our users already stored their Parquet files in S3, so it made sense to connect directly to S3 rather than asking them to ingest those Parquet files into Postgres.

It also accelerates the path to production readiness, since we're not touching Postgres internals (no need to mess with Postgres MVCC, write ahead logs, transactions, etc.)

This is anecdotal, but I feel that we (ParadeDB) have received more requests for Iceberg integration vs. Delta Lake. We were actually hesitant to launch pg_lakehouse without Iceberg support, but pulled the trigger on it because the iceberg-rust crate is still in its early days. We will probably be contributing to iceberg-rust to make it work with pg_lakehouse.

Datafusion and Deltalake abstract away most of Arrow/Parquet. And those APIs were very nice to work with.

Datafusion's SQL dialect has some slight quirks that pertain to Datafusion-specific functionality. For instance, the ability to create an external table.

With regards to ParadeDB, we rely on the Datafusion SQL parser, which can transform the Postgres SQL dialect into a Datafusion logical plan that can be executed by Datafusion. We actually have an open PR that adds support for user-defined functions...it will likely get merged within a few days.

Yes and no, depending on what you mean.

The custom types/indexes introduced by PostGIS won't work with deltalake tables. Even if it were possible, the benefits of using deltalake tables to execute geospatical queries are unclear, since PostGIS indexes are already optimized for this task.

But, what ParadeDB enables is for geospatial tables and deltalake tables to exist within the same database.

Sort ordering is handled automatically by Datafusion.

Collations at the column/operation level are not yet supported but we're working on it.

Yes. delta-rs is a Rust-based implementation of Delta Lake.

The existing version of pg_analytics uses delta-rs to manage Parquet files stored within Postgres. In the future, we plan on integrating external object stores. This means that you'll be able to query any Delta Lake directly from Postgres.

Iceberg support will come later, once the Rust implementation of Iceberg matures.

One of the authors of pg_analytics here.

1. On Clickbench, make sure you're doing an apples-to-apples comparison by comparing scores from the same instance. We used the most commonly-used c6a.4xlarge instance. While a few databases like DuckDB rank higher, the performance of Datafusion (our underlying query engine) is constantly improving, and pg_analytics inherits those improvements.

Then again, people only care about performance and benchmarks up to a certain threshold. The goal of pg_analytics is not to displace something like StarRocks, but to enable analytical workloads that require both row and column-oriented data or Postgres transactions.

2. We're working on TPC-H benchmarks. They're good for demonstrating JOIN performance and we'll have them published early next week.

On Clickbench, ParadeDB load times are 1.85x faster than Postgres. Typically, you would expect slower inserts if you were using an index to accelerate queries since inserting into the index takes time. But pg_analytics is not an index. It's introducing a new kind of table that uses column-oriented storage (Arrow + Parquet) instead of row-oriented, and allows the user to choose what kind of storage they want on a table by table basis.

Thanks!

We released support for metrics aggregations a few days ago, including count: https://docs.paradedb.com/aggregations/metrics#count.

We haven't gotten around to benchmarking aggregations - that's the focus for next week and we'll publish them once they're done. I would suspect that it's a lot faster than Postgres aggregates since it leverages Tantivy Columnar.

Blog post author and one of the pg_bm25 contributors here. Super excited to see the interest in pg_bm25!

pg_bm25 is our first step in building an Elasticsearch alternative on Postgres. We built it as a result of working on hybrid search in Postgres and becoming frustrated with Postgres' sparse feature set when it comes to full text search.

To address a few of the discussion points, today pg_bm25 can be installed on self-hosted Postgres instances. Managed Postgres providers like RDS are pretty restrictive when it comes to the Postgres extension ecosystem, which is why we're currently working on a managed Postgres database called ParadeDB which comes with pg_bm25 preinstalled. It'll be available in private beta next week and there's a waitlist on our website (https://www.paradedb.com/).

Hey HN - lead dev on pg_bm25 here. To provide a bit more context: the goal of pg_bm25 is to make BM25-based full text search feel native to Postgres. pg_bm25 is built on Tantivy, a Rust-based alternative to Apache Lucene. We've integrated Tantivy into Postgres as a native index type and introduced a custom SQL operator for executing search queries.

If you have any questions after trying it out, our Slack community is a great place to start https://join.slack.com/t/paradedbcommunity/shared_invite/zt-...

This looks neat.

https://github.com/ShishirPatil/gorilla feels like a project with certain similarities here. How would you compare yourselves to them?

Thanks, appreciate it!

We forked pgsync for the silly reason that they hadn't published to PyPi in months, and some of their dependencies were out of date. We haven't made any modifications to pgsync so maintaining compatibility shouldn't be an issue, and we'll likely revert back to the main library once their dependencies are brought up to speed.

Thanks! We debated what the right decision was in the beginning but are glad to have settled on Apache.

You don't need to define all keys in a JSON object - by default, new keys will automatically be added to the index mapping when a JSON document containing that key is added to the index.

Details on how to query JSON objects can be found in our docs: https://docs.getretake.com/search/object

1. Correct - we don't rely on pgvector. As a result, we're compatible with more existing managed Postgres services.

2. Probably the biggest differentiator between Vespa and Retake is the core architecture - Retake is built on top of OpenSearch. There's been quite a bit of debate regarding different search engines since Yahoo released Vespa - we leaned into OpenSearch because we saw that Open/ElasticSearch and its query language was much more familiar to more developers. Something that's coming soon to Retake is the ability to control how keyword/semantic scores are normalized and combined, which should give developers more fine-tuned control over their results.

3. In the short term, our support for models like SPLADE is constrained by OpenSearch, which uses BM25. In the medium to long term we would definitely consider modifying OpenSearch to do stuff like this.

4. We support both post-filtering and efficient kNN filtering, which takes place during the kNN search and guarantees that k results are returned. More details on the faiss kNN filter implementation can be found on the OpenSearch docs: https://opensearch.org/docs/latest/search-plugins/knn/filter...

We don't run any batch jobs - Retake streams changes in real time via CDC (change data capture). The only batch job you would need to run is to populate an index when it's first created.