Separate tables for collections and embeddings in `PGVectorStore`

[ezequiel-tcmrio]

Currently, PGVectorStore creates a single table to store embeddings and their metadata.
In some scenarios, it would be useful to have a more explicit relational structure, where:

• A collections table defines the collections (name, description, id, global metadata);
• An embeddings table stores the vectors and references the collection via a foreign key (collection_id).

This would make it easier to manage and organize multiple embedding collections in the same PostgreSQL database, especially in multi-user or multi-application contexts.

Question

Is there currently any native way in langchain_postgres to configure PGVectorStore to:

• Automatically create a table for collections (collections) and
• Associate each embedding (embeddings) to a collection via collection_id?

If not, is there any recommended workaround to achieve this separation?

Example desired schema

CREATE TABLE collections (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    collection_id INT REFERENCES collections(id),
    embedding VECTOR(1536),
    metadata JSONB,
    document TEXT
);

Context

I’m building a document ingestion system where each client has its own collection.
I want to avoid creating multiple embeddings tables — instead, I’d like to keep a single embeddings table and a separate collections table to simplify filtering and access control.

Environment

langchain_postgres version: 0.0.16
PostgreSQL version: 15
pgvector extension: installed ✅
Python: 3.11

Optional suggestion

A possible implementation could allow something like:

from langchain_postgres import PGVector

vectorstore = PGVector(
    connection_string="postgresql://user:pass@localhost/db",
    collection_table="collections",
    embedding_table="embeddings",
    create_schema=True,
)

This way, LangChain would create (or use) the tables as needed and maintain the relationship via collection_id.

If this feature exists, I would appreciate your assistance in implementing it.

[rmlekus]

@ezequiel-tcmrio This is how PGVector implemented the mapping of embeddings and collections to a Schema into the tables

CREATE TABLE public.langchain_pg_collection (
	"uuid" uuid NOT NULL,
	"name" varchar NOT NULL,
	cmetadata json NULL,
	CONSTRAINT langchain_pg_collection_name_key UNIQUE (name),
	CONSTRAINT langchain_pg_collection_pkey PRIMARY KEY (uuid)
);

CREATE TABLE public.langchain_pg_embedding (
	id varchar NOT NULL,
	collection_id uuid NULL,
	embedding public.vector NULL,
	"document" varchar NULL,
	cmetadata jsonb NULL,
	CONSTRAINT langchain_pg_embedding_pkey PRIMARY KEY (id),
	CONSTRAINT langchain_pg_embedding_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES public.langchain_pg_collection("uuid") ON DELETE CASCADE
);
CREATE INDEX ix_cmetadata_gin ON public.langchain_pg_embedding USING gin (cmetadata jsonb_path_ops);

PGVector got deprecated as of version 0.0.14+, though, see the warning in the Vector Store section of the README.MD

I would likt to add further reasons to continue re-enable the optional support for the older Table Layout, though:

When creating collections dynamically at run-time (typically small ( 101 to 103) number for embeddings per collection but potentially > 10**4 collections (e.g. documents associated with single authenticated agent sessions), the 1 Table per collection design is running into 2 impediments:

1. The database user used at run-time needs the right to create tables at least on the db(s)+schema(s) to be used in production
2. These database schemas are fille with potentially 10*? tables for the collections to be created.
3. In case of any subsequent DDL changes to the collection/embedding tables all these schemas need to be migrated manually, too

While the old PGVector DDL schema needed to be extended with further database indices, such as in some of my use-cases

CREATE UNIQUE INDEX ix_langchain_pg_embedding_id ON public.langchain_pg_embedding USING btree (id);

in order to support vectorstore queries with JSON filters to be applied, with such indices supporting the query execution planner to start with filtering on the collection name, followed by filtering on the JSON meta-data (which could also be supported with additional JSONB indices, e.g. as shown in https://neon.com/postgresql/postgresql-indexes/postgresql-json-index#4-creating-an-index-on-a-specific-field-of-a-jsonb-column.

Furthermore a 2 table schema also eases up any statistic queries or linking embedding db indices with LLM/Embedding Model Observability Platforms.

[averikitsch]

PGVector is deprecated but you are still able to utilize the class and the functionality. We highly recommend not using multiple table format (using collections ids) due to the increased latency of the filtering required.