Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Internals

These internal mechanisms may inform how to configure and tune the extension.

Vector backends

Vectors enable search capabilities on entities necessary for the RAG workflow. The vector backend determines how vectors are stored and processed.

The backend can be changed via set_vector_backend().

default

Vectors are stored as an array of single precision floating-point numbers (FLOAT4[]). Cosine distance is computed with an internal function that detects available CPU features and leverages SIMD instructions when supported.

pgvector

Uses the VECTOR type and cosine distance <=> operator. These are usually provided by pgvector, but other extensions, such as VectorChord, can be used too.

RAG workflow

The rag workflow for text-to-sql generation works as follows:

  • Get DDLs relevant to the user query. The process will vary depending on the engine’s table_filter_type value:
    • smart - The instruct model is asked to filter for DDLs relevant to the user query.
    • quick - DDLs are filtedetects available CPU features and leverages SIMD instructions when supported.red via cosine similarity through pgvector.
  • Provide the user query, DDLs, and relevant instructions to the instruct model.
  • The resulting query run against a PREPARE statement to check if it’s valid.
    • If it’s valid, return the query to the user.
    • If the query is not valid, the instruct model is asked to fix the query. The new query is returned to the user.

Metadata triggers

Each engine creates triggers for these operations in its target schema:

  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • COMMENT ON

When new tables or columns are added, the encoder model generates semantic vectors. These triggers are deleted when the engine is removed.

Prompt templates

  • All prompt templates are rendered using tera.
  • Templates require specific blocks to render correctly, some block values can be specified as parameters when creating an engine.
  • Default prompts can be found here.

system_prompt_template

System prompt containing high-level instructions for text-to-sql generation.

Blocks

  • output_format_description - Text instructions on how the model should output structured data to be consumed by the engine.

user_prompt_template

User prompt containing specific instructions for text-to-sql generation.

Blocks

  • user_query - Natural language query to be converted
  • relevant_ddls_block - DDL list with optional surrounding text, see relevant_ddls_template
  • similar_queries_block - List of SQL queries similar to the user query, see similar_queries_template.

relevant_ddls_template

Block used to present DDL listing when generating SQL from text.

Blocks

  • relevant_ddls - Raw list of DDLs relevant to the query

similar_queries_template

Block used to present similar queries when generating SQL from text. Currently unused.

filter_ddls_template

User prompt containing specific instructions for filtering DDLs. Used when table_filter_type is set to smart.

Blocks

  • user_query - User query used to filter DDLs with.
  • relevant_ddls - List of DDLs to filter.

syntax_correction_template

User prompt containing specific instructions for correcting an erroneous query.

Blocks

  • query - The erroneous query.
  • error_message - Error related to the query.

explain_query_template

User prompt containing specific instructions for explaining what a query does and how it works.

Blocks

  • output_format_description - Text instructions on how the model should output structured data to be consumed by the engine.
  • sql_query - The query to be explained
  • explain - Contains the output of EXPLAIN against the SQL query.