SQL+VS: Integrating Vector Search with SQL

• SQL+VS queries are composite database workloads that embed vector search as a first-class operator within full analytical SQL plans.
• They combine semantic retrieval with operations like joins, grouping, and lateral execution, leveraging systems such as FAISS on CPU and GPU.
• Benchmark studies using Vec-H and transfer optimization techniques highlight significant performance gains and informed CPU/GPU tradeoffs.

SQL+VS queries are composite database workloads in which vector search is embedded inside relational query plans rather than exposed as an isolated nearest-neighbor primitive. In recent systems work, they are treated as full analytical SQL plans with vector search embedded, combining semantic retrieval with joins, grouping, sorting, anti/semi joins, and lateral execution (Mageirakos et al., 15 May 2026). Adjacent literature addresses the surrounding lifecycle of such queries: natural-language specification, diagrammatic interpretation, semantic equivalence, data generation for non-equivalence witnesses, automated rewriting, and educational use (Redd et al., 29 Oct 2025, Baik et al., 2020, Leventidis et al., 2020, Chu et al., 2018, Somwase et al., 2024, Kumar et al., 2024).

1. Relational–vector composition as a query model

The principal motivation for SQL+VS is that many modern workloads require semantic retrieval plus structured filtering or aggregation. Representative examples include finding the most visually similar products and then computing supplier statistics, or finding reviews semantically similar to a complaint and then excluding suppliers associated with them (Mageirakos et al., 15 May 2026). This workload model differs from stand-alone vector search because the semantic-search stage is only one operator inside a larger relational plan.

A central systems observation is that relational engines already support rich plan composition, whereas most vector databases expose a narrower API. The resulting design question is therefore not only how to accelerate ANN or ENN, but how to optimize entire plans in which vector search interacts with join shape, post-filters, grouping, and lateral execution (Mageirakos et al., 15 May 2026). This suggests that SQL+VS should be understood as a database-engine problem rather than solely an information-retrieval problem.

The same broad pattern appears in text-to-SQL and agentic analytics systems. For realistic spatio-temporal workloads, natural-language interfaces must map vague phrasing to schema terms, generate executable SQL, handle temporal and spatial reasoning, and sometimes select a visualization rather than a raw table as the appropriate output (Redd et al., 29 Oct 2025). A plausible implication is that SQL+VS querying is increasingly part of a larger analytical workflow in which query generation, execution, and presentation are jointly optimized.

2. Benchmark structure and representative query patterns

A concrete benchmark formulation is provided by Vec-H, which extends TPC-H with vector data by adding REVIEWS and IMAGES tables, each with an EMBEDDING column (Mageirakos et al., 15 May 2026). The vector data is derived from the Amazon Reviews dataset, covering 48M unique products, 571M reviews, 133M images, and 34 product categories. Vec-H creates a one-to-one mapping from TPC-H parts to Amazon products, attaches all reviews and images for that product to the corresponding [PART](https://www.emergentmind.com/topics/prediction-augmented-residual-tree-part), and maps reviews to CUSTOMER using Amazon user IDs with random reassignment when needed to preserve TPC-H customer cardinality (Mageirakos et al., 15 May 2026).

The benchmark uses an approximate embedding-size model

$$\text{Vec\_bytes} \approx SF \times 200{,}000 \times (\overline{R}\times d_r + \overline{I}\times d_i)\times b$$

with $\overline{R}\approx 12$ average reviews per product and $\overline{I}\approx 4$ average images per product (Mageirakos et al., 15 May 2026). For $SF=1$, the reported instantiation uses Qwen 0.6B text embeddings for reviews with $d=1024$ and size about 9.8 GB, and SigLIP2 image embeddings with $d=1152$ and size about 3.4 GB, yielding a Rel:VS ratio of about 1:12 (Mageirakos et al., 15 May 2026).

The query templates are organized around where vector search enters the plan.

Pattern family	Example queries	Characteristic role
VS@Start	Q2, Q16, Q19	Vector search drives the plan
VS@Mid	Q10, Q13, Q18	Vector search enriches SQL output
VS@End	Q11, Q15	SQL produces vectors used by later search

These templates collectively cover five VS integration patterns: inner, left, lateral, anti, and semi joins (Mageirakos et al., 15 May 2026). That is significant because it moves beyond the common “filter + ANN” abstraction and treats vector search as an operator that participates in optimizer-visible join structure.

Because the vector component can be approximate, evaluation is performed at the query level rather than solely at the operator level. For ordinary queries the metric is recall, defined as the fraction of true output rows returned by ANN. For Q19, which returns a scalar revenue sum, the paper uses relative error,

$$\mathit{rel\_err} = \frac{|\mathit{rev}_{\text{ANN}} - \mathit{rev}_{\text{ENN}}|}{\mathit{rev}_{\text{ENN}}}$$

with a target of at least 95% recall per query and at most 1% relative error for Q19 (Mageirakos et al., 15 May 2026). Some queries require oversampling because post-filters can discard vector-search hits; on GPU, FAISS top-$k$ limits become a constraint for workloads such as Q15 (Mageirakos et al., 15 May 2026).

3. Operator semantics and execution architecture

The execution substrate described for SQL+VS is MaxVec, an extension of the heterogeneous query engine Maximus (Mageirakos et al., 15 May 2026). Operator placement is explicit and heterogeneous, with operators independently assigned to CPU or GPU through names such as cpu::acero::filter and gpu::cudf::hash_join. MaxVec promotes vector search to a first-class operator, backed by FAISS on CPU and FAISS/cuVS on GPU (Mageirakos et al., 15 May 2026).

The logical vector-search operator is binary:

$$\text{vector\_search(query vectors, embedding column, k)}$$

One input supplies the query-vector side and the other supplies the embedding corpus (Mageirakos et al., 15 May 2026). When the query side has cardinality 1, this behaves as ordinary top-$k$ search; with batched query vectors, it behaves as a similarity join. This operatorization is important because it lets vector search participate in plan construction as a relationally composable node rather than as an external RPC.

Embeddings are stored using embeddings_type, implemented as arrow::ListType on CPU and cudf::ListDType on GPU, with contiguous storage intended to enable zero-copy interoperability with FAISS where possible (Mageirakos et al., 15 May 2026). Physically, the operator has two ports. The data port is always blocking because the full search corpus must be materialized before search begins. The query port is streaming on CPU but blocking/batched on GPU, where query vectors are accumulated so the search can be expressed as a large GEMM over an $\overline{R}\approx 12$0 workload (Mageirakos et al., 15 May 2026). This batching behavior is central to GPU utilization.

The architecture supports both ENN and ANN. ANN uses IVF or graph indexes, while ENN performs exact exhaustive search (Mageirakos et al., 15 May 2026). The physical deployment space includes all-CPU execution, all-GPU execution, and heterogeneous strategies in which vector search and relational operators are placed differently; it also distinguishes whether data and indexes reside initially in host or device memory, and whether GPU execution must copy the index per query (Mageirakos et al., 15 May 2026).

4. CPU/GPU tradeoffs and index organization

A major empirical result is that, for SQL+VS workloads, the relational components benefit much more from running on the GPU than the vector search part (Mageirakos et al., 15 May 2026). The paper quantifies this with the share of total speedup attributable to relational acceleration,

$\overline{R}\approx 12$1

and reports median relational shares of 87% for CAGRA, 77% for IVF1024, 84% for IVF4096, and 44% for ENN (Mageirakos et al., 15 May 2026). Under ANN, relational operators on CPU were reported as 12× to 150× slower than the vector-search portion for several queries (Mageirakos et al., 15 May 2026).

The most important negative result concerns conventional data-owning vector indexes. When the index stores the embeddings themselves, moving the index to GPU is often prohibitively expensive; for ANN, index transfer frequently accounts for 95%+ of wall time (Mageirakos et al., 15 May 2026). Faster interconnects help but do not remove the bottleneck. Even under NVLink-C2C, transfer and transformation costs can dominate when the index is data-owning (Mageirakos et al., 15 May 2026). ENN behaves differently because its higher compute intensity amortizes transfer better, and GPU ENN on NVLink is reported as 4×–19× faster than CPU (Mageirakos et al., 15 May 2026).

To address this, the paper introduces a non-data-owning vector index. In this organization, the index stores only the search structure—such as IVF centroids or graph structure—while the embeddings remain in the base table (Mageirakos et al., 15 May 2026). The design is compared to a secondary B-tree index that points to table rows rather than duplicating them. On coherent NVLink-C2C systems with ATS, the GPU can access host memory directly, so only the compact search structure must be resident or transferred (Mageirakos et al., 15 May 2026).

Three transfer optimizations are reported: pinning, caching, and host-residency (Mageirakos et al., 15 May 2026). Their measured effect is large. IVF1024H metadata transfer is reduced from about 1266 ms to 4 ms, and CAGRAC+H metadata transfer from about 112 ms to 0.8 ms (Mageirakos et al., 15 May 2026). For CAGRA, total transfer improves from 853 ms to 425 ms with caching alone, from 578 ms to 187 ms with pinning on PCIe, and from 112 ms to 27.4 ms on NVLink with caching plus pinning (Mageirakos et al., 15 May 2026). The paper also identifies a structural inefficiency in IVF movement: 5121 cudaMemcpy calls for IVF1024 and 20481 for IVF4096, driving effective throughput below 2% of theoretical bandwidth (Mageirakos et al., 15 May 2026).

The practical recommendations are correspondingly conditional. If all data and the index fit on GPU, use gpu. If only the index fits, use gpu-i for IVF and hybrid for CAGRA/HNSW. If neither fits, hybrid is usually best, while copy-i can be an alternative for IVF at large batch sizes (Mageirakos et al., 15 May 2026). On unified-memory systems such as DGX Spark, where placement asymmetry largely disappears, GPU execution wins consistently for both ENN and ANN (Mageirakos et al., 15 May 2026).

5. Query specification, visualization, and agentic orchestration

SQL+VS workloads also raise an interface problem: composite analytical queries are difficult to specify directly. One response is dual-specification query synthesis, implemented in Duoquest, which combines a natural-language query with an optional table sketch query (TSQ) (Baik et al., 2020). The TSQ contains optional type annotations, optional example tuples, a sorting flag, and a limit value, and supports partial tuples, exact cells, empty cells, and range cells (Baik et al., 2020). Synthesis is performed by guided partial query enumeration (GPQE), a best-first search over partial SQL programs with early pruning by TSQ verification. In user studies, Duoquest achieved 85.9% success compared with 23.4% for the NLI baseline, a 62.5% absolute increase (Baik et al., 2020). On Spider, reported top-1 accuracy was 63.5% for Duoquest versus 30.2% on dev and 31.2% on test for the NLI baseline (Baik et al., 2020).

Interpretation of complex SQL is addressed by QueryVis, which renders SQL queries as logic-based diagrams grounded in FOL and tuple relational calculus (Leventidis et al., 2020). Tables appear as composite marks, joins as lines, predicates as labels, and quantifiers such as ∃, ¬∃, and ∀ as bounding boxes (Leventidis et al., 2020). The supported fragment includes SELECT ... FROM ... WHERE ..., conjunctions, join predicates, [[NOT](https://www.emergentmind.com/topics/neural-organ-transplantation-not)] EXISTS, [NOT] IN, and CO {[ALL](https://www.emergentmind.com/topics/cascading-annealed-language-learning-all) | ANY}(Q), under set semantics, two-valued logic, no NULLs, no aggregates, and no disjunctions in the main formal development (Leventidis et al., 2020). A within-subjects study with 42 participants reported that, for the 9 non-grouping questions, QV vs SQL was about 20% faster with $\overline{R}\approx 12$2, while error reductions were described as weak evidence (Leventidis et al., 2020).

At the execution end, agentic systems can transform natural-language requests into SQL plus a downstream presentation choice. A Mistral-based ReAct agent orchestrated with LangGraph has been evaluated on 35 spatio-temporal questions over NYC and Tokyo check-in data, using tools for schema inspection, SQL generation, SQL execution, plotting, and mapping (Redd et al., 29 Oct 2025). The agentic pipeline achieved 91.4% accuracy versus 28.6% for a naive baseline using the same SQL generator, with large gains on temporal, spatial, multi-step, external-knowledge, and cross-dataset categories (Redd et al., 29 Oct 2025). The system can select line plots for temporal queries, bar charts for categorical summaries, maps or heatmaps for spatial queries, and then produce a concise natural-language explanation (Redd et al., 29 Oct 2025). Although this work is not framed as vector search, it addresses the same orchestration problem: SQL generation is only one stage in an end-to-end analytical workflow.

6. Semantic equivalence, testing, and rewrite verification

Optimization of SQL+VS workloads inherits the classical SQL problem that rewrites must preserve semantics. One formal foundation is U-semiring, introduced for deciding semantic equivalences of SQL queries involving bags, sets, DISTINCT, subqueries, views, indexes, keys, and foreign keys (Chu et al., 2018). Relations are modeled as functions $\overline{R}\approx 12$3, and the formalism augments semiring semantics with unbounded summation, a squash operator for duplicate elimination, and a negation-like operator for NOT EXISTS (Chu et al., 2018). Key proof principles include equality substitution,

$\overline{R}\approx 12$4

and key constraints expressed equationally as

$\overline{R}\approx 12$5

The implementation, in Lean, translates SQL to U-expressions, rewrites them into SPNF, canonizes them under constraints, and compares normalized terms (Chu et al., 2018). The paper reports formal verification of 39 query rewrite rules drawn from classical work and real SQL engines (Chu et al., 2018).

For query testing and non-equivalence witnesses, a recent SMT-based approach represents base and intermediate results as symbolic tables with a count attribute CNT for bag semantics (Somwase et al., 2024). The system preprocesses and decorrelates subqueries, translates the query into constraints over base tables and result tables, solves them with Z3, and materializes concrete witness datasets (Somwase et al., 2024). It handles joins, outer joins, semijoins, anti-semijoins, aggregation, HAVING, DISTINCT, set operators, nested and correlated subqueries, and NULL-aware IN and NOT IN, while explicitly noting that it does not support full SQL three-valued logic because unknown is not modeled (Somwase et al., 2024). On the XDataBM benchmark of 84 original queries and 407 non-equivalent mutants, the new system killed 378/407 mutants, compared with 230/407 for VeriEQL and 244/407 for XDataO (Somwase et al., 2024). For correlated WHERE-clause subqueries, it reports 79/80 killed mutants versus 0 for VeriEQL (Somwase et al., 2024).

LLM-based rewrite systems add a further layer of optimization methodology. QUITE is a training-free, feedback-aware SQL query rewrite system organized as a multi-agent workflow under an FSM with Reasoning, Verification, Decision, and Termination stages (Song et al., 9 Jun 2025). It uses real-time DB feedback such as EXPLAIN, syntax errors, equivalence checks via SQLSolver, execution metrics, and plan characteristics, and augments rewriting with a knowledge base, a hybrid SQL corrector, an agent memory buffer, and fine-grained hint injection through pg_hint_plan (Song et al., 9 Jun 2025). Reported results include up to 35.8% execution-time reduction over prior approaches and 24.1% more rewrites, with equivalence rates of 100% on TPC-H, 96.8% on DSB, and 98.3% on Calcite (Song et al., 9 Jun 2025). This suggests that feedback-aware rewriting is becoming relevant wherever composite SQL plans are too diverse for fixed algebraic rule sets alone.

7. Null semantics, grading, and educational use

Formal SQL semantics remain relevant because composite queries are still interpreted under the core language. One line of work argues that SQL with nulls can be given an equally expressive two-valued semantics by conflating unknown with false during evaluation rather than only at the end of WHERE processing (Libkin et al., 2020). Under the proposed semantics, comparisons become

$\overline{R}\approx 12$6

and the paper proves that the two-valued semantics captures standard SQL efficiently for the SQL 1999 core, including SELECT-FROM-WHERE, GROUP BY, HAVING, subqueries, IN / EXISTS / ANY / ALL, set operations, and recursion (Libkin et al., 2020). It also reports that 120 of 121 benchmark queries across TPC-H and TPC-DS coincide under 2VL and 3VL, with only TPC-H Q16 failing the sufficient condition (Libkin et al., 2020).

In educational settings, the challenge is often not binary correctness but graded semantic proximity. A graph-based method for automated linear SQL grading models queries as nodes in an implicit graph and edits as weighted transitions, defining distance as the minimum-cost edit path

$\overline{R}\approx 12$7

(Köberlein et al., 2024). The prototype, implemented in TypeScript, includes 181 edits, of which 94 are atomic (Köberlein et al., 2024). In a survey over 11 scenarios with 20 participants, the approach obtained mean fairness $\overline{R}\approx 12$8 and comprehensibility $\overline{R}\approx 12$9, compared with $\overline{I}\approx 4$0 and $\overline{I}\approx 4$1 for dynamic analysis, and $\overline{I}\approx 4$2 and $\overline{I}\approx 4$3 for manual grading (Köberlein et al., 2024). The reported interpretation is that it approaches manual grading quality while remaining more explainable than purely dynamic methods.

Direct evidence on how learners seek help when writing SQL is provided by a randomized interview study in a database classroom (Kumar et al., 2024). In a 12-week introductory database systems course with 226 students, 39 volunteered for a study comparing web search, ChatGPT, and an instructor-tuned LLM for two SQL-writing tasks over Zoom (Kumar et al., 2024). The strongest quantitative effect concerned interaction volume: a Kruskal-Wallis test found $\overline{I}\approx 4$4, $\overline{I}\approx 4$5, and post-hoc Dunn tests showed that the instructor-tuned LLM elicited more than twice as many interactions as ChatGPT and web search (Kumar et al., 2024). The number of edits to the final query did not differ significantly, with $\overline{I}\approx 4$6, $\overline{I}\approx 4$7, and final query quality also showed no significant condition effect, $\overline{I}\approx 4$8, $\overline{I}\approx 4$9, although correctness was directionally higher for the LLM conditions (Kumar et al., 2024). Mental demand was likewise non-significant overall, $SF=1$0, $SF=1$1, but descriptively lower for the instructor-tuned LLM (Kumar et al., 2024). These findings position LLMs not merely as answer engines but as pedagogically structured supports for iterative SQL problem solving.

Taken together, the literature presents SQL+VS querying as both a systems problem and a workflow problem. At the systems level, the dominant issues are operator composition, approximate result quality, heterogeneous execution, and index organization. At the workflow level, the central concerns are specification, interpretability, semantic verification, rewrite safety, grading, and help-seeking. This suggests that the long-term development of SQL+VS will depend not only on faster ANN kernels or better GPU placement, but also on robust formalisms and interfaces for expressing, understanding, and validating increasingly composite analytical queries.