Vector, Graph, and Relational: Picking the Right Data Store for Each Job

I've spent the last decade building systems that store and query everything from user profiles to billion-node knowledge graphs. One thing I've learned: there is no universal database. The best storage engine depends entirely on your workload. In this article, I'll walk through the three major data models—vector, graph, and relational—and give you concrete criteria for choosing the right one.

The Relational Database: Still the Workhorse

Relational databases (PostgreSQL, MySQL, SQLite) are the default for a reason. They excel at ACID transactions, structured data with known schemas, and complex joins over normalized tables.

When to use:

• You need strong consistency and transactions.
• Your data fits neatly into rows and columns with well-defined relationships.
• Queries involve aggregations, filtering by multiple attributes, or range scans.

Real-world example: At my previous company, we stored all user account data in PostgreSQL. Each user had a row in users, with columns for email, name, and subscription tier. We ran queries like:

SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.subscription = 'premium'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

This query is a natural fit for a relational database. The join is on a foreign key, the filter is on an indexed column, and the aggregation is well-supported.

Performance numbers: With proper indexing, PostgreSQL can handle millions of rows and return such queries in milliseconds. For example, on a 10-million-row orders table, the above query runs in ~150ms with a B-tree index on subscription and a composite index on (user_id, id).

Limitations: Relational databases struggle with:

• Deep relationship traversal (e.g., friends-of-friends).
• Similarity search over high-dimensional vectors.
• Schema flexibility (though JSONB helps).

Graph Databases: When Relationships Are the Data

Graph databases (Neo4j, ArangoDB, Dgraph) treat relationships as first-class citizens. They are optimized for traversing connections between entities.

When to use:

• Your data is highly interconnected and queries involve multi-hop traversals.
• You need to answer questions like "what is the shortest path between A and B?" or "how are these two entities connected?"
• The schema is evolving and relationships are more important than attributes.

Real-world example: We built a recommendation engine for a social network using Neo4j. Users could follow topics, and topics were related to other topics. To recommend new topics, we needed to find topics followed by users similar to the target user.

MATCH (u:User {id: '123'})-[:FOLLOWS]->(t:Topic)<-[:FOLLOWS]-(other:User)
MATCH (other)-[:FOLLOWS]->(rec:Topic)
WHERE NOT (u)-[:FOLLOWS]->(rec)
RETURN rec.name, COUNT(DISTINCT other) as score
ORDER BY score DESC
LIMIT 10;

This query traverses a variable-depth path. In a relational database, this would require multiple recursive CTEs or self-joins, which become exponentially slower as depth increases. Neo4j handles it in under 50ms for a graph with 1 million users and 10 million edges.

Performance numbers: For a 2-hop traversal on a graph with 10 million edges, Neo4j is typically 10-100x faster than PostgreSQL doing recursive CTEs. The gap widens with depth.

Limitations: Graph databases are not great for:

• High-volume transactional writes (though some are improving).
• Aggregation-heavy queries (e.g., SUM, AVG over many rows).
• Queries that don't traverse relationships (e.g., simple key-value lookups).

Vector Databases: Similarity at Scale

Vector databases (Milvus, Qdrant, Weaviate, pgvector) are built for storing and querying high-dimensional vectors, typically embeddings from machine learning models.

When to use:

• You need to find items similar to a query item by vector distance (e.g., semantic search, image similarity, anomaly detection).
• Your data is unstructured but you've encoded it as vectors.
• You need approximate nearest neighbor (ANN) search at scale.

Real-world example: We built a semantic search engine for internal documents. Each document was chunked and embedded using a sentence transformer model, producing 768-dimensional vectors. We stored them in Milvus.

import pymilvus

client = pymilvus.Milvus(host='localhost', port='19530')
collection = client.get_collection('documents')

query_vector = embed('How do I reset my password?')
results = collection.search(
    data=[query_vector],
    anns_field='embedding',
    param={'metric_type': 'IP', 'params': {'nprobe': 10}},
    limit=5,
    output_fields=['title', 'url']
)

Performance numbers: With 10 million 768-dimensional vectors, Milvus can return top-10 results in under 10ms using IVF_FLAT index with nprobe=16. The recall is typically 95-99% compared to brute-force.

Limitations: Vector databases are not designed for:

• Exact querying or filtering on attributes (though some support scalar filtering, it's often slower).
• Complex joins or transactions.
• Queries that don't involve vector similarity.

Hybrid Approaches: Combining the Best

In practice, many systems need more than one data model. The key is to use each for what it's best at, and synchronize data between them.

Example architecture: A recommendation system that uses all three:

1. Relational (PostgreSQL): Stores user profiles, item metadata, and transaction logs. Handles all CRUD operations and reporting.
2. Graph (Neo4j): Stores user-item interactions and social connections. Traverses to find candidate items.
3. Vector (Milvus): Stores item embeddings. Ranks candidates by similarity to user's recent behavior.

The flow: When a user requests recommendations, PostgreSQL fetches user context, Neo4j generates a candidate list (e.g., items liked by friends), Milvus ranks those candidates by embedding similarity, and PostgreSQL serves the final result.

Data synchronization: We used change data capture (CDC) with Debezium to stream changes from PostgreSQL to a Kafka topic. Microservices consumed that topic and updated Neo4j and Milvus accordingly. The latency was under 1 second for most updates.

Decision Matrix

Lessons Learned

1. Don't force a square peg into a round hole. I've seen teams try to use PostgreSQL for graph traversal by writing recursive CTEs. It works for small datasets, but falls apart at scale. Use the right tool.
2. Measure, don't guess. Before committing to a database, run benchmarks with your actual data and query patterns. Synthetic benchmarks can be misleading.
3. Plan for data movement. If you use multiple databases, invest in robust data pipelines. CDC tools like Debezium or Kafka Connect can save you headaches.
4. Consider pgvector as a bridge. If you're already on PostgreSQL and need basic vector search, pgvector is a good option. It's not as fast as Milvus at scale, but it eliminates the need for a separate system.

Final Thoughts

Choosing a database is a tradeoff. Relational databases are the safe default for most workloads. Graph databases shine when relationships are complex and central. Vector databases are essential for AI-powered similarity search. The best architects understand the strengths of each and combine them judiciously.

Next time you start a new project, ask yourself: What does my query pattern look like? If it's mostly CRUD with simple joins, go relational. If you're traversing graphs of friends or dependencies, go graph. If you're comparing embeddings, go vector. And if you need all three, build a system that uses each for what it does best.