pg_pinecone: El Índice Vectorial de Pinecone Dentro del Postgres que Ya Tenés

1. Bajá la imagen, levantala y conectate (del quickstart)

# The image is pgvector/pgvector plus pg_pinecone (amd64 and arm64).
# Pin the version: "latest" currently points at the pg18 build.
docker pull pineconeteam/pg_pinecone:0.11.0-pg17

# shared_preload_libraries isn't needed for the native engine, but the remote
# engine and full-text search need it: setting it now saves a restart later
docker run -d --name pg_pinecone \
  -e POSTGRES_PASSWORD=secret \
  pineconeteam/pg_pinecone:0.11.0-pg17 \
  -c shared_preload_libraries=pg_pinecone

# No port is published: connect through the container as the postgres superuser
docker exec -it pg_pinecone psql -U postgres

2. Creá las dos extensiones y anotá las versiones

-- pgvector first: pg_pinecone builds on its vector type and distance operators
CREATE EXTENSION vector;
CREATE EXTENSION pg_pinecone;

-- The two versions the support page asks for in every bug report
SELECT extversion FROM pg_extension WHERE extname = 'pg_pinecone';
SELECT version();

3. El quickstart de la documentación: una tabla, un índice native, una query y su plan

-- The docs quickstart: hand-made 3-d vectors (food, nature, places)
CREATE TABLE docs (
    id        bigint PRIMARY KEY,
    body      text,
    embedding vector(3)
);

INSERT INTO docs (id, body, embedding) VALUES
    (1, 'The Eiffel Tower was completed in 1889 for the World''s Fair in Paris.', '[0.1, 0.1, 0.9]'),
    (2, 'Photosynthesis converts sunlight, water, and carbon dioxide into glucose.', '[0.05, 0.9, 0.1]'),
    (3, 'The Great Barrier Reef is the largest coral reef system in the world.', '[0.1, 0.8, 0.3]'),
    (4, 'Espresso is brewed by forcing hot water through finely ground coffee.', '[0.95, 0.05, 0.05]'),
    (5, 'Mount Everest sits on the border between Nepal and China.', '[0.05, 0.4, 0.8]'),
    (6, 'Honeybees communicate the location of flowers with a waggle dance.', '[0.2, 0.85, 0.05]'),
    (7, 'A croissant is a flaky pastry made from laminated, buttered dough.', '[0.95, 0.05, 0.1]'),
    (8, 'The Amazon rainforest produces a large share of the world''s oxygen.', '[0.05, 0.9, 0.3]');

-- native is the default engine since 0.11.0; naming it is explicit, not required
SELECT pinecone.create_index('docs', 'embedding', engine => 'native');

-- Eight rows: force the index so you can watch it work (tests only)
SET enable_seqscan = off;

SELECT id, body
FROM docs
ORDER BY embedding <=> '[1, 0, 0]'
LIMIT 2;
--  4 | Espresso is brewed by forcing hot water through finely ground coffee.
--  7 | A croissant is a flaky pastry made from laminated, buttered dough.

-- Which native path served that scan: flat, h or l (expect flat: under 10,000 rows)
SELECT pinecone.last_scan_mode();

EXPLAIN (COSTS OFF)
SELECT id
FROM docs
ORDER BY embedding <=> (SELECT embedding FROM docs WHERE id = 4)
LIMIT 2;
-- The plan should include: Index Scan using docs_embedding_pinecone_idx

1. La tabla, un set de queries reales y la línea base HNSW

-- Your corpus. STORAGE PLAIN keeps 768-d vectors in the heap row instead of
-- TOAST, which the native engine's exact rescore reads for every candidate
CREATE TABLE items (
    id        bigint PRIMARY KEY,
    tenant_id bigint,
    body      text,
    embedding vector(768) STORAGE PLAIN
);

-- Real query embeddings from the same model, kept apart from the corpus
CREATE TABLE eval_queries (id serial PRIMARY KEY, embedding vector(768));

-- Load both with your usual pipeline, THEN build the pgvector baseline
CREATE INDEX items_hnsw ON items USING hnsw (embedding vector_cosine_ops);
SET hnsw.ef_search = 100;

SELECT pg_size_pretty(pg_relation_size('items_hnsw'));

2. Recall@10 y latencia: pasada exacta una vez, pasada aproximada por índice

-- recall@10 of whichever index serves items right now, against exact answers.
-- My glue, plain Postgres. The exact pass forces sequential scans, the way
-- Pinecone's bench harness builds its baseline. Run it once.
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET pinecone.am_enabled = off;
CREATE TEMP TABLE exact AS
SELECT q.id AS qid, n.id
FROM eval_queries q
CROSS JOIN LATERAL (
    SELECT i.id FROM items i ORDER BY i.embedding <=> q.embedding LIMIT 10
) n;
RESET enable_indexscan;
RESET enable_bitmapscan;
RESET pinecone.am_enabled;

-- The approximate pass: rerun after every index change. \timing gives the
-- total, divide by the number of queries (check the plan with EXPLAIN first)
\timing on
DROP TABLE IF EXISTS approx;
CREATE TEMP TABLE approx AS
SELECT q.id AS qid, n.id
FROM eval_queries q
CROSS JOIN LATERAL (
    SELECT i.id FROM items i ORDER BY i.embedding <=> q.embedding LIMIT 10
) n;

SELECT round(count(*) / (10.0 * (SELECT count(*) FROM eval_queries)), 3) AS recall_at_10
FROM approx JOIN exact USING (qid, id);

3. Pasá a native, inspeccionalo y conocé el camino de vuelta

-- 1. Build the native index next to HNSW. Cosine is create_index's default
--    metric, matching vector_cosine_ops (use metric => 'l2' or 'ip' otherwise).
--    Reads keep using HNSW meanwhile; writes wait for the build.
SELECT pinecone.create_index('items', 'embedding', engine => 'native');
-- NOTICE:  pg_pinecone: calibrated for recall@10 0.95: probing ... lists and rescoring ... rows

-- 2. Drop the pgvector index: queries move to the native one
DROP INDEX items_hnsw;

-- 3. What the build chose, and what it weighs
SELECT * FROM pinecone.index_calibration('items_embedding_pinecone_idx');
SELECT pg_size_pretty(pg_relation_size('items_embedding_pinecone_idx'));

-- 4. Rerun the approximate pass and compare recall_at_10 and \timing

-- Going back is two statements, from the same guide
DROP INDEX items_embedding_pinecone_idx;
CREATE INDEX items_hnsw ON items USING hnsw (embedding vector_cosine_ops);

4. Ajustalo si el recall no llega

-- Session overrides for a sweep; both win over the index's calibrated defaults
SET pinecone.scan_fraction = 0.05;     -- share of IVF lists probed (nprobe / n_lists)
SET pinecone.rescore_multiplier = 5;   -- rescore pool = 5 x 100 rows

-- LIMIT 100 workloads: pin a 1,000-row pool, then rebuild (probes recalibrate)
ALTER INDEX items_embedding_pinecone_idx SET (rescore_multiplier = 10);
REINDEX INDEX items_embedding_pinecone_idx;

-- Or calibrate on real queries (a table of 50+ distinct ones, never eval_queries)
ALTER INDEX items_embedding_pinecone_idx SET (calibration_queries = 'public.calib_queries');
REINDEX INDEX items_embedding_pinecone_idx;

1. Reconstruí el índice con una columna de filtro y consultá un tenant

-- One pg_pinecone index per table: rebuild it with the tenant as filter column
-- (smallint, integer, bigint, text or varchar)
DROP INDEX items_embedding_pinecone_idx;
SELECT pinecone.create_index('items', 'embedding', engine => 'native', filter_column => 'tenant_id');

-- "=" on the filter column runs inside the scan, not after it
SELECT id, body FROM items
WHERE tenant_id = 42
ORDER BY embedding <=> (SELECT embedding FROM eval_queries WHERE id = 1)
LIMIT 10;

SELECT pinecone.last_scan_mode();   -- l: key at or below 3% of rows, h: above

2. Condiciones que el índice no puede empujar, y cómo leer el scan

-- Any other condition (a range, IN, ILIKE, a second column) filters each batch the
-- index returns. On native, pinecone.iterative_scan = auto (the default) keeps
-- scanning until LIMIT is filled; rows after the first batch arrive in batch
-- order, so re-sort them when exact order matters:
WITH relaxed AS MATERIALIZED (
    SELECT id, embedding <=> (SELECT embedding FROM eval_queries WHERE id = 1) AS distance
    FROM items
    WHERE tenant_id = 42 AND body ILIKE '%invoice%'
    ORDER BY distance
    LIMIT 10
)
SELECT * FROM relaxed ORDER BY distance + 0;

-- Did the scan stop at pinecone.max_scan_tuples (20,000 rows by default)?
SELECT batches, rescored, capped FROM pinecone.last_scan_stats();

-- On the pgvector engine, pgvector's own switch applies instead (0.8.0+)
SET hnsw.iterative_scan = relaxed_order;

Cambiá de motor, seguí la copia, consultá y volvé

-- Prerequisites: shared_preload_libraries = 'pg_pinecone' (the docker run in
-- step 1 sets it), a single-column primary key, and an API key in the database
SELECT pinecone.set_api_key('YOUR_API_KEY');

-- Creates a serverless index (aws us-east-1 unless you set pinecone.am_cloud and
-- pinecone.am_region first) and copies the vectors in the background.
-- Queries keep using the native index until the copy finishes.
SELECT pinecone.set_engine('items', 'remote');

-- mode is 'promoting' during the copy, 'remote' with backlog 0 once Pinecone serves
SELECT table_name, mode, index_name, backlog FROM pinecone.status();

-- The same query, unchanged: the filter column travels as a metadata filter
SELECT id, body FROM items
WHERE tenant_id = 42
ORDER BY embedding <=> (SELECT embedding FROM eval_queries WHERE id = 1)
LIMIT 10;

-- Back to native: a blocking local rebuild, then the Pinecone index is deleted
SELECT pinecone.set_engine('items', 'native');
SELECT table_name, mode FROM pinecone.status();   -- done when items no longer appears