A Survey of Text-to-SQL in the Era of LLMs: Where Are We and Where Are We Going?

A community overview of the August 2024 arXiv survey (arXiv:2408.05109), originally shared on zhichai.net.

Overview

| Field | Detail | |-------|--------| | Title | A Survey of Text-to-SQL in the Era of LLMs: Where are we, and where are we going? | | Authors | Xinyu Liu, Shuyu Shen, Boyan Li, Peixian Ma, Runzhi Jiang, Yuxin Zhang, et al. (10 authors) | | Published | August 2024, arXiv | | Link | https://arxiv.org/abs/2408.05109 | | Type | Survey | | Category | Querying Structured Information |

Background and Motivation

Text-to-SQL — translating natural language questions into SQL queries — has been reshaped by large language models (LLMs). Traditional pipeline approaches that treat retrieval, ranking, and generation as separate stages struggle to meet modern demands for natural language interaction, multi-hop reasoning, and access to up-to-date knowledge. This survey systematically maps the theoretical and practical boundaries of LLM-era Text-to-SQL, covering open-domain information access, enterprise knowledge retrieval, conversational search, and end-to-end architectures that coordinate external knowledge sources with generative models.

Core Contributions

• Provides a unified perspective that organizes scattered related work into a comparable framework.
• Clearly decomposes method components (representation learning, retrievers, re-rankers, planners, generators, feedback mechanisms) for easier engineering adoption.
• Offers reproducible benchmarks, datasets, and taxonomy tables, lowering the entry barrier for future researchers.
• Discusses interfaces with emerging paradigms such as LLM tool calling, reinforcement learning, and multi-agent collaboration, including migration paths from research prototypes to industrial systems.
• Identifies open problems: evaluation reliability, latency and cost, hallucination and safety, and cross-lingual/multimodal extension.

Taxonomy of Approaches

| Dimension | Subtypes | Representative Ideas | Strengths | Limitations | |-----------|----------|---------------------|-----------|-------------| | Modeling paradigm | Discriminative / generative retrieval | Dual-tower, cross-encoder, DSI, GPT indexing | Mature, scalable | Semantic drift, update cost | | LLM integration | RAG / Agent / Tool-use | Retrieval augmentation, search agents, API calls | Flexible, interpretable | Latency, error propagation | | Optimization goals | Relevance / diversity / freshness | Multi-objective LTR, RLHF, online learning | Business-aligned | Scarce annotations | | Evaluation | Offline / online / human | nDCG, MRR, LLM-as-judge, A/B tests | Comparable | Diverges from real satisfaction |

The field broadly divides into four main lines: dense retrieval (high recall, low latency, suited to first-stage retrieval), late interaction such as ColBERT (higher accuracy but larger indexes), generative IR (directly "generating" documents via tokens or docids, simplifying cascades), and agentic search (modeling search as sequential decision-making with multi-hop reasoning and self-reflection).

Research Timeline

• 2019–2021: BERT re-ranking and DPR establish the foundations of neural retrieval.
• 2022–2023: RAG and FreshLLM drive the fusion of retrieval and generation.
• 2024 onward: Conversational/agentic search and Gen-RecSys expand rapidly.
• 2025–2026 outlook: RL-trained search agents, Deep Research, and GraphRAG become new growth areas.

Evaluation Paradigms

Typical benchmarks and metrics discussed in this space:

• Datasets: MS MARCO, BEIR, Natural Questions, domain-specific corpora, public recommendation datasets.
• Metrics: nDCG@10, MRR, Recall@k, Hit@k, human preference, task success rate, latency, token cost.
• Baselines: BM25, dense retrieval, cross-encoder re-ranking, retrieval-free LLMs, commercial search APIs.
• Ablations: contribution of retrieval steps, re-ranking depth, and training data scale.

Specific numerical results should be verified against the original PDF; this post summarizes the experimental design based on the abstract and public metadata.

Key Insights for Search, Recommendation, and Personalization

1. Architecture: Cascaded retrieval + re-ranking + generation remains mainstream, but agentic paradigms are making "when and how often to retrieve" itself a learnable objective. 2. Data: High-quality instruction data and click/session logs are equally critical; synthetic data must guard against knowledge leakage and distribution shift. 3. Evaluation: The gap between offline metrics and online satisfaction is widening; LLM-as-judge needs cross-validation with human evaluation. 4. Product: Latency, cost, explainability, and safety policies are hard constraints for industrial deployment — academic benchmarks alone are insufficient.

Open Problems and Future Directions

The authors point to insufficient unified benchmarks, non-reproducible private data, LLM evaluation bias, and safety/cost constraints for agentic systems. Future work includes finer-grained process supervision, joint retrieval-reasoning training, enterprise-grade metadata governance, and multimodal and cross-lingual consistency.

Related Entries

• A Survey on Employing Large Language Models for Text-to-SQL Tasks (May 2025, ACM)
• Large Language Model for Table Processing: A Survey (Jan 2025, Frontiers)
• Next-Generation Database Interfaces: A Survey of LLM-based Text-to-SQL (arXiv:2406.08426)
• Querying Databases with Function Calling (arXiv:2502.00032)

Glossary

| Term | Meaning | |------|---------| | IR | Information Retrieval | | RAG | Retrieval-Augmented Generation | | LTR | Learning to Rank | | nDCG | Normalized Discounted Cumulative Gain | | Agentic Search | Modeling search as sequential decision-making with tool calls | | Gen-IR | Generative Information Retrieval |

Actionable Takeaways

1. Researchers: Reproduce core comparisons; check whether statistical significance and compute costs are reported. 2. Engineers: Extract pluggable modules (encoders, re-rankers, planners) and assess integration cost with existing stacks. 3. Product managers: Focus on user-perceivable benefits (latency, answer trustworthiness, multi-turn consistency) rather than offline nDCG alone.