PostgreSQL Nodes

Query and change PostgreSQL tables from a pipeline, load pipeline data into them with automatic column mapping, and store and search embeddings with pgvector.

Operations

Query · Execute · Write · Vector Search · Vector Upsert

Limits

50,000 rows or 40 MB per query

PostgreSQL is a production-grade relational database with advanced features. MaestroHub supports queries, inserts, updates, and complex transactions using connector nodes.

Configuration Quick Reference

Field	What you choose	Details
Parameters	Connection, Function, Function Parameters, Timeout Override	Select the connection profile, function, configure function parameters with expression support, and optionally override timeout.
Settings	Description, Timeout (seconds), Retry on Timeout, Retry on Fail, On Error	Node description, maximum execution time, retry behavior on timeout or failure, and error handling strategy. All execution settings default to pipeline-level values.

Node Types

PostgreSQL connector provides five specialized node types for different operation patterns:

Node	Purpose	Common Use Cases
Query	Execute SELECT queries and return structured rows	Reporting dashboards, data lookups, KPI calculations
Execute	Execute DML/DDL statements, return rows affected	INSERT/UPDATE/DELETE statements, schema changes, stored procedures
Write	Load pipeline data into a table with auto schema detection	Bulk sensor ingestion, auto-create tables, data loading from upstream nodes
Vector Search	Return the rows whose embedding is nearest to a query vector (pgvector)	Answer from manuals and work orders, find similar past events
Vector Upsert	Insert or replace rows with their embeddings, by id (pgvector)	Index document chunks from an embedding node, re-ingest a revised manual

---

Zoom

PostgreSQL Query Node

PostgreSQL Query Node

Execute SQL SELECT queries and return rows as structured data. Supports parameterized queries with $1 style placeholders.

Supported Function Types:

Function Name	Purpose	Common Use Cases
Query	Run parameterized SELECT queries against PostgreSQL	Reporting dashboards, data lookups, aggregated KPI calculations

---

Zoom

PostgreSQL Execute Node

PostgreSQL Execute Node

Execute DML/DDL statements (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP) and return rowsAffected. Use for data modifications and schema changes.

Supported Function Types:

Function Name	Purpose	Common Use Cases
Execute	Run any DML/DDL statement or stored procedure call	Data modifications, schema migrations, maintenance tasks

---

Zoom

PostgreSQL Write Node

PostgreSQL Write Node

Load pipeline data into a PostgreSQL table. Write reads the table's columns and maps the incoming data fields to them, in batches of Batch Size rows. With Create Table If Not Exists on, it creates a missing table first. With Allow Schema Evolution on, it adds a column when the data carries a field the table has no column for. See the Write Function for its fields.

Supported Function Types:

Function Name	Purpose	Common Use Cases
Write	Load structured data into a PostgreSQL table	Bulk sensor ingestion, auto-create tables from pipeline data, data loading

---

Zoom

PostgreSQL Vector Search Node

PostgreSQL Vector Search Node

Return the Top K rows whose embedding is nearest to a query vector, closest first, from a table with a pgvector vector column. The vector usually comes from an embedding node earlier in the pipeline, such as Vertex AI Embed Content (((embedding))); the node never calls an embedding model itself. With a Metadata Filter, only rows whose metadata column contains it are searched. See Vector search with pgvector for the table, the index and the fields.

The search runs in PostgreSQL: ORDER BY distance with a LIMIT, and the filter in the WHERE clause, with the vector, the filter and Top K sent as bound parameters. It raises the HNSW index's search width to Top K and, with a filter, turns on iterative index scans, so an indexed table returns the full Top K when that many rows match.

Every row carries distance, and lower is always closer:

Metric	Operator	distance is
cosine	<=>	the cosine distance, 0 (same direction) to 2 (opposite)
l2	<->	the Euclidean distance
innerProduct	<#>	the negative inner product, so the largest inner product comes first

Supported Function Types:

Function Name	Purpose	Common Use Cases
Vector Search	Find the nearest rows to a vector, optionally filtered by metadata	Answer questions from manuals, similar incidents, nearest past batches

---

Zoom

PostgreSQL Vector Upsert Node

PostgreSQL Vector Upsert Node

Store rows that each carry an id, an embedding (an array of numbers) and any other columns, such as content and a metadata object. A row whose id is already in the table is replaced: the columns the row carries are overwritten and the ones it leaves out keep their value. Sending the same rows again leaves the same rows, so re-ingesting a revised document replaces its chunks instead of duplicating them, and the node is safe for store-and-forward to resend. With Create Table If Not Exists on, a missing table is created with the id as primary key and a vector(n) column; no index is ever created. See Vector search with pgvector.

Each row is checked before anything is sent: a row with no id, no embedding, a value that is not a number, or a length different from Dimensions is refused on its own and the others still go. Rows go in statements of at most Batch Size, in order; when one fails in a way a retry may fix, the ones after it are not sent, and a resend skips the rows that landed.

Supported Function Types:

Function Name	Purpose	Common Use Cases
Vector Upsert	Insert or replace rows with their embeddings by id	Index manual chunks, keep an event library current

Output

Every PostgreSQL node delivers its data under result, and execution facts (success, functionId, durationMs, timestamp) under _metadata:

Node	Expression	Description
Query	$node["Name"].result.rows	The result rows: one object per row keyed by column name, up to the result limits (the 50,000-row cap and the 40 MB size budget by default). $node["Name"].result.rows[0].<column> reads a value
	$node["Name"]._metadata.truncated	true when the query hit a result limit and rows holds only what fit: the first 50,000 rows at the default cap, or fewer when the size budget tripped first
	$node["Name"]._metadata.truncatedBy	Which limit cut the result: rows or bytes. Present only when truncated is true
	$node["Name"].result.rowCount	How many rows were delivered, the same as result.rows.length
Execute	$node["Name"].result.rowsAffected	Rows the statement affected, as the driver reports it
Write	$node["Name"].result.rowsInserted	Rows inserted across every batch of this execution
Vector Search	$node["Name"].result.rows	The nearest rows, closest first: the Return Columns plus distance (lower is closer). A jsonb column such as metadata arrives as an object. $node["Name"].result.rows[0].content reads the closest row's text
	$node["Name"].result.rowCount	How many rows came back, the same as result.rows.length; fewer than Top K when fewer rows match
Vector Upsert	$node["Name"].result.rowsUpserted	Rows inserted or replaced by this call
	successCount and failureCount	Beside rowsUpserted, the platform counts how many of the given rows landed and how many were refused; the node's error names each refused row (rows[3]: "embedding" has 767 numbers, not 768)

The call's own facts ride along under _metadata next to the four every connected node carries. A Query delivers $node["Name"]._metadata.driver (the driver name), $node["Name"]._metadata.query (the SQL that ran) , $node["Name"]._metadata.truncated (true when the query hit a result limit and the rows were cut short) and, only then, $node["Name"]._metadata.truncatedBy (rows or bytes: which limit did it). An Execute delivers _metadata.driver and _metadata.query. A Write delivers _metadata.driver, _metadata.table, _metadata.batchSize, _metadata.totalRows (the rows it was given) and _metadata.matchedColumns (the table columns the data was mapped onto), plus _metadata.skippedFields when the data carried fields the table has no column for, or _metadata.schemaEvolution when schema evolution is on and the write added them as columns; when it created the table first, it delivers _metadata.tableCreated and the _metadata.columns it created instead of matchedColumns. A Vector Search delivers _metadata.driver, _metadata.table, _metadata.metric, _metadata.topK, _metadata.efSearch (the HNSW search width it set), _metadata.iterativeScan (true when a filter turned iterative index scans on) and _metadata.truncated. A Vector Upsert delivers _metadata.driver, _metadata.table, _metadata.rowCount (the rows it was given), _metadata.batchSize, _metadata.dimensions and _metadata.tableCreated, plus _metadata.skippedFields when rows carried fields the table has no column for.

Check truncated before you aggregate

A query stopped at the row limit returns a shorter rows array that looks exactly like a complete result. Nothing fails and nothing warns, so a downstream sum, average or count is silently wrong. The limits are the connectors module's queryResultMaxRows (50,000 rows by default) and queryResultMaxBytes (40 MB by default), whichever trips first, which is why _metadata.truncated is delivered on every query: branch on it, or narrow the query, rather than assuming the read was complete; _metadata.truncatedBy says which limit did it.