PostgreSQL 17 Collection Quickstart

This quickstart uses exact table-backed search, filters, count, and facet APIs. It intentionally avoids experimental HNSW serving so the expected outputs are deterministic and reproducible.

Install and Connect

Choose Docker, manual source, or local Compose from the installation guide (PGXN and Homebrew follow in a future update). For a source checkout, install into the PostgreSQL 17 instance selected by pg_config:

cargo pgrx install \
  -p context-pg \
  --release \
  --pg-config /path/to/postgresql-17/bin/pg_config \
  --no-default-features \
  --features pg17

The command writes extension artifacts into that PostgreSQL installation and may require filesystem privileges appropriate to it. Connect as a role allowed to install extensions, then run the SQL below. The packaged HNSW/filter demo is documented separately in Playground.

Create a Collection

CREATE EXTENSION IF NOT EXISTS pgcontext;

CREATE TABLE public.docs (
    id text PRIMARY KEY,
    embedding vector(2) NOT NULL,
    status text NOT NULL,
    body text NOT NULL,
    metadata jsonb NOT NULL
);

INSERT INTO public.docs (id, embedding, status, body, metadata) VALUES
    ('doc-1', '[1,0]'::vector, 'published', 'postgres vector search', '{"topic":"postgres"}'),
    ('doc-2', '[0,1]'::vector, 'published', 'rust extension guide', '{"topic":"rust"}'),
    ('doc-3', '[3,0]'::vector, 'draft', 'internal draft', '{"topic":"postgres"}');

SELECT * FROM pgcontext.create_collection('docs', 'public.docs');

Expected collection setup result:

 collection_name | table_name
-----------------+-------------
 docs            | public.docs

Register the vector and filterable fields:

SELECT pgcontext.register_vector('docs', 'embedding', 'embedding', 2, 'l2');
SELECT pgcontext.register_filter_column('docs', 'status', 'status');
SELECT pgcontext.register_jsonb_path('docs', 'topic', 'metadata', ARRAY['topic']);
SELECT pgcontext.upsert_points('docs', ARRAY['doc-1', 'doc-2', 'doc-3']);

Run exact nearest-neighbor search:

SELECT source_key, score
FROM pgcontext.search('docs', '[1,0]'::vector, 2);

Expected result order:

 source_key | score
------------+-------
 doc-1      | 0
 doc-2      | 1.4142135

Add a filter:

SELECT source_key, score
FROM pgcontext.search(
    'docs',
    '[1,0]'::vector,
    '{"must":[{"key":"status","match":"published"}]}',
    5
);

Expected result order:

 source_key | score
------------+-------
 doc-1      | 0
 doc-2      | 1.4142135

Count and facet use the same registered filter fields:

SELECT pgcontext.count(
    'docs',
    '{"must":[{"key":"topic","match":"postgres"}]}'
);

SELECT *
FROM pgcontext.facet('docs', 'topic', NULL, 10);

Expected count:

 count
-------
 2

Expected facet rows:

 value    | count
----------+-------
 postgres | 2
 rust     | 1

For hybrid dense plus full-text retrieval, use pgcontext.query. Keep pgcontext.search for single-vector exact or ANN-style retrieval.

Remove or Reinstall

Collections are backed by application tables, and those tables can depend on the extension-owned vector type. Remove each collection and its dependent table before dropping the extension:

SELECT pgcontext.drop_collection('docs');
DROP TABLE public.docs;
DROP EXTENSION pgcontext;

DROP EXTENSION pgcontext intentionally does not delete PostgreSQL application tables with CASCADE. Review and remove dependent objects explicitly. To reinstall the same packaged build, run CREATE EXTENSION pgcontext; again; no repository checkout is required once the control, SQL, and shared-library files have been installed into PostgreSQL 17.