neon-postgres

# 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).

# Hybrid Search

Use hybrid search when either semantic similarity or exact vocabulary can identify a relevant document. Lakebase Search does not provide a built-in hybrid function: run vector and BM25 retrieval separately, then combine their results with a fusion strategy suited to the workload.

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.

Reciprocal Rank Fusion (RRF) is the approach in the Lakebase Search get-started guide and a useful default because it combines ranks instead of incomparable raw distances and scores. It is not the only option: weighted rank fusion, normalized score fusion, or a reranker may fit applications with different relevance signals.

## RRF Example

For rank `r` and constant `k`, each retriever contributes `1 / (k + r)`. The documented starting point uses 40 candidates per retriever and `k = 60`; tune both for the corpus and workload.

Embed the query text through the same gateway 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
    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 RRF.
- Tune candidate counts and the RRF constant with judged or behavioral relevance data, plus latency measurements.
- 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.

Source: [Lakebase Search get-started guide][lakebase-search-guide].

[lakebase-search-guide]: https://neon.com/docs/ai/lakebase-search-get-started#combine-results-with-hybrid-search

# 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 the [hybrid search](hybrid-search.md) RRF query the same way: raw SQL through `db.execute`.

## 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)

# 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)
);
```

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).

---
name: neon-postgres
description: >-
  Guides and best practices for working with Lakebase Postgres on Neon:
  connections, pooled vs direct, schema migrations, branching, autoscaling,
  scale-to-zero, instant restore, read replicas, IP allow lists, logical
  replication, 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", "@neondatabase/neon-js", "neon inspect db",
  "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 `@neondatabase/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), use the `neon-postgres-branches` skill. If it isn't installed, fetch it from https://neon.com/docs/ai/skills/neon-postgres-branches/SKILL.md or install it with:

```bash
neon skills -s neon-postgres-branches -y
```

## 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.

## 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.

## Autoscaling

Use this when the user needs compute to scale automatically with workload and wants guidance on CU sizing and runtime behavior.

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; the timeout is configurable, and suspension can only be disabled on the Launch and Scale plans.
- 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

## 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, 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.