Collections

In pgContext, a collection is a catalog entry that groups table-backed retrieval configuration. The V1 milestone covers collection create/inspect/drop, dense vector-column registration, stable point ID mappings, exact table-backed search, deterministic point scrolling, and experimental sparse vector-column metadata.

Future consumption of vector configuration by quantized serving and named sparse ANN is tracked in the post-V1 roadmap.

Collection names must be ASCII identifiers:

  • start with an ASCII letter or underscore;
  • contain only ASCII letters, digits, and underscores;
  • fit within PostgreSQL’s 63-byte identifier limit.

Invalid names use SQLSTATE 22023 (invalid_parameter_value). Duplicate collection creation uses SQLSTATE 42710 (duplicate_object). Missing collection metadata uses SQLSTATE 42704 (undefined_object).

Create a Collection

SELECT collection_id, collection_name, owner_name
FROM pgcontext.create_collection('docs');

The collection owner is captured from SESSION_USER when the collection is created.

To associate a collection with an existing table, pass a schema-qualified table name:

CREATE TABLE public.docs (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  embedding vector
);

SELECT collection_id, collection_name, owner_name, table_schema, table_name
FROM pgcontext.create_collection('docs', 'public.docs');

The table must already exist and must be an ordinary or partitioned table. pgContext records catalog metadata only; it does not copy or own table data.

Inspect a Collection

SELECT collection_id, collection_name, owner_name, table_schema, table_name
FROM pgcontext.collection_info('docs');

table_schema and table_name are NULL for collections created without a source table.

Collection Aliases

Use collection aliases to route read traffic through a stable application-facing name while embedding model migrations prepare a replacement collection:

SELECT alias_name, collection_name
FROM pgcontext.create_collection_alias('docs_live', 'docs_v2');

Aliases are visible through the same collection ACL view used by search, query, scroll, count, facet, optimization status, telemetry, and diagnostics. Retarget an alias by calling create_collection_alias again with the same alias and a new target collection. List visible aliases with pgcontext.collection_aliases() and remove one with pgcontext.drop_collection_alias(alias_name).

Alias names use the same identifier rules as collections and cannot collide with real collection names. Creating a collection whose name is already an alias also uses SQLSTATE 42710 (duplicate_object). Point and catalog mutation APIs should use the real collection name so migrations remain explicit.

Configure Strict Limits

Collections can opt into strict-mode limits that reject operations before they would exceed collection-level budgets:

SELECT strict_mode,
       max_dimensions,
       max_vectors,
       max_points,
       max_filter_nodes,
       max_search_limit,
       max_candidate_budget,
       query_timeout_ms,
       max_index_memory_bytes
FROM pgcontext.configure_collection_limits(
  'docs',
  true,
  1536,
  1,
  1000000,
  128,
  100,
  1000,
  250,
  1073741824
);

Pass NULL for a limit that should keep the global pgContext policy. Inspect the current settings with pgcontext.collection_limits(collection_name).

Strict mode currently enforces vector dimensions, vector registration count, active point count before point upserts, search/scroll/facet result limits, and candidate-point batch budgets for candidate recheck search. Timeout, filter-node, and index-memory fields are stored as stable policy metadata for planners and diagnostics that consume those budgets. Invalid limit values use SQLSTATE 22023 (invalid_parameter_value); operations that exceed an enabled strict limit use SQLSTATE 54000 (program_limit_exceeded) before partial work is committed.

Register a Vector

Register a dense vector column before table-backed search can use it:

SELECT collection_name,
       vector_name,
       table_schema,
       table_name,
       vector_column,
       dimensions,
       metric
FROM pgcontext.register_vector('docs', 'embedding', 'embedding', 1536, 'cosine');

register_vector validates that the collection exists, has a source table, and that the named column currently exists on that table with type vector. Supported metric names are l2, inner_product, cosine, and l1.

The caller must have SELECT privilege on the source table when creating a table-backed collection and when registering vector columns. Collection mutation APIs run with pgContext catalog privileges but check the SQL session user against the stored collection owner. Non-owners cannot register vectors, map points, delete points, or drop the collection.

Missing tables use SQLSTATE 42P01 (undefined_table). Missing vector columns use SQLSTATE 42703 (undefined_column). Non-vector columns use SQLSTATE 42804 (datatype_mismatch). Invalid dimensions use SQLSTATE 22023 (invalid_parameter_value). Unsupported metrics use SQLSTATE 0A000 (feature_not_supported). Duplicate vector names or duplicate vector-column registrations use SQLSTATE 42710 (duplicate_object).

List registered dense vectors and their per-vector planner metadata with:

SELECT vector_name,
       vector_column,
       dimensions,
       metric,
       hnsw_options,
       quantization_options,
       status
FROM pgcontext.collection_vectors('docs');

The collection_vectors and configure_vector function contracts are experimental. Their HNSW and quantization option fields are validated metadata, but the serving semantics are outside the stable SQL compatibility promise until the HNSW and quantized-index parity rows graduate. PostgreSQL source tables and indexes remain authoritative. Newly registered vectors default to empty option objects and status ready.

Update the per-vector metadata with:

SELECT vector_name, hnsw_options, quantization_options, status
FROM pgcontext.configure_vector(
  'docs',
  'embedding',
  '{"m":16,"ef_search":64}'::jsonb,
  '{"mode":"scalar","levels":256}'::jsonb,
  'building'
);

Allowed status values are ready, building, disabled, and failed. Option values must be JSON objects. Missing vector registrations use SQLSTATE 42704 (undefined_object); invalid option shapes or status values use SQLSTATE 22023 (invalid_parameter_value). quantization_options may name metadata_version or version; future versions, unsupported modes, invalid scalar levels, and incompatible product-quantization dimensions are rejected with SQLSTATE 22023 instead of being stored silently.

Register a Sparse Vector

Register sparse-vector source columns when applications need collection-level metadata for lexical or sparse embedding branches:

SELECT collection_name,
       vector_name,
       table_schema,
       table_name,
       vector_column,
       dimensions,
       metric,
       storage_options,
       index_options,
       status
FROM pgcontext.register_sparse_vector('docs', 'lexical', 'sparse_terms', 4096, 'inner_product');

register_sparse_vector validates that the collection exists, has a source table, and that the named column currently exists on that table with type sparsevec. Supported sparse metric names are l2, inner_product, cosine, and l1. Sparse cosine rejects zero vectors because the distance is undefined.

List registered sparse vectors with:

SELECT vector_name,
       vector_column,
       dimensions,
       metric,
       storage_options,
       index_options,
       status
FROM pgcontext.collection_sparse_vectors('docs');

Update per-sparse-vector metadata with:

SELECT vector_name, storage_options, index_options, status
FROM pgcontext.configure_sparse_vector(
  'docs',
  'lexical',
  '{"format":"posting_lists"}'::jsonb,
  '{"strategy":"exact"}'::jsonb,
  'building'
);

Sparse metadata is experimental. It records first-class storage and index intent. Exact sparse top-k is available over explicit arrays and registered sparse source columns through pgcontext.search_sparse, and exact dense+sparse RRF fusion is available through pgcontext.query. Sparse ANN/index serving remains planned. Missing sparse columns use SQLSTATE 42703 (undefined_column). Non-sparsevec columns use SQLSTATE 42804 (datatype_mismatch). Unsupported sparse metrics use SQLSTATE 0A000 (feature_not_supported). Duplicate sparse vector names or duplicate sparse vector-column registrations use SQLSTATE 42710 (duplicate_object). Option values must be JSON objects, and status accepts ready, building, disabled, or failed.

Model Versions

Register embedding model versions used by a collection before planning migrations or shadow reads:

SELECT collection_name,
       model_name,
       model_version,
       dimensions,
       metric,
       is_active
FROM pgcontext.register_model_version(
  'docs',
  'text-embedding-3-small',
  '2026-07-02',
  1536,
  'cosine'
);

List registered model versions with:

SELECT collection_name,
       model_name,
       model_version,
       dimensions,
       metric,
       is_active
FROM pgcontext.model_versions();

Model names and versions are operator-defined labels. Supported metric names match vector registration. Duplicate model/version pairs use SQLSTATE 42710 (duplicate_object).

Embedding Migrations

Create a migration between two registered model versions before starting a backfill:

SELECT migration_id,
       collection_name,
       source_model,
       source_version,
       target_model,
       target_version,
       status,
       total_points,
       processed_points
FROM pgcontext.create_embedding_migration(
  'docs',
  'text-embedding-3-small',
  '2026-07-02',
  'text-embedding-3-large',
  '2026-07-02',
  100000
);

Update backfill progress with:

SELECT migration_id, status, total_points, processed_points
FROM pgcontext.update_embedding_migration(1, 25000, 'running');

List migrations with pgcontext.embedding_migrations(). status is the typed enum pgcontext."EmbeddingMigrationStatus" with Planned, Running, Completed, or Failed; update input uses lowercase planned, running, completed, or failed.

Register Filter Columns

Register ordinary source-table columns before using them in filters or facets:

SELECT collection_name, filter_key, table_schema, table_name, column_name
FROM pgcontext.register_filter_column('docs', 'tenant_id', 'tenant_id');

The filter_key is the user-facing JSON filter key. The column must exist on the collection source table, and duplicate keys or duplicate source columns use SQLSTATE 42710 (duplicate_object).

Register a JSONB path when the filter or facet value lives inside a JSONB source-table column:

SELECT collection_name, filter_key, table_schema, table_name, column_name, jsonb_path
FROM pgcontext.register_jsonb_path('docs', 'topic', 'metadata', ARRAY['topic']);

The JSONB column must exist and have type jsonb. Missing JSONB path values do not appear in facet counts.

Mutate Registered Payload Fields

Payload mutation helpers update user source tables directly, so pgContext keeps the writable surface explicit:

  • only collection owners can mutate payloads;
  • the session user must have UPDATE privilege on the source table;
  • source keys must already be active pgContext points and must still resolve to source table rows;
  • payload keys must be registered with register_filter_column or register_jsonb_path;
  • ordinary-column deletes and clears are allowed only for nullable columns.

Set registered payload keys with a JSON object:

SELECT source_key, updated
FROM pgcontext.set_payload(
  'docs',
  ARRAY['42'],
  '{"status":"published","topic":"postgres"}'::jsonb
);

For ordinary registered columns, pgContext converts JSON scalar values through the target SQL type for text, boolean, integer, floating, numeric, and JSONB columns. For registered JSONB paths, set_payload writes the value with jsonb_set(..., create_missing := true).

Delete selected registered keys:

SELECT source_key, updated
FROM pgcontext.delete_payload('docs', ARRAY['42'], ARRAY['topic']);

Clear all registered payload fields for selected points:

SELECT source_key, updated
FROM pgcontext.clear_payload('docs', ARRAY['42']);

Unknown payload keys use SQLSTATE 22023 (invalid_parameter_value). Missing collections or source keys use SQLSTATE 42704 (undefined_object). Attempts to clear non-null ordinary columns use SQLSTATE 23502 (not_null_violation).

Map Points

Point mappings connect application source-row keys to stable pgContext point IDs. pgContext stores only this mapping; source rows and vectors remain in the registered PostgreSQL table.

SELECT point_id, source_key, inserted
FROM pgcontext.upsert_points('docs', ARRAY['doc-1', 'doc-2']);

inserted is true for a new mapping and false when an existing mapping was reactivated or refreshed. Source keys must be non-empty text values no larger than the current catalog policy limit.

Delete point mappings with:

SELECT point_id, source_key
FROM pgcontext.delete_points('docs', ARRAY['doc-2']);

Deletes are logical. The source table is not modified, and a later upsert_points call for the same source key reuses the same point ID. Missing source keys are ignored by delete_points.

For large point sets, use the bulk progress APIs with an explicit batch size:

SELECT batch_number, processed_count, inserted_count, reactivated_count
FROM pgcontext.bulk_upsert_points(
  'docs',
  ARRAY['doc-1', 'doc-2', 'doc-3'],
  1000
);

SELECT batch_number, processed_count, deleted_count, missing_count
FROM pgcontext.bulk_delete_points('docs', ARRAY['doc-2', 'missing'], 1000);

bulk_upsert_points validates the whole supplied source-key array before any point rows are written, so malformed input fails without a partial insert. bulk_delete_points keeps the same logical-delete and missing-key behavior as delete_points, but returns per-batch counts for operational progress.

Backfill point mappings directly from the registered source table’s id column:

SELECT batch_number, processed_count, inserted_count, reactivated_count
FROM pgcontext.backfill_points('docs', 1000);

Backfill reads source rows as the invoking role, so source-table SELECT privileges and row-level security policies apply. Each batch is bounded by the requested batch size, reports progress as rows, and uses normal transaction semantics for cancellation and rollback.

Search a Table-Backed Collection

The current table-backed exact search path uses the source table’s id column as the stable source key. Point source_key values must match id::text.

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

Search uses the collection’s registered vector column and metric, skips logically deleted points, orders by score and then point ID, and rechecks the registered table, source-key column, vector column, ownership, and table privileges at execution time.

Pass Qdrant-style filter JSON to restrict candidates before exact scoring:

SELECT point_id, source_key, score
FROM pgcontext.search(
  'docs',
  '[0,0]'::vector,
  '{"must":[{"key":"tenant_id","match":"acme"}]}',
  10
);

Filter-first search uses registered filter columns and JSONB paths, materializes matching active point mappings as the candidate set, and then scores only those source rows. Unregistered filter fields use SQLSTATE 22023 (invalid_parameter_value). Partitioned source-table parents are supported; filter predicates remain ordinary SQL predicates, so PostgreSQL can apply its normal partition pruning rules. Search, scroll, count, facet, and hybrid query functions read source tables as the invoking role, so PostgreSQL row-level security policies on the source table are enforced during SQL recheck.

Candidate recheck search accepts a batch of point IDs, such as point IDs returned by a future ANN scan, and performs the same table-backed exact scoring only for active mappings in that batch:

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

Pass a filter before the candidate array to recheck only candidates that still match the same registered-field predicate plan used by filtered exact search, count, and facets:

SELECT point_id, source_key, score
FROM pgcontext.search(
  'docs',
  '[0,0]'::vector,
  '{"must":[{"key":"tenant_id","match":"acme"}]}',
  ARRAY[1, 2, 3]::bigint[],
  10
);

Query with Dense and Full-Text Fusion

Hybrid query combines the collection’s registered dense vector branch with a full-text branch over a source-table text column:

SELECT point_id, source_key, score
FROM pgcontext.query('docs', '[0,0]'::vector, 'database internals', 'body', 10);

The text column is passed by name and validated against the registered source table at execution time. Results use reciprocal rank fusion and skip logically deleted point mappings in both branches.

Inspect the retrieval stages with:

SELECT stage, detail
FROM pgcontext.explain('docs', 'body');

Scroll Point Mappings

Scroll active point mappings in stable point-ID order:

SELECT point_id, source_key, next_cursor
FROM pgcontext.scroll('docs', NULL, 100);

Pass the last returned next_cursor to continue from the next point:

SELECT point_id, source_key, next_cursor
FROM pgcontext.scroll('docs', 'v1:...', 100);

Cursor tokens are opaque. They are bound to the collection catalog id and last returned point id, and malformed, tampered, or stale collection cursors use SQLSTATE 22023 (invalid_parameter_value).

Count Point Mappings

Count active point mappings, optionally with the same registered-field filter JSON used by filtered search and facets:

SELECT pgcontext.count('docs');

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

Counts skip logically deleted point mappings, apply source-table SELECT privileges and row-level security, and reject unregistered filter fields with SQLSTATE 22023 (invalid_parameter_value).

Facet Point Mappings

Facet active point mappings by a registered ordinary column, with an optional Qdrant-style filter JSON string:

SELECT value, count
FROM pgcontext.facet(
  'docs',
  'status',
  '{"must":[{"key":"tenant_id","match":"acme"}]}',
  10
);

This slice supports ordinary source-table columns. Facets skip logically deleted points and NULL facet values, order by descending count then value, and reject unregistered filter or facet fields with SQLSTATE 22023 (invalid_parameter_value).

Group Search Results

Group exact vector search results by a registered ordinary column or JSONB path:

SELECT group_value, point_id, source_key, score
FROM pgcontext.grouped_search('docs', '[0,0]'::vector, 'tenant_id', 2, 10);

Grouped search applies the same collection ownership, source-table SELECT, row-level security, active-point, deleted-point, and registered payload-field rules as exact search and facets. group_limit caps rows per group; limit caps the final result set. Rows with NULL or missing group values are skipped, and ties are deterministic by score then point id.

Recommend Similar Points

Recommend points from positive and negative point examples:

SELECT point_id, source_key, score
FROM pgcontext.recommend('docs', ARRAY[101, 205], ARRAY[309], 10);

Positive and negative point IDs must be active and visible through the registered source table. Example point IDs are excluded from the result set, deleted points are rejected, and results use the collection’s registered vector metric with deterministic score then point-id ordering.

Raw vectors can be supplied instead when the application has example embeddings that are not already mapped to point IDs:

SELECT point_id, source_key, score
FROM pgcontext.recommend(
  'docs',
  ARRAY['[0.1,0.2,0.3]'::vector],
  ARRAY[]::vector[],
  10
);

Discover Diverse Points

Discover or explore points that are farthest from active context point IDs:

SELECT point_id, source_key, score
FROM pgcontext.discover('docs', ARRAY[101, 205], 10);

pgcontext.explore is an alias for the same exact diversity search. Context point IDs must be active and visible through the registered source table, are excluded from results, and deleted context points are rejected.

Drop a Collection

SELECT pgcontext.drop_collection('docs');

drop_collection returns true when a catalog entry was removed and false when the collection did not exist. Dropping a collection also removes its pgContext vector registrations and point mappings. It does not drop or alter the source table.