[DISCUSSION] Making Apache Cloudberry an Agent-Native Analytical Database #1967

Additional context: Lance for multimodal data

It may be useful to clarify what "AI-native / multimodal data" means here.

Lance is an Apache-2.0 open-source file and table format designed for AI datasets. It can keep structured metadata, text, vectors, images, audio, and video in the same versioned dataset. Large media objects can use blob encoding and be loaded lazily, so a system can scan metadata or embeddings first and fetch only the selected media payloads.

This is useful because a multimodal dataset is usually more than a collection of vectors. A single video, for example, may have:

• business and lineage metadata;
• the original video or audio;
• frame-, clip-, and text-level embeddings;
• captions, transcripts, labels, and model outputs;
• several generations of features produced by different models.

Lance is designed for workflows where these derived columns evolve frequently. New embeddings, captions, or predictions can be added without rewriting the original media dataset. The same dataset can then support random access for model training, vector and full-text retrieval for serving, and scans for feature engineering or analytics.

Typical applications include:

• text-to-image, text-to-video, and cross-modal retrieval;
• visual similarity search and duplicate detection;
• training-data sampling and feature engineering;
• media analytics over captions, transcripts, metadata, and embeddings;
• multimodal RAG and long-term agent memory.

There are already several publicly documented production examples. These are generally described as Lance or LanceDB deployments; LanceDB is built on the open-source Lance format.

• Netflix's Media Data Lake uses LanceDB to organize media assets together with metadata, embeddings, and ML-derived features for search, exploration, and training workflows.
• Runway reports using Lance in its generative-video model training pipeline, including a 1.8 TB in-memory video pipeline.
• ByteDance's Volcano Engine has publicly described Lance as the storage core of an AI data platform covering images, video, embeddings, structured metadata, and point clouds; it also uses LanceDB as the memory backend for its managed agent platform.

For Cloudberry, the interesting boundary would not be to move media decoding or model training into the database. Lance could remain the external AI-native dataset layer, while Cloudberry provides distributed SQL, joins with native or Iceberg tables, metadata filtering, Vector Top-K pushdown, and aggregation over the retrieved or derived results.

That is the main multimodal opportunity: not only "search similar vectors," but analyze business data and AI-derived information together through one MPP query layer.

Lance adoption across Apache projects

Another useful signal is that Lance is no longer isolated from the broader Apache data ecosystem. Several ASF projects already include released Lance integrations at different layers:

• Apache Hudi 1.2 supports Lance as a base-file format for vector and blob-oriented tables.
• Apache Paimon 2.0 includes Lance file-format readers and writers.
• Apache Fluss provides a Lance lake connector that continuously tiers streaming data into standard Lance tables.
• Apache Gravitino provides a Lance REST service for namespace, table, and metadata management.
• Apache SeaTunnel provides a Lance sink for batch and streaming ingestion.

There are also ecosystem-level integrations with Apache Spark, Apache Flink, and Apache DataFusion maintained by the Lance community, while Apache Polaris can manage Lance tables through its Generic Table API and a Lance Namespace adapter.

These integrations cover several complementary layers:

SeaTunnel / Fluss
 → ingestion and streaming tiering
Hudi / Paimon
 → table and file-format integration
Gravitino / Polaris
 → catalog, namespace, and governance
Spark / Flink / DataFusion
 → processing and query-engine integration

This does not mean that all of these projects use Lance as their default storage format, but it does show growing interoperability and real implementation work across ASF projects.

For Cloudberry, the opportunity would be to add a PostgreSQL-compatible MPP analytical path over Lance: distributed scans, joins with native or Iceberg data, Vector Top-K pushdown, and higher-level analytical/RAG capabilities exposed through MCP.

Great direction. I'd suggest referencing TiDB's "data agent" path when planning this out — several pieces are highly relevant:

1. MCP Server first: TiDB's earliest concrete step was the open-source TiDB MCP Server (https://docs.pingcap.com/ai/tidb-mcp-server/) (STDIO/SSE, tools like show_databases, query,
   execute) rather than internal database changes. Since Cloudberry already has an MCP server, adding vector_search/hybrid_search tools as Phase 0 would validate demand faster than
   waiting for the full Lance FDW.

2. Agent memory / write-back: PingCAP argues agent memory must write back (https://www.pingcap.com/blog/agent-memory-write-back-database/) — retrieval-only memory accumulates
   contradictions; you need an ACID engine to read, join, and write corrected state. This maps to the "Analytical RAG" section: results of AI extraction/classification need somewhere
   durable to land. Read-only FDW is the right Phase 1, but a later roadmap item could be writing extracted features back into Cloudberry tables.

3. Extreme multi-tenancy: TiDB X targets millions of logical tenants on object storage for agent workloads. If Cloudberry aims to be the analytical backend for many concurrent
   agents, how to manage many small ephemeral datasets/vector sets is worth borrowing from.

In short, TiDB's path is roughly "MCP interface → agent memory write-back → multi-tenant architecture," which complements the "FDW → vector pushdown → analytical RAG → MCP tools"
roadmap here — validate via MCP tools first, then decide how deep the Lance integration should go.

Thanks, this is very helpful.

I agree that an MCP-first Phase 0 would give us a lower-cost way to validate the Agent-Native use cases before making the Lance integration too deep. I also agree that a read-only Lance FDW is the right starting point for the storage integration.

The write-back point maps well to the "Analytical RAG" part of the proposal. My current understanding is:

• Lance can provide large-scale vector and multimodal data for retrieval.
• Cloudberry can query that data through the FDW and combine it with relational or Iceberg data.
• AI-generated results—such as extracted entities, classifications, summaries, scores, and other derived features—can later be written into Cloudberry native tables.
• Those results can then be versioned, joined, analyzed, and reused by subsequent RAG workflows without running the same extraction repeatedly.

I think this kind of AI-derived data write-back should be distinguished from general Agent Memory. The write-back path stores durable analytical results, while Agent Memory may separately cover task checkpoints, previously established facts, user or business context, and selected execution history. We probably do not need to store the agent’s entire reasoning trace.

So the architecture could roughly become:

Agent → MCP → Cloudberry → Native Tables / Iceberg / Lance

With two different paths:

• Read path: retrieve and analyze data from Native Tables, Iceberg, and Lance.
• Write-back path: persist selected AI-derived results and agent state into Cloudberry native tables.

I’ll revise the roadmap ordering along these lines:

Phase 0: MCP and agent-facing tools over existing Cloudberry capabilities
Phase 1: Read-only Lance FDW with filter and projection pushdown
Phase 2: Vector Top-K pushdown
Phase 3: Distributed Top-K and hybrid retrieval
Phase 4: Analytical RAG write-back for extracted and classified data
Phase 5: Agent memory, task state, and memory lifecycle management
Phase 6+: Semantic layer and extreme multi-tenancy exploration

This keeps the MCP interface independent of the Lance implementation, while giving Lance a clear role in AI-oriented retrieval and Cloudberry native tables a clear role in durable analytical results and agent state.

Thanks again — I think this separation makes the proposal and implementation path much clearer.

Thanks for the write-up. The "agent decides what, Cloudberry decides how" boundary makes sense to me, and MCP-first for Phase 0 is the right call. A few points I think need answers before Phase 1, since they decide whether Phases 2–3 are feasible on an MPP engine:

1. Segment ↔ fragment mapping. With mpp_execute 'all segments', a naive FDW makes every segment scan the whole Lance dataset and the result is silently multiplied by the segment count (we have hit exactly this with postgres_fdw on Cloudberry). Each segment must scan only its own subset of fragments (e.g. fragment_id % num_segments == gp_segment_id). This should be an explicit Phase 1 deliverable, since distributed Top-K in Phase 3 depends on it.

2. Global index vs. partitioned scan. Lance's vector index is built over the whole dataset, but MPP wants each segment to search only its fragments. Whether an IVF/PQ search can be restricted to a fragment subset with acceptable recall needs a quick spike. If it can't, Phase 2/3 collapse to either coordinator-only index scan (no MPP) or per-segment brute force (no index).

3. Answer question 3 explicitly. Cloudberry already has pgvector with per-segment indexes and ORDER BY dist LIMIT k via Gather Merge, which is distributed vector search. Lance's real differentiators are: data stays in the lake, lazy-loaded multimodal blobs / versioned datasets, and sharing the same files with Hudi/Paimon/Fluss. Worth stating up front, because "why not just pgvector?" will be the first review question. Also, the revenue-drop + tickets example is a pre-filtered ANN query; the filter has to be pushed into Lance, so I'd make that the Phase 2 acceptance test rather than plain distance + LIMIT.

4. Separate extension, not in-tree. Lance is Rust with no stable C API, so the FDW will need a Rust cdylib + FFI layer. Keeping it out of the core build (the way cloudberry_fdw is) avoids a long toolchain discussion and moves faster. So a clear yes on question 2.

Happy to discuss the fragment-assignment design in (1) further.

Thanks — these are exactly the execution constraints that need to be made explicit. I checked the current Cloudberry and Lance implementations and also ran a small local spike.

1. Segment-to-fragment assignment

Agreed. This should be part of Phase 1, not deferred to distributed Top-K.

The QD should pin one Lance dataset version, enumerate its fragments, assign each fragment exactly once, and pass the assignments to the QEs through fdw_private. This is similar to the existing PXF pattern: the dispatcher obtains and serializes the fragment list, while each segment keeps only its assigned subset.

fragment_id % num_segments is sufficient for an initial correctness prototype, although a size-aware assignment would eventually handle uneven fragments better.

The Phase 1 acceptance criteria should include:

• no duplicate rows with mpp_execute 'all segments';
• no missing fragments;
• all QEs reading the same pinned dataset version;
• stable behavior when the number of fragments differs from the number of segments.

2. Fragment-scoped and distributed ANN

I ran a local spike, and the current Lance model makes this feasible.

The important distinction is that normal scans can be assigned by data fragment, while indexed ANN should normally be assigned by whole physical index segments. A Lance logical index can contain multiple physical segments, each covering a disjoint fragment subset. Lance also exposes APIs for restricting a query to selected index segment UUIDs. This is the same ownership model used by Lance-Ray's distributed vector search.

In a small test with 32,000 vectors, four data fragments, and two physical IVF_FLAT index segments:

• a pure C program linked against lance-c v0.1.9;
• each index-segment-scoped query returned rows only from its covered fragments;
• there were zero fragment-ownership violations;
• merging the two local Top-K result sets matched Lance's global Top-K result for all 12 test queries.

I also tested a prefiltered ANN query through the C API. All returned rows satisfied the scalar predicate, and the Top-5 matched an exact filtered search in that test.

A separate IVF_PQ experiment confirmed that recall depends materially on nprobes, refinement, and per-worker oversampling. Therefore, this establishes functional feasibility, not yet acceptable MPP/object-store performance.

For Phase 3, the likely execution model is:

• QD pins the dataset snapshot and reads index-segment metadata;
• whole index segments are assigned to QEs;
• fragments not covered by an index use a flat-search fallback;
• each QE returns an oversampled local candidate set;
• Cloudberry performs the final distance-ordered global Top-K merge.

The next spike should run this inside Cloudberry and measure recall against an exact filtered search, along with S3 I/O, skew, and latency.

3. pgvector versus Lance

Agreed. Cloudberry already has distributed pgvector execution. One terminology detail is that the observed Cloudberry plan uses Gather Motion 3:1 with a Merge Key, rather than PostgreSQL's Gather Merge, but the substance of the point is correct.

The distinction I would make is:

• pgvector: vectors stored and indexed in Cloudberry native relational tables;
• Lance: external, versioned vector/multimodal datasets on object storage, shared with other lakehouse and ML engines;
• Cloudberry: SQL joins, aggregation, MPP planning, and global result merging across those sources.

So Lance is not justified merely by distributed vector search. Its value is avoiding ingestion and duplication of lake-resident multimodal data.

I also agree that Phase 2's acceptance test should be a prefiltered ANN query, not only plain distance + LIMIT.

4. Extension and C interface

I agree with keeping this as a separate extension, but the interface situation has changed: there is now an official lance-c project.

Its C API supports dataset scans, Arrow streams, SQL/Substrait filters, fragment restriction, vector search, index-segment enumeration, and index-segment-scoped search. I verified the read and ANN paths from a pure C11 consumer using the official v0.1.9 binary.

There are still two important qualifications:

• lance-c is implemented on top of Rust, although a Cloudberry FDW can link against the resulting liblance_c without implementing its own Rust FFI layer;
• the project explicitly treats its 0.x ABI as unstable across minor releases, so an extension should pin and package a tested lance-c version.

The v0.1.9 C API can build uncommitted distributed index segments, but I did not find a C API for committing those segments as one logical index. That does not block a read-only FDW consuming existing datasets and indexes, but distributed index construction would currently need another coordinator path or an additional upstream C API.

So my current conclusion is: yes to a separate extension, using the official version-pinned lance-c interface, with fragment ownership in Phase 1 and prefiltered ANN recall as the Phase 2 gate.