Ns Postgres Rag

(NS) PostgreSQL retrieval — pgvector, hybrid FTS+vector, GraphRAG tables, multi-hop, 1MM+ scale.

by nextstage-brasil · Apache-2.0

The skill

(NS) PostgreSQL retrieval — pgvector, hybrid FTS+vector, GraphRAG tables, multi-hop, 1MM+ scale. Use for RAG or GraphRAG on Postgres, pgvector, hybrid search, document links, entity merge, multi-hop, or 1MM+ retrieval — even if they say search, embeddings, or knowledge graph in SQL. Do NOT use for ontology/cite-or-refuse (`ns-graphrag`), app code, competing vector DBs, CrewAI, or generic web apps.

PostgreSQL retrieval (RAG / GraphRAG)

Postgres retrieval doctrine: pgvector, FTS, entity/edge, multi-hop.

Analyze. Recommend. No app code. Required reports approved (Retrieval Design Report; plus GraphRAG Process Report when mode is relational GraphRAG and ns-graphrag is installed) → ns-spec-driven (Specify / Tasks).

Central rule

Link = query result, never text inference. Vector = candidate. Edge only after key/rule confirm + stored provenance. Path always path + provenance + confidence.

Canonical hop (illustration only, English artifacts): company → contract → invoice → payment. Derive the corpus chain in GraphRAG Process Report.

Required: pgvector. Timescale: optional — temporal volume, retention, or time partition. Scale: 1MM+ files, document links.

Applicability

Context	Strength
Greenfield retrieval on Postgres	MUST Gates 1–3 before DDL counts as approved
Brownfield schema / indexes	MUST inventory DDL first; diagnose similarity-as-link
Approved required reports, human wants code	Stop. `ns-spec-driven` owns Specify / Tasks (Process Report required first when GraphRAG + `ns-graphrag` installed)

Read repo (DDL, migrations, Postgres version, extensions, source formats). Do not ask human for facts tree already shows.

Routing (read first)

Signal	Action
Wants app implementation now	Fill required reports if missing (Retrieval Design Report; Process Report when GraphRAG + `ns-graphrag`); else `ns-spec-driven`. No app code
Similarity used as stored link	`references/anti-patterns.md` + `references/entity-resolution.md`
Answer needs N≥2 hops; no single document holds chain	GraphRAG mode — `references/retrieval-decision.md`. If `ns-graphrag` is installed: after Retrieval Design Report, continue there for ontology / extract / answer shapes / cite-or-refuse (Process Report) before `ns-spec-driven`
Ontology, schema-locked extract, answer-shape routing, cite-or-refuse	`ns-graphrag` when installed — not this skill
Topic search, small corpus, no hop chain	Vector-only — refuse graph tables
GitLab `ISSUE_URL` or SDD version scope	Defer `../../ns-harness/references/code-skill-routing.md`

Boot (mandatory)

See ../../ns-harness/references/session-boot.md — complete Session boot (blocking). Then scan:

DDL / migrations (tables, indexes, vector, tsvector, entity/edge)
Postgres version; extensions (pgvector required; Timescale if present or Gate 2 trigger)
Source formats, corpus paths, counts if inferable
Continue this skill

Success: inventory from tree + project rules. Failure: invented schema or asking human for facts in repo.

Flow

boot. Gate 1 Corpus Inventory. Gate 2 Retrieval Decision. Vector or hybrid RRF or GraphRAG in Postgres. Gate 3 Retrieval Design Report. Human approve. If mode is relational GraphRAG and ns-graphrag is installed: GraphRAG Process Report there (approve) before ns-spec-driven. Else ns-spec-driven (or loop inventory).

Gate 1 — Corpus Inventory (blocking)

Fill from repo + data sample. Human only for gaps tree cannot show. Fields: templates/retrieval-design-report.md. Method: references/corpus-assessment.md.

Field	Why
Volume (files, bytes, chunk estimate)	Index + storage at 1MM+
Growth / mutability	Re-embed rate, upsert vs rebuild
Entity density	Graph tables vs vector-only
Question archetypes	Mode
Latency budget (p95)	HNSW / filter / hop caps

No skip. Missing inventory = no Gate 2.

Gate 2 — Retrieval Decision (blocking)

Pick exactly one base mode. Matrix: references/retrieval-decision.md.

Mode	When
Vector-only	Topic / similarity; no hop chain
Hybrid (`tsvector` + vector, rank fusion)	Keyword + semantic; one document often answers
Relational GraphRAG	N≥2 hops and no single document contains chain

Augmentations (additive — base mode unchanged): none | multi-index | agentic | both. Each non-none choice needs measured symptom that justifies it. Doctrine: references/retrieval-augmentations.md. Default none. Refuse stacking without symptom.

Extensions: pgvector always. Timescale only references/operations-and-scale.md — not default.

Post base mode + augmentation lock + one-paragraph why before full report. Wrong mode = rewrite Gate 2, not paper over in DDL.

Gate 3 — Retrieval Design Report

Copy templates/retrieval-design-report.md. Fill every section. No application code. SQL = target DDL / query shape, not shipped migration.

Human approve. Reject: loop Gate 1 or Gate 2.

If mode is relational GraphRAG and ns-graphrag is installed: do not jump to ns-spec-driven yet — follow ns-graphrag through the GraphRAG Process Report (ontology, extract, persistence model, answer shapes, cite-or-refuse). Both reports approved → then ns-spec-driven. If ns-graphrag is absent, say so and hand off with retrieval-only scope (process gaps remain open).

Reference map

Load on demand.

Reference	Read when
`references/corpus-assessment.md`	Gate 1; 1MM+ projection
`references/retrieval-decision.md`	Gate 2 base mode + partition / Timescale
`references/retrieval-augmentations.md`	Gate 2 augmentations (multi-index, agentic); symptom lock
`references/schema-and-indexes.md`	Target DDL, HNSW, filters, partition
`references/entity-resolution.md`	Merge, aliases, no edge without evidence
`references/ingestion-pipeline.md`	Extract, chunk, embed, idempotent upsert
`references/multi-hop-traversal.md`	Recursive SQL caps; company/contract/invoice/payment
`references/retrieval-contract.md`	Return shape; `path` / `provenance` / `confidence`
`references/evaluation-and-gates.md`	Golden questions, recall, false-link, EXPLAIN
`references/operations-and-scale.md`	1MM+ build, storage, retention, Timescale
`references/anti-patterns.md`	Before marking report done

Snippets (illustrative SQL / one return type): templates/snippets/.

Handoff to ns-spec-driven

After human approval of Retrieval Design Report — and, when mode is relational GraphRAG and ns-graphrag is installed, after human approval of the GraphRAG Process Report:

## Retrieval implementation planning
- Report: path/to/retrieval-design-report.md (approved)
- GraphRAG Process Report: path/to/graphrag-process-report.md (approved | N/A if not GraphRAG or skill absent)
- Mode: vector-only | hybrid | relational GraphRAG
- Target DDL: [tables]
- Indexes: [HNSW / GIN / partition]
- Ingestion: [idempotent key + hash]
- Eval gates: [recall@k, path precision, false-link, p95]
- Out of scope for this skill: application code; GraphRAG process when ns-graphrag owns it

ns-spec-driven owns Specify / Tasks. This skill writes no application code. Do not add ns-graphrag to this skill’s depends.

Stop conditions

Condition	Action
Human asks for app implementation	Required reports first (incl. Process Report when GraphRAG + `ns-graphrag`); then `ns-spec-driven` only
Similarity proposed as stored edge	Refuse; candidate + confirm + provenance
Graph mode without N≥2 hop need	Refuse graph; vector or hybrid
Competing vector store as default	Stay Postgres + `pgvector`
Unbounded recursive walk	Cap depth, fanout, cycles, score prune
Timescale with no temporal trigger	Do not recommend
Inventory skipped	Block Gate 2

Forbidden

Implement retrieval layer (consumer migrations, workers, HTTP)
Embedding distance as link
External graph store as default
Treating the canonical hop illustration as the only allowed corpus chain without P0 derivation
Name application languages or frameworks (SQL/DDL allowed; one illustrative return-type snippet)

Use this skill

Connect your agent to the Skill Store MCP server and ask for it by ref: nextstage-brasil/ns-postgres-rag-60774606804.

Download SKILL.md · Markdown version

Details

Publisher: nextstage-brasil
Category: Data & Analytics › Databases & SQL
License: Apache-2.0
Updated: 2026-10-16

References

Related skills

Ns Graphrag (NS) GraphRAG process.
Pgvector Semantic Search Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG ap…
Postgres Semantic Search Builds and tunes PostgreSQL retrieval with pgvector, full-text search, pg_trgm, hybrid fusion, Pa…
Pgvector Advanced Advanced pgvector 0.7+ on PostgreSQL.
Vector Databases Vector database integration for embeddings and similarity search.