Skip to content

sb-dev/openquant / .agents/skills/neon-postgres

neon-postgres

Verified

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

references/full-text-search.md

View on GitHub
# 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).
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall