Search a table with full-text search

pg_pinecone can keep a Pinecone index for full-text search in sync with a Postgres table’s text columns. You declare the searchable columns once and search them with SQL, and Postgres stays the system of record.

Before you begin

pg_pinecone installed, as the quickstart shows.
A Pinecone API key stored in the database. Store it with SELECT pinecone.set_api_key('YOUR_API_KEY');.
pg_pinecone in shared_preload_libraries, followed by a Postgres restart. See Preload the extension.
A table with a single-column primary key, which becomes each document’s ID. You must own the table.

Search a table

Create a sample table

This guide uses a table of help-center articles. The embeddings are only needed to combine text and vector search:

SQL

CREATE TABLE articles (
    id        bigint PRIMARY KEY,
    title     text,
    body      text,
    category  text,
    embedding vector(1024)
);

WITH input(id, title, body, category) AS (
    VALUES
        (1, 'Reset your password', 'Open Settings, choose Security, and select Reset password. A reset link arrives by email within a few minutes.', 'account'),
        (2, 'Enable two-factor authentication', 'Two-factor authentication adds a one-time code from an authenticator app to every sign-in.', 'account'),
        (3, 'Export your data', 'Go to Settings and choose Export to download every project as a CSV file.', 'data'),
        (4, 'Fix error E-1042 during import', 'Error E-1042 means the CSV file has a header row that does not match the table columns.', 'data'),
        (5, 'Change your billing plan', 'Upgrade or downgrade your plan from the Billing page. Changes take effect at the next invoice.', 'billing'),
        (6, 'Download past invoices', 'Every invoice is available as a PDF on the Billing page under Invoice history.', 'billing')
)
INSERT INTO articles (id, title, body, category, embedding)
SELECT i.id, i.title, i.body, i.category, e.embedding::vector
FROM input i
JOIN pinecone.embed_batch((SELECT array_agg(title || '. ' || body ORDER BY id) FROM input)) e
  ON e.ordinal = i.id;

Declare the searchable columns

Call pinecone.track_docs() with the text columns to index. metadata_columns lists columns you want to filter on, and vector_column is optional. text_columns takes a JSON list, and metadata_columns takes a Postgres array:

SQL

SELECT pinecone.track_docs(
    'articles',
    text_columns     => '["title", "body"]',
    metadata_columns => ARRAY['category'],
    vector_column    => 'embedding'
);

This creates a Pinecone index and queues the table’s existing rows to be copied to it. Only the declared columns leave Postgres. From then on, pg_pinecone copies every committed insert, update, and delete in the background.A list of column names uses English text analysis with stemming and stop words. To set options per column, pass an object instead. For example, on a table with a sku column, turn off stemming and stop words so identifiers match exactly:

SQL

text_columns => '{"title": {"language": "en"}, "sku": {"stemming": false, "stop_words": false}}'

Wait for the initial copy

Check progress with pinecone.status():

SQL

SELECT table_name, docs_backfill_done, docs_backfill_total, docs_queue_state
FROM pinecone.status();

The copy is finished when docs_backfill_done equals docs_backfill_total. pinecone.flush_docs('articles') waits until everything committed so far has been delivered, then returns true. It returns false if delivery doesn’t finish within 30 seconds. Pinecone then takes up to about a minute to index delivered documents and make them searchable.

Search the table

pinecone.search_docs() returns each match’s ID, score, and rank. Join back to the table for the rest of the row:

SQL

SELECT a.id, a.title, s.score
FROM pinecone.search_docs('articles', 'download invoice pdf', k => 3) s
JOIN articles a ON a.id = s.id::bigint
ORDER BY s.rank;

Output

 id |          title           |   score
----+--------------------------+-----------
  6 | Download past invoices   |  6.580365
  3 | Export your data         | 1.6908139
  5 | Change your billing plan | 1.0116261

Pinecone ranks results with BM25. Because the join reads the table with your own privileges, it respects row-level security and drops any row deleted since the last sync.

Filter results

Use filter to restrict results by metadata columns, and must to require text in a column:

SQL

-- Only articles in the account category
SELECT a.id, a.title
FROM pinecone.search_docs('articles', 'settings',
       filter => '{"category": {"$eq": "account"}}', k => 3) s
JOIN articles a ON a.id = s.id::bigint
ORDER BY s.rank;

-- Only articles whose body contains the exact error code
SELECT a.id, a.title
FROM pinecone.search_docs('articles', 'import error',
       must => '{"body": {"$match_phrase": "E-1042"}}', k => 3) s
JOIN articles a ON a.id = s.id::bigint
ORDER BY s.rank;

must accepts $match_phrase, $match_all, and $match_any. For the operators filter accepts, see Filter operators. To write the whole query in Lucene syntax, set mode => 'query_string':

SQL

SELECT a.id, a.title
FROM pinecone.search_docs('articles', 'title:(password OR invoices) -body:email',
       mode => 'query_string', k => 3) s
JOIN articles a ON a.id = s.id::bigint
ORDER BY s.rank;

Combine text and vector search

If you declared a vector_column, pass a query vector with vector, and name the column the text query must match with field. The text query narrows the results to rows that match it in that column, and the vector ranks what’s left:

SQL

SELECT a.id, a.title
FROM pinecone.search_docs('articles', 'billing', field => 'body',
       vector => pinecone.embed('how do I get a receipt for my payment'), k => 3) s
JOIN articles a ON a.id = s.id::bigint
ORDER BY s.rank;

Output

 id |          title
----+--------------------------
  6 | Download past invoices
  5 | Change your billing plan

Search one tenant

To keep each tenant’s documents in their own Pinecone namespace, pass a tenant column as partition_column when you call pinecone.track_docs(). Then name the tenant with partition on every search. This example uses a tickets table with a tenant_id column:

SQL

SELECT pinecone.track_docs('tickets',
    text_columns     => '["title", "body"]',
    partition_column => 'tenant_id');

SELECT s.id, s.score
FROM pinecone.search_docs('tickets', 'invoice pdf', partition => 42, k => 10) s;

A search covers exactly one tenant, and BM25 scores are calculated within that tenant’s documents. A search without partition returns an error. With engine => 'local', partition becomes a condition on the column instead. pinecone.delete_partition('tickets', 42) deletes one tenant’s documents from Pinecone, and its rows stay in the table. The partition column must be NOT NULL. Pinecone limits the number of namespaces per index by plan, and pg_pinecone warns when a table has more than 1,000 distinct values.

Highlight matches

pinecone.headline() marks the query terms in a column’s text, using the language you declared for that column:

SQL

SELECT a.id, pinecone.headline('articles', 'body', a.body, 'download invoice pdf') AS snippet
FROM pinecone.search_docs('articles', 'download invoice pdf', k => 2) s
JOIN articles a ON a.id = s.id::bigint
ORDER BY s.rank;

Output

 id |                                               snippet
----+-----------------------------------------------------------------------------------------------------
  6 | Every <b>invoice</b> is available as a <b>PDF</b> on the Billing page under <b>Invoice</b> history.
  3 | Go to Settings and choose Export to <b>download</b> every project as a CSV file.

Search without Pinecone

Set engine => 'local' to answer the same call with Postgres’s built-in full-text search instead of Pinecone:

SQL

SELECT a.id, a.title, s.score
FROM pinecone.search_docs('articles', 'download invoice', engine => 'local', k => 3) s
JOIN articles a ON a.id = s.id::bigint
ORDER BY s.rank;

The local engine reflects writes immediately. It also works on tables you haven’t declared with track_docs() if you name a column with field. It ranks results with Postgres’s ts_rank_cd instead of BM25 and requires every query word to match, so it can return fewer results than the remote engine, in a different order. It doesn’t support filter, must, vector, or mode => 'query_string', and it doesn’t create an index, so add a GIN index on large tables.

Manage a tracked table

Once a table is tracked, pg_pinecone keeps its Pinecone index in sync on its own. These functions handle the cases that need your attention:

Retry rejected documents: If Pinecone rejects a document, pg_pinecone sets it aside without stopping the rest of the table. pinecone.failed_docs('articles') lists rejected documents and why, and pinecone.retry_failed_docs('articles') queues them again.
Re-send every row: pinecone.backfill_docs('articles', clear => true) copies the whole table again, for example after restoring a database backup.
Change a declared column: Stop tracking the table before you drop, rename, or change the type of a declared column.
Stop tracking: pinecone.untrack_docs('articles', drop_index => true) stops syncing and deletes the Pinecone index.

Search an index directly

To work with a Pinecone index for full-text search that isn’t tied to a table, use pinecone.create_doc_index(), pinecone.upsert_docs(), and pinecone.search_doc_index():

SQL

SELECT pinecone.create_doc_index('faq',
  '{"fields": {"question": {"type": "string", "full_text_search": {"language": "en"}}}}');

SELECT pinecone.upsert_docs('faq',
  '[{"_id": "q1", "question": "How do I cancel my subscription?"},
    {"_id": "q2", "question": "Where can I find my invoices?"}]');

SELECT id, score
FROM pinecone.search_doc_index('faq',
  '{"type": "text", "fields": ["question"], "query": "invoices"}', k => 3);

Upserted documents become searchable shortly after Pinecone indexes them, so a search run immediately after an upsert may return nothing. The schema and query use the same JSON as the Documents API. To delete the index when you’re done:

SQL

SELECT pinecone.drop_doc_index('faq');

Get started

Build

Resources

Search a table with full-text search

Before you begin

Search a table

Create a sample table

Declare the searchable columns

Wait for the initial copy

Search the table

Filter results

Combine text and vector search

Search one tenant

Highlight matches

Search without Pinecone

Manage a tracked table

Search an index directly

​Before you begin

​Search a table

Create a sample table

Declare the searchable columns

Wait for the initial copy

Search the table

​Filter results

​Combine text and vector search

​Search one tenant

​Highlight matches

​Search without Pinecone

​Manage a tracked table

​Search an index directly

Before you begin

Search a table

Filter results

Combine text and vector search

Search one tenant

Highlight matches

Search without Pinecone

Manage a tracked table

Search an index directly