Where should the client/server line sit for a SQL layer over Milvus after 3.0?

[Neko1313]

Hi all.

I've been building milvusql — a PEP 249 DBAPI
(sync + async) that parses a SQL dialect and executes the resulting AST against pymilvus,
with a SQLAlchemy 2.0 dialect and a Django database backend on top of it. Personal project,
MIT-licensed, not affiliated with or endorsed by Zilliz.

Two things motivated it. First, teams already sitting on SQLAlchemy or Django who want vector
search without rewriting their data access layer — Django in particular has no path to Milvus
today. Second, agents: an LLM emits a correct SQL string far more reliably than it emits a
correct chain of client method calls, and a string is trivially reviewable before it runs.

Milvus 3.0 changes the calculus for a layer like this, and I'd rather find out now that I'm
building the wrong thing than after another few months of it. So I'm posting for a design read
from the maintainers rather than for visibility.

Where the line currently sits

Single-collection statements are one RPC and never materialize a DataFrame — a filter SELECT,
an ANN search, a hybrid search, a bare COUNT(*):

SELECT id, content FROM docs ORDER BY embedding <=> :q LIMIT 10

Anything needing more than one collection is planned into one Milvus read per collection and
reduced client-side with Polars. Single-collection WHERE conjuncts, projection, and equi-join
keys learned from the previous read are pushed down; joins, grouping, aggregates, window
functions, CTEs and set operations are evaluated locally. The full breakdown of what reaches
Milvus and what doesn't is in the README:
https://github.com/Callix-Tools/milvusql#join-group-by-and-subqueries

The key pushdown is what keeps it honest: an ANN search returning 50 hits joined against a
million-row collection reads 50 rows from it, not a million. Reads page past the 16384-row
per-call ceiling with primary-key cursor pages, on both the sync and async cursors. Constructs
that can't be translated faithfully — WITH RECURSIVE, INTERSECT ALL/EXCEPT ALL, window
frame clauses, correlated subqueries beyond EXISTS equality — raise rather than get
mistranslated.

Worth stating plainly before anyone opens the repo: everything above is 2.6.x. The
integration suite runs against Milvus 2.6 and Milvus Lite on the 2.6 pymilvus client line, and
3.0 is untested — the 3.0 server has its own client major (pymilvus 3.0.x, released alongside
it), which is outside the current dependency range, so this isn't a version I've simply not
gotten around to trying. That makes one factual question worth asking up front: is the 2.6
client wire-compatible with a 3.0 server at all, or is moving to the 3.x client line a hard
prerequisite? The answer decides whether 3.0 support is a compatibility pass or a port.

The questions

1. How much of the relational surface do you expect to land server-side over 3.1?
3.0 already pushes ORDER BY into the engine with per-segment sort and merge-sort across query
nodes, and adds GROUP BY with COUNT/SUM/AVG/MIN/MAX computed in the kernel. 3.1
lists predicate pushdown with page-index and bloom-filter pruning at the storage layer, plus
UDFs. I'd much rather delegate than duplicate — every operator I keep client-side is one I have
to keep correct forever. Is the direction "the engine absorbs the single-collection relational
surface entirely," or is there a deliberate stopping point?

2. Is cross-collection JOIN a deliberate scope boundary, or just not-yet?
I don't see it on the 3.0 or 3.1 roadmap. If it's out of scope by design, the
plan-per-collection-and-combine approach is a permanent gap-filler and worth investing in. If
it's coming, I'd rather build the plumbing to hand off to it than build a planner you'll make
redundant. This is the single answer that most changes what I do next.

3. Does a DBAPI-shaped interface fill a real gap, or does Spark DataSource v2 cover it?
My read is that they serve different workloads — the Spark connector is the batch, analytics and
backfill path, while a DBAPI is the request-path story for an application server that already
speaks SQLAlchemy or Django and wants one connection abstraction. But you hear the actual user
demand and I don't. If "Milvus as a table" is a need you mostly hear in a Spark/Databricks
context, that's useful to know.

4. How should a SQL dialect express 3.0's first-class TEXT type?
On 2.6 full-text means a TEXT column plus a generated sparse field:

content        TEXT,
content_sparse SPARSEVEC GENERATED ALWAYS AS (BM25(content))

which spells out the schema Function + analyzer + sparse index pipeline explicitly, and maps
TEXT onto an analyzer-enabled VARCHAR underneath. With 3.0's TEXT storing original text
alongside vectors with BM25 and text-match support, does that spelling still describe what the
server does, or has it become a leaky abstraction over something the engine now owns? A related
practical question: is the 16384-row per-call ceiling still the boundary in 3.0, or has the
paging story changed?

One observation I'd offer rather than a request: ROW_NUMBER() OVER (PARTITION BY category ORDER BY distance) over a search's hits is top-k per group, which an ANN index can't answer
directly, and it's a shape people ask for constantly. 3.0's search facets with per-bucket
statistics look like they occupy adjacent ground, and I'd be curious whether that framing has
come up internally.

Happy to be told the design is wrong — that's the more useful outcome of the two. If any of it
turns out useful upstream (a docs integration page, say), I'm glad to do that work, but I'd
rather get these settled first.

[yhmo]

• Wire compatibility (your prerequisite question).
  The 2.6 client line does work against a 3.0 server — the protocol evolves additively, so existing 2.6 interfaces continue to work as-is. What you cannot do from the 2.6 client is reach the new 3.0 capabilities: the first-class TEXT type, in-kernel GROUP BY/ORDER BY/aggregates, SearchAggregation, native cursor iteration (SearchIteratorV2Info/QueryIteratorCursor). So 3.0 support is not an all-or-nothing port — it's a progressive migration: your existing 2.6 SQL layer keeps working unchanged, and you adopt the 3.x client only when (and where) you want to unlock the new server-side features. That's a comfortable position: nothing forces the switch on day one, and each new capability you push down is a reason to migrate one more piece.

• Q1 — How much of the relational surface do you expect to land server-side over 3.1?
  The direction is clear: the engine is absorbing the single-collection relational surface, and it will keep moving that way. 3.0 already executes GROUP BY with COUNT/SUM/AVG/MIN/MAX and multi-field composite grouping, plus ORDER BY (merge-sort across query nodes, with NULLS FIRST/LAST), all in the kernel. 3.1 adds predicate pushdown with page-index/bloom-filter pruning and UDFs. The practical consequence: don't implement single-collection GROUP BY/ORDER BY/aggregates yourself — push them to the SDK and let your layer degenerate into SQL→SDK translation (e.g. group_by_fields/order_by_fields/SearchAggregation params). The operators you must keep client-side are the ones the engine explicitly won't do: cross-collection joins, window functions, CTEs, set operations.

• Q2 — Is cross-collection JOIN a deliberate scope boundary, or just not-yet?
  At this point, the honest answer is undecided. There is no JOIN structure anywhere in the 3.0 protos and nothing on the 3.0/3.1 roadmap, but the team hasn't made a final call on it either. My recommendation: don't bet on either outcome. Keep your plan-per-collection + client-side Polars reduction as a general-purpose gap-filler, but keep the cross-collection layer behind a replaceable interface so it can be swapped for server-side JOIN plumbing later without a rewrite.

• Q3 — Does a DBAPI-shaped interface fill a real gap, or does Spark DataSource v2 cover it?
  Yes. pymilvus has no DBAPI/PEP 249 implementation and no plans to add one — its surface is search/query-oriented (MilvusClient, ORM, iterators), not connection/cursor/fetchone-shaped. The Spark DataSource v2 connector serves the batch/analytics/backfill path; a DBAPI serves the request-path story for application servers already on SQLAlchemy or Django. These are different workloads and different abstractions, so your reading is correct: DBAPI is a real, currently unfilled gap.

• Q4 — How should a SQL dialect express 3.0's first-class TEXT type?
  Map TEXT straight to the native 3.0 TEXT column and map full-text/BM25 to the engine's text-match capability. The 2.6 spelling you describe (TEXT → analyzer-enabled VARCHAR + GENERATED ALWAYS AS (BM25(...)) sparse field) is a manual workaround for an engine that didn't yet own text; in 3.0 the engine natively manages TEXT + BM25 (channel-local IDF) + text-match/analyzer, so replicating the generated-field pipeline would be a leaky abstraction. On paging: the 16384 ceiling is no longer a hard line — topK/maxQueryResultWindow are configurable and a largeTopK path (default 1,000,000) exists since 2.6.14; more importantly, 3.0 has native cursor-based iteration (SearchIteratorV2Info + QueryIteratorCursor), so your home-grown PK-cursor paging can be replaced by the official iterator.

[Neko1313]

@yhmo — thank you, this is exactly what I needed, and more concrete than I expected. Marking your reply as the answer and closing the thread.

Three things changed on my side as a direct result:

The 2.6 → 3.0 path is a migration, not a port. That reframing is the most useful part of your answer. I was treating 3.0 as a wall; it's a gradient. The README now carries a compatibility table that says exactly this — 2.6 client works against a 3.0 server, 3.0-only capabilities need the 3.x client line.

I'm restructuring around a server-capability layer rather than special-casing 3.0. Single-collection GROUP BY/ORDER BY/aggregates get resolved to query(group_by_fields=...) / search's order-by specs where the server supports them, and fall back to the current Polars path where it doesn't. The same mechanism gives me the replaceable cross-collection boundary you recommended for Q2 — if a server-side JOIN ever lands, it's a capability flag and a planner branch rather than a rewrite. Betting on neither outcome is the right call and I'd have got that wrong on my own.

TEXT maps natively, and the generated-SPARSEVEC spelling becomes a 2.6-only legacy form with a deprecation path rather than a swap. Same for paging: the PK-cursor implementation stays for 2.6 (where QueryIterator is sync-only, and my async cursor needs an equivalent), and defers to the native iterator on 3.0.

One thing I ran into while working through Q1 that may be worth flagging internally, since it bites anything putting SQL semantics on top: query aggregation and search aggregation have different accuracy guarantees. Query aggregation is exact over filtered data; search aggregation is documented as approximate, computed over ANN-retrieved entities. A SQL GROUP BY ... COUNT(*) promises exactness, so those two can't share a translation target — the search path needs either explicit syntax or a rejection. Not asking anything here, just noting it in case the distinction is worth making louder in the docs for anyone building a similar layer.

Thanks again for taking the time on all four.

[xiaofan-luan]

Thanks for raising these questions — this is very much aligned with how we are thinking about the boundary between Milvus and higher-level SQL interfaces.

1. Aggregation and predicate processing are definitely part of Milvus itself. We are continuing to expand and improve these capabilities, so in general we would prefer SQL layers to push these operators down to Milvus rather than reimplement them client-side.

2. For cross-collection JOINs, we do not plan to support very complex relational joins in the near term. However, simpler subquery-style patterns are possible. For example, querying one collection to obtain a set of IDs and then using those IDs to filter another collection is a pattern we may support more directly. General-purpose complex joins are not a short-term goal.

3. We do have plans for a PostgreSQL-compatible connector/interface. The initial scope will likely remain relatively simple and will not try to expose a full relational engine with complicated JOINs and other advanced relational operators.

   Spark serves a different purpose in our view: it is primarily for batch processing, analytics, ETL/backfill, etc., rather than the online serving/request path. So I agree that a DBAPI/SQLAlchemy/Django-style interface addresses a different use case.

4. For TEXT, the main differences from VARCHAR are around size and storage semantics. Conceptually, PostgreSQL TEXT should map naturally to Milvus TEXT. A SQL layer should not need to expose the underlying analyzer/sparse-vector implementation details unless the user explicitly wants to configure them.

5. For reading more than the per-request result limit, Milvus supports iterators to access much larger result sets. We are continuing to optimize the iterator path, with more improvements planned for 3.1. So for SQL cursor semantics, I would recommend building around the iterator abstraction rather than treating 16,384 rows as a fundamental SQL-layer boundary.

On your ROW_NUMBER() OVER (PARTITION BY category ORDER BY distance) example: for the vector-search-specific top-k-per-group case, this maps quite naturally to Milvus Grouping Search.

Conceptually:

ROW_NUMBER() OVER (
  PARTITION BY category
  ORDER BY distance
)

with a filter such as row_number <= K is essentially asking for the top K nearest results within each group. That is exactly the type of problem Grouping Search is intended to address.

The SQL window-function syntax is of course more general than the Milvus search API, but for this particular ANN top-k-per-group semantic, I would map it to Grouping Search rather than to search facets. Facets are more about statistics/aggregation over buckets, while Grouping Search directly affects which search results are returned per group.

Overall, I think there is a useful role for a SQL layer here. The boundary I would suggest is: push single-collection filtering, sorting, aggregation, grouping, vector search, and similar operators into Milvus whenever Milvus has native semantics for them; keep the SQL layer focused on compatibility, planning, and the relational constructs that Milvus intentionally does not try to become a full relational database for.

[Neko1313]

@xiaofan-luan — thank you for taking the time on this personally; I didn't expect the project lead to work through four questions in that much detail.

Taking your closing boundary as the design principle: push single-collection filtering, sorting, aggregation, grouping and vector search into Milvus wherever Milvus has native semantics, and keep the SQL layer on compatibility, planning, and the relational constructs Milvus deliberately isn't becoming. That is a sharper statement of scope than I had written for myself, and it's going into the README as the stated design goal.

Four things change concretely on my side:

Dialect alignment toward PostgreSQL. Point 3 is the one that reshapes the project. My vector syntax is already pgvector-shaped (<=>, VECTOR(dim), SPARSEVEC), but the full-text and DDL corners drifted toward MySQL — MATCH ... AGAINST, AUTO_INCREMENT, SHOW TABLES. I'll add PostgreSQL-native spellings alongside them so SQL written against this layer stays valid when your interface arrives, rather than becoming a competing dialect someone has to migrate off.

The ID-set pattern gets its own seam. Query one collection for a key set, filter another by it — that's exactly what my planner does for equi-joins today, so that's the piece going behind a replaceable interface, since it's the most likely to get a native equivalent. Knowing that general complex joins are out of scope is equally useful: it makes the client-side reduction a permanent part of the design rather than a placeholder.

Grouping Search instead of facets for top-k-per-group. You're right that facets answer a different question. Recognising ROW_NUMBER() OVER (PARTITION BY x ORDER BY <vector> <=> :q) with rn <= K and compiling it to group_by_field + group_size turns a client-side window into one RPC — that's my first target for server-side delegation.

TEXT and cursors. The analyzer and sparse-vector plumbing comes out of the default path and becomes opt-in for people configuring their own analyzer; cursor semantics get rebuilt on the iterator abstraction instead of treating 16,384 as a boundary worth advertising.

You already answered the question I was most nervous about — whether a SQL layer has a useful role at all — so I won't re-ask it. Three narrower things, and please treat all of them as no-obligation:

1. Rough horizon and scope for the PostgreSQL-compatible interface. Not a date — just whether it's a next-few-releases thing or a longer arc, and whether it's aimed at wire-protocol compatibility or a SQL-syntax layer. That difference decides whether I'm building a bridge for servers without it or a layer that stands alongside it, and it's the one input I can't derive myself.
2. Would a docs integration page be welcome as a PR — something in the shape of your existing integration tutorials, e.g. "use Milvus from SQLAlchemy" or "use Milvus from Django," aimed at teams already on those stacks rather than at promoting the library. Happy to write it to whatever standard the docs team wants, or to drop the idea if the ecosystem section isn't taking third-party entries right now.
3. If there's ever an early design note or RFC for the Postgres interface, I'd like to read it and align rather than diverge. No expectation of access — just registering the interest, since converging is cheaper for both sides than two dialects that almost match.

Thanks again. This saved me a substantial amount of work in the wrong direction.

[zycgit]

@Neko1313 @yhmo @xiaofan-luan @augustocbx

Here’s a tool that lets you interact with Milvus using SQL through a JDBC driver. It supports common statements such as INSERT, UPDATE, DELETE, and SELECT, along with data import and many administrative operations. It looks like it could already serve as an alternative to the SDK.

https://www.dbvisitor.net/en/docs/features/milvus/about
https://github.com/zycgit/dbvisitor/tree/dev/dbvisitor-adapter/jdbc-milvus