SQL Is Not Boring: The Hidden Systems Behind Everyday Apps#

1. A Database Deadlock Is Basically Two Cars Refusing to Reverse#

Imagine a narrow intersection with two cars.

• Car A has entered from the north and wants to turn east.
• Car B has entered from the east and wants to turn north.
• Each car is blocking the road the other one needs.
• Neither driver can move forward.
• Neither driver wants to reverse.

Nothing is technically broken. Both cars are working exactly as intended. The problem is that their movements depend on each other.

That is a deadlock.

In database terms, the cars are transactions and the pieces of road are locked rows.

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B, running at the same time
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

Transaction A locks account 1 and waits for account 2.

Transaction B locks account 2 and waits for account 1.

Now both transactions are staring at each other in the middle of the intersection.

BEGIN;

-- Every code path locks accounts in the same order
SELECT id
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

-- Perform the updates after the locks are acquired
UPDATE accounts
SET balance = CASE
  WHEN id = 1 THEN balance - 100
  WHEN id = 2 THEN balance + 100
END
WHERE id IN (1, 2);

COMMIT;

2. A B-Tree Index Is the Difference Between Reading a Book and Using Its Index 📚#

Suppose somebody gives you a 1,000-page book and asks:

“Where is the paragraph about transaction isolation?”

Without an index, you start at page one and scan every page until you find it.

That is roughly what a sequential scan does.

SELECT *
FROM users
WHERE email = 'yonnon@example.com';

If email has no useful index, the database may need to inspect a huge portion of the table. On a tiny project, that can still feel instant. On a table with hundreds of millions of rows, it becomes a very different story.

Now imagine the same book has an alphabetical index at the back:

1. Look under T.
2. Find Transaction isolation.
3. Jump directly to page 742.

A B-Tree index gives the database a similar navigation structure.

CREATE INDEX idx_users_email ON users(email);

The data is organized in a balanced tree, so the database repeatedly narrows the search space instead of checking every row.

A simplified mental picture looks like this:

                         [M]
                      /       \
                  [D, H]     [R, W]
                 /  |  \     /  |  \
               ... ... ... ... ... ...

Each comparison tells the database which branch can be ignored.

With one billion indexed values, the database does not need one billion comparisons. The tree can often reach the relevant section in a small number of steps, followed by a lookup of the actual table row.

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-01-01';

3. Sync vs Async: One Toll Booth or a Multi-Lane Highway? 🚦#

Synchronous code is often explained as “slow,” while asynchronous code is presented as “fast.” That is catchy—but not quite correct.

A better analogy is traffic flow.

const user = getUser()
const orders = getOrders(user.id)
const recommendations = getRecommendations(user.id)

const [orders, recommendations] = await Promise.all([
  getOrders(user.id),
  getRecommendations(user.id)
])

4. Window Functions: The SQL Skill That Changes How You See Data#

A normal aggregate query compresses many rows into fewer rows.

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

That is useful, but the individual orders disappear from the result.

A Window Function lets you calculate across related rows without collapsing them. It is like opening a small analytical window around each row.

SELECT
  customer_id,
  created_at,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS running_total
FROM orders;

Every order remains visible, but each row can also “see” the relevant history around it.

That unlocks patterns such as:

• ranking products inside each category;
• calculating a running balance;
• comparing an order with the previous order;
• measuring time between user actions;
• finding the first or latest event in each group;
• calculating moving averages.

SELECT
  customer_id,
  created_at,
  amount,
  LAG(created_at) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS previous_order_at
FROM orders;

SELECT
  category_id,
  product_name,
  revenue,
  DENSE_RANK() OVER (
    PARTITION BY category_id
    ORDER BY revenue DESC
  ) AS revenue_rank
FROM product_revenue;

5. Could Grab Predict Demand With Three Lines of SQL?#

Let us be careful here: a production surge-pricing system is not literally three lines of SQL.

A real ride-hailing platform may combine:

• live driver supply;
• current ride requests;
• historical demand by location and time;
• traffic and weather signals;
• event data;
• forecasting models;
• pricing and marketplace constraints.

But SQL can still produce one of the most important signals with surprisingly little code.

SELECT zone_id, COUNT(*) AS requests
FROM ride_requests
WHERE requested_at >= NOW() - INTERVAL '5 minutes'
GROUP BY zone_id;

That query answers a simple but valuable question:

“How many people requested a ride in each area during the last five minutes?”

Now compare that number with available drivers:

SELECT
  r.zone_id,
  r.request_count,
  d.available_drivers,
  r.request_count::decimal / NULLIF(d.available_drivers, 0) AS demand_ratio
FROM recent_demand r
JOIN driver_supply d USING (zone_id);

If one area has 200 ride requests and only 20 available drivers, the marketplace is under pressure. A pricing or dispatch system can use that ratio as one input when deciding how to rebalance supply and demand.

The interesting part is not the number of SQL lines. It is the realization that everyday product behavior often begins with a small question asked against a large stream of events.

Where is demand rising faster than supply?

SQL turns that business question into a measurable signal.

💡 Human insight: users experience surge pricing as a number on a screen. Engineers see a marketplace trying to prevent 200 people from competing for the same 20 cars.

6. E-Commerce Funnels: Finding the Exact Step Where Customers Disappear#

An e-commerce team may say:

“People are visiting the product page, but sales are low.”

That statement is too vague to act on.

A customer journey usually has several steps:

Product View → Add to Cart → Checkout Started → Purchase

The useful question is not simply “How many people bought something?”

It is:

“At which step do we lose the most people?”

Suppose product events are stored like this:

user_id | event_name        | created_at
--------|-------------------|--------------------
101     | product_view      | 2026-07-15 10:01
101     | add_to_cart       | 2026-07-15 10:03
101     | checkout_started  | 2026-07-15 10:06
102     | product_view      | 2026-07-15 10:08

A simplified funnel query might look like this:

WITH funnel AS (
  SELECT
    user_id,
    MAX((event_name = 'product_view')::int) AS viewed,
    MAX((event_name = 'add_to_cart')::int) AS added,
    MAX((event_name = 'checkout_started')::int) AS checkout,
    MAX((event_name = 'purchase')::int) AS purchased
  FROM events
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  SUM(viewed) AS viewers,
  SUM(added) AS cart_users,
  SUM(checkout) AS checkout_users,
  SUM(purchased) AS buyers
FROM funnel;

Imagine the result:

10,000 product viewers
 3,800 added to cart
 3,200 started checkout
   900 completed purchase

The biggest leak is not the product page. It is the final payment stage.

That changes the conversation completely.

Instead of redesigning the homepage, the team can investigate:

• unexpected shipping fees;
• payment failures;
• a confusing address form;
• slow checkout performance;
• missing payment methods;
• forced account registration.

💡 The practical lesson: good SQL does more than describe the past. It tells a team where to look next.

7. Why Is WHERE Fast? The Honest Answer Is: Sometimes It Is Not#

Consider this query:

SELECT *
FROM orders
WHERE customer_id = 42;

It is tempting to imagine that WHERE somehow jumps directly to matching rows.

But WHERE is only the condition. It describes which rows are allowed through. It does not decide the physical route used to find them.

The query planner makes that decision.

Depending on the table, indexes, statistics, and expected number of matching rows, the database may choose:

• a sequential scan;
• an index scan;
• a bitmap index scan;
• a partition scan;
• or another execution strategy.

Without an index, the database may inspect rows one by one:

Row 1: customer_id = 8   → reject
Row 2: customer_id = 42  → keep
Row 3: customer_id = 11  → reject
...

With a useful index:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

The planner can navigate the index to locate references to rows where customer_id = 42.

You can inspect the chosen route with:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;

8. WebSockets: Keeping a Phone Call Open Between Frontend and Backend#

Traditional HTTP is like sending letters.

1. The frontend sends a request.
2. The backend returns a response.
3. The conversation ends.

That model works beautifully for many applications.

But imagine building:

• a chat application;
• a live delivery map;
• a trading dashboard;
• multiplayer collaboration;
• warehouse robot monitoring;
• live notifications.

The frontend cannot politely ask the server every millisecond:

“Anything new?”
“Anything new now?”
“How about now?”

A WebSocket is more like keeping a phone call open.

Once the connection is established, both sides can send messages whenever they have something to say.

const socket = new WebSocket('wss://example.com/realtime')

socket.addEventListener('message', (event) => {
  const update = JSON.parse(event.data)
  console.log('New server update:', update)
})

On the backend, the server can push a message immediately:

socket.send(JSON.stringify({
  type: 'DRIVER_LOCATION_UPDATED',
  driverId: 'driver-42',
  lat: 10.7769,
  lng: 106.7009
}))

9. The Pattern Connecting All of These Concepts#

Deadlocks, indexes, async code, Window Functions, funnels, and WebSockets may look like separate topics.

They are all answers to one question:

“What happens when a simple program meets real scale, real users, and real time?”

At small scale:

• every query feels fast;
• concurrency bugs rarely appear;
• polling seems acceptable;
• data analysis fits inside a few GROUP BY queries;
• architecture decisions feel theoretical.

At larger scale, hidden assumptions become visible.

• Two correct transactions can block each other.
• A tiny query can scan a billion rows.
• Waiting for I/O can freeze unrelated work.
• A single row makes sense only in the context of previous rows.
• A conversion problem can hide in one specific funnel step.
• A live interface needs a persistent communication strategy.

This is the part of engineering I find genuinely exciting. The syntax is rarely the hardest piece. The harder skill is building a mental model of what the system is doing when nobody is looking.

A Few Questions Worth Discussing 💬#

• What was the first database performance issue that forced you to learn indexes properly?
• Have you ever fixed a deadlock by changing lock order rather than adding retries?
• Which Window Function made SQL finally “click” for you?
• Where would you choose polling, Server-Sent Events, or WebSockets today?
• Which step in your product funnel currently has the biggest unexplained drop-off?

I would love to hear the real production stories—the ones where the clean architecture diagram met messy user behavior.

Final Thought#

SQL is often introduced as a language for tables.

I think that description is too small.

SQL is one of the ways we ask a running system to explain itself:

• Who is waiting?
• What is slow?
• Where are users leaving?
• Which event happened first?
• Where is demand rising?
• What changed in the last five minutes?

Once you start asking those questions, SQL stops feeling like a collection of commands.

It starts feeling like a way to see the machinery behind the product.

Happy querying.

Yonnon

Further Reading#

• PostgreSQL: Explicit Locking and Deadlocks
• PostgreSQL: B-Tree Indexes
• PostgreSQL: Using EXPLAIN
• PostgreSQL: Window Functions
• MDN: WebSocket API