Separate tables for collections and embeddings in PGVectorStore #97

New Issue

2026-02-16T05:16:32-05:00

yindo commented

2026-02-16 05:16:32 -05:00

Originally created by @ezequiel-tcmrio on GitHub (Nov 4, 2025).

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.

Originally created by @ezequiel-tcmrio on GitHub (Nov 4, 2025). 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 ```sql 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: ```python 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.

yindo commented

2026-02-16 05:16:32 -05:00

@rmlekus commented on GitHub (Nov 14, 2025):

@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:

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
These database schemas are fille with potentially 10*? tables for the collections to be created.
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.

@rmlekus commented on GitHub (Nov 14, 2025): @ezequiel-tcmrio This is how PGVector implemented the mapping of embeddings and collections to a Schema into the tables ```SQL 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](https://github.com/langchain-ai/langchain-postgres/?tab=readme-ov-file#vectorstore) **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 ( 10**1 to 10**3) 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 ```SQL 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.

yindo commented

2026-02-16 05:16:32 -05:00

@averikitsch commented on GitHub (Nov 14, 2025):

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.

@averikitsch commented on GitHub (Nov 14, 2025): 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.

yindo referenced this issue

2026-02-16 05:16:49 -05:00

[PR #97] [MERGED] fix: use self.embeddings.aembed_* in async methods #163

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: langchain-ai/langchain-postgres#97

Separate tables for collections and embeddings in PGVectorStore #97

Question

Example desired schema

Context

Environment

Optional suggestion

Separate tables for collections and embeddings in `PGVectorStore` #97