PostgreSQL (pgvector)

View as Markdown

A RocketRide vector-store node that stores embedded document chunks in PostgreSQL with pgvector and retrieves them by semantic or keyword search. Use it when a pipeline or agent needs vector retrieval in a PostgreSQL database.

About PostgreSQL

PostgreSQL is a relational database that stores structured data and supports SQL queries. The pgvector extension adds a vector data type and vector-distance operators to PostgreSQL. This combination is useful when vector retrieval should live beside the relational data and operational practices a team already uses.

What it does

The node accepts embedded documents on its documents lane and can retrieve matching documents for incoming questions. It also exposes the store to an agent as tools. The target table is created when documents are first added, and semantic retrieval requires question embeddings. Pick it over a dedicated vector database when the corpus should remain in a PostgreSQL database and the operator can provide pgvector there.

The created table contains content, document metadata, the embedding, its model name, and its vector size. That makes one table the store for both vector retrieval and keyword lookup, while the configured database retains PostgreSQL's normal connection and access controls.

Lanes

Lane in	Lane out	Description
documents	—	Store embedded document chunks.
questions	documents	Return matching documents.
questions	answers	Return matching documents as answers.
questions	questions	Enrich questions with matching documents.

As a tool

The configured tool-server name defaults to postgres.

Function	Description
search	Searches the store for a non-empty query; accepts optional top_k and metadata filter, and returns matching content, metadata, and scores.
upsert	Adds or updates a non-empty documents array. Each document requires content and an object ID; it can provide an embedding and embedding model or use the bound embedding provider.
delete	Deletes documents for a non-empty object_ids array and returns the deleted count.

search requires a bound embedding provider for semantic similarity search. The three functions return a failure object when their required input or an embedding cannot be obtained.

search accepts an optional filter object honoring only objectId, nodeId and parent; any other key is rejected. upsert accepts an optional metadata object storing nodeId, parent and chunkId, defaulting to "vectordb_tool", "/" and 0 respectively.

Configuration

Enter the PostgreSQL connection details and choose the table that will hold the chunks. The local profile supplies the initial values, including a retrieval score of 0.5 and cosine similarity. Start by confirming that these credentials reach a database where the vector type is available; the save-time probe opens a short-lived connection, runs SELECT 1, and tests that type before this node is used in a pipeline.

Connection and database

Host, port, user, password, and database are passed directly to the PostgreSQL client. Use a dedicated database or role when you want the retrieval corpus to have separate ownership from other application tables. Connection or permission errors during the save-time probe point to these fields; an error about the vector type means pgvector is not available in the selected database.

Table

The Table field names the table used for vectors; its default is rocketride. It must be a valid unquoted PostgreSQL identifier: at most 63 characters, starting with a letter or underscore, and thereafter containing only letters, digits, or underscores. The driver also accepts the legacy collection key, but the panel uses Table.

Change the table when a corpus needs its own physical store. The runtime uses the name in its SQL statements and does not quote it, which is why punctuation, spaces, and a leading digit are rejected instead of being escaped. A valid new name produces a new table at first ingest; it does not migrate rows from the old table.

Similarity Metric

Choose cosine, l2, or inner_product; the runtime maps them to PostgreSQL's vector-distance operators and rejects any other value. Use the setting that matches how the vectors are meant to be compared, because it determines ranking for semantic searches. cosine maps to <=>, l2 to <->, and inner_product to <#>.

The retrieval score defaults to 0.5, and the driver always removes results whose converted score is below its hard 0.20 floor. Raise the configured threshold to give downstream prompts fewer, more selective documents; lower it when relevant documents are missing. The conversion differs by metric—cosine uses 1 - distance, L2 uses 1 / (1 + distance), and inner product negates the returned distance—so do not carry a score threshold across a metric change without retesting the retrieval quality.

Retrieval and embedding compatibility

The first write creates the table with a vector column sized for its incoming embedding. Later semantic searches check the requested embedding model against the existing collection before executing. Keep documents produced by compatible embedding configurations in the same table; a model or vector-size mismatch is a sign to create or select a separate table, rather than to treat the failure as an ordinary relevance-tuning issue.

Tool Server Name

The tool-server name defaults to postgres and prefixes the three agent functions. Change it when multiple PostgreSQL stores are connected to the same agent so their functions do not share a namespace.

Authentication

Provide the configured PostgreSQL host, port, user, password, and database. The save-time probe opens a PostgreSQL connection with those values and verifies that the database supports the vector type.

Notes

Search and document lifecycle

Semantic search requires an embedding. Keyword search performs a LIKE match on content with the metadata filters. Re-ingesting an object deletes its existing rows before replacement chunks are inserted. The store can mark chunks deleted or active, and its default filters exclude marked-deleted chunks.

Semantic search orders by the chosen pgvector distance operator and checks that the stored embedding model is compatible before searching. Unlike the other paths, it cannot take a non-zero result offset. This matters for callers that try to paginate semantic results: use a limit or a separate retrieval strategy instead of expecting SQL-style offset pagination.

The metadata filters are translated to SQL predicates for node, parent, permissions, object IDs, chunk IDs, table data, and deletion state. Normal searches exclude marked-deleted rows. Use those filters to narrow one shared table to an eligible corpus; changing the table name is for an independently managed store, not the routine way to restrict a query.

Rendering

Rendering retrieves an object's chunks in chunkId order and sends joined text to the callback in renderChunkSize groups. The node's document count is a count of rows in the table.

The runtime removes an object's existing rows and commits that deletion before it inserts the replacement chunks. Retain a source of truth that can be ingested again: if a replacement write fails, the previous rows have already been removed rather than preserved as a fallback copy.

Upstream docs

• PostgreSQL documentation
• pgvector documentation

Schema

Field	Type	Description	Default
postgres.profile	string	Type of PostgreSQL host Connect to...	"local"
postgres.provider	string		const: "postgres"
postgres.serverName	string	Tool Server Name Namespace for agent-facing tool names, e.g. 'postgres' exposes tools as postgres.search / postgres.upsert / postgres.delete. Change this when running multiple Postgres nodes in the same pipeline so their tool names do not collide.	"postgres"
vector.collection	string	Table Name of the table to store vectors.	"rocketride"
vector.local.database	string	Database Name of the database	"postgres"
vector.local.host		Host Host name or IP address of the PostgreSQL server	"your-postgres-host.example.com"
vector.local.password	string	Password Password to connect to the PostgreSQL server
vector.local.port		Port Port number of the PostgreSQL server	5432
vector.local.user	string	User User to connect to the PostgreSQL server	"postgres"
vector.similarity	string	Similarity Metric The similarity metric to use for vector search	"cosine"

Dependencies

• psycopg2-binary
• pgvector