DuckDB v2.0: Your Database Deserves a Better Parser

Daniël ten Wolde2026-08-20 | 16 min TL;DR: DuckDB v2.0 replaces its PostgreSQL-derived SQL parser with a PEG-based parser that is easier to evolve and can be extended at runtime. At DuckDB, one of our goals is to make working with a database system as easy as possible. Users interact with the system through the widely understood Structured Query Language (SQL). Previous blog posts have covered DuckDB’s friendly SQL, including GROUP BY ALL and column selection using SELECT * EXCLUDE (...). Before DuckDB can execute a query using these features, however, it first has to determine whether its syntax is valid. That is the job of the parser, and in DuckDB v2.0 we are completely replacing it without you noticing. What is the Role of a Parser? At a high level, DuckDB processes a SQL query through the following stages: In this blog, we focus on the tokenizer, parser, and transformer: Tokenizer: This is the first step and is responsible for splitting up the raw input string into tokens. These can be of various categories, for example: KEYWORD, NUMBER, or IDENTIFIER. It is also where comments, in SQL denoted with either -- or /* */, are recognized and skipped. Parser: The parser determines whether these tokens follow DuckDB's grammar and produces a ParseResult tree. Transformer: Converts the generic parse results into DuckDB’s internal abstract syntax tree (AST), forming structures such as SQLStatement, TableRef, and ParsedExpression. The resulting AST is passed on to the binder. The parser determines whether a query is syntactically valid, while the binder determines whether the tables, columns, and functions it refers to actually exist. Consider the following query: SELECT * WHERE true FROM range(1); Parser Error: syntax error at or near "FROM" LINE 3: FROM range(1); ^^^^ Every individual token in this query is valid, but the clauses occur in an order that DuckDB’s grammar does not accept. Friendly SQL allows both SELECT-first and FROM-first syntax, but it does not allow the clauses to appear in an arbitrary order. By comparison, the following query is syntactically valid, so it passes the parser and transformer. However, it fails later in the binder because the table missing_table does not exist. FROM missing_table; Catalog Error: Table with name missing_table does not exist! LINE 1: FROM missing_table; ^^^^^^^^^^^^^ The DuckDB SQL Dialect Although a SQL standard exists, every database system supports different parts of the standard and adds its own syntax and behavior. The resulting variants are commonly referred to as SQL dialects. Examples include the dialects supported by PostgreSQL, Oracle, GoogleSQL for BigQuery, MySQL, MariaDB, SQLite, Spark SQL, and, of course, DuckDB. DuckDB’s SQL closely follows PostgreSQL conventions, but it has evolved considerably over the years. We have added features of our own, such as GROUP BY ALL, as well as features inspired by other database systems. At the same time, DuckDB does not implement every aspect of PostgreSQL’s behavior. DuckDB therefore speaks its own SQL dialect, which we will refer to as DuckSQL in this post, even though it remains strongly influenced by PostgreSQL. This distinction is important when talking about the parser. The SQL dialect that DuckDB accepts and the implementation used to parse that SQL are two separate things. For DuckDB v2.0, we are replacing the parser implementation and rewriting its grammar. What we are not replacing is DuckSQL itself. Outgrowing the PostgreSQL-Derived Parser When DuckDB started out, it made a lot of sense to use the PostgreSQL-derived parser and grammar. This parser was already part of the first commit to DuckDB in 2018. It gave DuckDB a mature, battle-tested SQL grammar based on syntax that many users were already familiar with. We adapted the parser to our needs and added a Transformer that converted the resulting PostgreSQL-style parse tree into DuckDB’s internal AST. However, over the years this parser also came with some downsides. Extending DuckSQL meant modifying the underlying YACC/Bison grammar.

This was one of the motivations behind our earlier blog post on runtime-extensible SQL parsers. In that post and the accompanying CIDR paper, we explored whether Parsing Expression Grammars (PEGs) could provide a better foundation for an extensible database parser. At the time, the PEG parser was still an experimental prototype capable of parsing only a subset of SQL. A Primer on PEG Parsers Before looking at how we turned the prototype into a production parser, let us briefly revisit how a PEG describes a language. A PEG consists of named rules that describe how an input should be matched. Consider the following rules from DuckDB’s new grammar: SelectFrom <- SelectFromClause / FromSelectClause SelectFromClause <- SelectClause FromClause? FromSelectClause <- FromClause SelectClause? The <- operator defines a rule, / specifies a choice between alternatives, and ? makes an element optional. Together, these rules state that DuckSQL accepts both a traditional SELECT-first query: SELECT * FROM range(1); And DuckDB’s Friendly SQL FROM-first equivalent: FROM range(1) SELECT *; A PEG evaluates alternatives in order. When matching SelectFrom, the parser first attempts SelectFromClause. If that does not match, it attempts FromSelectClause. The first successful alternative is selected. As a result, PEG grammars do not have the same shift/reduce and reduce/reduce conflicts as LALR grammars. Instead, alternatives are ordered explicitly, and that order forms part of the grammar’s behavior. We are not the only ones changing to a PEG-based parser. Python switched from its LL(1) parser to a PEG-based parser in Python 3.9, also motivated by the additional flexibility PEG provides to evolve the language. In DuckDB, these rules operate on the tokens produced by the tokenizer. The matcher applies the grammar rules to those tokens and constructs a generic ParseResult tree, which is subsequently transformed into DuckDB’s internal AST. Going from Prototype to Production The research prototype demonstrated that a PEG-based SQL parser was feasible. Replacing DuckDB’s existing parser, however, required considerably more than parsing a subset of SQL. The new parser had to accept all of DuckSQL and produce the same AST expected by DuckDB’s binder. The PEG grammar was first introduced in DuckDB v1.2, where it handled autocomplete in the CLI. Later, in DuckDB v1.5, we introduced the complete PEG parser as an experimental, opt-in feature. We also used it for an April Fools' joke that made DuckDB speak Dutch. Since then, the grammar, matcher, and transformer have been steadily improved to make the PEG parser the default for DuckDB v2.0. Among other things, the parser had to support: Every statement and expression type: Supporting the complete DuckSQL dialect includes both common syntax as well as the less frequently used statements and expressions. Operator precedence and associativity: For example, SELECT true OR true AND false; must be interpreted as (true OR (true AND false)), because AND binds more tightly than OR. Correct keyword classification: Some keywords, such as SELECT, are RESERVED and cannot be used as unquoted table or column names. Other keywords may be used as identifiers depending on their context. Compatibility with DuckDB’s internal AST: The PEG transformer must produce the same DuckDB AST structures as the transformer for the PostgreSQL-derived parse nodes wherever the language behavior is intended to remain unchanged. Correct error reporting: For an invalid query, the parser should report where parsing failed and, where possible, provide context and a useful indication of what went wrong. Ideally, it should do so without pointing to a manual. Performance on unusual inputs: Besides keeping normal parsing fast, we also had to make sure that malformed queries do not suddenly take a long time to parse. Avoiding Repeated Work with Packrat Parsing One issue we encountered was repeated work during backtracking. A naïve PEG matcher can evaluate the same grammar rule at the same token position many times while trying different alternatives. For certain malformed inputs, the amount of repeated work can grow exponentially. We encountered this with queries containing a large number of unmatched opening parentheses: SELECT ((((((((((((((((((; With the experimental PEG parser shipped in v1.5, adding one more opening parenthesis approximately doubled the parsing time: 18 opening parentheses: 5.303 seconds 19 opening parentheses: 10.640 seconds We addressed this using packrat parsing, a memoization technique commonly used with PEG parsers. For each memoized matcher, we store the result of applying it at a particular token position.