Semantic search over a Postgres table with Gemini — in SQL
Nobody searches for a vector. You search for a meaning, and something turns your words into a vector behind the scenes. This tutorial makes that “something” Pinecone’s hosted
gemini-embedding-2model, and the “behind the scenes” a singlepinecone.track_docs(...)call — no embedding pipeline, no pgvector column, no vectors on the wire.
By the end you’ll have an ordinary Postgres table that answers three kinds of search:
- semantic — “find rows that mean this”, ranked by Google’s
gemini-embedding-2; - keyword — classic BM25 full-text search;
- hybrid — semantic ranking narrowed by an exact filter.
All of it from SQL, with Pinecone embedding your text server-side on every write and every query.
Prerequisites
pg_pineconeinstalled and the Pinecone (remote) engine available — this feature is remote-only (more on that below).- A Pinecone API key on a Builder, Standard, or Enterprise plan.
gemini-embedding-2is not available on the Starter plan.
SELECT pinecone.set_api_key('pcsk_your_key'); -- stored: the background sync worker needs it
Step 1 — a plain table
Nothing special: text columns and whatever metadata you want to filter on later.
CREATE TABLE notes (
id bigint PRIMARY KEY,
body text,
category text
);
INSERT INTO notes (id, body, category) VALUES
(1, 'PostgreSQL is a relational database that stores tables of rows and columns.', 'database'),
(2, 'Pinecone is a managed vector database for semantic search over embeddings.', 'database'),
(3, 'An embedding model turns a piece of text into a numeric vector that captures its meaning.', 'ml'),
(4, 'BM25 ranks results by exact keyword overlap between the query and the document.', 'search'),
(5, 'Gemini is a family of large language and embedding models built by Google.', 'ml'),
(6, 'Hybrid search blends keyword matching with semantic similarity to improve recall.', 'search');
Step 2 — track it with Gemini embedding
One call. The embed option on the body column is the whole trick: it tells Pinecone to embed
that column’s text with gemini-embedding-2.
SELECT pinecone.track_docs('notes',
text_columns => '{"body": {"language": "en", "embed": {"model": "gemini-embedding-2"}}}',
metadata_columns => ARRAY['category']);
SELECT pinecone.flush_docs('notes'); -- wait until the six rows are delivered
track_docs creates a Pinecone document index whose body field carries both a full-text index
and a hosted gemini-embedding-2 embedding, registers triggers so future INSERT/UPDATE/
DELETEs stay in sync, and backfills the rows already there. You never compute or send a vector —
you send body text, and Pinecone embeds it.
Dimension, task type, etc.
embedis forwarded to Pinecone, so you can pin them if you want:{"embed": {"model": "gemini-embedding-2", "dimension": 1024, "write_parameters": {"task_type": "retrieval_document"}}}. Omit them and Pinecone picks sensible defaults (3072 dims, cosine,retrieval_documenton write /retrieval_queryon read).
Step 3 — search by meaning
Pass text; Pinecone embeds your query with the same model and ranks by cosine similarity. Join the results back to your table like any other query:
SELECT n.id, n.body, s.score
FROM pinecone.search_docs('notes', 'finding information by concept instead of the exact words typed',
mode => 'embed') s
JOIN notes n ON n.id = s.id::bigint
ORDER BY s.rank;
id | body | score
----+--------------------------------------------------------------------------+---------
6 | Hybrid search blends keyword matching with semantic similarity ... | 0.728
4 | BM25 ranks results by exact keyword overlap ... | 0.704
2 | Pinecone is a managed vector database for semantic search ... | 0.665
3 | An embedding model turns a piece of text into a numeric vector ... | 0.625
Notice the query shares almost no words with the rows it found — it matched on meaning. (Scores
are illustrative; exact values from a real gemini-embedding-2 run.)
Step 4 — keyword search (BM25), same column
The body column still has a full-text index, so mode => 'text' does classic keyword ranking:
SELECT n.id, n.body, s.score
FROM pinecone.search_docs('notes', 'relational database rows columns', mode => 'text') s
JOIN notes n ON n.id = s.id::bigint
ORDER BY s.rank;
id | body | score
----+----------------------------------------------------------------+-------
1 | PostgreSQL is a relational database that stores tables ... | 5.775
2 | Pinecone is a managed vector database for semantic search ... | 1.088
BM25 scores (here ~5.8) and semantic scores (~0.7) live on different scales — don’t compare them across searches.
Step 5 — hybrid: rank by meaning, filter exactly
A single request ranks by one method, so blend by putting the hard requirement in filter and the
ranking in mode => 'embed'. Here: rank by meaning, but only within category = 'ml':
SELECT n.id, n.body, s.score
FROM pinecone.search_docs('notes', 'models that understand human language',
mode => 'embed',
filter => '{"category": {"$eq": "ml"}}') s
JOIN notes n ON n.id = s.id::bigint
ORDER BY s.rank;
id | body | score
----+----------------------------------------------------------------+-------
3 | An embedding model turns a piece of text into a numeric vector ...| 0.716
5 | Gemini is a family of large language and embedding models ... | 0.701
You can also narrow with a text requirement instead of metadata — must => '{"body":
{"$match_all": "search"}}' keeps only rows containing search, still ranked by meaning. To rank by
both keyword and meaning, run the two searches and merge by rank client-side.
The remote-only guard
Semantic search needs the hosted model, which only runs at Pinecone. So asking for it on the local engine is refused — clearly, and before any call:
SELECT * FROM pinecone.search_docs('notes', 'anything', mode => 'embed', engine => 'local');
-- ERROR: pinecone.search_docs: mode => 'embed' (semantic search) is not supported on the
-- local engine — it needs the index's hosted embedding model
-- HINT: use engine => 'remote'
engine => 'local' (Postgres full-text search) still works for mode => 'text' on the same column —
you just can’t run Gemini without Pinecone.
Alternative: the ad-hoc document API
If your data doesn’t live in a Postgres table, drive the document index directly — same embed
field, same type: "embed" query:
SELECT pinecone.create_doc_index('kb-gemini', '{"fields": {"chunk_text": {
"type": "string",
"embed": {"model": "gemini-embedding-2"},
"full_text_search": {"language": "en"}
}}}');
SELECT pinecone.upsert_docs('kb-gemini',
'[{"_id": "1", "chunk_text": "Pinecone is a vector database for semantic search."}]');
SELECT * FROM pinecone.search_doc_index('kb-gemini',
'{"type": "embed", "fields": ["chunk_text"], "query": "a database for AI"}');
Good to know
- Freshness is eventual.
track_docssync is asynchronous;flush_docswaits for delivery, and Pinecone then indexes for up to about a minute. There’s no read-your-writes. - One dense field per index. An
embedcolumn uses the index’s single dense slot, so it can’t be combined with a bring-your-ownvector_column, and at most one text column may setembed. - Cleanup.
SELECT pinecone.untrack_docs('notes', drop_index => true);stops syncing and deletes the Pinecone index.