SQL vs NoSQL vs Vector

All databases can be categorized into 3 types: SQL, NoSQL, and Vector for modern software engineering

SQL / Structured / Relational NoSQL / unStructured / nonRelational Vector
What Tables with fixed rows and columns

SQL/NoSQL answers "Does this exact value exist?" 		Documents, Key-Values, or Graphs Stores High-dimensional Tensors + Metadata

Vector DB answers "What does this abstract meaning resemble?"
How Data is Stored Internally B-Trees or Hash Maps B-Trees, LSM-Trees, or Hash Maps. ANN Graphs (HNSW) or Inverted File Index (IVF)
Use Case Financial transactions and core business logic High-scale web apps and flexible data feeds AI memory, RAG, and semantic search
How they work Data is split into separate tables and linked using
relationships (foreign keys) 		Instead of rigid tables, they store data using flexible
models. This includes document stores (JSON files), key-value
dictionaries, or interconnected nodes (graphs). 		Instead of searching for exact keywords or IDs, they store
machine-learning generated numerical "embeddings" that capture
the conceptual meaning of text, images, or audio.
Query Type Exact Match & Boolean logic

(WHERE age = 30 AND city = 'NYC')
Exact Key lookup or range scans

(WHERE user_id = '123')
Similarity Search

Find me the Top-5 vectors closest to this Query Tensor
Math Used Comparisons (>, <, =) Hashing or Lexicographical comparisons Dot Product, Cosine Similarity, or Euclidean (L2) Distance
Examples Amazon(Aurora, RDS), MySQL, postgreSQL, mariaDB
  • Semistructured: Amazon (S3, dynamoDB), Apache Cassandra
  • Unstructured: Amazon S3, Apache CouchDB, MongoDB
    		•  Pinecone
    Types - ORDBMS(Object RDBMS):RDBMS build on OOD.Eg:PostGreSQL
    - RDBMS: mySQL     		a. KEY-VALUE DB: redis, Amazon dynamoDB, Voldemort, sled(rust)
    b. WIDE-COLUMN DB: Stores data as columns instead of rows. Eg: Cassandra, HBase
    c. DOCUMENT DB: Data is stored in documents(XML, JSON, binary)
    Eg: mongoDB, Amazon dynamoDB.
    d. GRAPH DB: Data is stored in form of graph.Eg: Neo4J, HyperGraphDB
    Advantage Strict ACID compliance ensures that a financial transaction either
    fully succeeds or cleanly fails without corrupting data.     		1. Super low latency
    2. Data are unstructured, or you do not have any relational data
    3. Store a massive amount of data
    4. Highly scalable horizontally
    5. Developers can change data structures on the fly     		Execute incredibly fast mathematical calculations to
    find content based on concept similarity rather than exact words
    Disadvantage Difficult to scale out horizontally across multiple servers Complex Join operations are not supported
    forcing developers to handle relationship logic inside application code     		These are specialized indexing tools and cannot handle typical
    transactional applications like user authentication or inventory tracking
    Format Table(Records searched using primary key) 
    
//Books Table

| book-id(pk) | title | author | genre | comments |
    		<key, value> or xml or json or objects
    Example: Book data stored in mongoDB in json format 
    
// Same as books table
/////// entry-1 /////////
{       
    "_id": ObjectId("5f85e7a06921e55c279b15a0"), //Primary key
    "title": "GumsRoad",
    "author": {
        "name": "Douglas Adams",
        "birth_year": 1952
    },
    "genre": "Science Fiction",
    "comments": [
        {
        "user": "Alice",
        "text": "One of my favorites!"
        },
        {
        "user": "Bob",
        "text": "Hilarious and clever."
        }
    ]
}
////////// entry-2
(Fields in json document can vary) /////////
{
    "_id": ObjectId("122312141241241"),     
    "title": "HedgeHog",
    "author": {
        "name": "Douglas Adams",
    },
    "genre": "Adventure",
}
    Huge Data Supoprt (~1TB) No Why? yes
    Horizontal Scaling Does not support Horizontal scaling efficiently yes
    Tech Support Good, query-writing:simple Poor, query-writing:complex
    Schema Fixed Not fixed. We can have variable
    Use Cases - Schema Flexibility: Allows Flexible schema
    - Data Locality: Documents in a NoSQL database often stores all related information together. suitable case for Sharding
    - Horizontal Scaling Support: noSQL databases are designed to scale Horizontal