Madhukar Vissapragada

Posted on Jul 28

SQL vs NoSQL: Choosing the Right Database Through a Real System Design

#software #database #distributedsystems #systemdesign

Choosing the Right Database — A Ticket Broker Platform Story

Introduction

Every engineer faces this question at some point:

"Should I use SQL or NoSQL for this?"

Most answers you find online are vague — "use NoSQL for scale" or "use SQL for structured data." But the real answer depends entirely on your system's requirements, access patterns and growth trajectory.

In this blog we will walk through the design of a ticket broker platform — a B2B product used by professional ticket brokers to manage their inventory, auto-price tickets and broadcast listings to secondary marketplaces like StubHub and SeatGeek. We will use this real world system to show you exactly when SQL is the right choice and when NoSQL becomes unavoidable.

The Platform

Professional ticket brokers buy tickets from primary marketplaces like Ticketmaster and Box Office at face value. They then resell these tickets on secondary marketplaces like StubHub and SeatGeek at a higher price. Our platform sits in the middle — it helps brokers manage their inventory, automate pricing and sync listings across multiple secondary marketplaces simultaneously.

Primary Marketplaces     Broker Platform        Secondary Marketplaces
(Ticketmaster,        →  (Our B2B Product)  →   (StubHub, SeatGeek)
 Box Office)             manages inventory,
                         auto-price, sync

We have 50,000 brokers using this platform. Each broker:

Has multiple purchase accounts on primary marketplaces — because each account has buying limits
Has multiple seller accounts on secondary marketplaces — to maximise listing reach
Can hold up to 1 million listings including historical data

SQL Databases

Relational databases predate modern computers. They are one of the most mature technologies in software engineering and people often use the terms "SQL database" and "relational database" interchangeably.

When we talk about SQL databases we are referring to relational databases — databases that store data in the form of tables where tables are related to each other through keys.

SQL (Structured Query Language) is the language used to query these databases and fetch data.

Some of the most widely used relational databases include:

MySQL
PostgreSQL
SQLite
Oracle Database
IBM DB2
Amazon RDS

Most cloud providers also offer managed database services where the provider handles infrastructure, backups, scaling and maintenance for you. Amazon RDS, IBM DB2 and Oracle Database are popular examples of managed services.

Normalization

Normalization ensures that data is not redundant and that there are no anomalies in the database.

To understand why normalization matters, let us look at our listings table.

Assume we do not have a separate users table. Instead all user information and listing information are stored in one giant table:

Now assume an event is cancelled and we need to delete all listings for that event. We run a delete query — and accidentally delete the broker's account along with the listings because the user data was stored in the same rows.

This is a deletion anomaly — a direct consequence of not normalizing the data.

After normalization we split the data into separate tables — users, listings, events — each with its own responsibility. Deleting listings for a cancelled event only removes rows from the listings table. The broker's account in the users table is completely untouched.

Strong Schema

Before designing any system it is important to identify all the entities involved. Once we have identified the entities we define a schema that adheres to normalization principles.

A strong schema gives us several advantages:

1. Clarity on what the data looks like

We always know what a row holds
We always know what type of data each column holds
If we try to insert data that violates the schema the database server throws an error immediately

2. Only structured data is stored
A strict schema enforces that only well defined, structured data enters the database. There is no room for inconsistent or malformed records.

3. Constraints on columns
We can apply strict constraints on table columns — NOT NULL, UNIQUE, CHECK constraints, foreign key constraints. These enforce business rules at the database level itself rather than relying on application code.

For our broker platform this means:

A listing cannot exist without a valid user_id
A purchase account cannot belong to a non-existent primary marketplace
A seller account must always have a valid api_token

The database enforces these rules automatically — no application level validation needed.

ACID Transactions

SQL databases guarantee ACID properties — one of the most important reasons to choose SQL for systems where data integrity is critical.

A — Atomicity

Atomicity means all or nothing. A transaction will never be left in a partial state. If anything fails during a transaction, the entire operation is rolled back to its original state as if nothing happened.

In our broker platform, when a sale request comes in several things need to happen together:

1. Mark the listing as sold
2. Deduct the ticket quantity from inventory
3. Create an order record
4. Update the broker's account balance

If step 3 fails after steps 1 and 2 have already completed — we cannot leave the system in that state. Atomicity ensures that either all four steps succeed together or none of them do.

C — Consistency

Consistency means the database enforces business rules at the schema level. Certain values can never be null and certain column values can never be negative — the database guarantees this on every write.

For our broker platform:

face_value can never be negative — a ticket cannot have a negative price
event_name can never be null — a listing must always belong to a named event
quantity can never be negative — you cannot list tickets you do not have

Without consistency enforcement we would need to add these validations in every part of the application code. A single missed validation could allow corrupt data to enter the database.

I — Isolation

Isolation ensures that when multiple transactions happen simultaneously they do not interfere with each other. SQL databases achieve this through internal locking mechanisms.

In our broker platform when a listing is broadcasted to multiple secondary marketplaces like StubHub and SeatGeek simultaneously, multiple sale requests can arrive at the exact same time for the same tickets. Without isolation both transactions could read the listing as available and both could mark it as sold — resulting in a double booking.

SQL databases prevent this using locks — only one transaction can hold the lock on a listing at a time. The second transaction waits, checks availability after the lock is released, and correctly returns an error if the listing is already sold.

D — Durability

Durability guarantees that once a transaction is committed it is permanently written to non-volatile storage. The data survives system crashes, power failures and restarts.

However durability alone is not enough. HDDs can physically fail. This is why durability in production systems is paired with replication — a primary DB with one or more replicas. If the primary DB's HDD crashes, a replica takes over immediately and no committed transaction is lost.

For a platform dealing with real money and real ticket sales — losing committed data is unacceptable.

Other SQL Features

Beyond ACID and normalization, SQL databases come with a rich set of built-in features:

Joins — query data across multiple related tables in a single statement
Aggregate queries — SUM, COUNT, AVG, MIN, MAX across rows
Group by — group results by a column and aggregate within each group
Recursive queries — query hierarchical data
Window functions — running totals, rankings, moving averages
JSON support — modern SQL databases like PostgreSQL support storing and querying JSON

Designing the System with SQL

All the SQL strengths we discussed — normalization, strong schema, ACID transactions and rich querying — are exactly what our broker platform needs.

The Database Schema

Our system has six entities — each with a clear responsibility and clean relationships between them.

Users
Every broker on our platform is a user. The users table stores the broker's identity — name, email, phone number and location. The user_id is a UUID primary key that uniquely identifies every broker.

Primary Marketplace
Primary marketplaces are platforms like Ticketmaster and Box Office where tickets are sold at face value. This is a simple lookup table — we just store the marketplace ID and name.

Purchase Accounts
A broker cannot buy unlimited tickets from a single account — each account on a primary marketplace has buying limits. So brokers create multiple purchase accounts across primary marketplaces to maximize how many tickets they can buy.

Each purchase account belongs to one user and one primary marketplace. The api_token stores the credentials the platform uses to interact with that marketplace on the broker's behalf.

Secondary Marketplace
Secondary marketplaces are platforms like StubHub and SeatGeek where brokers resell tickets at a higher price. Again a simple lookup table storing marketplace ID and name.

Seller Accounts
Similar to purchase accounts, brokers create multiple seller accounts across secondary marketplaces to maximize their listing reach. Each seller account belongs to one user and one secondary marketplace with its own api_token.

Events
Every listing is tied to an event. The events table stores the event name, URL, venue, date and time, state and minimum price. One event can have many listings from many brokers.

Listings
Listings are the core of the platform — they represent the tickets a broker wants to sell. A listing belongs to one user and one event. It stores the physical ticket details — section, row, seat, face value, quantity — along with ticket class, type and listing notes.

How Big is Our Data ?

Our system looks clean and well designed. But we have 50,000 brokers each holding up to 1 lakh listings on average. Let us calculate how large each table actually grows.

Users table

50,000 brokers × 200 bytes per user = 10 MB

Purchase Accounts table

50 purchase accounts per broker × 50,000 brokers × 100 bytes per account
= 2,500,000 accounts × 100 bytes
= 250 MB

Seller Accounts table

50 seller accounts per broker × 50,000 brokers × 100 bytes per account
= 250 MB

Primary Marketplace table

20 marketplaces × 100 bytes = 2 KB (negligible)

Secondary Marketplace table

30 marketplaces × 100 bytes = 3 KB (negligible)

Events table

100,000 events × 500 bytes per event = 50 MB

Listings table

1,00,000 listings per broker × 50,000 brokers × 500 bytes per listing
= 5,000,000,000,000 bytes
= 5 TB

Summary

Table	Size
Users	10 MB
Purchase Accounts	250 MB
Seller Accounts	250 MB
Primary Marketplace	~2 KB
Secondary Marketplace	~3 KB
Events	50 MB
Listings	5 TB

Can SQL databases handle this scale ?

Weaknesses of SQL Databases

Surprisingly the strengths of SQL databases can also be their weaknesses.

Normalization

On the frontend we always render data in a denormalized manner — a listing page shows the broker name, the event details and the ticket information all together in one view. But in our normalized SQL schema this data lives across multiple tables.To serve that view we need joins.
This is fine at small scale. But joins become expensive as data grows — and the problem gets significantly worse when the database is sharded.

Fanout queries

When listings are sharded across multiple servers, a join that touches listings and events may need to reach across different shards simultaneously. The database has to fan out the query to multiple shards, collect the results and merge them. This is slow and resource intensive — and it happens on every request.

Network overhead

In a sharded environment data from different shards has to travel over the network to be joined. This adds latency on top of the computational cost of the join itself.

Strong Schema

SQL enforces a strict schema which is a strength for structured data but becomes a limitation when dealing with unstructured or variable data.

In our broker platform each secondary marketplace expects listing data in its own specific format. If we could store listings in an unstructured format directly in the database we could skip the conversion overhead on every broadcast request. SQL's rigid schema makes this difficult.

ACID

ACID works perfectly when all data lives on the same server. The moment data is sharded across multiple servers ACID is effectively nullified.

Atomicity across servers
Achieving atomicity across shards requires something like 2 Phase Commit. This is exactly what the PACELC theorem describes — if you want consistency in a distributed system you have to give up availability and latency. 2PC is slow, complex and becomes a bottleneck at scale.

Isolation across servers
Isolation is even harder in a distributed environment. On a single server SQL uses internal row level locks. Across multiple servers you need distributed locks — locks that span the network. Acquiring and releasing distributed locks adds significant latency and introduces the risk of deadlocks across servers.

In our broker platform once the listings table is sharded across servers — maintaining ACID guarantees becomes a painful and expensive problem.

Sharding is hard in SQL

SQL databases have built-in support for replication but not for sharding. When data grows beyond a single server you have two options — use a third party extension or manage the sharding logic yourself in the application code.

In our broker platform the listings table hits 5 TB — it cannot fit on a single server. We are forced to shard. And SQL gives us no native help to do it.

This is the fundamental limitation of SQL at scale — it is designed to run on one powerful machine. Everything beyond that is an afterthought.

This is where NoSQL comes in.

NoSQL Databases

NoSQL stands for "Not Only SQL." It is not a replacement for SQL — it is a family of databases designed specifically for the problems SQL struggles with at scale.

BaSE

NoSQL databases follow the BaSE model instead of ACID.

BA — Basically Available

The system remains available most of the time for most users — but some users or services may be temporarily unavailable. This is directly linked to Partition Tolerance in the CAP theorem. When a network partition occurs the system chooses to stay available rather than refusing requests until consistency is restored.

S — Soft State

Unlike SQL where a transaction is strictly all or nothing, NoSQL allows transactions to be in a partial state for a period of time. The system does not guarantee that data is immediately consistent across all nodes — but given enough time the data will converge to a consistent state.

E — Eventual Consistency

By default NoSQL databases are eventually consistent. Writes propagate across nodes over time. If you need immediate consistency you can use something like a two phase commit — but this comes at the cost of latency and availability, which defeats the purpose of using NoSQL in the first place.

Horizontally Scalable

NoSQL databases are designed from the ground up for horizontal scaling. Sharding and replication can be configured automatically — no manual management layer needed.

This is the fundamental difference from SQL:

NoSQL databases treat sharding as a first class citizen — it is built into the database engine itself

Denormalized Data

NoSQL does not enforce a strict schema. Data can be stored in an unstructured or semi-structured format — typically as JSON documents.

Since the frontend always renders data in a denormalized way — showing broker name, event details and listing information together in one view — NoSQL stores it that way too. This eliminates the need for joins entirely.

Weaknesses of NoSQL

NoSQL databases are not a silver bullet. They typically do not natively support:

Joins
Aggregate queries
Grouping
CTE (Common Table Expressions) queries
And many other SQL features

NoSQL does one thing exceptionally well — it scales horizontally at massive data volumes. Everything else is a tradeoff you consciously make in exchange for that scale.

Storing Listings in NoSQL

Now that we understand NoSQL's strengths and tradeoffs, it is clear that the listings table belongs in a NoSQL database. The 5 TB of listing data can be sharded automatically across nodes — no manual sharding logic needed.

But to fetch data efficiently from a NoSQL database we need two things:

1. Sharding key — to reach the right shard

The sharding key determines which shard a listing lives on. The router hashes the sharding key and directs the request to the correct shard. All listings with the same sharding key value live on the same shard.

2. Primary key — to reach the right listing

Once we are on the correct shard, the primary key identifies the exact listing we need within that shard.

Request comes in
      ↓
Hash sharding key → find the right shard
      ↓
Use primary key → find the right listing within the shard
      ↓
Return listing ✅

What Constitutes a Good Sharding Key?

Choosing the right sharding key is one of the most critical decisions in designing a distributed system. A bad sharding key leads to hotspots, uneven load distribution and expensive fanout queries. Let us evaluate candidate sharding keys for our broker platform.

1. Equal data and load distribution across servers

The sharding key must distribute data and load evenly across all shards. No single shard should receive a disproportionate amount of traffic.

If we choose event_id as the sharding key:
Old events will never receive new requests — their shards will sit idle. Popular events like a Taylor Swift concert will receive massive traffic — their shard gets overwhelmed. Uneven distribution.

If we choose genre as the sharding key:
Some genres like pop and rock are far more popular than others. Shards for popular genres get bombarded while shards for niche genres sit idle. Uneven distribution.

If we choose user_id as the sharding key:
All broker data is evenly distributed across shards — regardless of which events they hold or which genres they prefer. Load is also evenly distributed since every broker generates roughly similar request volume.

2. High Cardinality

Cardinality refers to the number of distinct possible values a sharding key can have. A good sharding key must have high enough cardinality to support as many shards as the system will ever need.

If we choose event_id as the sharding key:
We may have a good number of events — say 100,000. But this limits the maximum number of shards to 100,000. And event_id still fails on equal distribution so it is ruled out regardless.

If we choose genre as the sharding key:
We have around 35 genres. This limits us to a maximum of 35 shards. If data grows beyond what 35 shards can hold we cannot provision new shards — there simply are not enough distinct genre values to route data to them.

If we choose user_id as the sharding key:
A UUID is 16 bytes — 128 bits — giving 2^128 possible values. Even at Google's scale we will never run out of distinct values. As data grows we can always provision new shards and the key will always have enough cardinality to route data to them.

3. Part of every read and write request

The sharding key must be present in every read and write request. If a query does not include the sharding key the router cannot determine which shard to send it to — resulting in a broadcast query across all shards.

In our broker platform every request includes the user_id — whether a broker is viewing their inventory, updating a listing or processing a sale. This makes user_id a natural sharding key since it is always available at query time.

4. No fanout reads for frequent queries

The most frequent queries must not require fanout reads. A fanout query hits multiple shards simultaneously — it is expensive and slow.

It is acceptable for rare or analytical queries to fanout. But the hot path — the query that runs thousands of times per second — must be a single shard lookup.

In our broker platform the most frequent query is "fetch all active listings for broker X." With user_id as the sharding key this always hits exactly one shard.

5. Immutable

The value of the sharding key must never change. If it changes the record needs to move to a different shard — an extremely expensive operation that requires downtime or complex migration logic.

user_id is assigned at registration and never changes — making it a safe and stable sharding key.

Applying this to our broker platform

Criteria	user_id	event_id	genre
Equal distribution	✅ Even across all brokers	✕ Popular events overloaded	✕ Popular genres overloaded
High cardinality	✅ UUID — 2^128 values	✅ Good but fails distribution	✕ Max 35 shards
Part of every request	✅ Always present	✕ Not always known	✕ Not always known
No fanout for frequent queries	✅ Single shard per broker	✕ Broadcast query	✕ Broadcast query
Immutable	✅ Never changes	✅ Never changes	✅ Never changes

user_id is the right sharding key for listings.

Types of NoSQL Databases

There are many NoSQL databases — each designed for a different purpose. Let us look at the two most popular types and understand which one fits our use case.

Document Databases

Document databases store unstructured or semi-structured data. Think of them as a collection of JSON files distributed across multiple servers.

Examples: MongoDB, CouchBase, Elasticsearch

How data is stored

Data is stored as JSON or JSONB. Each record is called a listing — or more generically a record — and has a unique identifier:

{
  "_id": "listing_001",
  "user_id": "broker_123",
  "event_id": "event_456",
  "section": 101,
  "row": "A",
  "seat": 5,
  "face_value": 150,
  "quantity": 2,
  "ticket_class": "VIP"
}

The _id field is the primary key. It can be auto-generated by the database or supplied by the application.

Querying

Query by _id for direct lookup
Query by top level attributes using indexes
Full text search across fields
Updating a single attribute requires loading and rewriting the entire record

Why updating a single field requires rewriting the entire record

RAM and HDD are byte addressable — not bit or field addressable. To update a single attribute in a 500 byte record, the entire record has to be loaded into memory, the field updated and the whole record written back to disk. This is a tradeoff we accept for the flexibility of schema-free storage.

Sharding key and primary key

_id is the primary key
If no explicit sharding key is defined, _id is used as the sharding key by default

This is important for our broker platform. If we shard by _id then listings for the same broker will be scattered across multiple shards. A query like "fetch all listings for broker X" would become a broadcast query — it fans out to every shard looking for that broker's listings. This is expensive.

Ideal for: Product listings, unstructured data, full text search

Key-Value Databases

A key-value database is essentially a giant distributed hashmap.

Examples: Redis, DynamoDB, Memcached

How data is stored

key   → value
"listing:001" → "{face_value: 150, quantity: 2}"
"session:abc" → "{user_id: 123, expires: ...}"

Both key and value are strings. The value format varies by database — some support only plain strings, others support richer types like lists, sets and sorted sets.

There is no schema — no tables, no columns, no types enforced.

Querying

Only three operations:

get(key)          → fetch a value
put(key, value)   → insert or update a value
delete(key)       → remove a value

Strengths

Extremely simple
Extremely fast — O(1) lookups
No overhead of schema validation or query planning
primary key and sharding key both are same

Weaknesses

No complex queries — no joins, no aggregations, no indexing, no full text search
No relationships between keys
Not suitable for data that needs to be queried in multiple ways

When to use

Caching — store frequently accessed data for fast retrieval
Session management — store user sessions

- Simple data that does not require joins or complex queries

Choosing the Right Database for Our System

Now that we have chosen user_id as the sharding key, let us evaluate our most common access patterns and see whether a Document Database or a Key-Value Database better suits our workload.

Query 1 — Get all active inventory for a broker

Input:
user_id

This is our hottest query. Since all listings belonging to a broker are stored on the same shard (user_id is the sharding key), the request is routed directly to a single shard.

Result: ✅ Single-shard lookup (fast)

Query 2 — Get all tickets available for an event

Input:
event_id

Since the data is partitioned by user_id rather than event_id, listings for the same event are distributed across many shards.

To answer this query, the system must query every shard and merge the results.

Result: ⚠ Fanout query

Fortunately, this query is much less frequent and internally done by business analysts, making the tradeoff acceptable.

Query 3 — Sale notification from a secondary marketplace

When a marketplace such as StubHub or SeatGeek notifies us that a ticket has been sold, the request typically contains the seller account information.

Secondary Marketplace
        │
        ▼
seller_email_id
        │
        ▼
SQL Database (Seller Accounts)
        │
Find user_id
        │
        ▼
Key-Value Database (Listings)
        │
Lookup using user_id + event_id + listing_id
        │
        ▼
Update broker inventory

The first lookup happens in the relational database because seller accounts are stored there.

Once we obtain the user_id, we can directly route the request to the correct shard.

Result: ✅ Single-shard lookup after one SQL lookup

Document Database

Query	Performance
Get broker inventory	✅ Single shard
Get tickets for an event	⚠ Fanout query
Process sale request	✅ Lookup broker shard using `user_id`, then filter by `event_id`

Filtering by event_id requires searching through the broker's documents after reaching the correct shard.

Key-Value Database

A composite key can be designed around our access pattern:

user_id#event_id#listing_id

Once routed to the correct shard, locating a listing becomes a direct key lookup.

Query	Performance
Get broker inventory	✅ Single shard
Get tickets for an event	⚠ Fanout query
Process sale request	✅ Direct key lookup

Compared to a document database, this avoids scanning documents within the shard because the lookup is performed directly using the key.

Final Decision

Our hottest operations are:

Fetch a broker's inventory
Process sale notifications from secondary marketplaces
Update inventory after a sale

All of these operations naturally include the user_id, which is also our sharding key.

The only expensive query is retrieving all listings for an event_id, which becomes a fanout query. Since this is not on the critical request path, we accept this tradeoff.

For these access patterns, a Key-Value Database provides the best fit because:

Direct O(1) key lookups
Natural alignment with our sharding strategy
Extremely fast inventory retrieval
Efficient sale processing
Simple horizontal scaling

Therefore, for the listings service, a Key-Value Database is the preferred choice.

Key Takeaways

Throughout this blog we designed a ticket broker platform and used it to understand when SQL is the right choice and when NoSQL becomes a better fit. Here are the major lessons:

1. Start with the data model, not the database

Before choosing a database, identify the entities, relationships, and access patterns. The database should be selected based on the system's requirements—not personal preference or popularity.

2. SQL excels at maintaining data integrity

Relational databases provide:

Normalization to eliminate redundancy
Strong schemas to enforce data correctness
ACID transactions for reliable updates
Rich querying capabilities such as joins, aggregations, and window functions

These features make SQL an excellent choice for structured business data.

3. Scale changes the design

As our platform grew, the listings table alone reached nearly 5 TB.

At this scale:

Joins become expensive
Cross-shard transactions become difficult
Manual sharding adds operational complexity

The database design must evolve with the scale of the system.

4. NoSQL solves a different problem

NoSQL is not a replacement for SQL.

It is designed for:

Horizontal scaling
Automatic sharding
High throughput
Flexible schemas
Fast key-based access

The tradeoff is giving up many of the powerful querying features available in relational databases.

5. Choosing the right sharding key is critical

A good sharding key should:

Evenly distribute data
Have high cardinality
Be present in every read and write request
Avoid fanout queries for hot paths
Never change over time

For our platform, user_id satisfied all these properties, making it the ideal sharding key.

6. Choose the database based on access patterns

The hottest operations in our system are:

Fetch all inventory for a broker
Process marketplace sale notifications
Update broker inventory

These operations naturally include the user_id, allowing every request to be routed directly to a single shard.

Rare queries such as "Show all tickets for an event" become fanout queries, but that is an acceptable tradeoff because they are not on the critical path.

Final Thoughts

There is no universally "best" database.

SQL and NoSQL solve different problems, and modern systems often use both.

In our ticket broker platform:

SQL manages users, marketplaces, accounts, and events where relationships and consistency are essential.
Key-Value NoSQL stores listings, where horizontal scalability and extremely fast lookups are the primary requirements.

The best architecture is rarely about choosing SQL or NoSQL.

It is about understanding your data, your access patterns, and your scaling requirements—and using each technology where it provides the greatest advantage.