The Allure of Vector Databases

When we started building AgentFSD's prompt assembly system, the obvious choice seemed to be a vector database. After all, we're dealing with embeddings, semantic similarity, and AI workflows. VectorDB Twitter was very convincing. Then we actually built a prototype.

What We Needed vs. What Vector DBs Offer

Our requirements for prompt versioning were:

• Store versioned prompt components (roles, rules, steps)
• Track relationships between components
• Support complex queries (by version, by project, by author)
• ACID transactions for consistent updates
• Sub-millisecond reads for assembly

Vector databases excel at similarity search. But we weren't searching—we were assembling. Every prompt component has a specific ID and version. There's nothing fuzzy about "give me role v1.3 with rules v2.1."

The Postgres + JSONB Solution

Here's what we landed on: `sql CREATE TABLE prompt_components ( id UUID PRIMARY KEY, project_id UUID REFERENCES projects(id), component_type TEXT NOT NULL, version INT NOT NULL, content JSONB NOT NULL, metadata JSONB, created_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(project_id, component_type, version) ); CREATE INDEX idx_component_lookup ON prompt_components(project_id, component_type, version); `

Why This Works

1. JSONB flexibility - Store complex nested content without migrations 2. Native indexing - GIN indexes for fast JSONB queries 3. Transactional guarantees - No race conditions during assembly 4. Operational simplicity - One database to manage, not two

Performance Numbers

• Component read: 0.3ms average
• Full prompt assembly (4 components): 1.2ms
• Version history query: 2.1ms

Vector databases added 15-20ms latency per operation and required separate connection pooling, failover, and backup strategies.

When Vector DBs Make Sense

To be clear: vector databases are excellent for semantic search, RAG pipelines, and similarity matching. If you're building a recommendation system or document retrieval, use them. But for deterministic assembly operations? Keep it simple. Postgres has been solving these problems for 25 years.