references/hybrid-search.md
# 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(1536),
body_tsv tsvector GENERATED ALWAYS AS
(to_tsvector('english', body)) STORED
);
```
Replace `1536` with the embedding model's dimension. Use the same model and preprocessing for stored-document and query embeddings, and 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.
Bind the query embedding as `$1`, query text as `$2`, and 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,
body_tsv <@> to_bm25query(
to_tsvector('english', $2),
'documents_body_bm25'::regclass
) AS score
FROM documents
ORDER BY score
FETCH FIRST 40 ROWS WITH TIES
) 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;
```
`RANK()` gives tied retrieval scores the same rank. Sort by `rrf_score` descending and use the stable ID as a final tie-breaker.
`FETCH FIRST ... ROWS WITH TIES` keeps every candidate tied at the cutoff, so `RANK()` receives the complete boundary tie group. The candidate set can therefore exceed 40 rows. `lakebase_bm25.default_limit` defaults to `1000`; increase it only when the BM25 candidate set needs to exceed that value.
## 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` above the BM25 candidate target and allow room for boundary ties.
- 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
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) — define the vector column and keep embedding dimensions consistent
- [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
Use any embedding provider whose vector dimensions and distance metric match the schema and index:
```sql
CREATE TABLE documents (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
embedding vector(1536)
);
```
Replace `1536` with the embedding model's dimension. Generate stored-document and query embeddings with the same model and preprocessing. Keep embedding generation outside SQL unless the architecture already provides an in-database embedding function.
## 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` | `<#>` |
```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 preprocessing used for stored documents, 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, the database
behind Neon. Covers setup, connection methods and drivers, pooled vs direct
connections, branching, schema migrations, autoscaling, scale-to-zero, instant
restore, read replicas, connection pooling, IP allow lists, and logical
replication. Also covers Lakebase Search: semantic vector search, full-text
search with BM25 ranking, and hybrid search.
Use when users ask about "Lakebase Postgres", "Neon setup", "connect to Neon",
"Neon project", "DATABASE_URL", "serverless Postgres", "Neon CLI", "neon", "Neon MCP",
"Neon Auth", "@neondatabase/serverless", "@neondatabase/neon-js",
"scale to zero", "Neon autoscaling", "Neon read replica",
"Neon connection pooling", "schema migrations", "database troubleshooting",
"Postgres performance", "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
npx skills add neondatabase/agent-skills --skill neon
```
# 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.
## Setup Flow
### 1. Select the organization and project
Use the CLI (default) or MCP server to list organizations and projects. Let the user select an existing project or create a new one. Check the `.neon` file for an existing linked project or branch.
### 2. Get the connection string
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
Always pair Neon with an ORM such as **Drizzle** for easy schema management and migrations. 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.
Recommendations:
- Drizzle as ORM (see https://neon.com/docs/guides/drizzle.md)
- 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
npx skills add neondatabase/agent-skills --skill neon-postgres-branches
```
## 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, 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).
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.