DEV Community: Mhamd Ghanoum

The Reconnect Bug Where Marking a User Online Erased the Data I Needed for Replay

Mhamd Ghanoum — Thu, 15 Oct 2026 06:58:40 +0000

While testing a reconnect system for a deliverer-facing WebSocket gateway: when someone reconnects after losing their connection, they should get a snapshot of their current job plus any events they missed while offline. Two pieces, both of which I was confident were correct on their own.

Marking presence:

async markOnline(userId: number) {
  await this.prisma.user.update({
    where: { id: userId },
    data: { isOnline: true, lastSeenAt: new Date() },
  });
}

Computing what was missed:

async replayDelivererEvents(delivererId: number) {
  const deliverer = await this.prisma.delivererProfile.findUnique({
    where: { id: delivererId },
    select: { user: { select: { lastSeenAt: true } }, activeJobId: true },
  });
  // ...fetch events since deliverer.user.lastSeenAt
}

And the connection handler that used both:

async handleConnection(client: Socket) {
  const deliverer = await this.getDelivererByUserId(payload.sub);

  await this.sessionService.markOnline(payload.sub);           // step 1
  const missedEvents = await this.replayService.replayForDeliverer(deliverer.id); // step 2

  client.emit('events.replay', missedEvents);
}

I tested it: disconnect, trigger a job assignment while offline, reconnect. events.replay came back empty, every time. The event genuinely happened. The query genuinely should have found it.

Why

Walk through the actual timeline. A deliverer disconnects — markOffline correctly stamps lastSeenAt with the disconnect time. Later, they reconnect. handleConnection fires, and the first thing it does is call markOnline, which immediately overwrites lastSeenAt to right now. Only after that does it call replayForDeliverer, which reads lastSeenAt to compute "what happened since they were last seen" except by then lastSeenAt no longer holds the disconnect time. It holds the current moment, because the line above just wrote over it.

getEventsSince({ since: lastSeenAt }) effectively became since: right now. The replay feature couldn't fail more quietly — no error, no exception, just a correctly-shaped empty array every single time.

What makes this bug worth writing about isn't the fix — it's that neither piece of code was wrong in isolation. Marking someone online should update lastSeenAt. Replay should read lastSeenAt. The bug only exists in the order they ran in, and that kind of bug doesn't show up by staring at either function — only by tracing the actual sequence of operations across the whole request.

The fix
Read before you overwrite:

async handleConnection(client: Socket) {
  const deliverer = await this.getDelivererByUserId(payload.sub);

  // Replay FIRST, using the lastSeenAt from before this connection
  const missedEvents = await this.replayService.replayForDeliverer(deliverer.id);

  // THEN mark online, which overwrites lastSeenAt for next time
  await this.sessionService.markOnline(payload.sub);

  client.emit('events.replay', missedEvents);
}

No schema change needed. Reordering two already-correct operations was the entire fix.

The part worth remembering

One field, two different jobs — "when did we last hear a heartbeat" and "what's the anchor point for replay" happened to share the same column, and that's exactly what made the collision possible. The one-line reorder fixes today's bug, but it leaves the deeper coupling in place: anything else that later wants to read lastSeenAt for a different purpose can walk into the same trap. The more robust version splits them. a dedicated lastDisconnectedAt, set only on disconnect, read only by replay so two unrelated concerns stop sharing state that one of them mutates as a side effect. I haven't built that yet. It's on the list specifically because I know why it would help, not just that it might.

Calling a Service Inside prisma.$transaction Doesn't Make It Atomic — Here's the Bug That Taught Me That

Mhamd Ghanoum — Mon, 12 Oct 2026 09:20:27 +0000

I was fixing a real bug in a delivery backend I'm building: a customer's order could get created in Postgres even when something downstream failed, leaving an order with no event history behind it. The fix looked simple. Wrap the whole thing in
prisma.$transaction:

const order = await this.prisma.$transaction(async (tx) => {
  const newOrder = await tx.order.create({ data: { ... } });

  await this.eventLogService.recordEvent({
    entityType: 'ORDER',
    entityId: newOrder.id,
    eventType: EVENTS.ORDER.CREATED,
    payload: { orderId: newOrder.id, status: OrderStatus.PENDING_VENDOR_CONFIRMATION },
  });

  return newOrder;
});

I ran it, broke the event-logging step on purpose to test the rollback, and the order still got committed. The transaction did nothing.

Why

$transaction(async (tx) => {...}) opens one database connection and starts a transaction on it. The tx object you get inside the callback is bound to that specific connection. Every query run through tx participates in the same transaction.

My EventLogService.recordEvent method didn't use tx. It used this.prisma — its own injected Prisma client, a completely different connection:

async recordEvent(params) {
  const event = await this.prisma.eventLog.create({ ... }); // not tx — its own connection
  ...
}

Nesting the call inside $transaction changes nothing here. Prisma has no way to know "we're inside a transaction right now, please route all queries through it." The only thing that determines whether a query participates in a transaction is which client object actually executed it. Since recordEvent ran on its own connection, its insert committed instantly and independently, with no awareness that anything else was "inside" a transaction around it.

The fix
Every method that needs to be transaction-capable has to accept an optional transaction client and fall back to the default when none is passed:

async recordEvent(params, tx?: Prisma.TransactionClient) {
  const client = tx ?? this.prisma;
  const event = await client.eventLog.create({ data: params });
  ...
}

Then the caller passes its own tx through explicitly:

await this.eventLogService.recordEvent(params, tx);

Now recordEvent's insert runs on the same connection, inside the same transaction, as everything else. If anything later in that transaction throws, Postgres rolls back the whole connection's uncommitted work — including this insert — because it genuinely was part of the same unit of work, not just nested inside it syntactically.

I retested the same failure scenario. This time, the order didn't get created either. Rollback worked as a single unit, order and event together.

The part worth remembering
Code structure and transaction membership are two different things. A function call being lexically inside a $transaction block tells you nothing about which database connection it actually used. If you're composing transactional logic across services in Prisma — or any ORM with a similar explicit-client model — every service in that chain needs to accept and thread through the transaction client deliberately. There's no implicit "ambient transaction" you can rely on.

The honest cost: this makes service signatures a bit noisier — an extra optional parameter on every method that might need to participate in a caller's transaction. I think that's a fair trade for not silently losing atomicity on exactly the kind of multi-step write where you'd assume you had it.

Why I Chose a Simple Capability Token for Waitless — and When I Would Harden It

Mhamd Ghanoum — Mon, 05 Oct 2026 07:24:37 +0000

While working on Waitless, I needed customers to be able to track their position in a queue and leave the queue without creating an account.

That led to a small but interesting security design question:

How can I authorize an anonymous user without introducing a full authentication system?

My current solution is deliberately simple: each queue entry receives a random UUID token, and that token is used as a capability for the tracking link.

UUID token instead of a sequential ID

A sequential identifier could look like:

/track/123

The problem is that IDs such as 123, 124, and 125 are predictable and enumerable.

For an anonymous tracking system, I don't want a user to be able to guess another customer's resource simply by changing the ID.

Instead, an entry has a separate token:

model Entry {
  id      String @id @default(uuid())
  token   String @unique @default(uuid())

  name     String
  email    String?
  position Int
  status   EntryStatus @default(WAITING)

  queueId String
  // ...
}

The public URL therefore looks more like:

/track/550e8400-e29b-41d4-a716-446655440000

The important point is that the token is not intended to be an ordinary identifier.

It acts as a capability.

What is a capability URL?

A capability can be thought of as:

An unguessable secret that grants access to a specific resource.

In my case:

possess valid token
        ↓
access this queue entry

The capability is deliberately designed to be difficult to guess.

That is different from simply hiding a sequential ID and hoping nobody discovers it.

This distinction matters:

Security by obscurity
→ "Nobody knows the ID."

Capability-based design
→ "Access requires possession of a deliberately unguessable credential."

The second is a much more intentional security model.

Why I kept the token simple for now

Waitless is an anonymous queueing system.

A customer does not need an account, password, or traditional login just to:

see their position
receive queue updates
leave the queue

Introducing a complete authentication system would add considerable complexity to the product and to the user experience.

For the current use case, a capability link gives me a simple model:

Join queue
    ↓
Generate random token
    ↓
Give customer tracking link
    ↓
Customer presents token
    ↓
Server identifies the queue entry
    ↓
Allow limited actions

This is a conscious trade-off.

I'm choosing simplicity and frictionless anonymous access because the current resource is relatively limited in scope.

But the token is also a bearer credential

There is an important consequence.

If possession of the token grants access, then:

whoever possesses the token can use it.

That makes the token a bearer credential.

For example, the current leave() operation relies on the same tracking capability.

So if the token is leaked, copied, logged, or shared, another person could potentially use it.

That means the design is intentionally simple, but it is not the strongest possible security model.

Known limitation

The tracking token currently also functions as a bearer credential for leave(), with no rate-limiting or one-time consumption.

I consider this an accepted limitation of the current design rather than something I want to hide.

A stronger design: capability URL → session cookie

One way to harden the system would be to use the tracking link only as an initial capability.

For example:

/claim/<secret>
      ↓
validate capability
      ↓
create anonymous session
      ↓
HttpOnly + Secure cookie
      ↓
redirect to /track

After that, the token would no longer need to remain in the URL.

The browser would use the session cookie for future requests.

This would reduce some of the risks associated with repeatedly carrying a secret in a URL and would give me more control over session lifecycle, expiration and revocation.

But it introduces another trade-off.

The trade-off: security vs. convenience

The current capability link has an important usability property:

The link itself works across devices.

A customer can open it on their phone, copy it to another device, bookmark it, or reopen it later.

With a one-time capability-to-session exchange, that behavior becomes more complicated.

For example:

Phone
  ↓
claim token
  ↓
session created

Now the same one-time token may no longer be usable on another device.

Supporting multiple devices would therefore require additional identity/session handling, which increases complexity.

For a simple anonymous queue, I decided that this trade-off is not currently justified.

When would this design change?

The answer is: when the product starts having identity requirements.

Imagine Waitless later introduces a reservation system.

Now a customer might have:

Account
   ↓
Reservations
   ↓
Queue entries
   ↓
Payment history
   ↓
Cancellation rules
   ↓
Personal information

The security requirements become much stronger.

At that point, a random tracking capability may no longer be enough as the main authorization mechanism.

I would probably move toward:

User authentication
        ↓
Authenticated session
        ↓
Authorization
        ↓
Reservation / queue access

The capability token could still exist for specific use cases, but it would no longer be responsible for the entire identity model.

UUIDv4 vs. UUIDv7

This also made me think more carefully about what an identifier is supposed to do.

For database identifiers, UUIDv7 is attractive because it is time-ordered and therefore provides better index locality than completely random UUIDv4 values.

For a public capability, however, my primary requirement is different:

I care more about unpredictability than database ordering.

That's why I would keep the concepts separate:

Database ID
    ↓
UUIDv7 can be a good choice

Public capability token
    ↓
Cryptographically random token / UUIDv4

This is an example of a broader engineering principle:

The best identifier for the database is not necessarily the best credential for the client.

What about storing the token itself?

Another improvement would be to store only a hash of the capability:

raw token
    ↓
SHA-256
    ↓
tokenHash

Then the database contains the verifier rather than the original bearer secret.

The application can hash the presented token and compare the result.

This gives additional protection if the database contents are exposed.

Again, it adds some implementation complexity, so it is a hardening step rather than something I needed to introduce simply to make the current Waitless flow work.

My current decision

For the current version of Waitless:

Random capability token
        ↓
Tracking URL
        ↓
Anonymous access
        ↓
Limited actions

I deliberately chose this because it provides a very small and understandable authorization mechanism without forcing customers through registration and login.

The trade-off is that the token is a bearer credential, so the system needs to treat it as sensitive.

I have therefore documented the limitation rather than pretending the design is perfect.

My planned evolution is roughly:

Current
Random capability URL
        ↓
Simple anonymous access


Later
Rate limiting
+ expiry / revocation
+ stronger token handling


When identity becomes important
        ↓
Capability URL
        ↓
Session / authentication
        ↓
Fine-grained authorization

The interesting lesson for me was not simply “use UUIDs.”

It was learning to ask:

What is this value actually supposed to represent?

An identifier, a capability, and an authenticated identity are three different concepts.

Once that distinction is clear, the architecture becomes much easier to reason about.

How I Used PostgreSQL Events and MongoDB Event Logs in Two Different Real-World Systems

Mhamd Ghanoum — Sat, 04 Jul 2026 15:51:22 +0000

I built two systems that both needed event tracking.
I used PostgreSQL in one and MongoDB in the other.
Here's exactly why — and what I learned from doing both.
Although the two systems were completely different in purpose, they shared one architectural requirement: each needed a reliable way to record everything that happened — not just the final state, but the story of how that state came to be.
That sounds simple, but the nature of the events in each system was so different that using the same database for both would have caused real problems. This article explains how I approached event tracking in each system, why the solutions diverged, and what the experience taught me about choosing the right persistence model.

PostgreSQL Events and Projections in the Delivery Backend

The delivery backend was built around structured workflows: vendors create orders, couriers pick them up, jobs move through well-defined states, and batches group multiple deliveries. Every action in this system has a clear relational meaning. An event like ORDER_CREATED or JOB_STARTED isn't just a log entry — it's part of the domain model.
Because of that, PostgreSQL was the natural home for events. Each event needed to be tied to a specific entity, validated against constraints, indexed for fast lookup, and projected into timeline tables that the dashboard could query efficiently.
The event log table looked like this:

model EventLog {
  id          Int             @id @default(autoincrement())
  entityType  EventEntityType
  entityId    Int
  eventType   String
  payload     Json
  createdAt   DateTime        @default(now())

  @@index([entityType, entityId, createdAt])
}

Every event is immutable. Every event belongs to a relational entity. Every event carries structured metadata.
Once an event is written, it is immediately transformed into a timeline entry:

switch (event.entityType) {
  case EventEntityType.ORDER:
    await this.orderTimelineProjection.project(event);
    break;

  case EventEntityType.JOB:
    await this.jobTimelineProjection.project(event);
    break;
}

This projection layer is what makes the system feel fast. Instead of running complex joins to reconstruct history, the dashboard reads from dedicated timeline tables optimized for sequential queries. It also makes debugging easier: if something goes wrong, I can replay the event stream and see exactly how the system reached its current state.
PostgreSQL excels in this environment because the events are structured, relational, and part of the domain's consistency model. They aren't just analytics — they're part of the truth.

MongoDB Event Logs and Aggregation in Waitless

Waitless is a completely different kind of system. It's a real-time queue management platform where customers join via QR code, track their position live, and leave without creating an account. The system generates a lot of events — but these events are not relational. They're behavioral.
A CUSTOMER_JOINED event might contain the customer's position.
A NO_SHOW event might contain a timestamp.
A CUSTOMER_LEFT event might contain a reason.
A CALLED_NEXT event might contain which token was called.

The shape of the metadata changes depending on the event type. Forcing this into a rigid SQL schema would either require dozens of nullable columns or a JSON column that PostgreSQL would struggle to index efficiently at scale.
MongoDB is built for exactly this. Each event is a document with flexible metadata:

@Schema({ timestamps: true })
export class QueueEvent {
  @Prop({ required: true })
  queueId!: string;

  @Prop({ required: true, enum: QueueEventType })
  eventType!: QueueEventType;

  @Prop()
  token?: string;

  @Prop({ type: Object })
  metadata?: Record<string, any>;
}

This flexibility becomes powerful when combined with MongoDB's aggregation pipeline. Waitless computes analytics like total joined, total arrived, total no-shows, completion rate, and drop-off rate — all derived from event counts, not relational queries.
A note on Redis: Waitless already had Redis in the stack backing BullMQ. But Redis is a key-value store optimized for job queues and caching — not for querying across documents or running aggregations. MongoDB was the right fit for this specific need.
The separation between MongoDB and PostgreSQL is also crucial for performance. If analytics were computed in PostgreSQL, every dashboard refresh would compete with real-time queue operations for the same connection pool.

If I had used PostgreSQL for Waitless events, every analytics query would hit the same connection pool as the queue state — meaning a slow dashboard query could delay a customer joining a queue.
MongoDB avoids this entirely. It handles flexible event shapes, high write throughput, and analytics workloads without interfering with the transactional state stored in PostgreSQL.

Why PostgreSQL for One and MongoDB for the Other?

The answer becomes clear once you see the nature of the data.
In the delivery backend, events are part of the domain model. They describe state transitions, belong to relational entities, and require strong consistency. PostgreSQL is the right tool.
In Waitless, events are part of analytics. They are flexible, metadata-heavy, and high-volume. They have no relational meaning — they're behavioral observations about what happened in a queue session. MongoDB is the right tool.
Using PostgreSQL for both would have created unnecessary coupling and performance bottlenecks. Using MongoDB for both would have sacrificed relational guarantees where they mattered most.
This is the essence of polyglot persistence: choose the database that matches the shape and purpose of the data, not the one you're most comfortable with.

The Question That Actually Matters

Building two event-driven systems taught me that "SQL vs NoSQL" is the wrong question entirely.
The real question is: what role do your events play in the system?
If events belong to the domain model — use a relational database.
If events belong to analytics — use a document database.
PostgreSQL gave me structured timelines and strong consistency. MongoDB gave me flexible event logs and fast aggregation. Both systems use events, but the events serve completely different masters.
Understanding that difference before writing a single line of code is what separates a working system from a well-designed one.

Understanding SQL Transactions: ACID, Isolation Levels, and How to Handle Locks

Mhamd Ghanoum — Sun, 28 Jun 2026 10:57:12 +0000

Modern backend systems rely on transactions to guarantee correctness especially in financial operations, delivery platforms, and any workflow where multiple users modify shared data.
A solid understanding of ACID, isolation levels, and locking is essential for designing reliable systems and performing well in backend interviews.

This article explains how transactions work, how isolation is implemented, and how to handle locks in real SQL databases such as PostgreSQL.

1. What Is a Transaction?
A Transaction is a group of SQL operations that must be executed as a single atomic unit.
Either:

all operations succeed → COMMIT, or
all operations fail → ROLLBACK

Example: Paying for a pizza
When customer clicks Buy:

Deduct $20 from their balance
Transfer $20 to the restaurant
Create the order
Commit

If the server crashes after step 1, the database rolls back the deduction.
This prevents partial updates and ensures data correctness.

2. ACID Properties

A — Atomicity
All steps succeed or none do.

C — Consistency
The database always moves from one valid state to another.

I — Isolation
Each transaction behaves as if it’s the only one running.

D — Durability
Once committed, data survives crashes.

3. Isolation Problems
When multiple transactions run concurrently, anomalies can occur:

Dirty Read — reading uncommitted data
Non‑repeatable Read — reading the same row twice with different results
Phantom Read — new rows appear between identical queries

Isolation levels define which anomalies are allowed.

PostgreSQL uses MVCC (Multi‑Version Concurrency Control), which allows:

readers to avoid blocking writers
writers to avoid blocking readers
This improves performance and reduces unnecessary locking.

4. How to Deal with Isolation in SQL?

Isolation is handled by choosing the appropriate isolation level for the workload.
For high‑throughput systems, Read Committed is usually enough.
For operations requiring strict correctness, Repeatable Read or Serializable is used.
PostgreSQL relies on MVCC snapshots to avoid unnecessary locking, and optimistic concurrency (versioning) can be used to prevent lost updates without blocking other transactions.

5. Isolation Levels

Read Uncommitted: all violations allowed.
Read Committed: prevents dirty reads only.
Repeatable Read: prevents dirty reads and Non‑repeatable Read.
Serializable: prevents all violations.

6. How to Deal with Locks in SQL?

Locks are handled by using them intentionally and keeping them as short as possible.
When exclusive access is required for example, assigning a driver to an order — SELECT … FOR UPDATE is used to lock the row.
Long‑running transactions are avoided, optimistic locking is used to reduce contention, and deadlock errors are caught and retried.

Scenario
Two drivers attempt to claim the same delivery order simultaneously.

Step 1 — Lock the row

BEGIN;

SELECT *
FROM orders
WHERE id = 42
FOR UPDATE;

This places a row‑level exclusive lock.
Other transactions attempting to lock or update this row will wait.

Step 2 — Update safely

UPDATE orders
SET driver_id = 7, status = 'assigned'
WHERE id = 42;

Because the row is locked:

no two drivers can claim the same order
no race conditions occur
no lost updates happen

Then the lock is released.

7. Preventing Lost Updates

Three common strategies:

Atomic Update

UPDATE orders
SET status = 'done'
WHERE id = 10 AND status = 'pending';

Optimistic Locking (Versioning)

UPDATE orders
SET status='done', version=version+1
WHERE id=10 AND version=5;

Explicit Locking

SELECT * FROM orders WHERE id=10 FOR UPDATE;

8. Deadlocks

A deadlock occurs when:

Transaction A waits for B
Transaction B waits for A

PostgreSQL resolves this automatically:

one transaction is terminated
an error is returned
the application retries the operation Deadlocks are normal in high‑concurrency systems

Conclusion
Transactions are the foundation of reliable backend systems.
A strong understanding of:

ACID
isolation levels
MVCC
locking
deadlocks
optimistic concurrency

…is essential for building correct, scalable, and safe applications.

This knowledge is frequently tested in backend interviews, especially for roles involving distributed systems, financial operations, or high‑concurrency workloads.

Indexing & Query Optimization: How to Make Your Database Fast

Mhamd Ghanoum — Tue, 09 Jun 2026 17:14:40 +0000

Imagine you have a huge vendors table in an e‑commerce system, and thousands of orders are created every second.
Every time a new order comes in, the system needs to:

check if the vendor exists
check if the vendor is active
check if the product belongs to that vendor

If your table is large, the database will scan the entire table to find the vendor.
This is called a full table scan, and it becomes extremely slow as your data grows.

This is where indexes save your system.

🟦 What Is an Index?
An index is a small, optimized data structure that tells the database engine exactly where a specific value is located.

Instead of scanning the whole table, the database:
looks at the index
finds the exact row location
jumps directly to it

This turns a slow O(n) search into a fast O(log n) lookup.

🟩 Example: Indexing the Vendor ID
Without an index:

SELECT * FROM vendors WHERE id = 123;

The database scans the entire table.

With an index:

CREATE INDEX idx_vendors_id ON vendors(id);

Now the database:

checks the index
finds the row instantly
returns the result in milliseconds

This is the difference between a system that collapses under load…
and a system that handles millions of requests smoothly.

🟧 Why Not Index Everything? (The Trade‑Off)
Indexes make reads faster,
but they make writes slower.

Every time you:

INSERT
UPDATE
DELETE …the database must also update every index.

Too many indexes = slow writes = bottlenecks.

So we only index the hot paths — the most frequently queried fields.

🟪 Clustered vs Non‑Clustered Indexes (PostgreSQL)
Clustered Index
The table is physically sorted by the index

Fast range queries
Only one per table
Non‑Clustered Index
Separate structure
Points to the actual rows
You can have many of them
PostgreSQL uses B‑Tree indexes by default.

🟦 Other Index Types in PostgreSQL (Short Overview)
PostgreSQL supports several index types optimized for different use cases:

B‑Tree (default)
Best for equality and range queries.

Hash Index
Fast equality lookups (=), but limited.

GIN Index
Perfect for:

JSONB
Arrays
Full‑text search
Tags

Example:

CREATE INDEX idx_products_tags ON products USING gin(tags);

GiST Index
Used for:

Geospatial data
Distances
Geometric shapes

Example (useful in delivery apps):

CREATE INDEX idx_locations_gist ON locations USING gist(geo_point);

BRIN Index
Great for very large tables with naturally ordered data (logs, events, time‑series).

Partial Index
Index with a condition:

CREATE INDEX idx_active_vendors ON vendors(id) WHERE active = true;

Expression Index
Index on computed values:

CREATE INDEX idx_lower_email ON users (LOWER(email));

This flexibility is one of PostgreSQL’s biggest strengths.

🟨 Query Optimization Tips
Indexing is powerful, but you also need efficient queries

✔️ Select only the columns you need

SELECT id, name FROM vendors;

Not:

SELECT * FROM vendors;

✔️ Use wildcards only at the end
Good:

WHERE name LIKE 'Sam%'

Bad:

WHERE name LIKE '%Sam%'

✔️ Use LIMIT when previewing data

SELECT * FROM orders LIMIT 50;

✔️ Run heavy queries during off‑peak hours
Especially analytics or batch jobs.

🟫 Conclusion
Indexes make your reads extremely fast

But they slow down writes

Use them wisely on the most important fields

Combine indexing with good query practices

Always measure performance before and after

Indexing is one of the simplest ways to make your backend feel instant, even under heavy load

SQL vs NoSQL — How to Choose the Right Database for Your System

Mhamd Ghanoum — Fri, 05 Jun 2026 10:32:01 +0000

Hello everyone!
My name is Mhamd, a backend engineer who loves building scalable systems and explaining complex backend topics in a simple, practical way.

If you enjoy system design, backend architecture, or want to exchange ideas — feel free to reach out!

Why This Topic Matters?
When designing a new system, one of the most important architectural decisions is choosing the right type of database.

This choice affects:

performance
scalability
consistency
development speed
and even cost

In this article, I’ll break down SQL vs NoSQL, when to use each, and how I combine both in real-world projects.

SQL — Structured, Relational, and Consistent
SQL databases (PostgreSQL, MySQL, SQL Server…) rely on:

A predefined schema
Strong consistency
ACID transactions
Clear relationships between tables When SQL is a great fit? Use SQL when:
Your data structure is known in advance
You need strong consistency
You have complex relationships (Users ↔ Orders ↔ Products)
You need powerful queries and joins

Example: E‑commerce
Entities like:

Products
Users
Orders
Payments

…have clear relationships and require consistent transactions.
SQL is perfect here.

But what about Amazon‑scale systems?
As the system grows globally:

traffic explodes
data becomes distributed
sharding becomes complex
consistency becomes harder
SQL still works — but requires advanced architecture.

NoSQL — Flexible, Fast, and Horizontally Scalable
NoSQL is not one technology — it’s a family:

Document stores (MongoDB)
Key‑value stores (Redis)
Wide‑column stores (Cassandra)
Graph databases (Neo4j)

When NoSQL is a great fit?
Use NoSQL when:

Your data is semi‑structured or unstructured
You need high write throughput
You need horizontal scaling
You’re building real‑time systems
Your schema changes frequently

Examples

Analytics
Real‑time feeds
Chat systems
IoT
Caching layers

Using Both Together — The Hybrid Approach
Modern systems rarely fit into one category.
In my projects, I often combine SQL + NoSQL.

Example: My Delivery Backend System
PostgreSQL for:

Orders
Products
Users
State machines
Auditing
Because these require structure and consistency.

Redis for:

Real‑time deliverer tracking
WebSocket events
Caching
Fast lookups
Because these require speed and flexibility.

What Happens When You Use GraphQL on Top of PostgreSQL?

When you put GraphQL above PostgreSQL:

PostgreSQL remains the source of truth GraphQL becomes a flexible API layer The client can request exactly the data it needs No overfetching or underfetching.Perfect for dashboards and mobile apps

This combination gives you:

SQL consistency
GraphQL flexibility
A single endpoint for all queries

Conclusion
SQL is perfect for structured, relational, consistent data

NoSQL is perfect for speed, flexibility, and large-scale distributed systems

Most real systems use both.
The right choice depends on your data and your system’s behavior — not on trends.

If you enjoyed this article
I’ll be writing more about:

system design
caching
event-driven architecture
microservices
real-time backend patter ns

Follow me on DEV Community — and feel free to share your thoughts or questions!