DEV Community: Mhamd Ghanoum

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!