Talk to your PostgreSQL database in plain English: a developer's guide

How natural-language-to-SQL actually works on PostgreSQL: schema introspection, retrieval, prompt design, sandboxing, and the failure modes that don't show up in demos.

“Talk to your database” demos look magical for ten minutes. Then someone asks a real question over a real schema and the wheels come off.

This is what actually goes into making natural-language-to-SQL on PostgreSQL work in production. Not the demo path, the production path. Written from the side of someone who has to support the system on Monday morning.

The pipeline, end to end

user question
  → schema retrieval (which tables are relevant?)
  → prompt assembly (schema + examples + question)
  → SQL generation (LLM)
  → SQL validation (parser + permission check)
  → execution (read-only session)
  → result formatting (table / chart / number)
  → answer with rationale

Each step has a failure mode. Most “talk to your database” tools fail at step 2 or step 5.

Step 1: schema retrieval

Your database probably has 80–800 tables. You cannot put all of them in the prompt. So you retrieve a subset.

Approaches, in increasing order of effort:

Static table list. Works for tiny apps, breaks fast.
Embedding similarity over (table_name, column_names, sample_values, comments). A solid baseline.
Hybrid: embedding retrieval + LLM re-ranker + table dependency closure (if you pull orders, also pull order_items and customers).

The single biggest accuracy improvement most teams see is adding column-level descriptions to the embedded text. total is ambiguous. total (₹, including GST, before discount) is not.

Step 2: prompt assembly

The prompt to the LLM should contain:

The schema for the retrieved tables (only those).
3–5 few-shot examples taken from a hand-curated set of question/SQL pairs that look like your real domain.
The user’s question.
A description of the SQL dialect (PostgreSQL 16 vs 14 matters: WINDOW, lateral joins, GENERATED columns).
A constraint section: read-only, no DDL, no DML, no pg_* access, max rows = X.

The model behaves differently if you tell it “if you cannot answer with high confidence, ask a clarifying question”. That single line shaves a lot of garbage SQL.

Step 3: SQL generation and validation

Don’t trust the output. Validate locally:

Parse with pg_query or libpg_query bindings. If it doesn’t parse, do not send it.
Check it’s a SELECT only. No INSERT/UPDATE/DELETE/COPY/CALL.
Inspect referenced tables against an allow-list.
Reject pg_catalog, information_schema unless explicitly allowed. Catalog scans are common attack vectors and noise vectors.

Then check the EXPLAIN cost. If a query has cost > some threshold, ask for confirmation before running.

Step 4: execution

This is where most prototypes have security holes:

Use a separate, read-only role with no access to system catalogs beyond what’s needed.
Set statement_timeout at the session level. 30s is generous; 5s is right for a chat UX.
Set idle_in_transaction_session_timeout.
Set lock_timeout = 0. Never wait on a lock for a NL query.
SET LOCAL search_path to known schemas. Don’t trust the connection default.
Limit row count with a wrapping LIMIT or FETCH FIRST N ROWS ONLY. Even on aggregations: a GROUP BY that explodes can return millions of rows.

If you’re going further, route NL queries to a read replica, not primary. They are bursty and unpredictable.

Step 5: result formatting

The answer is not the SQL result. The answer is:

A short text summary (1–2 sentences).
The right visualisation. Number, table or chart, picked from the result shape (single-row + single-column = number; two-column with one numeric = bar/line; three-column with date = time series).
The SQL, collapsed, expandable.
A “what this is based on” line listing tables, filters and date range used.

The transparency matters more than the polish. If a finance team can’t see which sales table you queried, they will not trust the number.

The failure modes worth knowing

Things that break in production that don’t break in demos:

Implicit timezones. Your timestamps are stored UTC, your business runs IST. NL queries about “yesterday” need to specify the user’s timezone in the prompt.
Soft deletes. Every table has deleted_at and the LLM forgets to filter. Solve with a view layer that pre-applies soft-delete filters, and let the NL system only see the views.
Multi-tenant filtering. WHERE org_id = ? must be enforced outside the LLM, by row-level security or a post-processor that injects the predicate. Never trust the LLM with tenancy.
NULL semantics. “Customers without orders” needs LEFT JOIN ... WHERE o.id IS NULL. The LLM gets this right 80% of the time. The other 20% silently miss customers with at least one cancelled order.

What to build vs what to use

If you’re a small team and the problem is “let our finance/ops/customer-success people query our Postgres without SQL”, building this in-house is roughly 3 to 4 engineer-months to a usable state, and a perpetual maintenance load after that.

For most teams it makes more sense to use a hosted natural-language analytics layer, hook it to your read replica, and spend your engineering on differentiated product.

If you want to see what one of those looks like over your own Postgres, book a 20-minute demo. Point AnalytAI at your read replica, and ask it the questions you’ve been emailing the data team about.