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

[MisterRaindrop]

I'd like to discuss a possible direction for Apache Cloudberry: making Cloudberry an open analytical backend for AI agents, rather than building another agent framework inside the database.

AI agents such as Codex, Claude, Maka, and other MCP-compatible agents are increasingly becoming a new interface for interacting with data systems.

The basic idea is:

                Agent
         Codex / Claude / Maka
            /      |      \
           /       |       \
         Web    Cloudberry  MCP Apps
                   │
            ┌──────┼──────────┐
            ▼      ▼          ▼
           SQL    RAG      Semantic
            │      │
         Iceberg  Lance

Cloudberry already has an MCP server and a mature MPP analytical engine.

Therefore, I don't think Cloudberry needs to build another agent runtime.

The external agent can be responsible for:

• reasoning and planning
• conversation and context
• web search
• external MCP tools
• multi-step tool orchestration

Cloudberry can focus on what an analytical database is good at:

• SQL analytics
• MPP execution
• structured data analysis
• vector / semantic retrieval
• structured + unstructured analysis

The responsibility boundary could be:

Agent:
    decide what to do

Cloudberry:
    decide how to analyze the data efficiently

Structured + AI-Native Data

Cloudberry already provides a strong foundation for structured analytical workloads through SQL, MPP execution, and lakehouse integration.

One possible missing piece is an AI-native data layer.

A possible architecture is:

                 Cloudberry
                     │
              Unified Analytics
                     │
          ┌──────────┴──────────┐
          ▼                     ▼
       Iceberg                 Lance
          │                     │
     Structured          AI-native Data
       Data             Vector / Text /
                        Multimodal Data

Iceberg can continue to serve structured analytical/lakehouse workloads.

Lance could complement it as an external data source optimized for AI-oriented datasets.

This could eventually allow queries combining structured business data with semantic or multimodal data.

For example:

Find customers whose revenue dropped by more than 20% in the last three months, then analyze their support tickets to determine the most common complaints.

Conceptually:

Iceberg / Cloudberry
        ↓
Structured SQL analysis
        ↓
Declining customers
        ↓
Lance semantic/vector search
        ↓
Support documents
        ↓
Cloudberry aggregation
        ↓
Agent reasoning

This is closer to analytical RAG than traditional Top-K RAG.

Why Lance?

The goal is not simply to add another vector index.

Lance is interesting because it is designed around AI-oriented datasets containing combinations of:

metadata
text
vectors
images
audio/video references
multimodal features

This makes it potentially useful as an AI-native data layer alongside Iceberg.

Cloudberry could remain responsible for SQL, JOIN, aggregation, MPP execution, and distributed query planning, while Lance provides storage and retrieval capabilities for AI-oriented datasets.

Why Not Just pgvector?

Cloudberry already supports pgvector, and pgvector is a good solution for storing and searching vectors inside PostgreSQL-compatible relational tables.

I see pgvector and Lance as solving different problems.

pgvector

Cloudberry native table
        │
        ├── relational columns
        └── vector column

This is a natural solution when embeddings are part of relational data.

The proposed Lance integration targets external AI-native datasets:

Lance Dataset
     │
     ├── metadata
     ├── text
     ├── vectors
     ├── multimodal data
     └── vector indexes

Therefore, Lance would not replace pgvector.

The three layers could coexist:

pgvector
    → vectors in native Cloudberry tables

Iceberg
    → structured lakehouse datasets

Lance
    → AI-native / multimodal datasets

Cloudberry
    → unified MPP analytical engine

A simple way to describe the distinction is:

pgvector provides vectors inside PostgreSQL. Lance provides an AI-oriented dataset layer. Cloudberry provides the analytical engine across them.

RAG and Hybrid Retrieval

Once Cloudberry can access Lance datasets, we could expose retrieval capabilities through the existing MCP server.

For example:

execute_query()
vector_search()
hybrid_search()
search_documents()
retrieve_context()

An external agent could then combine these tools.

For example:

              Codex / Claude
                    │
                    MCP
                    │
              Cloudberry
              /          \
             /            \
       SQL Analytics     RAG
            │             │
         Iceberg        Lance

The important point is that Cloudberry does not need to know whether the caller is Codex, Claude, Maka, or another agent.

MCP provides the common interface.

Analytical RAG

A longer-term opportunity is to go beyond traditional RAG.

Traditional RAG usually works as:

Documents
    ↓
Vector Search
    ↓
Top-K
    ↓
LLM

This works well for retrieval, but not for questions such as:

What percentage of all customer complaints last year were related to query latency, grouped by month?

This requires:

Large document dataset
        ↓
Semantic retrieval/filtering
        ↓
AI extraction/classification
        ↓
Cloudberry MPP
        ↓
COUNT / GROUP BY / JOIN

This could be an interesting area where Cloudberry's existing analytical engine provides capabilities beyond a standalone vector database.

Semantic Layer

Another possible future direction is a lightweight semantic layer.

Instead of requiring an agent to infer business meaning directly from physical schemas, Cloudberry could expose concepts such as:

Metrics:
    revenue
    churn_rate

Dimensions:
    region
    customer
    product

Relationships:
    customer → orders
    customer → support_tickets

The external agent could use this semantic information before generating SQL or retrieval requests.

This could improve the reliability of natural-language analytics without requiring Cloudberry itself to implement an agent runtime.

Proposed First Version

I think the first implementation should remain intentionally small.

Phase 1: Read-only Lance FDW

The initial goal could simply be:

Allow Cloudberry to query an existing Lance Dataset as an external table.

For example:

CREATE FOREIGN TABLE lance_documents (
    id bigint,
    customer_id bigint,
    content text,
    embedding float4[]
)
SERVER lance_server
OPTIONS (
    uri 's3://bucket/documents.lance'
);

Initially support:

• schema mapping
• sequential scan
• projection pushdown
• filter pushdown
• read-only access

No INSERT / UPDATE / DELETE would be required initially.

Data could be generated by existing Lance tools or a simple export utility.

Phase 2: Vector Top-K Pushdown

Then support queries such as:

SELECT id, content
FROM lance_documents
WHERE customer_id IN (...)
ORDER BY embedding <-> query_embedding
LIMIT 20;

Instead of:

Read all vectors
      ↓
Cloudberry distance calculation
      ↓
Sort
      ↓
LIMIT

Cloudberry could recognize:

Vector distance
+
LIMIT

and push the operation into Lance:

Cloudberry Planner
        ↓
Vector Top-K Pushdown
        ↓
Lance Vector Index
        ↓
Top-K candidates

This would be the first step toward a vector-aware analytical engine.

Phase 3: Distributed Vector Top-K

Cloudberry's MPP architecture could later provide:

                    QD
                     │
          ┌──────────┼──────────┐
          ▼          ▼          ▼
         QE0        QE1        QE2
          │          │          │
        Lance      Lance      Lance
          │          │          │
        Top-K      Top-K      Top-K
          └──────────┼──────────┘
                     ▼
                 Global Top-K

This could become a Cloudberry-specific capability rather than simply exposing Lance APIs.

Possible Roadmap

The whole direction could be explored incrementally:

Read-only Lance FDW
        ↓
Vector Top-K Pushdown
        ↓
Distributed Vector Top-K
        ↓
Hybrid Search / RAG
        ↓
MCP Analytical Tools
        ↓
Analytical RAG
        ↓
Semantic Layer

Each stage is independently useful.

The first experiment is deliberately narrow:

Can Cloudberry efficiently query Lance datasets and push down Vector Top-K operations?

If that proves useful, the higher-level AI analytical capabilities can be explored incrementally.

Long-Term Goal

The goal is not to turn Cloudberry into another vector database or another agent framework.

Instead, the idea is to explore whether Cloudberry can evolve from:

MPP Analytical Database

toward:

Agent-Native Analytical Database

Structured Analytics
        +
AI-Native / Multimodal Data
        +
RAG / Semantic Search
        +
MCP

External agents such as Codex, Claude, Maka, or any other MCP-compatible system could then use Cloudberry as an open-source analytical backend.

I'd especially like feedback from the community on:

1. Does Lance make sense as an external AI-native data source for Cloudberry?
2. Should Lance integration start as an independent FDW/extension?
3. Does Iceberg + Lance + Cloudberry MPP provide useful capabilities beyond existing pgvector support?
4. Would vector/RAG/analytical-search tools be useful additions to the existing MCP server?
5. Does the broader idea of an agent-native analytical database fit Cloudberry's long-term direction?

[MisterRaindrop]

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.

[MisterRaindrop]

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.

[yjhjstz]

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.

[MisterRaindrop]

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.

[my-ship-it]

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.

[MisterRaindrop]

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.