Types of Databases

When you walk into a hardware store, you do not see one type of screwdriver. You see flathead, Phillips, Torx, hex — each shaped for a specific kind of screw. Using the wrong one strips the head and wastes time. Databases are the same. Relational databases are extraordinarily good at what they do. They are also genuinely wrong for certain problems. The engineers who invented those alternatives were not being contrarian — they were solving problems that relational databases could not solve efficiently at scale.

Here is the core tension that drove the creation of every non-relational database type:

This module maps every major type of database — what it is built for, where it breaks down, and which Indian companies actually use it. By the end, you will be able to answer the question "which database should we use?" for any system you are ever asked to design.

To understand why different databases make different choices, you need to understand the CAP theorem. It is one of the most important concepts in all of distributed systems, and it directly explains every design decision you will see in this module.

The CAP theorem states that a distributed data system can guarantee at most two of the following three properties simultaneously:

In any real distributed system, network partitions will happen — servers go down, cables get cut, data centres lose connectivity. So Partition Tolerance is not optional. Every production database must tolerate partitions. This means the real choice is always between C and A — consistency or availability — when a partition occurs.

CP systems (Consistent + Partition Tolerant) choose to return an error or wait rather than return potentially stale data. PostgreSQL and MySQL are CP — during a partition, they will refuse to serve requests rather than risk returning incorrect data. AP systems (Available + Partition Tolerant) always respond, but the response might be slightly stale. Cassandra and CouchDB are AP — they always return a result, but it might not reflect the very latest write from a node that is currently unreachable.

Neither choice is wrong. It depends entirely on what your application cannot tolerate. A banking app cannot tolerate incorrect balance information — CP. A social media feed can tolerate showing a post from 2 seconds ago — AP. Every database type in this module makes one of these two choices.

You already know relational databases from Modules 01 and 02. They store data in tables with rows and columns, enforce relationships through foreign keys, guarantee ACID properties, and are queried with SQL. They are the right choice for the vast majority of business applications — and they have been for 50 years.

When relational databases are the right choice

Use a relational database when your data is structured and well-defined — you know in advance what columns exist and what types they hold. When relationships between entities matter and must be enforced — customers have orders, orders have items. When you need complex queries — multi-table joins, aggregations, subqueries, window functions. When ACID guarantees are non-negotiable — financial transactions, inventory management, booking systems.

When relational databases struggle

RDBMS starts to struggle when your schema changes frequently and unpredictably — adding new columns to a 500-million-row table takes hours and locks the table. When data is inherently hierarchical or graph-shaped — storing a social network's friend-of-friend relationships in relational tables requires complex recursive queries. When write throughput requirements exceed what a single master node can handle and you need to shard across hundreds of servers — relational databases can be sharded, but it is operationally complex and breaks some SQL features.

A document database stores data as documents — typically JSON or BSON objects — rather than rows in tables. Each document is a self-contained unit that can have any structure. Two documents in the same collection (the NoSQL equivalent of a table) can have completely different fields.

What a document looks like

{
  "_id": "cust_001",
  "first_name": "Aisha",
  "last_name": "Khan",
  "city": "Bangalore",
  "loyalty_tier": "Gold",
  "contact": {
    "email": "aisha.khan@gmail.com",
    "phone": "9876543210"
  },
  "recent_orders": [
    { "order_id": 1001, "total": 856, "date": "2024-01-05" },
    { "order_id": 1016, "total": 445, "date": "2024-02-14" }
  ],
  "preferences": ["organic", "dairy-free"]
}

Notice what is different from the relational model. The customer's contact details are embedded inside the document as a nested object — not in a separate contacts table. Recent orders are embedded as an array. Preferences are a free-form array. There are no foreign keys and no JOINs needed to get this customer's complete profile — it is all in one document, fetched in one read.

The core advantage — read performance for document-shaped data

In a relational database, getting a customer's profile plus their last 5 orders plus their preferences would require at minimum 3 JOINs across 3 tables. Each JOIN means more disk reads, more coordination, more CPU. In a document database, the same query is a single document fetch — one read, one result. For read-heavy workloads where the access pattern is always "give me everything about this entity," document databases are significantly faster.

The core weakness — no JOINs, limited cross-document consistency

Document databases do not support JOINs. If you need to find all customers who ordered a specific product, you either embed so much data that the document becomes enormous and stale, or you do multiple queries and join them in application code — which is slower and more complex than a SQL JOIN. Transactions across multiple documents in different collections are also either not supported or limited — atomicity within a single document is guaranteed, but cross-document atomicity is not always available.

MongoDB — the dominant document database in India

MongoDB is used by Zomato for restaurant and menu data (menus change constantly — no fixed schema), by Ola for driver and ride metadata, and by many early-stage startups that move fast and cannot afford to define a rigid schema before the product has found its shape. It is also extremely popular for storing event logs, user activity data, and any data where the structure varies per record.

A key-value database is the simplest possible database: every piece of data is stored as a pair — a unique key and a value. The value can be anything: a string, a number, a JSON object, a binary blob. You retrieve data by key. There is no schema, no columns, no relationships, no query language. Just: set(key, value) and get(key).

This radical simplicity is the source of their superpower. Because there is nothing to parse, plan, or coordinate — just a key lookup in memory — key-value databases are extraordinarily fast. Redis, the most popular key-value database, can serve over 1 million operations per second on a single node with sub-millisecond latency. No relational database comes close to this for simple key lookups.

What key-value databases are used for

Redis — the key-value database every Indian startup uses

Redis (Remote Dictionary Server) stores everything in memory, which is what makes it fast. It also supports persistence — writing snapshots to disk so data survives restarts. Redis is not just a simple key-value store — it supports rich data structures: strings, lists, sets, sorted sets, hashes, streams, and geospatial indexes. This makes it useful for a surprisingly wide range of use cases beyond simple caching.

Almost every Indian tech company of any size runs Redis. Swiggy uses Redis to cache restaurant menus — a menu does not change every second, so serving it from Redis instead of PostgreSQL handles the 50× traffic spike at 7 PM without the database breaking a sweat. Razorpay uses Redis for rate limiting API keys. PhonePe uses it for session management across hundreds of millions of users.

Column-family databases (also called wide-column databases) store data in tables, but the tables work very differently from relational tables. In a column-family database, each row can have a completely different set of columns — and tables can have millions of columns. Data is stored physically by column rather than by row, which makes range scans across specific columns extremely fast.

The dominant column-family database is Apache Cassandra. It was built by Facebook to handle their Inbox search — billions of messages, hundreds of millions of users, write rates that no relational database could sustain. Cassandra is designed from the ground up for massive write throughput and linear horizontal scalability — add more nodes and throughput scales linearly.

How Cassandra is different from PostgreSQL

Who uses Cassandra in India

Flipkart uses Cassandra for their product catalogue and recommendation engine — hundreds of millions of products, billions of user interaction events, all written at a rate no relational database could absorb. Ola uses Cassandra for ride event data — every GPS ping from every driver, every second, across millions of active rides. Hotstar (now JioCinema) used Cassandra for user watch history and playback state. The pattern is consistent: Cassandra is chosen when write volume is the primary constraint and the data does not need complex relational queries.

In a relational database, relationships are stored implicitly through foreign keys and are reconstructed at query time through JOINs. In a graph database, relationships are first-class citizens — they are stored explicitly as edges with their own properties, and the database is optimised to traverse them. This makes certain types of queries dramatically faster than any relational equivalent.

The graph model

A graph database stores data as nodes (entities — a person, a product, a location) and edges (relationships between entities — FOLLOWS, PURCHASED, LOCATED_IN). Both nodes and edges can have properties. The database is physically designed for traversal — following edges from node to node — which is what makes multi-hop relationship queries fast.

Where relational databases fail at graph queries

Consider this question: "Find all users who might know Aisha Khan — specifically people who are followed by at least 3 of Aisha's direct followers, but who Aisha does not already follow." In SQL this requires multiple levels of self-joins on a users and follows table. On a social network with 100 million users, this query takes minutes — the JOIN fan-out is exponential. In Neo4j (the dominant graph database), the same query is expressed as a simple graph traversal and executes in milliseconds because the edges are physically stored next to their nodes.

Where graph databases are used in India

LinkedIn India's connection graph, fraud detection at fintech companies (Razorpay uses graph analysis to detect fraud rings — accounts that share phones, addresses, or devices form a graph, and suspicious clusters become visible), recommendation engines at e-commerce companies (customers who bought this also bought that — a product graph), and knowledge graphs at content platforms. Neo4j and Amazon Neptune are the most common graph database choices in production.

A time-series database is optimised for storing and querying data that is indexed by time — measurements taken at regular intervals, or events that occur with a timestamp. Every data point has a timestamp, and the most common query pattern is "give me all values for this metric between time A and time B."

Why time-series data is different

Time-series data has properties that standard databases do not optimise for. It is append-only — you almost never update or delete historical measurements. It arrives in time order — writes are always for "now." It is queried in ranges — "last 6 hours," "last 30 days." It is often aggregated — "average CPU per 5-minute bucket." And it grows without bound — a server emitting metrics every second generates 86,400 data points per day, millions per year.

Relational databases can store this data, but they are not optimised for the write rate (millions of inserts per second across thousands of metric series) or the range-aggregation query pattern. Time-series databases use specialised storage formats — columnar compression, time-partitioned storage — that make them 10–100× more efficient for this specific access pattern.

Popular time-series databases

InfluxDB is the most popular open-source time-series database — used for application metrics, server monitoring, and IoT sensor data. TimescaleDB is PostgreSQL with time-series extensions — you get full SQL plus time-series optimisations, which makes it popular at companies that already run PostgreSQL and want one less database to operate. Prometheus is the standard for infrastructure metrics in Kubernetes environments — almost every Indian startup running on k8s uses Prometheus with Grafana dashboards.

Who uses time-series databases in India

Every company running cloud infrastructure uses time-series databases for monitoring — CPU, memory, latency, error rates, request volume. Swiggy monitors millions of time-series metrics across thousands of microservices. PhonePe tracks transaction success rates per second across payment rails. Ola tracks GPS pings and driver location updates. Any IoT application — smart meters, factory sensors, connected vehicles — is a natural time-series use case. Tata Motors uses time-series databases for vehicle telemetry from their connected car fleet.

No production company uses just one type of database. Every system of meaningful complexity uses two, three, or four database types simultaneously — each handling the part of the problem it is best suited for. Here is how two well-known Indian companies actually structure their data infrastructure.

Swiggy — food delivery at scale

Razorpay — payment infrastructure

When you are designing a system and need to choose a database, run through these five questions in order. The answers will almost always point to the right choice.

For this entire SQL course, you will use DuckDB — a modern analytical relational database that runs in your browser. Every query you write here translates directly to MySQL, PostgreSQL, or any other relational database. The relational model and SQL are universal — learn them once and you can work with any relational database on day one.

You are a backend engineer at a Series B fintech startup in Hyderabad. The company processes UPI payments for small merchants. The CTO calls a system design meeting — you have hit 50,000 transactions per day and need to plan for 5 million. You are asked to review the current architecture and recommend database changes.