Skip to main content
View source

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 inLane outDescription
documents—Store embedded document chunks.
questionsdocumentsReturn matching documents.
questionsanswersReturn matching documents as answers.
questionsquestionsEnrich questions with matching documents.

As a tool​

The configured tool-server name defaults to postgres.

FunctionDescription
searchSearches the store for a non-empty query; accepts optional top_k and metadata filter, and returns matching content, metadata, and scores.
objdirLists the distinct parent paths in the table as a map of parent path to one object ID each; accepts optional parent, offset, and limit (default and cap 1000), and pages count paths. Neither listing has an index to use, so every objdir and list_documents page scans the whole table (list_documents a second time for total); ask for large pages.
list_documentsLists the documents one by one, ordered by object ID, each with object_id, parent, and chunk_count; accepts optional parent (exact match), deleted (true lists the trash), offset, and limit (default 100, cap 1000). The reply carries total, offset, and has_more.
getReturns up to limit chunks (default and cap 100) of one document by object_id, ordered by chunk ID, with total, has_more, and truncated. Each call reads at most 10,000 chunks; total counts fetched chunks and has_more refers only to that fetched set. When truncated=true, completeness is not guaranteed, even if has_more=false. An object ID the table does not hold, or one in the trash, returns a failure object.
statsReturns the row (chunk) count of the table including its internal schema row and soft-deleted chunks, the number of active documents (document_count), the embedding model name, and the vector size.
upsertAdds or replaces 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. The chunks sent for one object ID are numbered in the order given unless each carries its own chunkId, which may not repeat within the call.
deleteDeletes the chunks of a non-empty object_ids array for good; the reply echoes the distinct IDs and deleted_count, their number (not the documents found).
soft_deleteMoves the documents of a non-empty object_ids array to the trash, keeping their chunks; the reply echoes the distinct IDs and matched_chunks, the chunk rows the table held for them.
restoreTakes the documents of a non-empty object_ids array out of the trash, with the same reply as soft_delete. A document removed with delete cannot be restored.

search requires a bound embedding provider for semantic similarity search. The functions return a failure object when their required input or an embedding cannot be obtained, and refuse the object ID schema, which names the table's control row. The write functions (upsert, delete, soft_delete, restore) are offered only while the node's allow_write config key is on; it is on by default and not a declared setting on this node, so writes to the whole table are offered unless a pipeline sets allow_write: false on the node. The expose_tools config key, which decides whether the node offers any function at all, is likewise on by default and undeclared; a pipeline sets expose_tools: false on the node to offer none.

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; nodeId defaults to "vectordb_tool", parent to "/", and chunkId to the document's position among the chunks sent for its object ID in the call (0 for the first), and an explicit chunkId may not repeat within the call.

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 labels this node in the messages the tool functions return. Agents and apps address the functions by the node's pipeline id, which keeps several PostgreSQL stores apart.

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. A batch that holds an object's chunk 0 replaces that object's existing rows with the new chunks in one transaction, so a re-ingest replaces the object; a batch without chunk 0 adds its chunks to the stored ones, so an object written in several batches keeps them all. Rows get generated IDs, so a batch without chunk 0 that repeats a chunk number already stored keeps both copies; to change a chunk, write the object again from chunk 0. 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.

This node does not create a vector index. If an administrator adds HNSW, it uses the same PgVectorStoreBase search as rocketride_vector: on pgvector 0.8+, a transaction-local iterative scan continues past deleted or excluded neighbours, within the server's scan and memory budgets. If no matches meet the score threshold and 0.20 minimum floor, an exact retry checks for hidden active documents. Nonempty results remain approximate and do not trigger a retry merely because they fall short of the requested limit. On older pgvector versions, underfilled results also retry. Unindexed tables avoid that redundant retry, and an unbounded request uses exact search directly. Exact retries can be expensive on large tables, including queries that genuinely have no matches.

The shared base serializes access to each connection. Chunk replacements and iterative searches use explicit transactions; other statements autocommit, so completed reads do not hold open transactions between calls.

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.

When a batch holds an object's chunk 0, the runtime removes that object's existing rows and inserts the batch in one transaction. If the write fails, the transaction rolls back and the previous rows remain in place.

Upstream docs​

Schema​

FieldTypeDescriptionDefault
postgres.profilestringType of PostgreSQL host
Connect to...
"local"
postgres.providerstringconst: "postgres"
postgres.serverNamestringTool Server Name
Label for this store in tool-response messages (for example, a refused write names postgres.delete). Pipeline agents address the available functions as node-id.function; apps use the function name and node ID. Changing this label does not change those addresses. See the node README for the available functions and their tool-exposure and write-permission settings.
"postgres"
vector.collectionstringTable
Name of the table to store vectors.
"rocketride"
vector.local.databasestringDatabase
Name of the database
"postgres"
vector.local.hostHost
Host name or IP address of the PostgreSQL server
"your-postgres-host.example.com"
vector.local.passwordstringPassword
Password to connect to the PostgreSQL server
vector.local.portPort
Port number of the PostgreSQL server
5432
vector.local.userstringUser
User to connect to the PostgreSQL server
"postgres"
vector.similaritystringSimilarity Metric
The similarity metric to use for vector search
"cosine"

Dependencies​

  • psycopg2-binary
  • pgvector