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_typevalue: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
PREPAREstatement 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 TABLEALTER TABLEDROP TABLECOMMENT 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
EXPLAINagainst the SQL query.