DEV Community: Tim Nguyen

COUNT(column) vs COUNT(*) in SQL — With INNER, LEFT, RIGHT & FULL JOIN Explained

Tim Nguyen — Tue, 24 Feb 2026 08:57:09 +0000

Counting seems simple in SQL… until joins enter the picture.

One of the most common bugs in reporting queries comes from misunderstanding:

COUNT(*) vs COUNT(column)

1. The Core Rule

✅ COUNT(*) -> Counts rows

✅ COUNT(column) -> Counts non-NULL values in that column

That’s it.
But joins create NULLs — and that’s where things change.

2. Example setup

Customer

id	name
1	Alice
2	Bob
3	Carol

Orders

id	customer_id
10	1
11	1
12	2

a. INNER JOIN

SELECT c.id, COUNT(*), COUNT(o.id)
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

id	COUNT(*)	COUNT(o.id)
1	2	2
2	1	1

Why?

INNER JOIN removes non-matching rows.
No NULL rows are produced.

So:

COUNT(*) == COUNT(o.id)

No surprises here.

b. LEFT JOIN (Where Most Bugs Happen)

SELECT c.id, COUNT(*), COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

id	COUNT(*)	COUNT(o.id)
1	2	2
2	1	1
3	1	0

Why is Carol different?

LEFT JOIN keeps all customers.
For Carol: A row exists + o.id is NULL

So:

COUNT(*) counts the row → 1
COUNT(o.id) ignores NULL → 0

3. Usage

When you have to count multiple joined tables with different expressions, mind to use count + join properly and wisely

4. Final thoughts

Most production reporting bugs involving SQL counts come from misunderstanding how NULL behaves in outer joins.

Once you internalize:

COUNT(*) counts rows
COUNT(column) counts values

You’ll stop shipping incorrect dashboards.

PostgreSQL MVCC vs MySQL Key-Next Locking: How Transaction Isolation Affects Concurrency

Tim Nguyen — Tue, 09 Dec 2025 03:14:16 +0000

When developers talk about ACID databases, two giants dominate: PostgreSQL and MySQL (InnoDB). Both claim full MVCC (Multi-Version Concurrency Control) support, but their implementations are fundamentally different.

This post explains the key differences, how transaction isolation levels behave in each, and why PostgreSQL is truly non-blocking for readers while MySQL often isn't.

TL;DR Summary

Feature	PostgreSQL	MySQL InnoDB
MVCC Implementation	Versioned tuples (true MVCC)	Undo logs + next-key locking (hybrid)
Readers block writers?	No	Yes (in REPEATABLE READ)
Writers block readers?	No	Yes (in REPEATABLE READ)
Default isolation level	READ COMMITTED	REPEATABLE READ
Non-blocking READ COMMITTED	Yes	No (uses semi-consistent reads)
Phantom reads in default mode	Possible (READ COMMITTED)	Prevented (REPEATABLE READ + gap locks)
Lock escalation / gap locks	No	Yes (notorious source of deadlocks)

1. PostgreSQL's True MVCC: How It Achieves Non-Blocking Reads & Writes

PostgreSQL is famous for its rock-solid ACID compliance and incredible concurrency. The secret sauce? True Multi-Version Concurrency Control (MVCC) — an implementation that lets readers and writers fly past each other without ever blocking.

Let’s dive deep into how it actually works under the hood.

What "True MVCC" Really Means

Every time a row is UPDATE or DELETE, PostgreSQL does not overwrite the old row. Instead:

A new version (tuple) of the row is created.
Old versions get metadata:
- xmin → transaction ID that created this version
- xmax → transaction ID that deleted/updated it (or still active)
Each transaction gets a snapshot (via the transaction ID snapshot) that determines which row versions are visible to it.
Dead tuples are cleaned up later by VACUUM.

Result: No read locks are ever needed.

-- This SELECT will NEVER block, even if another transaction is updating the row right now
SELECT * FROM orders WHERE customer_id = 123;

Visibility Rules — The Heart of PostgreSQL MVCC

Condition	Visible to my transaction?
Row's xmin committed and ≤ my snapshot	Yes
Row's xmax committed and > my snapshot	No (deleted/updated after I started)
Row's xmax is still active (in progress)	No (not yet committed)

How Isolation Levels Change Snapshot Behavior

Level	Snapshot Taken	Non-Repeatable Reads?	Phantom Reads?	Blocking?
READ COMMITTED	New snapshot per statement	Possible	Possible	Never
REPEATABLE READ	Snapshot at first statement of tx	No	Possible	Never
SERIALIZABLE	Snapshot at tx start + SSI (predicate checks)	No	No	Never (aborts instead of deadlocks)

Yes — even in SERIALIZABLE, PostgreSQL never blocks; it aborts one transaction if a conflict is detected.

Real Example: Zero Blocking

-- Session 1 (long-running report)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM users WHERE age > 30;  -- runs for 20 seconds

-- Session 2 (concurrent write)
UPDATE users SET age = 35 WHERE id = 999;  -- finishes instantly!

-- Session 1 continues happily with its old snapshot
SELECT COUNT(*) FROM users WHERE age > 30;  -- still sees pre-update data
COMMIT;

No waiting. No deadlocks. Pure magic.

Benifits and Trade-offs

Benefits:
- Readers never block writers
- Writers never block readers
- Almost zero deadlocks (only from explicit FOR UPDATE/LOCK TABLE)
- Great for analytics + OLTP mixed workloads
- VACUUM and autovacuum keep bloat under control
Trade-offs:
- Table bloat if VACUUM lags (solved with good autovacuum tuning)
- Slightly higher write amplification (new tuple per update)

2. MySQL InnoDB: MVCC + Next-Key Locking (The Hidden Truth)

MySQL InnoDB’s MVCC Is Not What You Think: The Truth About Next-Key Locks

Everyone says "InnoDB has MVCC". That’s technically true — but it’s not the same as PostgreSQL’s non-blocking MVCC.

InnoDB combines MVCC with row locks and gap/next-key locks, and the default isolation level (REPEATABLE READ) turns innocent SELECTs into blocking operations.

Let’s uncover what really happens.

InnoDB’s Hybrid Approach

Old row versions are stored in undo logs
Each transaction gets a read view (similar to a snapshot)
But… to prevent phantom reads in REPEATABLE READ, InnoDB uses next-key locking:
- Record lock (on existing row)
- Gap lock (on the "gap" between index entries)

This means: plain SELECTs can acquire locks and block INSERTs/UPDATEs.

The Infamous Gap Lock Example

-- Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;  -- scans index on age

-- This query places GAP LOCKS on age ranges (...,20], (20,25], (25,30], (30,...) 

-- Session 2 (gets BLOCKED!)
INSERT INTO users (name, age) VALUES ('Alice', 27);
-- Waits... forever... until Session 1 commits/rollbacks

A harmless SELECT just blocked an INSERT on a non-existing row!

How Isolation Levels Really Behave in InnoDB

Level	Snapshot?	Gap Locks?	Readers Block Writers?	Writers Block Readers?	Deadlocks Common?
READ UNCOMMITTED	No	No	No	Yes	Rare
READ COMMITTED	Partial	No	No	Yes	Less
REPEATABLE READ (default)	Yes	Yes	Yes	Yes	Very common
SERIALIZABLE	Yes	Yes + stricter	Yes	Yes	Extremely common

Yes — in the default REPEATABLE READ, reads block writes and writes block reads.

Why InnoDB Chose This Design

Goal: Prevent phantom reads without predicate locking (like PostgreSQL’s SSI).
Solution: Lock the gaps in indexes → no phantoms, but at the cost of blocking.

Real-World Pain Points

A background analytics query can freeze your entire app
Random deadlocks at high concurrency
Hard to debug: SHOW ENGINE INNODB STATUS shows giant lock wait chains
Scaling writes often requires partitioning or read replicas

When InnoDB’s Approach Is Acceptable

Low-to-medium concurrency
Mostly primary key lookups (no range scans → fewer gap locks)
You’re already deep in the MySQL ecosystem

How to Reduce Pain (if you’re stuck with MySQL)

-- Use READ COMMITTED + row-based replication
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Avoid SELECT ... FOR UPDATE/SHARE unless necessary
-- Use primary key lookups instead of range scans when possible
-- Consider MariaDB (its default is often less aggressive)

3. Conclusion

InnoDB’s "MVCC" is a hybrid of versioning + locking.
It gives you strong consistency (no phantoms by default) but sacrifices true non-blocking concurrency.
If you ever wondered why your MySQL app randomly deadlocks at scale even though "you’re just reading" — now you know: your reads are silently locking gaps.
PostgreSQL proved decades ago that you can have both MVCC and no blocking. MySQL chose a different trade-off.
Choose your database accordingly.

Why Your `@Transform` Decorator Doesn’t Run in class-transformer (and How to Fix It)

Tim Nguyen — Sun, 07 Sep 2025 11:57:59 +0000

If you’ve ever used class-transformer with @Transform, you might have run into this weird situation:

import 'reflect-metadata';
import { plainToInstance, Transform } from 'class-transformer';
import { IsEmail, IsNotEmpty } from 'class-validator';

class MobileSignupBody {
  @IsNotEmpty()
  @IsEmail()
  email!: string;

  @Transform(({ obj }) => obj.email)
  normalized_email?: string;
}

const dto = plainToInstance(MobileSignupBody, { email: 'abc@test.com' });
console.log(dto); // MobileSignupBody { email: 'abc@test.com' }
console.log(dto.normalized_email); // ❌ undefined

Wait… why is normalized_email undefined even though I clearly added a @Transform?

The Gotcha

By default, class-transformer only runs @Transform on properties that exist in the plain object input.

In this case, the plain object only had:

{ "email": "abc@test.com" }

Since there was no normalized_email key, class-transformer skipped that property completely.
That’s why the transform never fired.

The Fix: Use `@Expose`

You need to tell class-transformer: “always include this property, even if it’s not in the plain object.”

import { Expose, Transform } from 'class-transformer';

class MobileSignupBody {
  @Expose() // Will always include in class/plain object -> be transformed
  @IsNotEmpty()
  @IsEmail()
  email!: string;

  @Expose()
  @Transform(({ obj }) => obj.email)
  normalized_email?: string;
}

const dto = plainToInstance(MobileSignupBody, { email: 'abc@test.com' });
console.log(dto.normalized_email); // ✅ "abc@test.com"

Now it works because @Expose forces normalized_email into the transform pipeline.

Global Config Options

If you don’t want to sprinkle @Expose() everywhere, you have a couple of global strategies:

excludeExtraneousValues

const dto = plainToInstance(MobileSignupBody, { email: 'abc@test.com' }, {
  excludeExtraneousValues: true,
});

In this mode, only properties with @Expose() are kept.
Downside: you must decorate all properties you want to keep.

strategy: 'exposeAll'

@Expose({ strategy: 'exposeAll' })
class MobileSignupBody {
  email!: string;

  @Expose()
  @Transform(({ obj }) => obj.email)
  normalized_email?: string;
}

This makes all properties exposed by default, and you only need @Expose() where you want transforms.

Alternative: Use a Getter

Sometimes, the simplest solution is just a computed property:

class MobileSignupBody {
  email!: string;

  get normalized_email(): string | undefined {
    return this.email;
  }
}

No @Expose, no @Transform, always works.

Takeaways

@Transform only runs if the property exists in the plain object.
Use @Expose to force properties into the transformation pipeline.
For global behavior, use excludeExtraneousValues or strategy: 'exposeAll'. Or skip it all and use getters if you just want a derived property.

🔗 Useful refs:

class-transformer docs

class-validator docs

Have you ever hit this @Transform gotcha? How do you usually handle derived fields in DTOs — getters, transforms, or something else? Let me know 👇

😬 The Silent Gotcha in MySQL: ENUMs and Non-Strict SQL Mode

Tim Nguyen — Tue, 01 Jul 2025 15:31:45 +0000

If you're using MySQL and rely on ENUM columns for validation, you might be silently inserting bad data without realizing it. Let's talk about what happens when SQL mode is not strict.

🧪 The Setup

You define a table like this:

CREATE TABLE users (
  marital_status ENUM(
    'single',
    'married',
    'unmarried',
    'divorced',
    'separated',
    'widowed',
    'never_married'
  ) NOT NULL
);

Looks solid, right? Now, let’s try inserting an invalid value:

INSERT INTO users (marital_status) VALUES ('unknown');

😱 What Happens in Non-Strict Mode?

If you're not in strict SQL mode, MySQL won't throw an error. It will:

Insert the value as an empty string ''
Silently trigger a warning
Store it as the internal ENUM value 0

Yep, your invalid input just became... nothing.

SELECT * FROM users;
-- Output:
-- | marital_status |
-- |----------------|
-- |                |  <-- EMPTY STRING!

🔍 Why This Happens

Internally, MySQL maps ENUM values to integers (and enum in MySQL has index starting at 0 docs):

Value      Index (internal)
''(empty)  0
'single'   1
'married'  2
...        ...

When you insert an invalid value, MySQL stores 0, which corresponds to ''.

🔥 How to Avoid It: Enable Strict Mode

Use strict SQL mode to make MySQL behave properly:

SET sql_mode = 'STRICT_TRANS_TABLES';

Now try that bad insert again:

INSERT INTO users (marital_status) VALUES ('unknown');
-- ❌ ERROR 1265 (01000): Data truncated for column 'marital_status' at row 1

Perfect. You’re now protected from silent failures.

💡 Pro Tip: Make It Permanent

Edit your MySQL config file:
On Linux (/etc/mysql/my.cnf) or macOS:

[mysqld]
sql_mode=STRICT_TRANS_TABLES

Then restart MySQL.

Avoid This Silent Timeout Bug with Axios in Lambda as proxy, ALB, and Content-Length

Tim Nguyen — Thu, 19 Jun 2025 06:21:05 +0000

Have you ever encountered a 504 Gateway Timeout from your AWS Application Load Balancer (ALB) when proxying requests via Axios, even though everything looks right on the surface?

Here’s a tricky issue I ran into — and how to avoid it in the future.

💥 The Setup

You're using AWS Lambda using a Node.js proxy to forward a POST request to a backend behind ALB:

await axios.post('http://my-backend', {
  timestamp: '2025-06-10 10:49:46',
  token: 'fEjck23HJkLlrRkDKtNWVI:APA91bHphS5as...'
}, {
  headers: {
    'Content-Type': <keep it from original request>,
    'Content-Length': <get from original request>
  }
});

Looks harmless, but suddenly:

504 Gateway Timeout from ALB

And the backend logs show:

SyntaxError: Unterminated string in JSON at position xxx

🧠 Root Cause
The error comes from a mismatch between the Content-Length and the actual body size.

What actually happens:

Axios serializes the data object to JSON.
You manually set Content-Length: xxx or forward it from orignial request, but the serialized body is longer.
The backend receives only the first xxx bytes.
It tries to parse, but the JSON is incomplete.
It waits for the rest of the body, but it never comes.
Eventually, the connection times out → ALB returns a 504.

❌ Why Not a 400?

Normally, a malformed JSON gives a 400 Bad Request.
But here:
- The backend doesn't know the full body has been sent.
- It thinks the rest is still coming.
- So it never sends a response → ALB gives up and returns 504.

✅ The Fix

Just don’t manually set Content-Length. Let Axios compute it.

await axios.post('http://my-backend', {
  timestamp: '2025-06-10 10:49:46',
  token: 'fEjck23HJkLlrRkDKtNWVI:APA91b...'
}, {
  headers: {
    'Content-Type': <keep it from original request>
    // ✅ Do not manually set Content-Length
  }
});

Axios will:

Serialize the object to JSON
Compute the correct length
Set the header automatically

Have fun coding!

CloudFront + S3 + Static domain routing

Tim Nguyen — Wed, 11 Dec 2024 14:43:25 +0000

What are existing:

S3 bucket 1: xxx.s3.us-east-1.amazonaws.com
S3 bucket 2: yyy.s3.us-east-1.amazonaws.com
Domain name: abc.com

What is requiring:

Using only 1 static domain name: static.abc.com
Using only 1 CloudFront distribution to serve both S3 buckets above with prefix (S3 bucket 1 - prefix admin and S3 bucket 2 - prefix public)
Eg: What is served with https://xxx.s3.us-east-1.amazonaws.com/img.jpg can be served with https://static.abc.com/admin/img.jpg and what is served with https://yyy.s3.us-east-1.amazonaws.com/img.png can be served with https://static.abc.com/public/img.png

Idea

Create a distribution in CloudFront with 2 origins are 2 bucket above
Create 3 distribution behaviors:
- /admin/* - To Distribution's Origin of S3 Bucket 1
- /public/* - To Distribution's Origin of S3 Bucket 2
- Default(*) - To either Origin of S3 Bucket 1 or Origin of S3 Bucket 2
Modify 3 distribution behaviors to have function associations to Lambda@Edge function (this will be in another post, but the document for create one here)

Node.js + ioredis + elasticache

Tim Nguyen — Thu, 05 Dec 2024 02:36:22 +0000

For who is struggling with ioredis connection to elasticache (cluster enable)

const REDIS_COMMAND_TIMEOUT = 10000;
const MAX_REDIS_ATTEMPS = 3;
const REDIS_BACKOFF_RETRY = 2000;

const REDIS_HOST = process.env.REDIS_HOST || '127.0.0.1';
const REDIS_PORT = Number(process.env.REDIS_PORT) || 6379;

// https://github.com/redis/lettuce/wiki/Redis-URI-and-connection-details
const REDIS_SCHEME = REDIS_CLUSTER_ENABLED ? `rediss` : `redis`;

const redis = new Redis(`${REDIS_SCHEME}://${REDIS_HOST}:${REDIS_PORT}`, {
  commandTimeout: REDIS_COMMAND_TIMEOUT,
  retryStrategy: (times: number): number | null => {
    if (times > MAX_REDIS_ATTEMPS) {
      return null; // Stop retrying
    }
    return REDIS_BACKOFF_RETRY; // Retry after some time
  },
  tls: REDIS_CLUSTER_ENABLED ? {} : undefined,
});

MySQL syntaxes

Tim Nguyen — Tue, 25 Jun 2024 08:23:44 +0000

What

👎 JSON_UNQUOTE + JSON_EXTRACT vs 👍 ->>



SELECT JSON_UNQUOTE(JSON_EXTRACT(metadata, 'field')) 👎

SELECT metadata->>'$.field' 👍

Why it matters?

JSON_EXTRACT vs -> only return the json result
JSON_UNQUOTE + JSON_EXTRACT vs ->> return the parsed result (you can think of the JSON.parse method in JS)

Lambda configurations for ALB to reach

Tim Nguyen — Fri, 07 Jun 2024 04:27:54 +0000

We need

A lambda function:
- No need Function URL enabled
- VPC configs to be in the same VPC and subnet with main VPC used by ALB
- Security group with inbound from main VPC CIDR block (eg: 10.1.0.0/16) and outbound to internet (eg: 0.0.0.0/0)
- Permission set to accept both ALB arn and Target group arn
A target group set to lambda function
An application load balancer has a listener to lambda target group above

AWS Lambda - Socket hang up Nodejs

Tim Nguyen — Thu, 05 Oct 2023 06:53:32 +0000

Context: Lambda error said 'socket hang up' within nodejs code when performing http/https request to 3rd party backend

Let's describe my tries on solving the problem

Increase the timeout of lambda -> Not help
Increase the timeout of httpClient -> Not help
Figure out that lambda having some problem with connection -> Put HttpAgent to keepalive the connection -> Work perfectly

Code demo:

new JwksClient({
      jwksUri: 'xxx' + '/oidc/2/certs',
      timeout: 10000,
      requestAgent: new Agent({
        keepAlive: true
      })
    })

Quote hàng ngày #1

Tim Nguyen — Mon, 19 Jun 2023 14:54:59 +0000

Quote hôm nay là...

Good code is not just functional, it is also beautiful. Good code is organized, easy to read, and well documented. Organization can be achieved by separating code into useful functions and collecting functions into modules or libraries. Good organization means that at any one time, we only need to focus on a small part of a program.
(Computer Science I - Dr. Chris Bourke)

Chuyện là...

Hôm nay rảnh nợ ngồi đọc quyển Computer Science thì thấy đoạn quote khá hợp ý và mình cũng chiêm nghiệm khá đúng khi đi làm một vài năm. Đôi khi có một vài bạn fresher hoặc junior được mình kèm hoặc muốn được mình góp ý để có thể code tốt hơn sau nhiều lần ăn blame từ các senior, mentor hay leader 🤣.

Mình nghĩ rằng...

Việc code tốt lên thì cũng có nhiều yếu tố, có lý do khách quan ví dụ như được ở trong môi trường làm việc chuẩn, có đủ tài nguyên cần thiết để phát triển, cũng có lý do chủ quan, do bản thân tự thân mày mò để mình tốt hơn, ví dụ như tự đi tham khảo tài liệu, học từ những người giỏi hơn. Chung quy đều là phải có tài liệu, phải có tiêu chuẩn, từ tiêu chuẩn thì mới có thể phát triển mở rộng ra những thứ khác được. Ở đâu đó mình có nghe 1 câu là "Khi bạn có 1 nền tảng vững chắc rồi thì việc bạn giỏi lên chỉ còn là vấn đề thời gian."

Đúc kết...

Quay lại việc code tốt lên, ngoài việc code chạy được, chạy đúng ra thì việc code sạch, đẹp, dễ bảo trì cũng là một yếu tố tiên quyết để đánh giá 1 developer có đủ tính kiên nhẫn, tỉ mỉ và gọn gàng hay không. Qua vài năm đi làm thợ code thì mình đúc kết lại được như thế này:

Đầu tiên, bạn phải biết bạn đang làm gì giữa 1 đống chữ (Điều này rất quan trọng, bạn phải biết bạn đang làm gì khi muốn thêm 1 phần code vào giữa những phần khác, phải đánh giá được nó sẽ ảnh hưởng thế nào với những phần code còn lại)
Thứ hai, bạn cần phải đặt tên theo một tiêu chuẩn nhất định (cái này gọi là naming convention, refer cái repo này: https://github.com/kettanaito/naming-cheatsheet#naming-convention)
Thứ ba, nhận biết một hàm như thế nào là đủ "lớn" và cần refactor (về vấn đề này thực sự có khá nhiều ý kiến, nhưng theo kinh nghiệm cá nhân mình, một hàm thực sự chỉ nên có dưới 50 dòng không tính khoảng trống và comment)
Thứ tư, một hàm nên chỉ làm một nhiệm vụ (ví dụ hàm getMessage thì sẽ không tạo thêm message, nó làm mất đi tính uniqueness của hàm và khiến việc đặt tên hàm dường như không còn nhiều ý nghĩa, cùng với việc viết unit test sẽ có cảm giác như đang viết e2e testing vậy)
Thứ năm, guard clause có thể giúp bạn code đẹp hơn (nếu bạn thấy quá nhiều if-else lồng nhau thì bạn nên dừng lại và nghĩ về việc refactor đoạn if-else sử dụng điều kiện tổng hợp hoặc guard clause, như này: https://deviq.com/design-patterns/guard-clause)
Thứ sáu, nên tuân theo các design pattern và best practice trên internet, đừng tự invent lại những gì đã có sẵn trừ khi bạn nắm chắc được hầu hết các chuẩn khác và nhận ra "Ồ cái này có thể làm tốt hơn")

Và quan trọng hơn cả là luôn học hỏi từ những thứ trên internet, bất cứ điều gì có thể học được đều luôn tốt. Have a nice day!!