pgvector

Vector Database integration · 12 node(s).

00Overview

Store and query embeddings in a PostgreSQL database with the pgvector extension

Every field below is exactly what you see in the Flomation editor. Fields marked ● live picker let you choose from a list pulled live from your account — no IDs to look up.

01Connecting pgvector

Add your credentials as a Flomation environment secret, then pick them in each node. The connection fields are:

Field	Type		Details
Password	`secret`	Required	Database password
Database Host	`string`	Required	db.example.com or 192.168.1.20 — hostname or IP, no scheme
Port	`integer`		5432
Username	`string`	Required	postgres
Embedding API Key	`secret`
Embedding Base URL	`string`		http://ollama.internal:11434 — Azure OpenAI: https://my-resource.openai.azure.com
AWS Access Key ID	`secret`
AWS Secret Access Key	`secret`

Pick an Environment on your flow (Flow Settings → Environment) so the secret resolves. Secret fields never show the value — they reference ${secrets.your_secret}.

02Document

Count Documents

vectordatabase/pgvector/document_count · Action

Count the documents in the store, with an optional metadata filter

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
Metadata Column	`string`		Leave blank to work it out from the table
Metadata Filter	`key_value_array`		Only count documents whose metadata matches every pair
Advanced Metadata Filter (JSON)	`object`		{"source": {"eq": "handbook"}, "page": {"gt": 3}}
Collection	`string`		Optional — a named sub-set of the table to work within
Collection Table	`string`		flomation_vector_collections — where collection names are recorded

Returns: count, result, tool_result, success, error

Delete Documents

vectordatabase/pgvector/document_delete · Action

Remove documents by ID or by a metadata filter

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave blank to work it out from the table
Content Column	`string`		Leave blank to work it out from the table
Metadata Column	`string`		Leave blank to work it out from the table
Embedding Column	`string`		Leave blank to work it out from the table
Delete	`string`		choices: Documents with these IDs, Documents matching a metadata filter
Document IDs	`string`		One or more IDs, comma-separated — or a JSON array
Metadata Filter	`key_value_array`		Only documents whose metadata matches every pair are deleted
Advanced Metadata Filter (JSON)	`object`		{"source": {"eq": "handbook"}, "page": {"gt": 3}}
Collection	`string`		Optional — restrict the delete to one named collection
Collection Table	`string`		flomation_vector_collections — where collection names are recorded
I understand this permanently deletes data	`boolean`	Required	Deleted documents cannot be recovered. Tick to allow, or bind a variable such as ${var.approved}

Returns: deleted, result, tool_result, success, error

Get Document

vectordatabase/pgvector/document_get · Action

Fetch a single document by its ID

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave empty to work it out — the primary key, or a column called "id"
Content Column	`string`		Leave empty to work it out — text, content, document…
Metadata Column	`string`		Leave empty to work it out — metadata, meta…
Embedding Column	`string`		Leave empty to work it out — the table's vector column
Document ID	`string`	Required	The ID of the document to fetch
Include the raw embedding	`boolean`		Off by default — an embedding is thousands of numbers and is rarely wanted downstream

Returns: id, content, metadata, embedding, found, result, tool_result, success, error

Insert Documents

vectordatabase/pgvector/document_insert · Action

Add documents to the vector store, embedding the text automatically

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave empty to work it out from the table
Content Column	`string`		Leave empty to work it out from the table
Metadata Column	`string`		Leave empty to work it out from the table
Embedding Column	`string`		Leave empty to work it out from the table
Embedding Source	`string`	Required	choices: Embed the text for me, Use a vector from a previous step
Embedding Vector	`object`		Pick the Embedding output of an Embed Text step
Embedding Provider	`string`	Required	choices: OpenAI, OpenAI-compatible (Azure, vLLM, LocalAI, TEI…), Azure OpenAI, Ollama (self-hosted), AWS Bedrock (Titan)
API Version	`string`		2024-10-21
AWS Region	`string`		us-east-1
Embedding Model	`combobox`		text-embedding-3-small — choices: OpenAI text-embedding-3-small (1536 dimensions), OpenAI text-embedding-3-large (3072 dimensions), OpenAI text-embedding-ada-002 (1536 dimensions), Bedrock Titan Text v2 (1024 dimensions), Bedrock Titan Text v1 (1536 dimensions), Ollama nomic-embed-text (768 dimensions), Ollama mxbai-embed-large (1024 dimensions)
Dimensions	`integer`		Leave empty for the model's default — must match the table
Content	`text`		The document text to store and make searchable
Metadata (JSON)	`object`		{"source": "handbook.pdf", "page": 3}
ID	`string`		Leave empty to let the database generate one
Documents (JSON array)	`object`		Bulk insert: [{"content": "…", "metadata": {…}, "id": "…", "embedding": […]}] — overrides the single Content field above
Chunk Size (characters)	`integer`		0 = store the document whole. Set e.g. 1000 to split long text into overlapping chunks
Chunk Overlap (characters)	`integer`		Characters of overlap between chunks — only used when Chunk Size is set
Create the table if it doesn't exist	`boolean`		Off by default: a typo in the table name would otherwise build a second, empty table
Collection	`string`		Optional — tag these documents as part of a named collection within the table
Collection Table	`string`		flomation_vector_collections — where collection names are recorded

Returns: ids, id, count, chunks, result, tool_result, success, error

List Documents

vectordatabase/pgvector/document_list · Action

Browse the documents in the store, with an optional metadata filter

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave blank to detect it automatically
Content Column	`string`		Leave blank to detect it automatically
Metadata Column	`string`		Leave blank to detect it automatically
Embedding Column	`string`		Leave blank to detect it automatically
Metadata Filter	`key_value_array`		Only show documents whose metadata matches every pair, e.g. source = handbook
Advanced Metadata Filter (JSON)	`object`		{"page": {"gt": 3}, "tag": {"in": ["policy","hr"]}}
Limit	`integer`		Documents to show (default 50, max 1000)
Offset	`integer`		Skip this many documents first (default 0)
Sort By	`string`		Leave empty to sort by ID
Sort Direction	`string`		choices: Ascending, Descending
Include Embeddings	`boolean`		Return the raw vectors too — thousands of numbers per document
Fetch every matching document	`boolean`		Page through the whole table instead of one page
Collection	`string`		Optional — a named sub-set of the table to list within
Collection Table	`string`		flomation_vector_collections — where collection names are recorded

Returns: results, count, total, result, tool_result, success, error

Search Documents

vectordatabase/pgvector/document_search · Action

Find the documents most similar in meaning to a query

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave empty to work it out from the table
Content Column	`string`		Leave empty to work it out from the table
Metadata Column	`string`		Leave empty to work it out from the table
Embedding Column	`string`		Leave empty to work it out from the table
Embedding Source	`string`	Required	choices: Embed the text for me, Use a vector from a previous step
Embedding Vector	`object`		Pick the Embedding output of an Embed Text step
Embedding Provider	`string`	Required	choices: OpenAI, OpenAI-compatible (Azure, vLLM, LocalAI, TEI…), Azure OpenAI, Ollama (self-hosted), AWS Bedrock (Titan)
API Version	`string`		2024-10-21
AWS Region	`string`		us-east-1
Embedding Model	`combobox`		text-embedding-3-small — choices: OpenAI text-embedding-3-small (1536 dimensions), OpenAI text-embedding-3-large (3072 dimensions), OpenAI text-embedding-ada-002 (1536 dimensions), Bedrock Titan Text v2 (1024 dimensions), Bedrock Titan Text v1 (1536 dimensions), Ollama nomic-embed-text (768 dimensions), Ollama mxbai-embed-large (1024 dimensions)
Dimensions	`integer`		Leave empty for the model's default — must match the table
Search Query	`text`		What are you looking for? e.g. "how do I reset a password"
Number of Results	`integer`		4
Distance Metric	`string`		cosine — choices: Cosine — best for text embeddings, Inner Product, Euclidean (L2)
Minimum Score	`string`		0.0–1.0. Leave empty for no minimum. 0.7 is a good starting point for cosine
Metadata Filter	`key_value_array`		Only search documents whose metadata matches
Advanced Metadata Filter (JSON)	`object`		{"page": {"gt": 3}, "tag": {"in": ["a","b"]}}
Include Metadata	`boolean`		Return each document's metadata alongside its text
Include Embeddings	`boolean`		Return the raw embeddings too — usually not needed and very large
HNSW ef_search	`integer`		Leave empty for the server default. Higher = better recall, slower
Collection	`string`		Optional — a named sub-set of the table to search within
Collection Table	`string`		flomation_vector_collections — where collection names are recorded

Returns: results, count, top_result, context, tool_result, success, error

Update Document

vectordatabase/pgvector/document_update · Action

Change a document's text or metadata, re-embedding it automatically

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave empty to work it out automatically
Content Column	`string`		Leave empty to work it out automatically
Metadata Column	`string`		Leave empty to work it out automatically
Embedding Column	`string`		Leave empty to work it out automatically
Embedding Source	`string`	Required	choices: Embed the text for me, Use a vector from a previous step
Embedding Vector	`object`		Pick the Embedding output of an Embed Text step
Embedding Provider	`string`	Required	choices: OpenAI, OpenAI-compatible (Azure, vLLM, LocalAI, TEI…), Azure OpenAI, Ollama (self-hosted), AWS Bedrock (Titan)
API Version	`string`		2024-10-21
AWS Region	`string`		us-east-1
Embedding Model	`combobox`		text-embedding-3-small — choices: OpenAI text-embedding-3-small (1536 dimensions), OpenAI text-embedding-3-large (3072 dimensions), OpenAI text-embedding-ada-002 (1536 dimensions), Bedrock Titan Text v2 (1024 dimensions), Bedrock Titan Text v1 (1536 dimensions), Ollama nomic-embed-text (768 dimensions), Ollama mxbai-embed-large (1024 dimensions)
Dimensions	`integer`		Leave empty for the model's default — must match the table
Document ID	`string`	Required
New Content	`text`		Leave empty to keep the current text
Metadata (JSON)	`object`		{"reviewed": true}
Merge into the existing metadata rather than replacing it	`boolean`
Re-generate the embedding from the new text	`boolean`

Returns: id, updated, result, tool_result, success, error

Upsert Documents

vectordatabase/pgvector/document_upsert · Action

Insert documents, or overwrite them if they already exist

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave empty to work it out from the table
Content Column	`string`		Leave empty to work it out from the table
Metadata Column	`string`		Leave empty to work it out from the table
Embedding Column	`string`		Leave empty to work it out from the table
Embedding Source	`string`	Required	choices: Embed the text for me, Use a vector from a previous step
Embedding Vector	`object`		Pick the Embedding output of an Embed Text step
Embedding Provider	`string`	Required	choices: OpenAI, OpenAI-compatible (Azure, vLLM, LocalAI, TEI…), Azure OpenAI, Ollama (self-hosted), AWS Bedrock (Titan)
API Version	`string`		2024-10-21
AWS Region	`string`		us-east-1
Embedding Model	`combobox`		text-embedding-3-small — choices: OpenAI text-embedding-3-small (1536 dimensions), OpenAI text-embedding-3-large (3072 dimensions), OpenAI text-embedding-ada-002 (1536 dimensions), Bedrock Titan Text v2 (1024 dimensions), Bedrock Titan Text v1 (1536 dimensions), Ollama nomic-embed-text (768 dimensions), Ollama mxbai-embed-large (1024 dimensions)
Dimensions	`integer`		Leave empty for the model's default — must match the table
Content	`text`		The document text to store
Metadata (JSON)	`object`		{"source": "handbook", "page": 3}
ID	`string`		The document's ID — the same ID next time overwrites this document
Documents (JSON list)	`object`		[{"id": "doc-1", "content": "…", "metadata": {"source": "handbook"}}] — upsert many at once, instead of Content above
Chunk Size	`integer`		Split long text into pieces of this many characters — leave empty to store it whole
Chunk Overlap	`integer`		Characters each piece repeats from the one before, e.g. 200
Match Existing Documents By	`string`		choices: ID, A metadata field
Metadata Field	`string`		external_id — needs a unique index on (metadata->>'external_id')
Collection	`string`		Optional — tag these documents as part of a named collection within the table
Collection Table	`string`		flomation_vector_collections — where collection names are recorded

Returns: ids, count, result, tool_result, success, error

03Hybrid

Hybrid Search

vectordatabase/pgvector/hybrid_search · Action

Combine meaning-based and keyword search, so exact terms and product codes are not missed

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
ID Column	`string`		Leave empty to work it out from the table
Content Column	`string`		Leave empty to work it out from the table
Metadata Column	`string`		Leave empty to work it out from the table
Embedding Column	`string`		Leave empty to work it out from the table
Embedding Source	`string`	Required	choices: Embed the text for me, Use a vector from a previous step
Embedding Vector	`object`		Pick the Embedding output of an Embed Text step
Embedding Provider	`string`	Required	choices: OpenAI, OpenAI-compatible (Azure, vLLM, LocalAI, TEI…), Azure OpenAI, Ollama (self-hosted), AWS Bedrock (Titan)
API Version	`string`		2024-10-21
AWS Region	`string`		us-east-1
Embedding Model	`combobox`		text-embedding-3-small — choices: OpenAI text-embedding-3-small (1536 dimensions), OpenAI text-embedding-3-large (3072 dimensions), OpenAI text-embedding-ada-002 (1536 dimensions), Bedrock Titan Text v2 (1024 dimensions), Bedrock Titan Text v1 (1536 dimensions), Ollama nomic-embed-text (768 dimensions), Ollama mxbai-embed-large (1024 dimensions)
Dimensions	`integer`		Leave empty for the model's default — must match the table
Search Query	`text`		What are you looking for? e.g. my parcel never arrived
Keyword Query	`text`		Leave empty to use the Search Query above
Number of Results	`integer`		10
Distance Metric	`string`		cosine — choices: Cosine — best for text embeddings, Inner Product, Euclidean (L2)
Text Search Language	`string`		english
Fusion Constant (k)	`integer`		Reciprocal Rank Fusion constant — 60 is the standard
Candidates Per Search	`integer`		How many candidates each search contributes before they're fused
Indexed Text Column	`string`		Leave empty to search the content column directly. Set this to a generated tsvector column for a much faster keyword search
Metadata Filter	`key_value_array`		Only search documents whose metadata matches, e.g. source = handbook
Advanced Metadata Filter (JSON)	`text`		{"page": {"gt": 3}, "tag": {"in": ["a","b"]}}
Include Metadata	`boolean`
Include Embeddings	`boolean`		Return the stored vector with each result — large, and rarely needed

Returns: results, count, top_result, context, tool_result, success, error

04Index

Create Search Index

vectordatabase/pgvector/index_create · Action

Add an approximate-nearest-neighbour index so similarity search stays fast as the table grows

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents
Embedding Column	`string`		Leave blank to work it out from the table — set it if the table holds nothing but embeddings
Index Type	`string`		choices: HNSW — best recall, slower to build, IVFFlat — faster to build, needs data present
Distance Metric	`string`		Must match the metric your searches use, or they can't use the index — choices: Cosine — best for text embeddings, Inner Product, Euclidean (L2)
HNSW: Links per Row (m)	`integer`		16 — higher means better recall, a bigger index and a slower build
HNSW: Build Effort (ef_construction)	`integer`		64 — higher means a slower build and better recall (at least twice m)
IVFFlat: Lists	`integer`		100 — roughly rows ÷ 1000 for tables up to a million rows
Build without locking the table	`boolean`		Inserts and updates keep working while the index builds, but the build takes longer
Update Table Statistics	`boolean`		Run ANALYZE afterwards so Postgres starts using the new index (recommended)

Returns: index_name, created, result, tool_result, success, error

05Table

Create Vector Table

vectordatabase/pgvector/table_create · Action

Create a table that stores documents and their embeddings, with the index that makes search fast

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	my_documents
Embedding Dimensions	`integer`	Required	1536 for OpenAI text-embedding-3-small, 1024 for Bedrock Titan v2
ID Column	`string`		id
Content Column	`string`		text
Metadata Column	`string`		metadata
Embedding Column	`string`		embedding
Create the pgvector extension if it's missing	`boolean`		On by default — needs a database user that can install extensions
Create a search index	`boolean`		On by default — without it every search reads the whole table
Index Type	`string`		nsw — choices: HNSW — fastest searches, slower to build, IVFFlat — quicker to build, needs data to be loaded first
Distance Metric	`string`		cosine — choices: Cosine — best for text embeddings, Inner Product, Euclidean (L2)
Also index the text for keyword search	`boolean`		Lets Hybrid Search match on exact words as well as meaning

Returns: table, created, dimensions, index_name, result, tool_result, success, error

Inspect Vector Table

vectordatabase/pgvector/table_info · Action

See a table's columns, embedding dimensions, indexes and row count

Field	Type		Details
Database	`string`	Required	vectordb
SSL Mode	`string`		isable — choices: Disable — no encryption, Allow, Prefer — encrypt if the server offers it, Require — encrypt, but don't verify the certificate, Verify CA — encrypt and check the certificate authority, Verify Full — encrypt and check the hostname too
Schema	`string`		public
Table	`string`	Required	documents

Returns: columns, vector_column, content_column, metadata_column, id_column, dimensions, row_count, indexes, extension_installed, table_size, has_vector_index, result, tool_result, success, error

06Notes & Limitations

Behaviours and constraints worth knowing before you build with these nodes.

Connections are made without encryption unless you choose an SSL mode, so pick one of the encrypted SSL modes whenever the database is reached over an untrusted network.
The embedding you search or store with must have the same number of dimensions the table's vector column was declared for, and a step fails up front if it does not match — usually a sign the embedding model differs from the one the table was built for, such as a 1536-dimension model pointed at a table built for 1024-dimension vectors.
On a search, the distance metric has to match the one the index was built with; if it does not, the index is silently skipped and the query falls back to a full sequential scan that still returns correct results but runs slowly, so keep the metric consistent with the index.
Inserting a document whose ID already exists fails with a duplicate-key error, so use the Upsert Document step when you want to overwrite an existing document instead of adding a new one.
A single Insert or Upsert step handles at most 1000 documents at once.
Fetching every matching document is capped at 20 pages of 1000 rows — about 20,000 documents — so for larger tables narrow the filter or page through the results with Limit and Offset.