Deploy and Host pgvector on Railway

pgvector is a PostgreSQL extension that adds vector similarity search capabilities to standard PostgreSQL databases. It enables storage and querying of high-dimensional vectors for machine learning applications, embeddings, and similarity search operations.

About Hosting pgvector

pgvector runs as a PostgreSQL extension requiring a compatible PostgreSQL version and proper extension installation during database initialization. You'll need to manage vector index creation, query performance tuning for similarity searches, and storage scaling as vector datasets grow. The database handles vector operations like cosine similarity, L2 distance, and inner product calculations while maintaining ACID compliance. Connection pooling becomes important for concurrent vector operations, and backup strategies must account for large vector datasets.

Common Use Cases

• Machine Learning Engineers: Store and query embeddings from neural networks, language models, and recommendation systems
• AI Application Developers: Build semantic search, recommendation engines, and similarity matching features using vector databases
• Data Scientists: Perform similarity analysis on high-dimensional data and build retrieval-augmented generation systems

Dependencies for pgvector Hosting

• PostgreSQL Database: Compatible PostgreSQL version with pgvector extension support
• Vector Storage: Sufficient storage capacity for high-dimensional vector datasets
• Query Optimization: Index management and performance tuning for vector similarity operations

Deployment Dependencies

• pgvector GitHub Repository
• pgvector Documentation
• PostgreSQL Vector Operations Guide
• Vector Index Types

Implementation Details

Setup Configuration:

This template provides PostgreSQL with the pgvector extension pre-installed. TCP proxying is configured to allow accessing the database from anywhere, enabling external connections to your vector database.

Database Connection:

Reference the DATABASE_URL variable from your service to connect to the database in your tool of choice:

${{pgvector.DATABASE_URL}}

Connection Details:

You can connect to your database using the proxied domain and port found on the service settings page. The password can be found on the Variables page.

Testing pgvector Installation:

Once connected, test that pgvector is properly configured with the following SQL:

CREATE EXTENSION vector;
CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Vector Operations:

-- Create table with vector column
CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)  -- OpenAI embedding dimension
);

-- Insert vector data
INSERT INTO documents (content, embedding) VALUES 
('Sample document', '[0.1, 0.2, 0.3, ...]');

-- Similarity search using different distance metrics
-- L2 distance (Euclidean)
SELECT content FROM documents ORDER BY embedding <-> '[0.1, 0.2, 0.3]' LIMIT 10;

-- Cosine distance
SELECT content FROM documents ORDER BY embedding <=> '[0.1, 0.2, 0.3]' LIMIT 10;

-- Inner product
SELECT content FROM documents ORDER BY embedding <#> '[0.1, 0.2, 0.3]' LIMIT 10;

Index Creation for Performance:

-- Create HNSW index for approximate nearest neighbor search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Create IVFFlat index for exact nearest neighbor search
CREATE INDEX ON documents USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

Integration Examples:

-- Python example with psycopg2
import psycopg2
import numpy as np

conn = psycopg2.connect(database_url)
cur = conn.cursor()

# Insert vector
embedding = np.array([0.1, 0.2, 0.3])
cur.execute("INSERT INTO items (embedding) VALUES (%s)", (embedding.tolist(),))

# Query similar vectors
cur.execute("SELECT id FROM items ORDER BY embedding <-> %s LIMIT 5", (embedding.tolist(),))
results = cur.fetchall()

Performance Considerations:

• Vector Dimensions: Higher dimensions require more storage and slower query performance
• Index Types: HNSW for approximate search, IVFFlat for exact search with better recall
• Memory Usage: Vector operations are memory-intensive, especially for large datasets
• Query Optimization: Use appropriate distance metrics based on your embedding model

Common Distance Metrics:

• <-> L2 distance (Euclidean)
• <=> Cosine distance
• <#> Inner product

Why Deploy pgvector on Railway?

Railway is a singular platform to deploy your infrastructure stack. Railway will host your infrastructure so you don't have to deal with configuration, while allowing you to vertically and horizontally scale it.

By deploying pgvector on Railway, you are one step closer to supporting a complete full-stack application with minimal burden. Host your servers, databases, AI agents, and more on Railway.