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-2 model, and the “behind the scenes” a single pinecone.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_pinecone installed 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-2 is 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. embed is 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_document on write / retrieval_query on 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_docs sync is asynchronous; flush_docs waits for delivery, and Pinecone then indexes for up to about a minute. There’s no read-your-writes.
  • One dense field per index. An embed column uses the index’s single dense slot, so it can’t be combined with a bring-your-own vector_column, and at most one text column may set embed.
  • Cleanup. SELECT pinecone.untrack_docs('notes', drop_index => true); stops syncing and deletes the Pinecone index.