AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

neon-postgres

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", "@neon/neon-js", "neon inspect db", "semantic search", "vector search", "full-text search", "BM25", or "hybrid search".

Original source
neondatabase
License
Apache-2.0
Pinned revision
d3da7cb5436edbb2bc8c00747fafd31ddf0644f4

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read neondatabase--neon-postgres at revision d3da7cb5436edbb2bc8c00747fafd31ddf0644f4, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 5

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:

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:

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:

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:

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.

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.