[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.

[MisterRaindrop]

Progress update, and a question about where lance_fdw should live

A short update on the roadmap agreed above, and one decision I'd like the community's view on before going further.

Where things stand

Phase 0 – MCP retrieval tools. Merged in #1991: list_searchable_columns, vector_search, fulltext_search and hybrid_search over pgvector and full-text columns.

Phases 1–3 – lance_fdw. Implemented as a separate extension, as agreed here, at MisterRaindrop/cloudberry-lance. It links a pinned lance-c and runs its regression and stability suites in CI against a three-segment cluster.

• Phase 1, read-only scan. The QD pins a dataset version and assigns every fragment to exactly one segment through fdw_private, so mpp_execute 'all segments' returns no duplicate and no missing rows. Filters and projections are pushed down when Cloudberry and Lance agree exactly on their meaning. Structs are read as composites and maps as jsonb, and Arrow batches are charged to the memory accounting. Local paths and S3 both work.
• Phase 2, vector Top-K pushdown. lance_fdw defines <->, <=> and <#> on real[], with the same names as pgvector, so no pgvector dependency is needed. WHERE ... ORDER BY emb <-> $1 LIMIT k becomes one Lance nearest-neighbour search with the filter applied as a prefilter, which was the acceptance test asked for above.
  • lance_fdw.nprobes and lance_fdw.refine_factor are exposed. With IVF_PQ, refine_factor is what restores recall.
  • A cancel or statement_timeout now reaches a running search: about 20 ms median, where it used to wait for the search to finish (250–400 ms on 4M rows).
• Phase 3, distributed Top-K. When the index has several segments, the QD sends the search to every segment. Each segment searches a contiguous range of fragments, its covering index segments, and brute-forces unindexed fragments, with no duplicates and no losses; the QD merges the per-segment Top-K. This needed no new lance-c API: restricting the search to a fragment set was enough.
  • With a single-segment index it stays on one segment, and lance_fdw.vector_search_mode overrides the choice.
  • Without an index, on the same host: 1M rows went from 93 to 74 ms, and 4M rows from 329 to 224 ms.

MCP over Lance. A follow-up PR to mcp-server (coming shortly) makes lance_fdw foreign tables discoverable, readable and searchable with vector_search. It also bounds what one tool call returns, by rows, value length and bytes, and keeps credentials out of get_table_ddl.

The question: separate repository, or contrib/?

@my-ship-it's point above was to keep Lance out of the core build so that Cloudberry would not need a Rust toolchain, and I agreed. I'd like to check whether that still holds, because two things are now clearer than they were then:

1. lance_fdw is plain C. All the Rust lives in lance-c, the official C library from the Lance project, which ships as an ordinary shared library. The FDW never calls into Rust directly.
2. The out-of-tree cost is real. The FDW uses MPP-specific hooks: fragment assignment through fdw_private, dispatch to QEs, and the planner path for Top-K. A change in Cloudberry can break it without anyone in this repository noticing, and users have to find and build a second project.

That leaves three options I can see:

Option	Cloudberry build	Source release	Cost
A. Keep it separate (current)	unchanged	unchanged	Breakage is found late, and users install a second project
B. contrib/lance_fdw, built only with --with-lance, linking an installed liblance_c the way --with-libxml links libxml2	no Rust or cargo; off by default	C sources only, no Rust crates	CI needs a liblance_c binary for x86_64 and aarch64; configure checks the pinned lance-c version
C. contrib/ with lance-c vendored as a submodule and built by cargo	needs Rust when enabled	must account for several hundred crates in LICENSE/NOTICE	the toolchain discussion we wanted to avoid

My preference is B, if the community is open to it. It keeps the core build exactly as it is, and puts the FDW where Cloudberry's own CI exercises it. If the answer is A, that is fine too, and I'll keep developing and releasing it from the separate repository.

If B is acceptable, I'd propose landing it as a sequence of reviewable PRs rather than one large one:

1. Build integration (--with-lance, off by default), options, IMPORT FOREIGN SCHEMA, and the parallel read-only scan with type mapping.
2. Filter and projection pushdown.
3. Vector operators and Top-K pushdown, including cancel during a search.
4. Distributed Top-K.

Each PR would come with its regression tests. The extension would start at 1.0 in-tree.

Questions for the community:

1. Is B acceptable, or should lance_fdw stay a separate project (A)?
2. Is there a precedent or preference for providing a prebuilt third-party library to Cloudberry's CI?
3. Any objection to that PR split?

Next: Phase 4

Phase 4 (writing AI-derived results back) is being designed along the lines discussed above:

• The extraction runs outside the database, driven by the agent through MCP.
• Results go into ordinary Cloudberry tables: one typed column per extracted field, upserted by a source key, with model and extracted_at columns recording where each row came from.

I'll post the design separately.

see https://github.com/MisterRaindrop/cloudberry-lance

@my-ship-it @tuhaihe

[MisterRaindrop]

A concrete version of option B, for reference

Following up on the question above about where lance_fdw should live: to make it easier to discuss, I've prepared what option B would look like on my fork. This isn't meant to pre-empt the decision. If the community prefers to keep lance_fdw as a separate project, I'm happy to continue it there.

What it looks like

• contrib/lance_fdw is built only with configure --with-lance, which is off by default. configure looks for <lance/lance.h> and liblance_c through --with-includes and --with-libraries, much as it does for libxml2. Only contrib/lance_fdw links the library.
• No Rust code, crates or cargo invocation are added to the tree. The only bundled third-party code is Apache Arrow nanoarrow 0.7.0 (Apache-2.0).
• On the CI question I asked earlier: rather than depending on a prebuilt binary, a new ic-lance-fdw entry builds the pinned lance-c revision from source and caches it. The cold build takes about 15 minutes. Like ic-datalake-fdw, it then builds the extension with PGXS, generates the test datasets, serves them from a local SeaweedFS S3 gateway, and runs the regression suites. It currently passes on Rocky 8, 9 and 10.

A possible split, following the outline above. Each step builds and passes its own suites:

1. Build integration, CI, options, IMPORT FOREIGN SCHEMA, and the parallel read-only scan with type mapping and memory accounting
2. Filter and projection pushdown
3. Vector operators and Top-K pushdown, including cancel during a search
4. Distributed Top-K

The extension would start at 1.0.

If this direction seems reasonable, I could open a tracking issue that lists these four PRs and the two mcp-server PRs (Lance reads, and Phase 4 result write-back), and then open the PRs for review. I'd be glad to hear any concerns or preferences first, about the approach, the split, or the CI setup.

Thanks also to @igor-suhorukov for the pg_duckdb suggestion. As noted in my reply, the two look complementary rather than interchangeable.

@my-ship-it @tuhaihe

[igor-suhorukov]

@MisterRaindrop great progress on lance_fdw. One suggestion for the read path before deciding between A/B/C: have you looked at using pg_duckdb together with DuckDB's lance extension, either instead of a hand-written FDW or alongside it?

Why it might be worth it

• Lance reading is already solved upstream. DuckDB's lance extension was built with LanceDB and DuckDB Labs. It reads and writes Lance datasets from local paths and S3. It also exposes lance_vector_search, lance_fts and lance_hybrid_search as table functions, with a prefilter option. That covers Phase 1–2 and the hybrid search planned for later.
• pg_duckdb supports PG 14, which matches Cloudberry's kernel. It can load DuckDB extensions and run DuckDB SQL from inside Postgres.
• Less to maintain. Several things would become someone else's job: Lance format changes, type mapping (structs, maps), S3 credentials, and FTS and hybrid scoring. We also wouldn't need to pin and package the lance-c 0.x ABI, which upstream says is unstable.
• Iceberg comes with the same engine. DuckDB already reads Iceberg and Parquet, which fits the Iceberg + Lance picture from the original proposal.
• Phase 4 write-back. DuckDB can write Lance (COPY ... TO ... (FORMAT lance)), so AI-derived features could go back into Lance as well as into native tables.

Rough sketch (exact pg_duckdb syntax to be verified):

SELECT duckdb.install_extension('lance');

SELECT r['id'], r['content'], r['_distance']
FROM duckdb.query($$
  SELECT id, content, _distance
  FROM lance_vector_search(
         's3://bucket/documents.lance', 'embedding',
         [0.1, 0.2, ...]::FLOAT[768],
         k = 20, prefilter = true)
$$) r;

Caveats

• MPP. As far as I know, pg_duckdb has not been ported to or tested on Cloudberry. It takes over planning and runs the query inside a single backend. On Cloudberry that most likely means the QD only, which loses the fragment-to-segment assignment and distributed Top-K you've built. For retrieval-style queries the Top-K result is small, so QD-only may be acceptable. For large scans joined with distributed tables it probably isn't.
• Two engines. Memory used by DuckDB sits outside Cloudberry's memory accounting and resource groups. Type mapping between the two engines also adds a layer that has to be maintained.

Suggestion

A quick benchmark would settle this:

1. Prefiltered vector Top-K
2. A full Lance scan joined with a native distributed table

Run both with pg_duckdb + lance on the QD and with lance_fdw. If pg_duckdb is good enough for (1), it could be the low-maintenance default for retrieval and MCP tools. lance_fdw would then focus on what it does better: MPP scans and distributed Top-K.

[MisterRaindrop]

Thanks for the suggestion. I ran a local correctness spike with pg_duckdb and DuckDB’s official lance extension.

The test environment was Cloudberry 3.0.0-devel / PostgreSQL 16.9 with three primary segments, using a temporary compatibility port of pg_duckdb main and DuckDB/Lance 1.5.4. This is not yet a performance comparison with lance_fdw; the goal was first to verify the execution and resource-management boundaries.

1. Prefiltered vector Top-K works, but runs on the QD

A filtered query using:

lance_vector_search(..., k = 2, prefilter = true)

returned the expected two rows from a five-row Lance dataset.

I added probes to the CustomScan begin and execute callbacks. Every invocation reported the dispatcher role with segindex = -1; there were no corresponding executions on the QEs.

The full Lance scan also returned exactly five rows, so there was no duplicate scan in this path. However, this is because the scan ran only once on the QD—not because fragments were assigned across segments.

The Top-K is therefore global only in the single-process DuckDB sense:

QD backend
    → DuckDB
        → Lance vector search over the whole dataset
            → final Top-K

There are no per-QE candidate sets and no Cloudberry Gather Motion ... Merge Key stage. This can be reasonable for small retrieval results, but it is not distributed Top-K.

2. Joining with a distributed Cloudberry table does not currently work

I also tested a Lance scan joined with a native Cloudberry table distributed across all three segments.

DuckDB’s PostgreSQL table reader invoked the Cloudberry planner and produced:

Gather Motion 3:1
  -> Seq Scan on native_dim
       Filter: id >= 1 AND id <= 5

However, execution then failed with:

Unexpected Motion Node Id: 1

The outer DuckDBScan plan does not contain the Cloudberry slice/interconnect metadata required to execute that nested Motion.

So the current limitation is stronger than merely losing parallelism: a distributed native-table join needs explicit Cloudberry planner/executor integration before it can run correctly.

3. DuckDB memory is outside Cloudberry’s current accounting

For a DuckDB query with a 512 MB DuckDB limit, the QD backend reached approximately:

process RSS:                 605 MB
Cloudberry tracked vmem:     15 MB
active QEs:                   0

This confirms that DuckDB’s allocator is not accounted through Cloudberry’s VmemTracker.

DuckDB’s own limit still works independently. With duckdb.max_memory = 64 MB, a larger allocation failed at about 50.2 MB used with a DuckDB out-of-memory error.

A production integration would therefore need an explicit memory-grant bridge:

• obtain a query/session grant from Cloudberry before starting DuckDB;
• derive duckdb.max_memory from that grant;
• account and release allocations through Cloudberry on success, cancellation and error;
• also bound DuckDB threads and temporary spill space.

Simply replacing DuckDB allocations with gp_malloc is probably insufficient because DuckDB uses worker threads, while Cloudberry’s current memory-protection path expects allocations from the backend owner thread.

Compatibility note

pg_duckdb did not compile or install on this Cloudberry build without PostgreSQL/Cloudberry API compatibility changes. The extension installation SQL also required a trigger compatibility adjustment. Some dynamic duckdb.query result shapes triggered a QD crash in CdbDispatchPlan in this temporary port, so this is not yet a supported integration.

My current conclusion is:

• pg_duckdb + lance is technically interesting as an optional QD-only retrieval path;
• it could reduce maintenance for Lance format, S3, FTS and hybrid-search functionality;
• it does not currently replace lance_fdw for fragment ownership, MPP scans, joins with distributed tables, distributed Top-K or Cloudberry memory accounting.

So the two approaches could eventually be complementary, but lance_fdw still covers the Cloudberry-specific execution work that DuckDB/pg_duckdb does not provide today.