pgvector

The vector extension (pgvector) adds a vector data type and index methods (IVFFlat, HNSW) to PostgreSQL for similarity search and embedding storage. Thalassa Cloud DBaaS includes pgvector for AI/ML workloads that need to store and query vector embeddings.

Check availability

SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE name = 'vector';

Enable pgvector

CREATE EXTENSION IF NOT EXISTS vector;

Verify installation

SELECT extversion FROM pg_extension WHERE extname = 'vector';

Basic usage

-- Create a table with a vector column (e.g. 1536 dimensions for OpenAI embeddings)
CREATE TABLE embeddings (
  id    BIGSERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

-- Insert vectors
INSERT INTO embeddings (content, embedding)
VALUES ('Example text', '[0.1, 0.2, ...]'::vector);

-- Exact nearest neighbour (small datasets)
SELECT id, content, embedding <-> '[0.1, 0.2, ...]'::vector AS distance
FROM embeddings
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector
LIMIT 5;

-- Approximate nearest neighbour with HNSW index (larger datasets)
CREATE INDEX ON embeddings USING hnsw (embedding vector_cosine_ops);

SELECT id, content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM embeddings
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

Operators

Operator	Description
`<->`	Euclidean distance (L2)
`<#>`	Negative inner product
`<=>`	Cosine distance

For full documentation, see the pgvector documentation.

PostGIS