Enterprise-grade Node-RED nodes for PostgreSQL + pgvector with OpenTelemetry observability, structured logging, and production-ready resilience.
npm install @nagual69/node-red-pgvectorsql
CREATE EXTENSION vector;
`
Install
From the Node-RED palette manager, search for @nagual69/node-red-pgvector and click install.
Or install via npm in your Node-RED user directory (typically ~/.node-red):
`bash
npm install @nagual69/node-red-pgvector
`
Restart Node-RED after installation.
Nodes
$3
Connection pooling configuration node for PostgreSQL. Configure once and reuse across all pgvector nodes.
Configuration:
- Host, port, database name
- User credentials (stored securely)
- SSL options
- Connection pool size
$3
Run arbitrary SQL queries against PostgreSQL.
Input:
- msg.sql or msg.topic - SQL query string
- msg.params - Array of query parameters (optional)
Output:
- msg.payload - Array of result rows
- msg.count - Number of rows returned
$3
Insert rows with vector embeddings into a table.
Input:
- msg.payload - Single record or array of records with vector property
- msg.table - Target table name (optional, can be configured)
- msg.column - Vector column name (optional, can be configured)
Example:
`javascript
msg.payload = {
vector: [0.1, 0.2, 0.3, ...],
metadata: { title: "Document 1" }
};
`
$3
Upsert (insert or update) rows with vector embeddings.
Input: Same as pgvector-insert
- Uses ON CONFLICT to update existing rows
$3
Perform similarity search using various distance metrics.
Input:
- msg.payload.vector or msg.vector - Query embedding (array of floats)
- msg.filter - Object with key=value filters
- msg.where - Additional SQL WHERE clause
- msg.limit - Maximum results (default: 10)
- msg.metric - Distance metric: cosine, l2, or inner-product
- msg.normalize - Normalize vector before search (boolean)
Vector formats supported:
- Float arrays: [0.1, 0.2, 0.3]
- JSON strings: "[0.1, 0.2, 0.3]"
- Comma-separated: "0.1, 0.2, 0.3"
- Base64-encoded Float32Array
Output:
- msg.payload - Array of similar records with similarity scores
Example:
`javascript
msg.payload = {
vector: [0.1, 0.2, 0.3, ...]
};
msg.filter = { category: "tech" };
msg.limit = 5;
`
$3
Inspect database schema to find tables and vector columns.
Output:
- msg.payload - Schema information including vector column dimensions
$3
Administrative operations for pgvector setup and index management.
Actions:
- create-extension - Install pgvector extension
- create-table - Create table with vector column
- create-ivfflat - Create IVFFlat index for approximate nearest neighbor search
- create-hnsw - Create HNSW index for approximate nearest neighbor search
- set-probes - Configure IVFFlat search quality
- drop-index - Remove an index
Example Flows
Ready-to-use example flows are provided in the examples/ directory. All flows are tested with the included Docker environment.
$3
The most thoroughly tested example with four separate tabs:
- Setup Database: Create extension, table, and HNSW index
- Insert Embeddings: Bulk insert articles with 384-dim vectors
- Similarity Search: Find similar articles using cosine distance
- Custom Queries: Run arbitrary SQL queries
To import: Menu ā Import ā select examples/sample-flows.json
$3
- complete-example.json - Single-tab end-to-end workflow with products table
- basic-flows.json - Minimal starter template for quick prototyping
See examples/README.md for detailed usage instructions.
Quick Start
1. Add Configuration Node
- Drag a pgvector-search node to the canvas
- Click the pencil icon next to "Connection"
- Configure your PostgreSQL host, database, and credentials
2. Setup Database
- Add a pgvector-admin node
- Set action to "create-extension"
- Deploy and trigger to install pgvector
- Add another admin node with "create-table" action
- Configure table name (e.g., "embeddings"), column name (e.g., "vector"), and dimension (e.g., 1536 for OpenAI embeddings)
3. Insert Embeddings
- Use pgvector-insert or pgvector-upsert
- Send messages with msg.payload.vector as your embedding array
4. Search for Similar Vectors
- Use pgvector-search
- Provide query vector in msg.payload.vector
- Results in msg.payload with similarity scores
Distance Metrics
- cosine (default) - Cosine similarity, best for normalized vectors
- l2 - Euclidean distance (L2 norm)
- inner-product or ip - Inner product, useful for maximum inner product search
Indexing for Performance
For large datasets (>10,000 vectors), create an index:
IVFFlat - Good balance of speed and accuracy:
- Use pgvector-admin with action "create-ivfflat"
- Set list parameter based on rows (sqrt of row count is a good starting point)
HNSW - Better recall, slower insert:
- Use pgvector-admin with action "create-hnsw"
- Generally provides better search quality
Documentation
- Example Flows Guide - Detailed walkthrough of all example flows
- Testing Guide - Comprehensive testing procedures and troubleshooting
- pgvector Extension Docs - Vector database operations
- Node-RED Documentation - Flow development and deployment
Development
$3
`bash
npm install
npm link
In Node-RED directory
cd ~/.node-red
npm link @nagual69/node-red-pgvector
`
$3
`bash
cd test
docker-compose up -d
Node-RED available at http://localhost:1880
`
$3
`
pgvector.js # Main entry point registering all nodes
package.json # NPM package manifest
index.d.ts # TypeScript type definitions
README.md # This file
TESTING.md # Comprehensive testing guide
LICENSE # MIT license
.gitignore # Git ignore rules
nodes/ # Node implementations (7 files)
āāā pgvector-config.js/html
āāā pgvector-admin.js/html
āāā pgvector-query.js/html
āāā pgvector-insert.js/html
āāā pgvector-upsert.js/html
āāā pgvector-search.js/html
āāā pgvector-schema.js/html
lib/ # Utility libraries
āāā client.js # Connection pool management
āāā vector-utils.js # Vector parsing, validation, operators
examples/ # Ready-to-import example flows
āāā sample-flows.json # Complete feature demonstration (4 tabs)
āāā complete-example.json # End-to-end workflow
āāā basic-flows.json # Minimal starter
āāā README.md # Example flow documentation
test/ # Docker test environment
āāā docker-compose.yml # PostgreSQL + Node-RED
āāā init.sql # Database initialization
āāā README.md # Testing instructions
`
$3
Node Implementation Pattern:
- All nodes follow standard Node-RED structure
- Use withClient(pool, fn) for database operations
- Call node.error(err, msg) AND done(err) for error handling
- Set node.status() for visual feedback (blue=processing, red=error)
- Properties use non-conflicting names (e.g., nodeMetric not metric)
Vector Utility Functions:
- parseVector() - Accepts arrays, JSON strings, CSV, base64
- normalizeVector() - L2 normalization for cosine similarity
- validateDimension() - Ensures vector matches configured dimension
- buildSimilarityQuery()` - Generates parameterized SQL with correct operators