references/branching.md
# Lakebase Postgres Branching
**Outcome:** a created Neon branch — or a clear, actionable next step if creation cannot proceed. Choose the correct branch type, then execute branch creation with the CLI (or MCP where the CLI isn't usable).
- **Normal branch** for realistic migration and query testing with real data.
- **Schema-only branch (Beta)** for sensitive data workflows where structure is needed without copying rows.
## Branch Type Decision
Use this decision rule first:
1. If the user wants to test complex migrations, performance, or behavior against production-like data, choose a **normal branch**.
2. If the user needs to avoid copying sensitive data, choose a **schema-only branch**.
If the request is ambiguous, ask one clarifying question:
"Do you need realistic data for testing, or only schema structure because the data is sensitive?"
## Tool Selection: CLI or MCP
Support both the Neon CLI and the Neon MCP server, but **default to the CLI**. Use MCP only when the CLI is unavailable or blocked in your environment, cannot be authenticated, or the user explicitly asks for MCP.
- CLI link: https://neon.com/docs/cli/quickstart.md
- MCP link: https://neon.com/docs/ai/neon-mcp-server.md
### Selection order
1. Check the CLI first:
- Run `neon --version` to confirm the CLI is installed.
- Run `neon projects list` to confirm auth/context.
2. If the CLI is missing, direct installation via quickstart.
3. If the CLI is installed but not authenticated, guide the user through `neon auth` (or API key auth), then continue.
4. Switch to MCP when the CLI cannot be used — no CLI access in the environment, execution blocked, or authentication not possible — or when the user explicitly asks for MCP. Confirm Neon MCP tools are available and authenticated (for example, listing projects works), then follow the MCP branch flow below.
5. If neither path is successful, use the Neon REST API:
- https://neon.com/docs/guides/branching-neon-api.md
### MCP branch flow
1. Choose normal vs schema-only based on data sensitivity and migration-testing goals.
2. Use branch tools (for example, `create_branch`) to create the branch.
3. Validate with read tools (for example, `describe_branch`).
4. For migration workflows, prefer branch-based migration flows before applying to main.
## Create a Normal Branch (Preferred for Real-Data Migration Testing)
Use this when the user needs realistic testing conditions.
Real production-like data can expose edge cases your seed or data migration scripts miss, which helps catch migration issues before going live.
Link: https://neon.com/docs/introduction/branching.md
### Steps
1. Settle the tool path first (see [Selection order](#selection-order)): verify the CLI with `neon --version`, and fall back to MCP only if the CLI isn't usable.
2. Ensure project context is set (`neon set-context --project-id <your-project-id>`) or include `--project-id` on commands.
3. Create the branch:
```bash
neon branches create \
--name <branch-name> \
--parent <parent-branch-id-or-name> \
--expires-at 2026-12-15T18:02:16Z
```
4. Optionally fetch a connection string for the new branch:
```bash
neon connection-string <branch-name>
```
## Create a Schema-Only Branch (Beta, Sensitive Data)
Use this when users must not copy production rows into the test branch.
Link: https://neon.com/docs/guides/branching-schema-only.md
### Steps
1. Settle the tool path first (see [Selection order](#selection-order)): verify the CLI with `neon --version`, and fall back to MCP only if the CLI isn't usable.
2. Create the schema-only branch:
```bash
neon branches create \
--name <schema-only-branch-name> \
--parent <parent-branch-id-or-name> \
--schema-only \
--expires-at 2026-12-15T18:02:16Z
```
If multiple projects exist, include `--project-id`:
```bash
neon branches create \
--name <schema-only-branch-name> \
--parent <parent-branch-id-or-name> \
--schema-only \
--project-id <your-project-id> \
--expires-at 2026-12-15T18:02:16Z
```
### Beta Support Guidance (Mandatory)
Schema-only branching is in Beta. If users report unexpected behavior, errors, or missing capabilities:
1. Ask them to share feedback in the Neon Console:
- https://console.neon.tech/app/projects?modal=feedback
2. Recommend opening a support conversation in the Neon Discord:
- https://neon.com/discord
## Reset from Parent
Use this when a child branch has drifted and the user wants a clean refresh from the parent branch's latest schema and data.
Link: https://neon.com/docs/guides/reset-from-parent.md
### What it does
- Fully replaces the child branch schema and data with the parent's latest state.
- Does not merge; local changes on the child branch are lost.
- Keeps the same connection details, but active connections are briefly interrupted during reset.
### When to recommend it
- Development or staging branch is too far behind production.
- User wants to start a new feature from a clean parent-aligned state.
- Team wants to refresh staging from production for consistent testing baselines.
### Hard constraints and blockers
- Only child branches can be reset (root branches and schema-only root branches cannot be reset from parent).
- If the target branch has children, reset is blocked until those child branches are removed.
- After a parent branch is restored from snapshot, reset-from-parent may be unavailable for up to 24 hours.
- Reset-from-parent always uses the current parent state; use Instant restore for point-in-time recovery needs.
### CLI usage
```bash
neon branches reset <id|name> --parent --preserve-under-name <backup-branch-name>
```
If project context is not already set, include the project ID:
```bash
neon branches reset <id|name> --parent --preserve-under-name <backup-branch-name> --project-id <project-id>
```
`--preserve-under-name` keeps the pre-reset state as a backup branch for rollback, but adds one extra branch to clean up later.
Optional context setup to avoid repeating `--project-id`:
```bash
neon set-context --project-id <project-id>
```
### Console and API usage
- **Console:** Open the target child branch, then select **Reset from parent** from **Actions**.
- **API:** Use the restore endpoint for the branch and set `source_branch_id` to the parent branch ID.
## Notes and Caveats
- Schema-only branches are for structure-only cloning and sensitive/compliant data controls.
- Schema-only branches are independent root branches (no parent branch and no shared history), so reset-from-parent does not apply.
- For migration testing that depends on real-world row shapes, volumes, and edge cases, prefer normal branches.
- Root branch allowances and per-branch storage limits can cap how many schema-only branches users can create.
- If a user is unsure, default recommendation is:
- **Normal branch** for migration validation.
- **Schema-only branch** for compliance and privacy constraints.
## Useful Workflow Patterns
If the user asks for process recommendations (not just a single command), suggest these:
- **One branch per PR:** Create branch when PR opens, delete when merged/closed, keep migration tests isolated.
- **One branch per test run:** Create branch at pipeline start, run migrations/tests, delete at end for deterministic CI.
- **One branch per developer:** Isolated dev environments with production-like shape; avoid team collisions on shared test data.
- **PII-aware branching:** If production has sensitive data, derive dev/PR branches from an anonymized branch or use schema-only branches.
- **Ephemeral lifecycle hygiene:** Set branch expiration and automate cleanup so old branches do not accumulate avoidable storage/history cost.
### Post-creation environment update prompt
After branch creation, ask whether the user wants to update local environment credentials to point at the new branch.
- Ask: "Do you want me to update your `.env` `DATABASE_URL` to this new branch connection string?"
- If yes, write the new branch connection string to the requested env file/key.
- If no, leave credentials unchanged and share the connection string for manual use.
- Never overwrite an existing env key without explicit confirmation.
## Neon Infrastructure as Code (`neon.ts`)
Beyond creating branches imperatively (CLI / MCP / API above), you can **program what configuration new branches receive** declaratively in `neon.ts` — Neon's infrastructure-as-code file (see the `neon` skill for the full reference). The `branch` property is a function of the branch being evaluated that returns its settings, so every branch born from your project gets a consistent lifecycle and compute profile without per-branch flags.
```bash
npm i @neon/config
```
```typescript
// neon.ts
import { defineConfig } from "@neon/config/v1";
export default defineConfig({
branch: (branch) => {
if (branch.exists) return {}; // never reconcile existing branches
if (branch.isDefault) return { protected: true };
if (branch.name.startsWith("preview/") || branch.name.startsWith("dev")) {
return {
parent: "main",
ttl: "7d", // ephemeral: auto-expire 7 days after creation (max 30d)
postgres: {
computeSettings: {
autoscalingLimitMinCu: 0.25, // scale to zero
autoscalingLimitMaxCu: 1, // keep throwaway branches cheap
suspendTimeout: "5m",
},
},
};
}
return {};
},
});
```
The closure receives a read-only descriptor of the target branch — `name`, `exists`, `isDefault`, `parentId`, and more — and returns the tuning to apply: `parent`, `ttl` (auto-expiry), `protected`, and `postgres.computeSettings`. This is the declarative complement to the **Ephemeral lifecycle hygiene** and per-PR / per-test patterns above: instead of remembering `--expires-at` on every `neon branches create`, the TTL and compute profile live in version control and apply to every matching branch.
Because `neon checkout` applies this policy when it **creates** a branch, a fresh `preview/*` or `dev-*` branch comes up already expiring and scaled-to-zero. Checking out an _existing_ branch doesn't reconcile it — run `neon deploy` (alias for `neon config apply`) to apply changes to a branch that already exists.
## Branching in CI/CD
Common CI/CD use cases for Neon branches:
- **Per-PR preview deployments:** Branch on PR open, deploy the preview against it, delete on close. Each PR gets an isolated database branch. Injecting the branch's `DATABASE_URL` into the deployed app is hosting-provider-specific — see [preview-branches-with-cloudflare](https://github.com/neondatabase/preview-branches-with-cloudflare), [preview-branches-with-vercel](https://github.com/neondatabase/preview-branches-with-vercel), or [preview-branches-with-fly](https://github.com/neondatabase/preview-branches-with-fly) for tested patterns.
- **Migration testing in CI:** Run risky schema changes against a branch with production-like data before merge.
- **Schema diff visibility:** Use the [schema-diff GitHub Action](https://github.com/marketplace/actions/neon-schema-diff-github-action) to auto-comment a DB-layer diff on the PR.
## Examples
### Example 1: Migration testing with realistic data
**User input:** "I need to test a risky migration against production-like data."
**Agent output shape:**
1. Recommend a normal branch and explain why.
2. Share docs link: https://neon.com/docs/introduction/branching
3. Check the tool path first (CLI with `neon --version`; MCP only if the CLI isn't usable).
4. Provide commands:
- `neon branches create --name migration-test --parent main --expires-at 2026-12-15T18:02:16Z`
- `neon connection-string migration-test`
### Example 2: Sensitive data development workflow
**User input:** "We cannot copy production data because of compliance."
**Agent output shape:**
1. Recommend schema-only branch and explain why.
2. Share docs link: https://neon.com/docs/guides/branching-schema-only
3. Check the tool path first (CLI with `neon --version`; MCP only if the CLI isn't usable).
4. Provide command:
- `neon branches create --name compliance-dev --parent main --schema-only --project-id <your-project-id> --expires-at 2026-12-15T18:02:16Z`
5. Mention Beta support path:
- https://console.neon.tech/app/projects?modal=feedback
- https://neon.com/discord
## Further Reading
- https://neon.com/docs/guides/branch-expiration.md
- https://neon.com/docs/guides/neon-github-integration.md
- https://neon.com/docs/ai/neon-mcp-server.md
- https://neon.com/branching
references/egress-optimization.md
# Postgres Egress Optimizer
Guide the user through diagnosing and fixing application-side query patterns that cause excessive data transfer (egress) from their Postgres database. Most high egress bills come from the application fetching more data than it uses.
Work the four steps in order: **diagnose** which queries transfer the most data, **analyze** the codebase behind them, **fix** the anti-patterns, then **verify** nothing broke and the transfer actually dropped.
## Step 1: Diagnose
Identify which queries transfer the most data. The primary tool is the `pg_stat_statements` extension.
### Check if pg_stat_statements is available
```sql
SELECT 1 FROM pg_stat_statements LIMIT 1;
```
If this errors, the extension needs to be created:
```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```
On Neon the extension is available by default, but it may still need this CREATE EXTENSION step. Ask before creating it because installing an extension modifies the database.
### Handle empty stats
Stats are cleared when a Neon compute scales to zero and restarts. If the stats are empty or the compute recently woke up:
1. Ask before resetting the stats, then start a clean measurement window with `SELECT pg_stat_statements_reset();`
2. Let the application run under representative traffic for at least an hour.
3. Return and run the diagnostic queries below.
If the user has stats from a production database, use those. If they have no access to production stats, proceed to Step 2 and analyze the codebase directly — code-level patterns are often sufficient to identify the worst offenders.
### Diagnostic queries
Run these to identify the top egress contributors. Focus on queries that return many rows, return wide rows (JSONB, TEXT, BYTEA columns), or are called very frequently.
**Queries returning the most total rows:**
```sql
SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
FROM pg_stat_statements
WHERE calls > 0
ORDER BY rows DESC
LIMIT 10;
```
**Queries returning the most rows per execution** (poorly scoped SELECTs, missing pagination):
```sql
SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
FROM pg_stat_statements
WHERE calls > 0
ORDER BY avg_rows_per_call DESC
LIMIT 10;
```
**Most frequently called queries** (candidates for caching):
```sql
SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
FROM pg_stat_statements
WHERE calls > 0
ORDER BY calls DESC
LIMIT 10;
```
**Longest running queries** (not a direct egress measure, but helps identify problem queries during a spike):
```sql
SELECT query, calls, rows AS total_rows,
round(total_exec_time::numeric, 2) AS total_exec_time_ms
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_exec_time DESC
LIMIT 10;
```
### Interpret the results
Rank findings by estimated egress impact:
- **High row count + wide rows** = biggest egress. A query returning 1,000 rows where each row includes a 50KB JSONB column transfers ~50MB per call.
- **Extreme call frequency** on even small queries adds up. A query called 50,000 times/day returning 10 rows each = 500,000 rows/day.
- **Cross-reference with the schema** to identify which columns are wide. Look for JSONB, TEXT, BYTEA, and large VARCHAR columns.
## Step 2: Analyze the Codebase
For each query identified in Step 1, or for each database query in the codebase if no stats are available, check:
- Does it select only the columns the response needs?
- Does it return a bounded number of rows (LIMIT/pagination)?
- Is it called frequently enough to benefit from caching?
- Does it fetch raw data that gets aggregated in application code?
- Does it use a JOIN that duplicates parent data across child rows?
## Step 3: Fix
Apply the appropriate fix for each problem found. Below are the most common egress anti-patterns and how to fix them.
### Unused columns (SELECT \*)
**Problem:** The query fetches all columns but the application only uses a few. Large columns (JSONB blobs, TEXT fields) get transferred over the wire and discarded.
**Fix:** Name only the columns the response needs.
**Before:**
```sql
SELECT * FROM products;
```
**After:**
```sql
SELECT id, name, price, image_urls FROM products;
```
### Missing pagination
**Problem:** A list endpoint returns all rows with no LIMIT. This is an unbounded egress risk — every new row in the table increases data transfer on every request. Flag this regardless of current table size.
This is easy to miss because the application may work fine with small datasets. But at scale, an unpaginated endpoint returning 10,000 rows with even moderate column widths can transfer hundreds of megabytes per day.
**Fix:** Bound the result set with `ORDER BY` plus `LIMIT`/`OFFSET`.
**Before:**
```sql
SELECT id, name, price FROM products;
```
**After:**
```sql
SELECT id, name, price FROM products
ORDER BY id
LIMIT 50 OFFSET 0;
```
When adding pagination, check whether the consuming client already supports paginated responses. If not, pick sensible defaults and document the pagination parameters in the API.
### High-frequency queries on static data
**Problem:** A query is called thousands of times per day but returns data that rarely changes. Every call transfers the same rows from the database. This pattern is only visible from `pg_stat_statements` — the code itself looks normal.
Look for queries with extremely high call counts relative to other queries. Common examples: configuration tables, category lists, feature flags, user role definitions.
**Fix:** Add a caching layer between the application and the database so it avoids hitting the database on every request. Reuse a cache the app already has. Otherwise, a serverless Redis such as [Upstash Redis](https://upstash.com/docs/redis) works from serverless and edge runtimes without connection management.
### Application-side aggregation
**Problem:** The application fetches all rows from a table and then computes aggregates (averages, counts, sums, groupings) in application code. The full dataset transfers over the wire even though the result is a small summary.
**Fix:** Push the aggregation into SQL.
**Before:** The application fetches entire tables and aggregates in code with loops or `.reduce()`.
**After:**
```sql
SELECT p.category_id,
AVG(r.rating) AS avg_rating,
COUNT(r.id) AS review_count
FROM reviews r
INNER JOIN products p ON r.product_id = p.id
GROUP BY p.category_id;
```
### JOIN duplication
**Problem:** A JOIN between a wide parent table and a child table duplicates all parent columns across every child row. If a product has 200 reviews and the product row includes a 50KB JSONB column, the join sends that 50KB × 200 = ~10MB for a single request.
This is distinct from the SELECT \* problem. Even if you select only needed columns, a JOIN still repeats the parent data for every child row. The fix is structural: avoid the join entirely.
**Fix:** Split the join into two queries, one per table.
**Before:**
```sql
SELECT * FROM products
LEFT JOIN reviews ON reviews.product_id = products.id
WHERE products.id = 1;
```
**After (two separate queries):**
```sql
SELECT id, name, price, description, image_urls FROM products WHERE id = 1;
SELECT id, user_name, rating, body FROM reviews WHERE product_id = 1;
```
Two queries instead of one JOIN. The product data is fetched once. The reviews are fetched once. No duplication.
## Step 4: Verify
After applying fixes:
1. **Run existing tests** to confirm nothing broke.
2. **Check the responses** — make sure the API still returns the same data shape. Column selection and pagination changes can break clients that depend on specific fields or full result sets.
3. **Measure the improvement** — if pg_stat_statements data is available, ask before resetting it (`SELECT pg_stat_statements_reset();`), let traffic run, then re-run the diagnostic queries to compare before and after.
## Neon Infrastructure as Code (`neon.ts`)
The fixes above cut **egress** (data transferred out of Postgres). The other big non-prod cost lever is **compute**, and you can codify it durably in `neon.ts` — Neon's infrastructure-as-code file (see the `neon` skill for the full reference) — so dev, preview, and CI branches stay cheap by default instead of relying on per-branch flags:
```bash
npm i @neon/config
```
```typescript
// neon.ts
import { defineConfig } from "@neon/config/v1";
export default defineConfig({
branch: (branch) => {
if (branch.exists || branch.isDefault) return {}; // don't touch prod
return {
ttl: "7d", // ephemeral branches auto-expire instead of accruing storage
postgres: {
computeSettings: {
autoscalingLimitMinCu: 0.25, // scale to zero when idle
autoscalingLimitMaxCu: 1, // cap autoscaling on throwaway branches
suspendTimeout: "5m",
},
},
};
},
});
```
```bash
neon config apply # apply to the current branch (neon deploy is an alias)
```
This is complementary, not a substitute: query-pattern fixes are what actually reduce egress charges, while these settings keep non-production compute and storage from quietly inflating the same bill. Because `neon checkout` applies the policy when it creates a branch, new dev/preview branches inherit the cheap profile automatically.
## Further Reading
- https://neon.com/docs/introduction/network-transfer.md
- https://neon.com/docs/introduction/cost-optimization.md
references/full-text-search.md
# Full-Text Search with BM25 Ranking
Use `lakebase_text` for BM25 relevance ranking with PostgreSQL's standard `tsvector` type. The `lakebase_bm25` index adds corpus-aware ranking and top-K pushdown.
Lakebase Search requires Postgres 16 or later. Enable the extension before creating the index:
```sql
CREATE EXTENSION IF NOT EXISTS lakebase_text;
```
`lakebase_text` has no extension dependency. It relies on a preloaded library that Neon enables by default; if the project customized its preloaded-library list, confirm the library remains enabled.
## Prepare and Index Text
Prefer a stored generated `tsvector` when search text comes from stable table columns:
```sql
CREATE TABLE documents (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
body_tsv tsvector GENERATED ALWAYS AS
(to_tsvector('english', body)) STORED
);
```
Create the index after the initial corpus has been inserted so build-time corpus statistics are meaningful. BM25 scoring is tuned by two storage parameters set at index-build time:
- `k1` controls term-frequency saturation (default `1.2`, range `1.2`–`2.0`): higher values let repeated terms keep adding relevance.
- `b` controls document-length normalization (default `0.75`, range `0.0`–`1.0`): higher values penalize longer documents more.
Both can only be set in the `WITH` clause, and updating them rebuilds the index:
```sql
CREATE INDEX documents_body_bm25 ON documents
USING lakebase_bm25 (body_tsv)
WITH (k1 = 1.2, b = 0.75);
```
After a large bulk load, run `VACUUM` to refresh the statistics used by BM25 scoring.
## Query and Interpret Scores
`to_bm25query` binds the query `tsvector` to the BM25 index whose corpus statistics should be used. The `<@>` operator returns a negative BM25 score, so lower (more negative) values are more relevant and must sort ascending:
```sql
SELECT id, title, score
FROM (
SELECT
id,
title,
body_tsv <@> to_bm25query(
to_tsvector('english', $1),
'documents_body_bm25'::regclass
) AS score
FROM documents
) AS keyword_scored
WHERE score < 0
ORDER BY score
LIMIT $2;
```
Scalar BM25 scoring returns zero for documents containing none of the query terms. Filter `score < 0` before `ORDER BY` and `LIMIT` so keyword results exclude non-matches under both sequential and index plans. Partial query-term matches remain eligible.
Use the same text-search configuration for document and query vectors. Select a language-specific or custom configuration that matches the corpus.
## Set the Candidate Limit
`lakebase_bm25.default_limit` controls how many rows the index returns before PostgreSQL applies the SQL `LIMIT`. Its default is `1000`; setting it close to the requested top-K avoids unnecessary scoring.
## Use Prefilter Selectively
Enable prefilter when a `WHERE` condition is strict or unpredictable and cheap to evaluate. It lets the index prune rows before BM25 scoring. A loose or expensive filter can be slower with prefilter enabled.
```sql
BEGIN;
SET LOCAL lakebase_bm25.default_limit = 20;
SET LOCAL lakebase_bm25.prefilter = on;
SELECT id, title, score
FROM (
SELECT
id,
title,
body_tsv <@> to_bm25query(
to_tsvector('english', $1),
'documents_body_bm25'::regclass
) AS score
FROM documents
WHERE id % 1000 = 0
) AS keyword_scored
WHERE score < 0
ORDER BY score
LIMIT $2;
COMMIT;
```
## Set Parameters at Build Time or Per Query
Several BM25 parameters can be set in two places. As an index storage parameter in the `CREATE INDEX` `WITH` clause, a value is fed into the index as its build-time default. As a session GUC via `SET` (or `SET LOCAL`), it applies per query and takes precedence over the stored default when both are present.
- `default_limit` and `prefilter` exist in both forms: set an index default that fits the common case, then override it per query with a GUC without rebuilding.
- `k1` (default `1.2`) and `b` (default `0.75`) are storage parameters only. There is no GUC for them.
- `enable_scan` (default `on`) is a GUC only.
The examples use `SET LOCAL` so each override is scoped to its own transaction, which is required behind a connection pool or stateless driver.
Source: [`lakebase_text` documentation](https://neon.com/docs/extensions/lakebase-text).
references/hybrid-search.md
# Hybrid Search
Use hybrid search when either semantic similarity or exact vocabulary can identify a relevant document. Run vector and full-text search separately, then combine their results with fusion or reranking.
Lakebase Search requires Postgres 16 or later. Hybrid search uses both extensions:
```sql
CREATE EXTENSION IF NOT EXISTS lakebase_vector CASCADE;
CREATE EXTENSION IF NOT EXISTS lakebase_text;
```
`lakebase_vector` installs `pgvector` through `CASCADE`; `lakebase_text` has no extension dependency. Both rely on preloaded libraries that Neon enables by default. If the project customized its preloaded-library list, confirm both libraries remain enabled.
Prepare a table with both vector and text-search columns:
```sql
CREATE TABLE documents (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
embedding vector(1024),
body_tsv tsvector GENERATED ALWAYS AS
(to_tsvector('english', body)) STORED
);
```
The 1024-dimensional column is an example. Set it to the output dimension of your selected embedding model. Generate document embeddings in batches and embed each query with the same model and dimensions; [Vector search](vector-search.md#prepare-embeddings) shows the AI Gateway calls. Follow any model-specific query instructions, while keeping the original query text for BM25. Choose a PostgreSQL text-search configuration appropriate for the corpus.
Create and validate each retriever independently before combining them. Follow [Vector search](vector-search.md) and [Full-text search](full-text-search.md) for their indexes, query operators, and tuning.
Choose how to order the combined results:
- **Jev reranking:** retrieve vector and full-text search candidates, deduplicate by document ID, score each candidate with Jev, and take the final top results.
- **Reciprocal Rank Fusion (RRF):** fuse the retrievers' ranks instead of their incomparable vector distances and BM25 scores, with no additional model call.
Jev can score the deduplicated candidate pool directly. RRF is optional when you want to prioritize or trim a larger pool before reranking, or provide a model-free fallback. Measure relevance and added latency on your corpus to choose a path.
## Rerank with Jev
Retrieve up to 40 candidates per retriever and deduplicate with `UNION`. Bind the query embedding as `$1` and the original query text as `$2`:
```sql
WITH vector_candidates AS (
SELECT id
FROM documents
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1::vector
LIMIT 40
),
keyword_candidates AS (
SELECT id
FROM (
SELECT
id,
body_tsv <@> to_bm25query(
to_tsvector('english', $2),
'documents_body_bm25'::regclass
) AS score
FROM documents
) AS keyword_scored
WHERE score < 0
ORDER BY score, id
LIMIT 40
),
candidate_ids AS (
SELECT id FROM vector_candidates
UNION
SELECT id FROM keyword_candidates
)
SELECT d.id, d.title, d.body
FROM candidate_ids AS c
JOIN documents AS d ON d.id = c.id
ORDER BY d.id;
```
Apply the same access-control and tenant filters inside both retrievers, before their limits. Take the final top results only after Jev scoring.
Batch yes/no relevance questions with the query shared in `state` and each candidate in its own question's `instructions`. Sort the returned probabilities in descending order; equal probabilities keep the input order.
Use Neon's provider with the Vercel AI SDK. Requirements:
- Install the tested versions: `npm install @neon/ai-sdk-provider@1.1.0 ai@7.0.133`. Decision models require AI SDK 7.0.133+; pin versions and retest upgrades while `experimental_decide` is experimental.
- Load the same `NEON_AI_GATEWAY_TOKEN` and `NEON_AI_GATEWAY_BASE_URL` used for [embeddings](vector-search.md#prepare-embeddings); the provider handles the endpoint and credentials.
- The AI Gateway, for both Jev and embeddings, requires prepaid AI Gateway credits, available on every Neon plan, including Free. Without them, embed documents and queries with one model from another provider and use [RRF](#rrf-example).
```typescript
import { neon } from "@neon/ai-sdk-provider";
import { experimental_decide as decide } from "ai";
type Candidate = { id: string; title: string; body: string };
async function rerankWithJev(
queryText: string,
candidates: Candidate[], // deduplicated, authorized rows in a stable order
limit = 10,
batchSize = 80,
): Promise<Candidate[]> {
if (!Number.isInteger(limit) || limit < 1) {
throw new Error("limit must be a positive integer");
}
if (!Number.isInteger(batchSize) || batchSize < 1) {
throw new Error("batchSize must be a positive integer");
}
if (candidates.length === 0) return [];
const model = neon.decisionModel("typesafeai-jev-1-14-0");
const criteria = {
true: "The candidate states the specific facts or steps the query asks for.",
false:
"The candidate only shares keywords or a topic with the query, answers a different question, or lacks the requested information.",
};
const abortSignal = AbortSignal.timeout(10_000); // whole reranking budget
const scored: { doc: Candidate; index: number; probability: number }[] = [];
for (let start = 0; start < candidates.length; start += batchSize) {
const batch = candidates.slice(start, start + batchSize);
const { answers } = await decide({
model,
state: { query: queryText }, // original text, without embedding instructions
questions: Object.fromEntries(
batch.map((doc, offset) => [
`d${start + offset}`, // response key only; the candidate goes in instructions
{
type: "boolean" as const,
instructions: {
question:
"Given the search query in state.query, does this candidate passage supply the specific information the user is looking for?",
candidate: { title: doc.title, body: doc.body },
},
criteria,
},
]),
),
maxRetries: 0, // propagate errors to the caller without retrying
abortSignal,
});
for (const [offset, doc] of batch.entries()) {
const index = start + offset;
const answer = answers[`d${index}`];
if (answer.type !== "boolean") {
throw new Error(`Missing relevance probability for d${index}`);
}
scored.push({ doc, index, probability: answer.probability });
}
}
return scored
.sort((a, b) => b.probability - a.probability || a.index - b.index)
.slice(0, limit)
.map(({ doc }) => doc);
}
```
The SDK's `boolean` answer exposes the probability of yes as `answer.probability` (TypeSafe calls it `noul`). This is a probability that the passage answers the query, not a degree of completeness. Calibrate a threshold on judged no-match queries if the application must return "no answer."
`batchSize = 80` matches the maximum candidate pool above. Each request must fit Jev 1.14's 65,536-token context window, including all candidate text and questions; reduce the batch size for longer passages. Tune the shared timeout to the request's latency budget.
The caller can catch reranking errors and use [RRF](#rrf-example) as a fallback.
## RRF Example
Use this query on its own when you want hybrid search without an additional model call.
For rank `r` and constant `k`, each retriever contributes `1 / (k + r)`. Start with a target of 40 candidates per retriever and `k = 60`; vector cutoff ties can exceed that target. Tune both for the corpus and workload.
Embed the query text with the same embedding model used for the stored documents, then bind that vector as `$1`, the original query text as `$2`, and the final result count as `$3`:
```sql
WITH vector_ranked AS (
SELECT id, RANK() OVER (ORDER BY distance) AS rank
FROM (
SELECT id, embedding <=> $1::vector AS distance
FROM documents
WHERE embedding IS NOT NULL
ORDER BY distance
FETCH FIRST 40 ROWS WITH TIES
) AS vector_candidates
),
keyword_ranked AS (
SELECT id, RANK() OVER (ORDER BY score) AS rank
FROM (
SELECT id, score
FROM (
SELECT
id,
body_tsv <@> to_bm25query(
to_tsvector('english', $2),
'documents_body_bm25'::regclass
) AS score
FROM documents
) AS keyword_scored
WHERE score < 0
ORDER BY score, id
LIMIT 40
) AS keyword_candidates
)
SELECT
d.id,
d.title,
COALESCE(1.0 / (60 + v.rank), 0) +
COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score
FROM documents AS d
LEFT JOIN vector_ranked AS v ON v.id = d.id
LEFT JOIN keyword_ranked AS k ON k.id = d.id
WHERE v.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY rrf_score DESC, d.id
LIMIT $3;
```
`score < 0` excludes BM25 non-matches. Many matching documents can share a BM25 score, so `WITH TIES` could admit a large group at the cutoff. `ORDER BY score, id LIMIT 40` keeps keyword candidates bounded and breaks boundary ties by ID. `RANK()` uses score alone so retained ties receive equal ranks.
Vector retrieval preserves cutoff ties and can exceed 40 candidates. `lakebase_bm25.default_limit` defaults to `1000`; the SQL limit bounds keyword rows entering fusion, not total scoring work.
## Adapt the Hybrid Search
- Retrieve more candidates from each source than the final result count; otherwise one retriever can dominate before fusion has enough overlap. Keep `lakebase_bm25.default_limit` at least as large as the BM25 candidate target.
- Keep each retriever's operator and index configuration correct independently before tuning fusion or reranking.
- Tune candidate counts and the RRF constant with judged or behavioral relevance data, plus latency measurements.
- Compare vector-only, BM25-only, RRF-only, the deduplicated union plus Jev, and optional RRF plus Jev on the same judged queries to isolate each stage's contribution. Measure recall before reranking and nDCG@10 or MRR after it, alongside p95 latency, input-token cost, and fallback rate.
- Add weights only when product evidence shows one retriever should contribute more. Weight the reciprocal-rank contributions, not the raw vector distance and negative BM25 score.
- Apply the same access-control and tenant filters to both candidate CTEs. If BM25 filters are strict and cheap, evaluate whether `lakebase_bm25.prefilter` improves the filtered query.
Sources: [Lakebase Search get-started guide][lakebase-search-guide] and [TypeSafe reranking cookbook](https://docs.typesafe.ai/cookbooks/rerank_typesafe).
[lakebase-search-guide]: https://neon.com/docs/ai/lakebase-search-get-started#combine-results-with-hybrid-search
references/lakebase-search-drizzle.md
# Managing Lakebase Search with Drizzle
When the user wants Lakebase Search managed through Drizzle, treat the SQL in [Vector Search](vector-search.md), [Full-Text Search](full-text-search.md), and [Hybrid Search](hybrid-search.md) as the source of truth and apply it as below. Use Drizzle for all schema and migration management unless the user says otherwise.
Requires `drizzle-orm` 0.36+ and `drizzle-kit` 0.27+: the schema below returns its indexes as an array from the `pgTable` extra-config callback. Those versions also include generated-column support for the `tsvector` column, the custom-method `.using(...).op(...)` index API for `lakebase_ann`, the `vector` column type, and the `cosineDistance` helper.
Contents:
- [Config](#config): `drizzle.config.ts` and the migration connection
- [Extensions](#extensions): custom migration required to create extensions (Drizzle can't)
- [Schema](#schema): columns, generated `tsvector`, and the ANN index
- [BM25 Index](#bm25-index): created after the corpus is seeded
- [Generate and Store Embeddings](#generate-and-store-embeddings): call the AI Gateway and insert vectors with Drizzle
- [Query](#query): vector, BM25, and hybrid reads
- [Tune Per Query](#tune-per-query): per-query GUCs
Rules:
- Express everything Drizzle can in `schema.ts`: the columns, the generated `tsvector`, and the `lakebase_ann` index. Only `CREATE EXTENSION` and the post-seed `lakebase_bm25` index need custom migrations.
- Use `drizzle-kit generate` then `migrate`. Never run `drizzle-kit push` (it reconciles the database to `schema.ts`, so it drops the post-seed `lakebase_bm25` index and any other object not declared there)
- Run every migration over the direct (unpooled) connection.
- The extension must exist before the `vector` column and the `lakebase_ann` index that depend on it.
## Config
`drizzle-kit generate` and `migrate` read `drizzle.config.ts`. If the connection string is in `.env`, install `dotenv` (`npm install dotenv`) and load it here. Point `dbCredentials.url` at the **direct (unpooled)** connection string:
```typescript
// drizzle.config.ts
import "dotenv/config";
import { defineConfig } from "drizzle-kit";
const databaseUrl = process.env.DATABASE_URL_UNPOOLED;
if (!databaseUrl) throw new Error("Missing DATABASE_URL_UNPOOLED");
export default defineConfig({
schema: "./src/schema.ts",
out: "./drizzle",
dialect: "postgresql",
// Direct (unpooled) URL. Neon exposes it as DATABASE_URL_UNPOOLED.
dbCredentials: { url: databaseUrl },
});
```
## Extensions
Drizzle cannot express `CREATE EXTENSION`, and the `vector` column and `lakebase_ann` index below depend on `lakebase_vector`, so generate a custom migration for the extensions **first** so it runs before the schema migration:
```bash
npx drizzle-kit generate --custom --name=lakebase_extensions
```
```sql
-- drizzle/0000_lakebase_extensions.sql
CREATE EXTENSION IF NOT EXISTS lakebase_vector CASCADE;
CREATE EXTENSION IF NOT EXISTS lakebase_text;
```
## Schema
The columns, the generated `tsvector`, and the `lakebase_ann` index all go in `schema.ts`. `tsvector` has no built-in Drizzle type, so define it using the `customType`:
```typescript
// src/schema.ts
import {
pgTable,
bigint,
text,
vector,
index,
customType,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
const tsvector = customType<{ data: string }>({
dataType() {
return "tsvector";
},
});
export const documents = pgTable(
"documents",
{
id: bigint("id", { mode: "number" })
.generatedByDefaultAsIdentity()
.primaryKey(),
title: text("title").notNull(),
body: text("body").notNull(),
embedding: vector("embedding", { dimensions: 1024 }),
bodyTsv: tsvector("body_tsv").generatedAlwaysAs(
sql`to_tsvector('english', "body")`,
),
},
(table) => [
index("documents_embedding_ann").using(
"lakebase_ann",
table.embedding.op("vector_cosine_ops"),
),
],
);
```
The 1024-dimensional column is an example; set it to the output dimension of your selected embedding model. Use the same model and dimensions for stored documents and queries. Postgres maintains `body_tsv`, so never write it from the app. Generate and apply the migration after the extensions migration above:
```bash
npx drizzle-kit generate --name=lakebase_search
npx drizzle-kit migrate
```
## BM25 Index
Keep the `lakebase_bm25` index out of `schema.ts`. It must be built only after the initial corpus is loaded, so its build-time statistics are meaningful (see [Full-text search](full-text-search.md)) — a schema migration would build it against an empty table. Add it in a later custom migration that runs after seeding:
```bash
npx drizzle-kit generate --custom --name=bm25_index
```
```sql
-- drizzle/NNNN_bm25_index.sql, applied after the corpus is seeded
CREATE INDEX documents_body_bm25 ON documents USING lakebase_bm25 (body_tsv);
```
## Generate and Store Embeddings
Use the [Vercel AI SDK](https://ai-sdk.dev) (`ai`, 6+) with Neon's `@neon/ai-sdk-provider` 1.0.0+. Follow the [gateway setup](vector-search.md#prepare-embeddings), then install the packages with `npm install @neon/ai-sdk-provider ai dotenv`:
```typescript
import "dotenv/config";
import { neon } from "@neon/ai-sdk-provider";
import { embed, embedMany } from "ai";
const embeddingModel = neon.embeddingModel("qwen3-embedding-0-6b");
const providerOptions = { neon: { dimensions: 1024 } };
// Qwen3 query prompt adapted for Neon documentation search; documents stay unprefixed.
const queryPrompt =
"Instruct: Given a question about Neon, retrieve documentation passages that answer the question\nQuery: ";
```
Generate a document vector, then insert it with Drizzle:
```typescript
const { embedding } = await embed({
model: embeddingModel,
value: body,
providerOptions,
});
await db.insert(documents).values({
title,
body,
embedding,
});
```
For bulk imports, skip blank rows and embed/insert one batch at a time:
```typescript
const batchSize = 64;
const nonBlankRows = rows.filter((row) => row && row.body.trim().length > 0);
for (let offset = 0; offset < nonBlankRows.length; offset += batchSize) {
const batch = nonBlankRows.slice(offset, offset + batchSize);
const { embeddings: vectors } = await embedMany({
model: embeddingModel,
values: batch.map((row) => row.body),
providerOptions,
maxParallelCalls: 1,
});
await db.insert(documents).values(
batch.map((row, index) => {
const embedding = vectors[index];
if (!embedding)
throw new Error(`Missing embedding for batch row ${index}`);
return { ...row, embedding };
}),
);
}
```
`embedMany` splits embedding requests using the provider's per-request input limit. This loop also bounds database inserts and memory; tune `batchSize` for your workload. Chunk long documents before embedding. Earlier batches stay committed if a later batch fails.
Apply `queryPrompt` only to queries, keeping the original text for BM25. Use the same model and dimensions for documents and queries; re-embed documents when switching models.
## Query
Use the query builder with Drizzle's `cosineDistance` helper for vector search. It emits the `<=>` operator, so keep the index on `vector_cosine_ops`:
```typescript
import { cosineDistance } from "drizzle-orm";
import { documents } from "./schema";
const { embedding: queryEmbedding } = await embed({
model: embeddingModel,
value: `${queryPrompt}${queryText}`,
providerOptions,
});
const distance = cosineDistance(documents.embedding, queryEmbedding);
const rows = await db
.select({ id: documents.id, title: documents.title, distance })
.from(documents)
.orderBy(distance)
.limit(k);
```
BM25 has no Drizzle helper: `<@>` and `to_bm25query` require raw SQL. Reference the generated column by its `body_tsv` name. Bind user input as parameters through the `sql` template:
```typescript
import { sql } from "drizzle-orm";
const rows = await db.execute(sql`
SELECT id, title, score
FROM (
SELECT id, title,
body_tsv <@> to_bm25query(
to_tsvector('english', ${queryText}),
'documents_body_bm25'::regclass
) AS score
FROM documents
) AS keyword_scored
WHERE score < 0
ORDER BY score
LIMIT ${k}
`);
```
Filter `score < 0` before the candidate cutoff: scalar BM25 scoring returns zero for non-matches, which must not become keyword hits. Partial query-term matches remain eligible.
Run either [hybrid search](hybrid-search.md) SQL query the same way: raw SQL through `db.execute`. For reranking with AI Gateway decision models, retrieve the deduplicated union of vector and full-text search candidates, including document bodies, and pass those rows directly to `rerankWithJev` before the final result cutoff. Use the RRF query as the alternative that needs no additional model call; RRF is optional before model-based reranking.
## Tune Per Query
Per-query GUCs (`lakebase_ann.probes`, `lakebase_ann.epsilon`, `lakebase_bm25.default_limit`, `lakebase_bm25.prefilter`) must be set with `SET LOCAL` inside a transaction so they apply to the same pooled connection as the query:
```typescript
import { cosineDistance, sql } from "drizzle-orm";
import { documents } from "./schema";
const distance = cosineDistance(documents.embedding, queryEmbedding);
const rows = await db.transaction(async (tx) => {
// SET LOCAL scopes the GUC to this transaction's connection; do not hoist it out.
// Keep probes at 'auto' unless an IVF `lists` layout exists: a numeric value must
// match the `lists` shape or it errors ("need 0 probes ..."). See vector-search.md.
await tx.execute(sql`SET LOCAL lakebase_ann.probes = 'auto'`);
return tx
.select({ id: documents.id, title: documents.title, distance })
.from(documents)
.orderBy(distance)
.limit(k);
});
```
Sources:
- [AI Gateway embeddings](https://neon.com/docs/ai-gateway/embeddings)
- [Get started with Lakebase Search](https://neon.com/docs/ai/lakebase-search-get-started)
- [Schema migration with Lakebase Postgres and Drizzle ORM](https://neon.com/docs/guides/drizzle-migrations)
references/vector-search.md
# Semantic Vector Search
Use `lakebase_vector` for approximate nearest-neighbor retrieval over embeddings. It retains pgvector's vector types, distance operators, and query syntax; the index access method is `lakebase_ann`.
## Contents
- [Create the extension](#create-the-extension) — enable `lakebase_vector` and its `pgvector` dependency
- [Prepare embeddings](#prepare-embeddings) — generate vectors with the AI Gateway and match the column dimension
- [Build the index](#build-the-index) — match the distance metric, operator class, and query operator
- [Tune the index](#tune-the-index) — configure index-build options and concurrent index management
- [Query](#query) — rank by vector distance or filter by a similarity radius
- [Tune search](#tune-search) — inspect the index and tune recall against latency
- [Use prefilter selectively](#use-prefilter-selectively) — apply selective filters before ANN scoring
## Create the Extension
Lakebase Search requires Postgres 16 or later. Enable the extension before creating vector columns or indexes:
```sql
CREATE EXTENSION IF NOT EXISTS lakebase_vector CASCADE;
```
`lakebase_vector` installs `pgvector` through `CASCADE`. It relies on a preloaded library that Neon enables by default; if the project customized its preloaded-library list, confirm the library remains enabled.
## Prepare Embeddings
Neon's [AI Gateway embeddings endpoint](https://neon.com/docs/ai-gateway/embeddings) is an OpenAI-compatible way to generate vectors for Lakebase Search. This table uses 1024 dimensions as an example; set the column dimension to the output of the model and any `dimensions` parameter you choose:
```sql
CREATE TABLE documents (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
embedding vector(1024)
);
```
The AI Gateway is available on every Neon plan, including Free, and draws down prepaid AI Gateway credits. If a model shows `enabled: false` in `GET /v1/models`, the branch's AI Gateway page in the Neon Console lists the reason and the fix. See the `neon-ai-gateway` skill.
Enable the AI Gateway on the branch and get `NEON_AI_GATEWAY_TOKEN` and the bare-host `NEON_AI_GATEWAY_BASE_URL`:
- With `neon.ts`, declare `aiGateway: true`, run `neon deploy`, then run `neon env pull --file .env`.
- Without `neon.ts`, run `neon env pull --service ai-gateway --file .env` from a directory linked to the intended project and branch. This requests the gateway variables explicitly and skips other services.
- Alternatively, get both values from the Neon Console. Neon Functions receive them automatically.
### AI SDK (Recommended)
For TypeScript apps, use Neon's `@neon/ai-sdk-provider` 1.0.0+ with the [Vercel AI SDK](https://ai-sdk.dev) (`ai`, 6+) (`npm install @neon/ai-sdk-provider ai dotenv`). The provider reads `NEON_AI_GATEWAY_TOKEN` and `NEON_AI_GATEWAY_BASE_URL` directly and sends embedding requests to `/v1/embeddings`. Load `.env` in a standalone app; Neon Functions inject the variables automatically.
```typescript
import "dotenv/config";
import { neon } from "@neon/ai-sdk-provider";
import { embed, embedMany } from "ai";
const embeddingModel = neon.embeddingModel("qwen3-embedding-0-6b");
const providerOptions = { neon: { dimensions: 1024 } };
// Qwen3 query prompt adapted for Neon documentation search; documents stay unprefixed.
const queryPrompt =
"Instruct: Given a question about Neon, retrieve documentation passages that answer the question\nQuery: ";
const { embeddings: documentEmbeddings } = await embedMany({
model: embeddingModel,
values: documentBodies, // non-empty document batch; store vectors by input index
providerOptions,
maxParallelCalls: 1,
});
const { embedding: queryEmbedding } = await embed({
model: embeddingModel,
value: `${queryPrompt}${queryText}`,
providerOptions,
}); // bind to $1 below
```
Skip empty imports before calling `embedMany`. It returns vectors in input order and splits requests according to the provider's item limit. For large imports, also split document text within the endpoint's token limits and keep database writes bounded.
For Neon documentation search, the example adapts the task instruction in Qwen3's stored `query` prompt from [config_sentence_transformers.json](https://huggingface.co/Qwen/Qwen3-Embedding-0.6B/blob/main/config_sentence_transformers.json). Its `document` prompt is empty, so document text stays unprefixed. Neither SDK applies SentenceTransformers prompts automatically. Keep the original `queryText` for BM25, and use the selected model's own prompt conventions if you switch models.
### OpenAI SDK Alternative
For an existing OpenAI SDK integration, install `openai` and `dotenv` (`npm install openai dotenv`) and configure the client explicitly:
```typescript
import "dotenv/config";
import OpenAI from "openai";
const apiKey = process.env.NEON_AI_GATEWAY_TOKEN;
const gatewayBaseUrl = process.env.NEON_AI_GATEWAY_BASE_URL;
if (!apiKey || !gatewayBaseUrl) {
throw new Error("Missing Neon AI Gateway credentials");
}
const client = new OpenAI({
apiKey,
baseURL: `${gatewayBaseUrl}/v1`,
});
async function embedBatch(inputs: string[]): Promise<number[][]> {
if (inputs.length === 0) return [];
const response = await client.embeddings.create({
model: "qwen3-embedding-0-6b",
input: inputs,
dimensions: 1024,
encoding_format: "float",
});
const vectors: number[][] = [];
for (const item of response.data) vectors[item.index] = item.embedding;
return inputs.map((_, index) => {
const embedding = vectors[index];
if (!embedding) throw new Error(`Missing embedding for input ${index}`);
return embedding;
});
}
const documentEmbeddings = await embedBatch(documentBodies);
const [queryEmbedding] = await embedBatch([`${queryPrompt}${queryText}`]);
if (!queryEmbedding) throw new Error("Missing query embedding");
```
Use the same `queryPrompt` defined above, only for query inputs. Keep each batch within the endpoint's input and rate limits, and pair each vector with its source row using the response `index`. The [OpenAI JavaScript SDK v6](https://github.com/openai/openai-node/blob/v6.0.0/src/resources/embeddings.ts) defaults to requesting base64 and decodes the response as base64. If a compatible endpoint returns float arrays instead, that decoder can silently turn them into zeros. Pass `encoding_format: "float"` explicitly for the AI Gateway; the SDK then returns the float arrays directly. Base64 can reduce response size when an endpoint actually returns base64, but do not assume that format for this gateway.
Use the same model and output dimensions for stored documents and queries. Follow the model's input conventions: some models recommend a query instruction while document text remains unprefixed. If the model supports shorter vectors, compare retrieval quality, storage, and latency before changing dimensions; set the column and both embedding requests to the chosen size. Switching models requires re-embedding the corpus; changing dimensions also requires migrating the vector column and index. Generate embeddings in the application, outside SQL, unless the architecture already provides an in-database embedding function. Other embedding providers are also valid when their output matches the schema and index.
## Build the Index
Choose the operator class and query operator as a matched pair:
| Metric | Common use | Operator class | Distance operator |
| -------------- | ------------------------------------------------------------ | ------------------- | ----------------- |
| Cosine | Most text embeddings | `vector_cosine_ops` | `<=>` |
| L2 / Euclidean | Absolute distance matters; vectors do not need normalization | `vector_l2_ops` | `<->` |
| Inner product | Unit-normalized vectors; matches cosine for unit vectors | `vector_ip_ops` | `<#>` |
Choose the metric recommended for the selected model, and keep its index operator class and query operator aligned. The [Qwen3 Embedding model card](https://huggingface.co/Qwen/Qwen3-Embedding-0.6B) demonstrates cosine similarity and normalized dot product for retrieval. For unit-normalized vectors, cosine, inner product, and L2 have the same exact ranking; approximate indexes can still differ in recall and latency, so benchmark on your corpus.
```sql
CREATE INDEX documents_embedding_ann ON documents
USING lakebase_ann (embedding vector_cosine_ops);
```
## Tune the Index
The default index options suit most workloads:
- `build_mode = 'standard'` balances recall and index build time. Use `quality` for better recall when a longer build is acceptable.
- `lists = 'auto'` chooses the IVF partition layout from the number of indexed vectors. Choose between `auto` and a manual value case by case: test both on the target dataset and use the value that better meets recall and performance targets.
To prioritize recall over index build time:
```sql
CREATE INDEX documents_embedding_ann_quality ON documents
USING lakebase_ann (embedding vector_cosine_ops)
WITH (build_mode = 'quality');
```
To override the automatic partition layout instead:
```sql
CREATE INDEX documents_embedding_ann_lists ON documents
USING lakebase_ann (embedding vector_cosine_ops)
WITH (lists = '1024');
```
For a large table, use `CREATE INDEX CONCURRENTLY` to avoid locking out writes while creating the index. For a frequently changing table, periodically use `REINDEX INDEX CONCURRENTLY` to rebuild the index with minimal write locking.
## Query
Generate the query embedding with the same model and dimensions used for stored documents, following the model's query-input conventions (using the Qwen3 query prompt shown above), then bind it as a parameter:
```sql
SELECT id, title, embedding <=> $1::vector AS distance
FROM documents
ORDER BY distance
LIMIT $2;
```
Distance sorts ascending: a smaller value is a closer match. Keep the query operator consistent with the index operator class.
To filter by a similarity radius, use the matching boolean range operator in `WHERE` and the distance operator in `ORDER BY`:
```sql
SELECT id, title
FROM documents
WHERE embedding <<=>> sphere($1::vector, 0.5)
ORDER BY embedding <=> $1::vector
LIMIT $2;
```
The cosine range operator `<<=>>` returns a boolean; do not use it as the ranking expression.
## Tune Search
Inspect the index before overriding defaults:
```sql
SELECT lakebase_ann_index_info('documents_embedding_ann');
```
This reports `lists`, `default_probes`, and `default_epsilon`. Small datasets use exact flat search before IVF lists are built. In that state, `lists` and `default_probes` are empty. Leave `lakebase_ann.probes` set to its default of `'auto'`; `lakebase_ann.epsilon` still controls full-precision reranking during flat search.
For an IVF index, `lakebase_ann.probes` controls how many partitions are searched at each level. Higher values generally improve recall at the cost of speed. Its default is `'auto'`. When `lists` is not empty, the shape of `probes` must match the shape of `lists`: use one value for a one-level index or two comma-separated values for a two-level index. At each level, the `probes` value must be no larger than the corresponding `lists` value. A mismatched or out-of-range value causes an error.
`lakebase_ann.epsilon` controls how many candidates are reranked using full-precision distances. Higher values rerank more candidates and take longer. Its default is `'auto'`, which works well for most workloads.
### Use Prefilter Selectively
By default, PostgreSQL applies non-vector filters after the ANN index returns candidate rows. Enable prefilter when a filter is cheap to evaluate and removes most rows. Leave it off for loose or expensive filters.
```sql
BEGIN;
SET LOCAL lakebase_ann.prefilter = on;
SELECT id, title
FROM documents
WHERE id % 100 = 0
ORDER BY embedding <=> $1::vector
LIMIT $2;
COMMIT;
```
Start with `probes` and `epsilon` set to `'auto'`. Benchmark manual probe values against representative query embeddings and choose the smallest values that satisfy recall and tail-latency targets. Keep session settings and the query in the same transaction when using a connection pool or stateless driver.
Source: [`lakebase_vector` documentation](https://neon.com/docs/extensions/lakebase-vector).
SKILL.md
---
name: neon-postgres
description: >-
Guides and best practices for working with Lakebase Postgres on Neon:
connections, pooled vs direct, schema migrations, branching (including
schema-only, reset-from-parent, and branch-per-PR workflows), autoscaling,
scale-to-zero, instant restore, read replicas, IP allow lists, logical
replication, egress optimization, and Lakebase Search. Use when the work is
an existing DATABASE_URL, SQL, schema, inspect, or search. New backends, Auth,
files, Functions, and LLM calls go to the parent `neon` skill. Live queries,
reactive UIs, and realtime data go to `neon-realtime`. Also use for
"@neondatabase/serverless", "@neon/neon-js", "neon inspect db", "neon
postgres computes", "Neon branch", "schema-only branch", "reset branch",
"branch per PR", high database bills, network transfer or egress, SELECT *
optimization, query overfetching, "semantic search", "vector search", "full-text search", "BM25", or "hybrid
search".
metadata:
parent: neon
source: https://github.com/neondatabase/agent-skills/tree/main/skills/neon-postgres
---
**FIRST**: Use the parent `neon` skill for a Neon overview, getting started with Neon, Neon development best practices, and more.
If the `neon` skill is not installed, fetch it from https://neon.com/docs/ai/skills/neon/SKILL.md or install it with:
```bash
neon skills -s neon -y
```
# Lakebase Postgres
Lakebase Postgres is the database at the core of Neon. It runs on the lakebase architecture — OLTP built directly on cloud object storage — which decouples storage from compute to offer autoscaling, branching, instant restore, and scale-to-zero. It's fully compatible with Postgres and works with any language, framework, or ORM that supports Postgres.
It is the same database whether you reach it through Neon or through Databricks; this skill covers the Neon access path.
Login, users, sessions, and `@neon/auth` belong in `neon-auth`.
Live queries, reactive UIs, and realtime data belong in `neon-realtime`.
## Setup Flow
### 1. Select the organization and project
If a `DATABASE_URL` is already supplied (prompt, environment, or repo) or a `.neon` file points at a project, use it. Do not list organizations or create a second project for schema work.
Otherwise use the CLI (default) or MCP server to list organizations and projects. Let the user select an existing project or create a new one.
### 2. Get the connection string
If a `DATABASE_URL` is already supplied, use it. Do not fetch another through the CLI or MCP.
Otherwise use the CLI (default), `neon env pull`, or the MCP server to get the connection string. Store it in `.env` as `DATABASE_URL`. Read the file first before modifying it, to avoid overwriting existing values.
#### When to use pooled vs direct connections
| Use case | Connection type |
| ---------------------------------------- | ---------------- |
| Web applications, serverless functions | Pooled (-pooler) |
| Schema migrations | Direct |
| pg_dump / pg_restore | Direct |
| Logical replication | Direct |
| Long-running analytics with temp tables | Direct |
| Admin tasks needing SET or session state | Direct |
| LISTEN / NOTIFY | Direct |
### 3. Pick the connection method and driver
Preserve the existing ORM and driver. For new TypeScript schema work with no established choice, Drizzle is a suggestion: https://neon.com/docs/guides/drizzle.md. Refer to the connection methods guide to pick the correct driver based on how the runtime treats your code: https://neon.com/docs/connect/choose-connection.md.
Driver notes:
- On Vercel, use `node-postgres` (`npm install pg`) with Vercel Fluid compute and `import { attachDatabasePool } from "@vercel/functions";`
- On Cloudflare, use `node-postgres` with Cloudflare Hyperdrive
- On Neon Functions, use `node-postgres`, as the functions are long-running and reuse the pool across requests.
- Use the `@neondatabase/serverless` driver for serverless and edge environments (for example, when using Netlify) — HTTP transport for one-shot queries, WebSocket for transaction support. Link: https://neon.com/docs/serverless/serverless-driver.md
### 4. Set up the schema
Manage schemas and migrations as code. Avoid running ad hoc schema migrations against your database, since they're hard to manage.
If you're using an ORM, follow your ORM's best practices to manage schemas and migrations. For example, if using Drizzle, only use Drizzle for schema and migration management unless instructed otherwise.
## Branching
Use this when the user is planning isolated environments, schema migration testing, preview deployments, or branch lifecycle automation.
Key points:
- Branches are instant, copy-on-write clones (no full data copy).
- Each branch has its own compute endpoint.
- Use the neon CLI or MCP server to create, inspect, and compare branches.
Link: https://neon.com/docs/introduction/branching.md
For detailed branch creation workflows (normal vs schema-only branches, reset-from-parent, CLI/MCP selection, and CI/CD patterns), read [Branching workflows](references/branching.md).
## Migrations
Test a migration on a branch of production, against production-like data, before applying it to production.
Use a **direct (non-pooled)** connection string when you run the migration, not a pooled one. `neon connection-string` returns the direct string by default; make sure the hostname does not include the `-pooler` suffix.
## Egress Optimization
Use this when a user reports high database bills, unexpected network transfer, egress spikes, query overfetching, or wants a cost-efficiency review of database query patterns.
Before editing code, read [Egress optimization](references/egress-optimization.md). Audit all five patterns: unused columns, missing pagination, repeated static reads, application-side aggregation, and JOIN duplication. Do not stop after fixing `SELECT *`.
Work in four steps: diagnose which queries transfer the most data, analyze the code behind them, fix the anti-patterns, then verify that behavior is preserved and transfer drops.
## Troubleshooting and Neon-Specific Performance
Use Neon's predefined, read-only diagnostics before writing catalog queries by hand. The Neon CLI `neon inspect db` subcommands and the Neon MCP server's `inspect_database` tool run the same checks.
This section covers Neon-specific diagnostic tools, compute cache behavior, and platform signals. When the evidence points to generic Postgres work such as rewriting a query, choosing an index, changing a schema, or interpreting plan nodes, load the [`postgres-best-practices`](https://github.com/neondatabase/postgres-skills/tree/main/skills/postgres-best-practices) skill and carry the diagnostic evidence into that workflow.
Docs:
- CLI: https://neon.com/docs/cli/inspect.md
- Query performance: https://neon.com/docs/postgresql/query-performance.md
- `pg_stat_statements`: https://neon.com/docs/extensions/pg_stat_statements.md
- Neon Local File Cache: https://neon.com/docs/extensions/neon.md
### Choose CLI or MCP
Prefer the Neon CLI when terminal access and authentication are available:
```bash
neon inspect db <check>
```
The CLI resolves the project and branch from the current Neon context. Use `--project-id`, `--branch`, and `--database-name` to override it. Omit `--database-name` to inspect every database on the branch. Use `--db-url` only when inspecting a Postgres database directly instead of resolving it through the Neon API.
When using Neon MCP, call `inspect_database` with `projectId` and one `check`. Pass `branchId`, `databaseName`, or `computeId` only when needed. Omit `databaseName` to inspect all databases on the branch. Increase `limit` only when the result says it was truncated.
### Pick the Diagnostic
| Symptom or question | Checks |
| ---------------------------------------------- | ------------------------------------ |
| Which relations consume storage? | `table-sizes`, `index-sizes` |
| Is an index unused or a table scanned heavily? | `unused-indexes`, `seq-scans` |
| What has run for 5+ minutes or holds locks? | `long-running-queries`, `locks` |
| Which queries consume the most total time? | `outliers` |
| Which queries run most often? | `calls` |
| Does the active data fit in compute cache? | `lfc-hit-rate`, `working-set` |
| Is autovacuum behind or is space wasted? | `vacuum-stats`, `bloat` |
| Is logical replication healthy? | `replication-slots`, `subscriptions` |
Do not confuse these checks:
- `long-running-queries` reports statements running **right now** for more than five minutes.
- `outliers` ranks the top queries by cumulative execution time since statistics were reset. It does not rank by mean latency.
- `calls` ranks by execution count over the same statistics history.
`outliers` and `calls` require `pg_stat_statements`. `lfc-hit-rate` and `working-set` require the `neon` extension. If a check reports a missing extension, ask before running the suggested `CREATE EXTENSION` statement because installing an extension modifies the database.
### Interpret Results Safely
- Treat `unused-indexes` as a candidate list, not permission to drop indexes. Confirm the observation window, constraints, and workload before removal.
- A sequential scan can be correct for a small table or a query reading much of a table. Check table size, selectivity, and the query plan before adding an index.
- `bloat` is a statistical estimate. Confirm the impact and plan locks or maintenance before `VACUUM FULL`, `REINDEX`, or similar remediation.
- Cache and Postgres statistics reset when compute restarts, including scale-to-zero suspension. Run a representative workload before interpreting fresh `lfc-hit-rate`, `working-set`, `vacuum-stats`, or `pg_stat_statements` results.
- Compute-wide checks (`lfc-hit-rate`, `working-set`, and `replication-slots`) run once even when inspecting every database.
- One failing database can fail an all-databases inspection; retry the relevant check with an explicit `databaseName` to isolate it.
### Inspect Neon Cache Behavior Per Query
Standard `EXPLAIN (ANALYZE, BUFFERS)` reports Postgres shared-buffer activity, but it does not show Neon's Local File Cache (LFC) or page prefetching. For a safe read-only query, add Neon's `FILECACHE` and `PREFETCH` options:
```sql
EXPLAIN (ANALYZE, BUFFERS, PREFETCH, FILECACHE)
SELECT ...;
```
- `File cache: hits` counts pages found in the compute's LFC.
- `File cache: misses` counts pages not found in the LFC and fetched from database storage.
- `Prefetch: hits`, `misses`, `expired`, and `duplicates` show how effectively Neon fetched pages before the executor requested them.
`FILECACHE` and `PREFETCH` provide metrics for this query and do not require the `neon` extension. By contrast, `neon inspect db lfc-hit-rate` and `working-set` provide compute-wide statistics and do require the extension.
The MCP `explain_sql_statement` tool can produce a standard plan but does not expose `FILECACHE` or `PREFETCH` options. To collect those Neon-specific metrics through MCP, use `run_sql` with the explicit, read-only `EXPLAIN` statement above.
Because `ANALYZE` executes the statement, use it only when execution is safe; do not run it autonomously for mutating SQL. Compare cold- and warm-cache runs carefully because the first execution can populate the cache and materially change later results.
### Performance Workflow
1. Reproduce the symptom and note its time window.
2. Run the smallest relevant `inspect` checks from the table above.
3. Identify a specific query before changing schema or compute. Use MCP `explain_sql_statement` for a standard plan, or the Neon-specific `EXPLAIN` above when LFC or prefetch behavior matters.
4. If the bottleneck is query shape, indexing, schema, locking, or vacuum behavior, load `postgres-best-practices` and carry forward the inspection results and query plan. Keep Neon compute, cache, connection, and platform decisions in this skill.
5. Re-run the same check and workload to verify the change.
Use MCP `list_slow_queries` instead of `inspect_database` when the user specifically needs queries ranked by average execution time with a custom threshold and limit. Outside the explicit `EXPLAIN` case above, use `run_sql` only for read-only diagnostic SQL when the predefined checks do not answer the question.
## Computes
Use this when the user wants to resize a compute, change its scale-to-zero timeout, or start, suspend, restart, or delete it.
Manage computes with `neon postgres computes`. `neon pg` and `neon lakebase` are aliases of `neon postgres`.
```bash
neon postgres computes list # --branch <branch> or the branch .neon pins; --all-branches for the project
neon postgres computes get <compute-id>
neon postgres computes update <compute-id> --cu 0.25-2 --suspend-timeout 300
neon postgres computes restart <compute-id> # also start, suspend, delete
```
`update` sends only the flags you pass (`--cu`, `--suspend-timeout`, `--name`, `--disabled`) and fails when you pass none. Each change waits until Neon applies it; `update`, `start`, `suspend`, and `restart` then print the compute. With `--no-wait` the command returns once the API accepts the change; find the operation with `neon operations list` and check it with `neon operations get <operation-id>`. Add a compute, such as a read replica, with `neon branches add-compute <branch>`.
## Autoscaling
Use this when the user needs compute to scale automatically with workload and wants guidance on CU sizing and runtime behavior.
Set a compute's autoscaling range with `neon postgres computes update <compute-id> --cu <min>-<max>` (for example `--cu 0.25-2`), or a fixed size with `--cu 2`.
Link: https://neon.com/docs/introduction/autoscaling.md
## Scale to Zero
Use this when optimizing idle costs and discussing suspend/resume behavior, including cold-start trade-offs.
Key points:
- Idle computes suspend automatically after a default of 5 minutes. Change the timeout with `neon postgres computes update <compute-id> --suspend-timeout <seconds>`: `0` uses the plan default and `-1` disables suspension. Free cannot change it, Launch can only enable or disable suspension, and Scale can set any value from 60 to 604800 (one week).
- First query after suspend typically has a cold-start penalty (around hundreds of ms)
- Storage remains active while compute is suspended.
Link: https://neon.com/docs/introduction/scale-to-zero.md
### Keep hot reads off Postgres
Scale to zero only suspends an idle compute. When the same rarely changing rows (config, categories, feature flags) are read on every request, cache them outside Postgres, for example in [Upstash Redis](https://upstash.com/docs/redis) with a TTL. Cached reads cut egress and cold-start latency, and let an otherwise idle compute stay suspended.
## Instant Restore
Use this when the user needs point-in-time recovery or wants to restore data state without traditional backup restore workflows.
Key points:
- History windows for instant restore depend on plan limits.
- Users can create branches from historical points-in-time.
- Time Travel queries can be used for historical inspection workflows.
Link: https://neon.com/docs/introduction/branch-restore.md
## Read Replicas
Use this for read-heavy workloads where the user needs dedicated read-only compute without duplicating storage.
Key points:
- Replicas are read-only compute endpoints sharing the same storage.
- Creation is fast and scaling is independent from primary compute.
- Typical use cases: analytics, reporting, and read-heavy APIs.
Link: https://neon.com/docs/introduction/read-replicas.md
## Connection Pooling
Use this when the user is in serverless or high-concurrency environments and needs safe, scalable Postgres connection management.
Key points:
- Neon pooling uses PgBouncer.
- Add `-pooler` to endpoint hostnames to use pooled connections.
- Pooling is especially important in serverless runtimes with bursty concurrency.
Link: https://neon.com/docs/connect/connection-pooling.md
## IP Allow Lists
Use this when the user needs to restrict database access by trusted networks, IPs, or CIDR ranges.
Link: https://neon.com/docs/introduction/ip-allow.md
## Logical Replication
Use this when integrating CDC pipelines, external Postgres sync, or replication-based data movement.
Key points:
- Neon supports native logical replication workflows.
- Useful for replicating to/from external Postgres systems.
Link: https://neon.com/docs/guides/logical-replication-guide.md
## Lakebase Search
Use Lakebase Search for semantic, full-text, and hybrid search:
- For semantic search, including generating embeddings with the Neon AI Gateway, read [Vector search](references/vector-search.md).
- For full-text search with BM25 ranking, read [Full-text search](references/full-text-search.md).
- For combining semantic and lexical results with fusion or reranking, read [Hybrid search](references/hybrid-search.md).
- For managing any of the above through Drizzle ORM, read [Managing Lakebase Search with Drizzle](references/lakebase-search-drizzle.md).
Links:
- [Get started with Lakebase Search](https://neon.com/docs/ai/lakebase-search-get-started)
- [`lakebase_vector` reference](https://neon.com/docs/extensions/lakebase-vector)
- [`lakebase_text` reference](https://neon.com/docs/extensions/lakebase-text)
## Gotchas
### Pooled vs direct connections: use the direct URL for migrations, dumps, and replication
Neon gives you two connection strings for the same database: a **pooled** one (hostname with the `-pooler` suffix) and a **direct/unpooled** one (no `-pooler` suffix). `neon env pull` writes them as `DATABASE_URL` and `DATABASE_URL_UNPOOLED`. The pooled connection routes through PgBouncer in transaction mode, which doesn't support session-level operations. Choose the right one:
- **Pooled (`DATABASE_URL`)** — your application's normal query traffic, especially serverless and connection-per-request workloads.
- **Direct (`DATABASE_URL_UNPOOLED`)** — schema migrations (Prisma Migrate, Drizzle Kit, Alembic, and others), `pg_dump` / `pg_restore`, logical replication, `LISTEN`/`NOTIFY`, and anything relying on `SET` or other session state.
Running migrations, dumps, or replication over the pooled connection can fail, and never in a way that names pooling: `prepared statement "s0" already exists` from Prisma Migrate, a `SET search_path` that doesn't persist past its own transaction so the next query reports `relation "mytable" does not exist`, or a write intermittently hitting a read-only transaction (`SQLSTATE 25006`) that a pooled backend inherited from an earlier client. Migration tools generally take both strings at once — Prisma's `directUrl` alongside `url` — so point that at the direct one rather than swapping `DATABASE_URL` and losing pooling for the application. See https://neon.com/docs/connect/connection-pooling.md.