@nagual69/node-red-pgvector

Node-RED nodes for PostgreSQL + pgvector: create vector tables, insert/upsert embeddings, run similarity search (cosine, L2, inner product), and manage indexes.

Enterprise-ready with OpenTelemetry observability, structured logging, automatic retries, and comprehensive validation.

✨ Enterprise Features

- 🔭 OpenTelemetry - Distributed tracing, metrics, and observability (Setup Guide)
- 📊 Structured Logging - JSON logs with pino for production monitoring
- 🔄 Automatic Retries - Exponential backoff for transient failures
- ⏱️ Query Timeouts - Prevent hanging queries (default: 60s)
- 🏥 Health Monitoring - Connection pool metrics and error handling
- ✅ Validation - Comprehensive pre-flight checks with actionable errors
- 🔒 Security - Parameterized queries, identifier escaping, credentials vault
- 📈 Performance - Query duration tracking and slow query detection

See Enterprise Guide for production deployment.

Prerequisites

- PostgreSQL with the pgvector extension installed
- Node-RED version 2.0.0 or later
- Node.js version 18 or later

To install pgvector on PostgreSQL:
``sql
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

$3

1. Ensure all nodes follow error handling pattern
2. Test with provided Docker environment
3. Update examples if adding features
4. Add tests to TESTING.md
5. Update TypeScript definitions in index.d.ts

License

MIT - See LICENSE