DEV Community: Harry Phung

System design: Bloom Filters & Use Cases

Harry Phung — Sun, 11 Oct 2026 10:03:27 +0000

When designing a system with millions of users or resources, we often use Redis caching to reduce database load. But what happens when requests keep looking for IDs that do not exist? Redis keeps returning cache misses, the database still checks every ID, and legitimate users can experience slower responses as query volume grows.

This is also a useful scenario to discuss in a system design interview: how do you avoid unnecessary queries when bots or attackers send large numbers of requests for nonexistent resource IDs? This article explores how Bloom filters in Redis screen these requests, how the workflow changes, and what the benefits and limitations look like through concrete examples.

1. Overview: Reject Nonexistent IDs Before Reading Data

A Bloom filter provides a lightweight screening step before data retrieval. When the filter is complete and synchronized, a negative result lets the application return “not found” immediately. A positive result continues to the cache and, when necessary, the database.

Without a Bloom filter: nonexistent ID → Redis cache miss → database lookup → 404.
With a Bloom filter: nonexistent ID → usually a negative filter result → 404, skipping the cached payload and database lookup.
Benefit: fewer unnecessary queries, especially when bots or attackers keep requesting different nonexistent IDs.

A simple example: out of one million requests, suppose 500,000 look for nonexistent data. If the filter rejects all of them, the system avoids 500,000 cached-payload reads and 500,000 database queries. With a 1% false-positive rate on this group, the expected number avoided is 495,000 at each layer.

A Bloom filter hosted in Redis still requires a Redis membership check. These figures describe cached-payload reads and database queries skipped, rather than API requests disappearing or all Redis accesses being eliminated.

A Bloom filter returns either negative (definitely absent from the set loaded into the filter) or positive (possibly present, requiring further verification). A positive result can be a false positive. Use negative results to skip lookups only when the filter is complete and synchronized. If a new user exists in the database but has not been added to the filter, the application could incorrectly return 404.

For a closer look at the internal mechanism, read Bloom Filters Explained — System Design, which illustrates insertion, membership checks, and false positives.

2. Requests for Missing Data Still Consume Resources

Suppose you operate a service with 10 million users. A typical user lookup follows this path:

User lookup request → Redis cache → cache miss → database.

This works well for existing users. But if a client keeps sending IDs that do not exist, Redis keeps missing and the database still checks every request.

This is cache penetration : requests for nonexistent data pass through the cache and reach the database. It differs from a cache stampede, where many requests simultaneously fetch the same existing data after its cache entry expires.

An indexed SELECT may be fast. Tens of thousands of unnecessary SELECTs per second still consume connections, CPU, network bandwidth, and operating budget.

The question we want to answer before those lookups is: could this ID exist in the dataset?

3. How Attackers Exploit Cache Misses to Overload the Database

An attacker does not need access to the data to create load. They can repeatedly send requests containing nonexistent resource IDs that still match the format accepted by the API.

Without a Bloom filter: request a nonexistent ID → Redis cache miss → database query → no record → 404. Switch to another nonexistent ID, and the entire workflow repeats.

If the application does not cache “not found” results, each request can trigger a database query. Even with negative caching, constantly changing the ID makes previously cached results difficult to reuse.

The result can be a busy connection pool, higher database CPU usage, and longer waits for legitimate requests. The cache still serves valid data, but it does not stop requests for nonexistent IDs from reaching the database.

With a Bloom filter: request a nonexistent ID → check the filter in Redis → most checks return negative → return 404 immediately, without reading the cached payload or querying the database. Only positive results proceed.

This reduces database load caused by cache penetration. Application and Redis traffic remain, so rate limiting is still necessary. The configured false-positive rate also does not guarantee the same reduction for every attack pattern: repeatedly requesting an ID that produces a false positive can still bypass the filter. Negative caching and rate limits help address this case.

4. A Redis Cache Miss Does Not Mean the User Does Not Exist

Redis typically caches only part of the dataset. Keys can expire or be evicted. The database remains the source of truth.

The Bloom filter in this article therefore represents user IDs in the database , rather than just keys currently present in the cache.

The application receives a request to look up a user by ID.
If the filter is unavailable or cannot be trusted, use the normal cache → database workflow.
If the filter is ready, check the ID using the Bloom filter in Redis.
Negative: return “not found,” skipping the profile cache and database.
Positive: read the profile cache; on a hit, return the user.
Cache miss: query the database. If the user exists, update the cache and return the data. Otherwise, return “not found.”

Four scenarios illustrate the difference:

Scenario	Bloom filter	Profile cache	Database	Response
User exists and is cached	Positive	Hit	Not queried	200
User exists, but cache entry expired	Positive	Miss	User found	200, refresh cache
User does not exist	Usually negative	Not read	Not queried	404
User does not exist, but filter returns a false positive	Positive	Miss	User not found	404

A false positive causes an extra lookup, but must never cause the system to return a user that does not exist.

5. Load Examples: How Many Database Queries Can You Avoid?

5.1. One Million Requests, Half for Nonexistent Data

Suppose the system receives 1,000,000 resource lookup requests :

500,000 requests look for existing resources.
500,000 requests look for nonexistent resources, using different IDs.
The filter is complete and synchronized; there is no negative cache or other blocking layer.
The normal workflow reads Redis cache first and queries the database on a miss.

For the 500,000 requests targeting nonexistent resources:

Operation for nonexistent IDs	Without a Bloom filter	With a filter, all results negative	With a filter, 1% false positives
Bloom filter checks in Redis	0	500,000	500,000
Cached-payload reads in Redis	500,000	0	About 5,000
Database queries after cache misses	500,000	0	About 5,000
Reads avoided at each cache/database layer	0	500,000	About 495,000

The Bloom filter keeps nearly 500,000 requests out of the data retrieval workflow. With a 1% false-positive rate, it is expected to reject 495,000 requests: 49.5% of all incoming requests and 99% of requests for nonexistent data.

The 500,000 requests for existing resources still continue through data retrieval. Their database query count depends on the cache hit rate. For example, with a 90% cache hit rate, existing resources contribute another 50,000 database queries:

Total database queries across one million requests	Query count
Without a Bloom filter	500,000 + 50,000 = 550,000
With a Bloom filter, 1% false positives	5,000 + 50,000 = 55,000
Queries avoided	495,000 , a 90% reduction

These are calculations under stated assumptions, rather than benchmark results. Since the filter lives in Redis, all one million requests still check it, and positive results also read the cached payload. The benefit is a substantial reduction in database queries and skipping cached-payload reads for negative results; it does not guarantee fewer total Redis commands.

5.2. An Example in Requests per Second

Suppose the API receives 50,000 requests per second:

80% target nonexistent users: 40,000 requests per second.
20% target existing users: 10,000 requests per second.
Existing users have a 90% cache hit rate.
The filter contains all user IDs and has a 1% false-positive rate.

Without a Bloom filter: 40,000 nonexistent-user requests plus 1,000 cache misses for existing users produce 41,000 database queries per second.

With a Bloom filter: 40,000 × 1% plus 1,000 produces an expected 1,400 database queries per second.

Under these assumptions, database queries drop by approximately 96.6%. This is an illustrative calculation. Repeated requests, ID distribution, cache hit rate, and filter synchronization all affect actual results.

With Redis's Bloom filter support, each request still accesses Redis for a membership check. The application must load the IDs and keep them synchronized; Redis does not automatically read the database to maintain this set.

6. Illustrated Use Cases

6.1. Profile APIs with Millions of Users

A community application has 10 million users. Alongside legitimate profile views, the API receives invalid links and ID-probing requests from bots.

Without a Bloom filter: every nonexistent ID causes a Redis cache miss followed by a database lookup that finds no user. With 10,000 requests per second targeting different nonexistent IDs, the database could receive 10,000 queries per second just to confirm their absence, assuming no other blocking layer.

With a Bloom filter: request → check the user ID set in Redis → return 404 on a negative result; otherwise read the cache and database as needed. If approximately 1% of these nonexistent-ID requests produce false positives, their expected database query count falls to about 100 per second.

Benefit: most unnecessary queries are rejected before reaching the database, reducing competition for resources with legitimate users. This is an illustrative calculation, rather than a guaranteed reduction for every workload.

For existing users, the effect differs:

Example request	Without a filter	With a filter	Effect
Alex, ID 1001, profile cached	Cache hit → return profile	Filter positive → cache hit → return profile	Neither queries the database; the filter adds a check
Ben, ID 1002, cache entry expired	Cache miss → database → refresh cache	Filter positive → cache miss → database → refresh cache	A database read is still required for the existing user
ID 99999999 does not exist, filter negative	Cache miss → no database record → 404	Filter negative → 404	Avoids the database query and cached-payload read
Nonexistent ID producing a false positive	Cache miss → no database record → 404	Filter positive → cache miss → no database record → 404	No database query reduction for this request

6.2. Resource Lookups: Posts, Products, and Files

A store has millions of products, or a publishing platform has millions of posts. An attacker repeatedly sends nonexistent IDs to the resource detail API.

Without a Bloom filter: product/post request → Redis cache miss → database ID lookup → no data → 404. When IDs constantly change, cached entries for existing resources do not reduce these queries.

With a Bloom filter: request → check the filter for the resource type → return 404 immediately on a negative result; otherwise continue to the cache and database. Each resource type has its own ID set, preventing a product filter from being used to check post IDs.

Benefit: fewer queries to check nonexistent resources, leaving database capacity for legitimate product and post retrieval. Existing resources that are not cached still require database reads. Deleted resources whose IDs remain in the filter may still pass through and require verification against the source of truth.

For files, the workflow is similar: stop on a negative result; on a positive result, look up metadata, check permissions, and retrieve the file from storage. The filter can reduce unnecessary metadata lookups, but it does not confirm that the file is still present in storage.

In a multi-tenant system, include the tenant in the membership key, such as tenant:42:product:123, to distinguish namespaces. A positive result only permits the lookup to continue; access control remains a separate requirement.

6.3. Checking Username Availability During Registration

An application lets users check username availability during registration. Users try new names, or bots submit large numbers of unused names.

Without a Bloom filter: each availability check consults an exact data store to determine whether the username exists, unless the result is already cached.

With a Bloom filter: normalize the username → check the registered-name set → skip the duplicate-check read on a negative result; on a positive result, query the database to distinguish an existing name from a false positive. Do not report that a name is taken based only on a positive result.

Benefit: fewer availability-check reads for unused names. Account creation still writes to the database; the filter does not reduce write costs or guarantee that the name remains available when the account is saved.

Two requests can still attempt to register the same name simultaneously. A database unique constraint must enforce uniqueness. A Bloom filter does not replace that constraint.

Apply consistent username normalization during insertion and lookup, including case handling, Unicode handling, and product-specific rules.

6.4. Checking Previously Crawled URLs or Processed Events

A crawler receives new URLs, or an event-processing system needs to know whether an event ID has already been handled.

Without a Bloom filter: before processing, the system reads its history store for every URL or event ID to check for duplicates.

With a Bloom filter: check the recorded-ID set → on a negative result, skip the history-check read and proceed to recording/processing; on a positive result, consult the exact store to confirm a duplicate. Recording must still handle concurrent requests correctly.

Benefit: fewer history reads when most URLs or events are new. If most have already been recorded, the filter usually returns positive and saves fewer reads. Recording and processing costs remain.

Discarding an event based solely on a positive result could lose a legitimate event because of a false positive. For payments or other workflows requiring exact handling, use idempotency keys and storage mechanisms with appropriate guarantees.

7. Considerations Before Production

7.1. Synchronizing New Users

Suppose the database has just committed user:2001, but the filter update is delayed. A request arriving during that window could incorrectly receive 404.

One way to reduce this window is to add the ID to the filter before the record becomes visible to readers. If the database write fails, the extra ID in the filter merely causes unnecessary lookups. However, updating the filter first does not resolve every issue: cached 404 responses, rollbacks, filter loss, and replica lag still need handling.

If updates are asynchronous through an outbox or CDC, provide a way to bypass the filter or directly check newly created data until synchronization completes. Monitoring lag helps detect problems, but does not itself guarantee the correctness of every negative result.

7.2. Initial Loading and Rebuilding

With 10 million users, read IDs in batches and build a new filter that does not yet serve traffic. Capture writes occurring during the scan, and switch to the new version only after loading and synchronization complete through a consistent process.

Do not enable negative-result blocking while the dataset is only partially loaded. Bypass the filter if its version, readiness, or completeness cannot be trusted.

7.3. Deleting Users

A standard Bloom filter does not support deleting an individual item by clearing its bits, because multiple IDs may share them. A deleted user can still produce a positive result, followed by a database lookup that finds no record.

Periodic rebuilding can remove old IDs. If frequent deletion is required, consider a structure that supports it and evaluate that structure's guarantees separately.

7.4. Negative Caching and Rate Limiting

Caching “not found” results with a short TTL reduces lookups for repeated IDs, including those producing false positives. Distinguish a negative cache entry from a cache miss, and invalidate it when the resource is created.

Bloom filters help with many different nonexistent IDs. Negative caching helps with repeated IDs. Rate limiting remains necessary because rejected requests still consume application resources.

7.5. Measuring Effectiveness

Track the negative-result rate, database query volume, latency, filter size, and update lag. Positive results followed by missing records in the source of truth can reflect deleted IDs or synchronization issues as well as false positives; do not automatically treat them all as pure filter false positives.

8. When Should You Use a Bloom Filter?

Consider a Bloom filter when the ID set is large, many lookups target nonexistent data, downstream lookups are costly, and the system can maintain a complete filter.

If most requests target existing users, the filter may simply add CPU work or an extra Redis round trip. Measure it against a realistic workload before deploying broadly.

In the API workflow, use the Bloom filter as a screening step: a trustworthy negative result lets you skip a lookup; a positive result still requires verification against an exact data source. The database determines whether the data exists, and the application determines whether the caller may access it.

I hope this article gives you more ideas for answering system design questions.

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

Harry Phung — Thu, 08 Oct 2026 08:57:46 +0000

Common Concurrency Interview Question: Handling High-Traffic Purchases and Key Approaches

In technical and system design interviews, one of the most frequently asked questions is: “How do you handle thousands of users attempting to purchase the same product at the exact same second without overselling or crashing the database?” This article walks through the common approaches to this problem—including patterns

](https://dev.to/quangpv/common-concurrency-interview-question-handling-high-traffic-purchases-and-key-approaches-1d7i-temp-slug-5727368)

In our previous article, we discussed how the Reservation Pool pattern eliminates hot-row contention by allowing concurrent transactions to bypass locked records.

However, when engineers deploy SKIP LOCKED to production or run their first high-concurrency load test, they frequently encounter an unexpected surprise:

In MySQL: Transactions crash with Deadlock found when trying to get lock.

In PostgreSQL: Transactions abort with ERROR: could not serialize access due to concurrent update.

Wasn't SKIP LOCKED designed specifically to eliminate lock waiting? Why would it trigger deadlocks or serialization errors?

The issue is not SKIP LOCKED itself. The culprit is the database engine's Transaction Isolation Level —specifically, running under the default REPEATABLE READ. This deep dive examines the internal database engine mechanics behind both engines and explains why switching to READ COMMITTED is mandatory.

1. The Core Conflict: Repeatable Read vs. Non-Blocking Traversal

The isolation level REPEATABLE READ is designed to guarantee that any data read during a transaction remains consistent throughout its lifecycle, preventing phenomena like Non-repeatable Reads and Phantom Reads.

To provide these strong guarantees, database engines employ specialized locking and snapshot mechanics:

MySQL (InnoDB): Uses Next-Key Locks (Record Locks combined with Gap Locks) to physically block inserts into scanned index intervals.
PostgreSQL: Uses Snapshot Isolation , freezing a read snapshot at transaction start.

Under high-concurrency reservation workflows, these exact mechanisms directly counteract the non-blocking intent of SKIP LOCKED.

2. MySQL (InnoDB): Gap Locks and Deadlock Cycles

2.1. What is a Gap Lock?

In InnoDB, REPEATABLE READ prevents phantom reads by placing locks not only on physical records, but also on the empty intervals (gaps) between them.

$$\text{Next-Key Lock} = \text{Record Lock} + \text{Gap Lock}$$

For example, consider a table reservation_pool with IDs 1, 5, and 10:

[Record 1] ─── (Gap 1 to 5) ─── [Record 5] ─── (Gap 5 to 10) ─── [Record 10]

When a query scans index record id = 1, InnoDB not only locks record 1, but also places a Gap Lock on the interval (1, 5) to prevent other transactions from inserting rows between 1 and 5.

2.2. The Conflict with SKIP LOCKED

The expected behavior of SKIP LOCKED is simple: if record 1 is locked, skip it immediately and lock record 5.

However, under REPEATABLE READ:

Transaction B is prevented from advancing because interval (1, 5) is gap-locked by Transaction A.
When hundreds of transactions scan adjacent slots simultaneously, their Gap Locks overlap in a circular dependency, forcing InnoDB to abort transactions with: Deadlock found when trying to get lock.

2.3. The Resolution: Switching to READ COMMITTED

When you configure the transaction to run under READ COMMITTED :

InnoDB completely disables Gap Locking for index searches (retaining gap locks only for foreign key and duplicate-key checks).
Record locks remain active, but the gaps between records are free.
Transaction B detects that id = 1 holds a Record Lock, skips it, and locks id = 5 without blocking.
All concurrent transactions proceed in parallel without deadlocks.

3. PostgreSQL: Snapshot Isolation and Serialization Failures

PostgreSQL does not use gap locks, and its system default is already READ COMMITTED.

However, if an application or connection pool is explicitly configured to REPEATABLE READ , SKIP LOCKED fails under a different mechanism: Snapshot Isolation Conflict.

3.1. How Snapshot Isolation Works in PostgreSQL

Under REPEATABLE READ, PostgreSQL takes a snapshot of the database at the start of the transaction ($T_0$). The transaction operates in an immutable view of the world: any writes committed by other transactions after $T_0$ are completely invisible to it.

3.2. Why REPEATABLE READ Aborts Concurrent Queries

Consider two checkout transactions executing concurrently:

The sequence of events:

Transaction B's frozen snapshot at $T_0$ evaluates Slot 1 as available.
Because Transaction A already committed at $T_1$, Slot 1 currently has no active lock. Therefore, SKIP LOCKED does not bypass Slot 1.
When Transaction B attempts to lock Slot 1, PostgreSQL inspects the tuple on disk and discovers that it was updated and committed by Transaction A after Transaction B's snapshot point ($T_1 > T_0$).
Under REPEATABLE READ, PostgreSQL cannot allow Transaction B to view the new version (violating repeatable read semantics), nor can it allow Transaction B to overwrite Transaction A's committed changes.
PostgreSQL immediately aborts Transaction B with:

ERROR: could not serialize access due to concurrent update

3.3. The Resolution: READ COMMITTED and EvalPlanQual

Under READ COMMITTED :

Each SQL statement takes a fresh snapshot at the moment it begins execution.
PostgreSQL utilizes its internal EvalPlanQual mechanism:
- When the engine encounters Slot 1 (modified by Transaction A), it fetches the latest committed version.
- It re-evaluates the WHERE status = 'available' predicate against the latest version.
- Because the latest version is now status = 'reserved', it no longer matches the predicate.
- PostgreSQL cleanly advances to Slot 2 without raising an error.

4. Summary Matrix: MySQL vs. PostgreSQL

Technical Metric	MySQL (InnoDB)	PostgreSQL
Default Isolation Level	`REPEATABLE READ`	`READ COMMITTED`
Gap Locking Mechanism	Active in `REPEATABLE READ`	Not implemented
Behavior under `REPEATABLE READ`	Blocked on index gaps; causes Deadlocks	Aborts with serialization failure
Behavior under `READ COMMITTED`	Disables gap locks; skips locked rows cleanly	Uses `EvalPlanQual`; advances to next row cleanly
Recommended Configuration	Explicitly set to `READ COMMITTED`	Retain default `READ COMMITTED`

5. Production Configuration Rules

When implementing Reservation Pools or database-backed task queues:

Always explicitly define the isolation level at the transaction level rather than relying on global server defaults:
Index Alignment: Ensure the reservation_pool table has a composite index on (product_id, status) so the engine evaluates only matching records without broad table scans.

6. Conclusion

In high-concurrency architectures, choosing the right SQL syntax is only half the battle. Understanding how your database engine handles index locks and snapshot versions is what determines whether your system scales or deadlocks under pressure.

By pairing SELECT ... FOR UPDATE SKIP LOCKED with READ COMMITTED, you remove the underlying causes of both MySQL Gap Lock deadlocks and PostgreSQL snapshot serialization conflicts, achieving true non-blocking parallel execution.

Common Concurrency Interview Question: Handling High-Traffic Purchases and Key Approaches

Harry Phung — Thu, 08 Oct 2026 08:38:41 +0000

In technical and system design interviews, one of the most frequently asked questions is: "How do you handle thousands of users attempting to purchase the same product at the exact same second without overselling or crashing the database?"

This article walks through the common approaches to this problem—including patterns you might already be using in production—evaluating the advantages and trade-offs of each, and presenting an optimal solution using the Reservation Pool Pattern.

(Note on Redis: You could introduce an in-memory Redis cluster to decrement stock counters using atomic operations. However, from my perspective, introducing additional infrastructure brings substantial operational overhead, higher hosting costs, and added maintenance burden. Furthermore, it introduces distributed dual-write complexity and difficult rollback handling between cache and database. For these reasons, this article focuses strictly on solving the problem end-to-end within a single relational database transaction).

1. Approach 1: DB Constraint (CHECK > 0)

In this approach, you avoid explicit locking (FOR UPDATE) and instead embed the condition directly into the UPDATE statement alongside a database-level integrity constraint:

-- Database-level integrity constraint
ALTER TABLE inventory ADD CONSTRAINT chk_stock_positive CHECK (stock >= 0);

-- Atomic update statement
UPDATE inventory 
SET stock = stock - 1 
WHERE item_id = 99 AND stock > 0;

The application checks the result: if affected rows > 0, the purchase succeeded; if 0, the item is out of stock.

Advantages:

Simple to implement with no changes to table structure.
Guarantees zero overselling: The CHECK constraint and WHERE stock > 0 condition enforce data integrity at the database engine level.
Eliminates an initial SELECT round-trip.

Trade-offs:

Implicit Row Lock: The UPDATE statement still acquires an exclusive lock on that row. Thousands of requests remain serialized.
Connection Pool Saturation: While waiting for the row lock to release, waiting requests hold active database connections, leading to connection pool exhaustion during traffic spikes.
Tail Latency on Stockout: If 100 items remain and 5,000 requests arrive, the last 4,900 requests queue sequentially just to eventually receive affected rows = 0.

2. Approach 2: Row-Level Lock (FOR UPDATE)

The traditional approach explicitly locks the inventory record before executing application logic:

START TRANSACTION;

SELECT stock FROM inventory WHERE item_id = 99 FOR UPDATE;

-- Application checks: if stock > 0, decrement inventory
UPDATE inventory SET stock = stock - 1 WHERE item_id = 99;

COMMIT;

Advantages:

Enforces strict serializability.
Allows application code to execute complex business rules and validations before committing the change.
Well-suited for single-user idempotency (e.g., preventing duplicate orders when a user clicks the checkout button multiple times due to network delay).

Trade-offs:

Severe Hot-Row Contention: When thousands of distinct users compete for the exact same record, all transactions queue sequentially behind a single row lock.
Prolonged Connection Retention: Connection holding time grows linearly with queue depth, quickly depleting the connection pool and causing timeout errors across unrelated services.

3. Approach 3: Reservation Pool & SKIP LOCKED

Instead of directing all traffic to a single row, this approach partitions available stock into discrete records (a pool of slots) and allows transactions to claim slots concurrently using SKIP LOCKED.

3.1. Workflow & Architecture

3.2. Implementation

-- 1. Master ledger tracking total counts
CREATE TABLE inventory_ledger (
    product_id VARCHAR(50) PRIMARY KEY,
    total_stock INT NOT NULL,
    sold_count INT NOT NULL DEFAULT 0
);

-- 2. Reservation pool where each record represents an allocatable slot
CREATE TABLE reservation_pool (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    status ENUM('available', 'reserved', 'sold') NOT NULL DEFAULT 'available',
    reserved_by VARCHAR(100) NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_status (product_id, status)
);

Transaction execution:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Claim 1 available slot, automatically bypassing slots locked by active transactions
SELECT id FROM reservation_pool 
WHERE product_id = 'SNEAKER-99' AND status = 'available' 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

-- Update the claimed slot to reserved
UPDATE reservation_pool 
SET status = 'reserved', reserved_by = 'USER_101' 
WHERE id = :slot_id;

-- Increment sold counter in master ledger
UPDATE inventory_ledger 
SET sold_count = sold_count + 1 
WHERE product_id = 'SNEAKER-99';

COMMIT;

Advantages:

Parallel Execution: Transactions do not block one another. Subsequent transactions automatically skip locked slots and claim the next available row.
Minimal Connection Holding Time: Each transaction holds a connection for only 2ms to 5ms before returning it to the pool, preventing connection exhaustion.
Instant Stockout Detection: When no slots remain, the query returns an empty result set immediately without queue delay.
100% ACID Consistency: All operations complete within a single database transaction.

Trade-offs:

Requires schema adjustments and an additional pool table.
Requires a replenishment mechanism (e.g., maintaining a buffer of 500 to 1,000 active rows from the ledger rather than pre-generating rows for the entire catalog).

4. Isolation Level: Why READ COMMITTED

When implementing SKIP LOCKED, configuring the transaction isolation level is mandatory: it must run under READ COMMITTED.

The key differences between database engines:

MySQL (InnoDB): The default isolation level is REPEATABLE READ. Under this level, InnoDB places Gap Locks across index intervals. When multiple transactions scan for slots concurrently, overlapping Gap Locks block one another, neutralizing SKIP LOCKED and causing Deadlocks. Switching to READ COMMITTED disables gap locking for index scans.
PostgreSQL: The default is already READ COMMITTED. If set to REPEATABLE READ, PostgreSQL uses Snapshot Isolation and aborts transactions with ERROR: could not serialize access due to concurrent update when a row is modified by another committed transaction. Under READ COMMITTED, PostgreSQL's internal EvalPlanQual mechanism automatically re-evaluates the latest row version and skips locked or committed rows cleanly without errors.

For an in-depth database engine analysis, refer to:

[

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

Common Concurrency Interview Question: Handling High-Traffic Purchases and Key ApproachesIn technical and system design interviews, one of the most frequently asked questions is: “How do you handle thousands of users attempting to purchase the same product at the exact same second without overselling or crashing the database?” This article walks

Quang's Tech CornerQuangPV

](https://blog.quangpv.top/deep-dive-why-skip-locked-requires-read-committed-in-mysql-and-postgresql/)

5. Summary Comparison

Evaluation Metric	Approach 1: Constraint `> 0`	Approach 2: Row Lock (`FOR UPDATE`)	Approach 3: Pool Pattern + `SKIP LOCKED`
Locking Mechanism	Implicit lock on `UPDATE`	Explicit exclusive row lock	Row lock on individual slot; skips busy rows
Concurrency Model	Serialized (Blocking)	Serialized (Blocking)	Parallel (Non-blocking)
Connection Holding Time	Increases with queue depth	Increases with queue depth	Minimal (2ms – 5ms)
Connection Pool Risk	High during traffic surges	High during traffic surges	Very low
Out-of-Stock Latency	Must wait through queue	Must wait through queue	Instantaneous (empty result in ~2ms)
Implementation Complexity	Very low	Low	Moderate

6. Conclusion

Hopefully, these approaches give you broader perspectives on handling concurrency trade-offs and provide a clear edge in your technical interviews.

Being able to explain when a simple constraint is sufficient versus when an advanced pattern like a Reservation Pool with SKIP LOCKED is required demonstrates practical engineering maturity under real-world constraints.

Realtime Config Updates for Multiple Instances with Redis Pub/Sub

Harry Phung — Mon, 18 May 2026 06:54:35 +0000

In this article, I want to share one way to handle dynamic configuration updates with Redis Pub/Sub.

But before getting into the implementation, I think it is worth stepping back and asking a more basic question:

When configuration can change while the system is running, where should the application read that configuration from?

There are a few common approaches, and each comes with its own trade-offs.

Common Ways to Handle Dynamic Configuration

When configuration can change at runtime, one natural approach is to store it in a database or Redis DB.

For example, whenever the application needs to know whether it should send notifications to Slack or Telegram, it reads configuration from a DBMS, Redis DB, or a config service:

{
  telegram: true,
  slack: false,
}

The clear advantage of this approach is that the application always reads the latest value.

The downside is that every request, or every time some piece of logic runs, the application has to hit the DBMS or Redis to fetch configuration. If that configuration is read frequently but changes rarely, a relatively static piece of data becomes a repeated access point in the system.

Another common approach is:

Read the configuration once when the application starts.
Keep it in memory inside each instance.
When the configuration changes, update the source of truth and restart the instances so they reload the new value.

This avoids touching the database or Redis on every request. But once the number of instances grows, restarting stops being an invisible operation. It becomes part of the operational workflow:

restart in the right order,
make sure no instance is missed,
accept a delay before the whole system becomes consistent again.

Another Approach: Keep Config in Memory and Use Redis Pub/Sub for Realtime Updates

Instead of reading from the DB or Redis every time configuration is needed, I can keep the configuration in memory inside each instance for fast access.

The remaining question is: when an admin updates the configuration, how do all running instances know that they should refresh their local copy?

This is where Redis Pub/Sub is useful.

Instead of making every instance poll for changes, I let them listen for a shared event.

The flow is simple:

Admin updates config
        |
        v
Publish event to Redis channel
        |
        v
All subscribed instances receive the event
        |
        v
Each instance updates its in-memory config

The benefits are:

no application restart required,
no need to hit the DBMS or Redis DB every time configuration is read,
no continuous polling,
multiple instances can receive the update at nearly the same time,
in-memory configuration can be refreshed immediately after an event arrives.

Redis Pub/Sub is not the source of truth for configuration here.

It acts more like a bell: “the config changed, refresh your local copy.”

Example with NestJS

Suppose the system can send notifications to Telegram and Slack.

The initial configuration may look like this:

{
  telegram: true,
  slack: true,
}

Later, an admin wants to disable Slack notifications while keeping Telegram enabled:

{
  telegram: true,
  slack: false,
}

In practice, I usually do not create a separate Redis client for every service.

Redis should be treated as shared application infrastructure, so multiple features can reuse the same publisher and listener:

one global RedisPublisher to publish events,
one global RedisListener to subscribe and dispatch messages to interested modules.

1. Global Redis Publisher and Listener

import { Injectable, OnModuleInit } from '@nestjs/common';
import Redis from 'ioredis';
import { EventEmitter } from 'node:events';

export const RUNTIME_CONFIG_CHANNEL = 'runtime-config';

@Injectable()
export class RedisPublisher {
  private readonly client = new Redis(process.env.REDIS_URL);

  async publish(channel: string, payload: unknown) {
    await this.client.publish(channel, JSON.stringify(payload));
  }
}

@Injectable()
export class RedisListener implements OnModuleInit {
  private readonly client = new Redis(process.env.REDIS_URL);
  private readonly emitter = new EventEmitter();

  async onModuleInit() {
    await this.client.subscribe(RUNTIME_CONFIG_CHANNEL);

    this.client.on('message', (channel, message) => {
      this.emitter.emit(channel, JSON.parse(message));
    });
  }

  on(channel: string, handler: (payload: unknown) => void) {
    this.emitter.on(channel, handler);
  }
}

2. API for Updating Notification Configuration

Here I use the key notification.channels, because this configuration belongs specifically to the notification feature.

import { Body, Controller, Put } from '@nestjs/common';

type NotificationChannelsConfig = {
  telegram: boolean;
  slack: boolean;
};

@Controller('admin/notification-config')
export class NotificationConfigController {
  constructor(private readonly redisPublisher: RedisPublisher) {}

  @Put()
  async update(@Body() payload: NotificationChannelsConfig) {
    await this.redisPublisher.publish(RUNTIME_CONFIG_CHANNEL, {
      key: 'notification.channels',
      value: payload,
    });

    return { success: true };
  }
}

When the admin calls the API with this payload:

{
  telegram: true,
  slack: false,
}

the published message carries the updated configuration.

3. `NotificationService` Keeps Config in Memory

NotificationService has a config property.

Its default value is loaded from .env. When the module initializes, the service registers with the global Redis listener and only reacts to the key it cares about.

import { Injectable, OnModuleInit } from '@nestjs/common';

type NotificationChannelsConfig = {
  telegram: boolean;
  slack: boolean;
};

@Injectable()
export class NotificationService implements OnModuleInit {
  private config: NotificationChannelsConfig = {
    telegram: process.env.NOTIFY_TELEGRAM === 'true',
    slack: process.env.NOTIFY_SLACK === 'true',
  };

  constructor(private readonly redisListener: RedisListener) {}

  onModuleInit() {
    this.redisListener.on(RUNTIME_CONFIG_CHANNEL, (payload) => {
      const message = payload as {
        key: string;
        value: NotificationChannelsConfig;
      };

      if (message.key === 'notification.channels') {
        this.config = message.value;
      }
    });
  }

  getConfig() {
    return this.config;
  }

  async notify(message: string) {
    const config = this.getConfig();

    if (config.telegram) {
      await this.sendToTelegram(message);
    }

    if (config.slack) {
      await this.sendToSlack(message);
    }
  }

  private async sendToTelegram(message: string) {
    // send telegram message
  }

  private async sendToSlack(message: string) {
    // send slack message
  }
}

The key point is that notify() does not need to query the database or Redis every time it runs.

It only calls getConfig() to read the configuration already available in the instance's memory.

When the admin updates the configuration through the API:

{
  telegram: true,
  slack: false,
}

all running instances receive the event, update this.config, and from the next request onward, notify() sends only to Telegram without requiring an application restart.

An Important Note Before Going to Production

Redis publishers and subscribers can lose their connection, reconnect slowly, or be temporarily offline at the exact moment a message is published.

So in a real system, I would still want:

automatic reconnect and retry logic for both publishers and subscribers,
logs and metrics to know when Redis connections drop,
a config source of truth such as a database, Redis key-value storage, or a config service so instances can load the correct configuration on startup or reconnect.

Also, plain Redis Pub/Sub follows a fire-and-forget model.

It does not provide ACKs or retry delivery automatically when a subscriber misses a message.

If the configuration needs stronger guarantees, I can add an ACK mechanism:

Each event carries an eventId or version.
After applying the update, the subscriber sends back an ACK for the applied version.
If the publisher does not receive enough ACKs within a time window, it can retry or reconcile the state again.

publish config.updated(version=42)
        |
        v
subscriber updates this.config
        |
        v
subscriber ACKs version=42
        |
        v
if ACK is missing -> retry / reconcile state

In short:

Pub/Sub helps with realtime updates. Reconnects, ACKs, and retries are what make the system safer to run in production.

Conclusion

If a configuration value is read very often but changes only occasionally, keeping it in memory and using Redis Pub/Sub to synchronize updates across instances can be an efficient approach:

fewer DB/Redis reads,
no need to restart the application every time configuration changes,
near-realtime updates across instances.

The trade-off is that I also need to manage an additional moving part in the system: Redis Pub/Sub itself, along with reconnect, retry, and ACK mechanisms when stronger reliability is required.

I hope you found this article useful.

The Auto-Increment Trap: Scaling Systems and the Distributed ID Problem

Harry Phung — Fri, 24 Apr 2026 16:08:46 +0000

In the world of relational databases, AUTO_INCREMENT is like a trusted "old friend." Why do we love it so much?

Excellent Performance : Since IDs increase sequentially, B-Tree indexes work extremely efficiently. New data is always inserted at the end of the page, avoiding index fragmentation.
Space Efficiency : INT or BIGINT types take only 4-8 bytes, much smaller than UUIDs (16 bytes), which reduces storage size and speeds up scans.
Human-Friendly : Looking at ID=123 is much easier to read and debug than a long, messy string like 550e8400-e29b-41d4-a716-446655440000.

However, this "friend" starts to get "cranky" when your system enters the Scale-out or High Availability (HA) phase. At this point, the initial convenience becomes an annoying technical hurdle.

1. The "Disaster" Scenario: When Slave is Promoted to Master

In typical High Availability (HA) architectures, we usually have a Master-Slave cluster. All write operations are sent to the Master and then replicated to the Slaves.

The problem arises when the Master fails and we need to "promote" a Slave to be the new Master.

Why the Bug?

Replication in many databases (like MySQL) is often Asynchronous or Semi-synchronous. When the Master writes a record with ID=100, the Slave might only have received up to ID=98 before the Master goes down. When this Slave is promoted to Master, it will continue issuing IDs starting from 99.

The Result: The system will attempt to insert records with ID=99, 100 — values that already exist in business logic or related systems (like Cache, ElasticSearch, or even the old Master's backup files). A "Duplicate Entry" bug explodes, and data recovery becomes a nightmare due to primary key conflicts.

2. The "Step k" Solution: A Temporary but Risky Lifesaver

To solve the issue of multiple instances writing without ID collisions, a classic technique is to set an Offset and an Increment.

Suppose you have 2 DB Instances; you could configure them as follows:

Server A: auto_increment_offset = 1, auto_increment_increment = 2 (IDs: 1, 3, 5, 7...)
Server B: auto_increment_offset = 2, auto_increment_increment = 2 (IDs: 2, 4, 6, 8...)

MySQL Configuration Example:

-- On Server 1
SET @@auto_increment_increment = 10; -- Assuming overhead for up to 10 servers
SET @@auto_increment_offset = 1;

-- On Server 2
SET @@auto_increment_increment = 10;
SET @@auto_increment_offset = 2;

Why is this solution hard to maintain?

Low Flexibility: If you initially set the step k=2 (for 2 servers) and later need to scale to 5 servers, you must reconfigure all existing instances. This is extremely risky and often requires downtime.
Distributed Management: You must ensure that offsets never overlap between servers. In a cloud environment with dozens of microservices and DB instances, manual maintenance is painful.
ID Waste: If a server is down for a long period, the ID range reserved for it remains unused. While not a huge issue with BIGINT, it represents a lack of optimization.

3. Auto-increment and the Pain of "Sharding"

When data becomes too massive, we must split the database into multiple "shards." At this point, Auto-increment is almost useless if you need a Globally Unique ID.

If each shard runs its own AUTO_INCREMENT:

Shard 1 has User ID = 10.
Shard 2 also has User ID = 10.

When the application needs to perform "cross-shard" queries (e.g., getting friends of User 10, where those friends are scattered across multiple shards), the system won't know which "User 10" is which. Merging data from shards into a central warehouse (Data Warehouse) for analysis will also cause immediate Primary Key conflicts.

4. More Suitable Alternatives

When AUTO_INCREMENT can no longer handle the load, consider these "candidates" based on your specific use case:

Twitter Snowflake (and its variants):
UUID v7 (The modern choice):
Database Ticket Server (Flickr/Instagram approach):
Segment Technique (Meituan/Leaf approach):

Conclusion

In the end, every technical choice is a Trade-off. There is no "silver bullet" for every situation:

If the project is small and needs speed: Stick with Auto-increment.
If you need HA and sequential ordering: UUID v7 or Snowflake are excellent choices.
If you want short IDs for a massive system: Consider the Ticket Server or Segment model.

Choose the one that best fits your current context and your team's operational capabilities, rather than simply choosing the most complex one!

Big O: Don't talk about speed!

Harry Phung — Fri, 24 Apr 2026 15:53:47 +0000

In the world of computer science, we are often taught a simple hierarchy: (O(1) < O(log N) < O(N) < O(N log N) < O(N^2)). We are led to believe that an (O(N)) algorithm is inherently "better" than an (O(N^2)) one.

However, in the real world, this is a dangerous oversimplification. Big O notation is a tool for classifying growth rates as (N) approaches infinity, not a stopwatch for measuring absolute execution time for practical data sizes. One of the most common pitfalls in performance optimization is ignoring the "hidden constants" and the reality of modern hardware.

1. The Deception of Hidden Constants

Let’s look at a concrete example. Suppose you are choosing between two algorithms to solve the same problem:

Algorithm A : Complexity is (O(100,000,000 times N)) -> In Big O terms, we drop the constant, so it is (O(N)).
Algorithm B : Complexity is (O(2 times N^2)) -> Dropping the constant, it is (O(N^2)).

On paper, a junior developer would choose Algorithm A without hesitation. But let’s see what happens when we run these algorithms with (N = 100):

Algorithm B takes: (2 times 100^2 = 20,000) steps.
Algorithm A takes: (100,000,000 times 100 = 10,000,000,000) (10 billion) steps!

With (N = 100), Algorithm B is 500,000 times faster than Algorithm A. While A is theoretically "better," it is practically unusable for small inputs due to its massive constant factor.

2. The Crossover Point: Where Theory Meets Reality

So, when does (O(N)) actually become faster? Only when (N) is large enough.

This is known as the "Crossover Point." On a performance graph, the (O(N^2)) curve starts lower but grows quadratically. The (O(N)) curve starts much higher due to the constant but grows linearly. They eventually intersect at the crossover point.

In our example, the crossover point is at (N = 50,000,000). Unless your dataset is larger than 50 million elements, the (O(N^2)) algorithm is actually the superior choice.

3. Hardware Reality: The Critical Cache Factor

Big O assumes all operations are equal. In reality, hardware costs vary wildly. A single addition is fast, but a memory access can be a bottleneck. Specifically, reading from Main Memory (RAM) can be 100x slower than reading from the CPU Cache (L1/L2).

This leads to a fascinating reality: An (O(N)) algorithm that jumps around randomly in memory (causing constant Cache Misses) can be significantly slower than an (O(N^2)) algorithm that accesses data sequentially (utilizing Cache Locality). The theoretical "fewer operations" of (O(N)) are completely wiped out by the massive latency of the hardware.

4. Real-World Data & The Trap of Micro-Optimization

Real-world data size is the most important factor. Before you complicate your codebase with advanced data structures just to reduce a Big O class, ask yourself: "How large is my data really?"

If your array rarely exceeds 100 elements, switching from a simple nested loop to a complex balanced tree might actually slow down your program due to setup overhead and poor cache usage.

Prioritize benchmarking over theoretical complexity. Use profiling tools to measure actual execution time with real-world data. Avoid "micro-performance" improvements that make the code unreadable without providing a measurable gain.

5. Final Conclusion: Developing a Pragmatic Perspective

To summarize the key takeaways:

Big O is a classification tool, not a measurement : It describes how an algorithm scales, not how fast it runs on your machine today.
Don't ignore constants and hardware : Large constants or poor cache locality can make a "better" Big O class perform worse in practice.
Real-world data is king : Always measure and benchmark with actual data sizes before deciding to optimize. Avoid premature micro-optimization that adds unnecessary complexity.

Understanding these nuances will give you a much sharper perspective on algorithm design. It provides you with solid arguments when debating with colleagues during design reviews and helps you stand out to interviewers by demonstrating a pragmatic, senior-level approach to performance engineering.

Distributed Locking & The 'Old Overwrites New' Problem (Fencing Tokens)

Harry Phung — Wed, 22 Apr 2026 08:38:20 +0000

When designing Distributed Systems, we often use a Distributed Lock to ensure that at any given time, only one process is allowed to perform a specific task. For example: writing data to a shared file, or deducting money in a database.

However, there is a deadly trap. Even if you use the most sophisticated locking algorithms, your system can still break due to unintended data overwriting. Today, we will explore this problem and its solution: Fencing Tokens.

The Problem: The "Awakened" Node and Data Overwrites

Typically, when using a Distributed Lock, our code looks something like this:

// THIS CODE IS NOT NECESSARILY SAFE (UNSAFE)
function writeData(filename, data) {
    var lock = lockService.acquireLock(filename);
    if (!lock) {
        throw 'Failed to acquire lock';
    }
    try {
        var file = storage.readFile(filename);
        var updated = updateContents(file, data);
        storage.writeFile(filename, updated);
    } finally {
        lock.release();
    }
}

Although the logic seems airtight, in a distributed system environment, the code above has a critical flaw. Let's look at 2 common cases that cause errors:

Case 1: Node A pauses for too long

Node A acquires the lock but is paused (e.g., due to Garbage Collection, network issues...) until the lock's Time To Live (TTL) expires. At this point, Node B sees the lock is available, jumps in to acquire it, and successfully writes its data. Later, Node A suddenly "wakes up", continues its remaining execution, and overwrites the result on top of the data Node B just wrote. Result: Node B's data is unintentionally overwritten by Node A.

Illustration: Case 1 (Data Overwrite due to Pause)

Case 2: Intersection Time Conflict (Race Condition)

Even if Node A finishes processing before Node B (meaning Node B overwriting Node A would be logically correct sequentially), there is a problem. If Node A's TTL expires right as it is preparing to write, an "intersection" of time occurs between when Node A is finishing its operation (without the lock) and when Node B has just acquired the lock to start reading/processing. This resource overlap creates potential Race Condition bugs that are extremely hard to debug.

Illustration: Case 2 (Intersection Conflict)

As you can see, the root cause is that we are relying solely on the client to determine if it has permission to write. But in a distributed system, the time and state of the client are unreliable.

The Solution: Fencing Tokens

To completely solve this problem, Martin Kleppmann (author of Designing Data-Intensive Applications) emphasizes the importance of Fencing Tokens.

The idea here is: Every time a Node acquires a lock, the Lock Service doesn't just return "OK", it must also grant the Node a Token (a monotonically increasing number).

The process changes as follows:

Node A acquires the lock, receives Token = 33.
Node A is "frozen", lock expires.
Node B acquires the lock, receives Token = 34 (since the token number always increases).
Node B sends a write request along with Token: 34. The Storage Service records the highest current token as 34 and saves the data.
Node A"wakes up", sends a write request along with Token: 33.
The Storage Service checks: 33 is less than 34 (Token is old). Node A's request is REJECTED. Data is safe!

Solution Illustration (Fencing Token)

The Big Takeaway

Never completely trust the Client: Time (Clocks), Network Delays, or process interruptions (GC Pauses) can trick the client into thinking it's still holding the lock safely.
Protect from the Resource side (Storage): The destination of the data (Storage, Database, External API...) must have a "Gating Mechanism" to verify the validity of the request. Fencing Tokens are a perfect mechanism for this.
If you only need a Lock for Efficiency optimization (avoiding duplicate work), the lock occasionally slipping might be okay. But if you need a Lock to ensure data Correctness , lacking Fencing Tokens (like some simple locking algorithms) is a fatal flaw!
Core principle: When a task takes longer to process than the lock's TTL, that execution flow is inherently Unsafe. Always be careful with the actual execution time of a task!

Reference: The article How to do distributed locking by Martin Kleppmann.

How CPU Cores, Processes, and Threads Actually Work

Harry Phung — Wed, 22 Apr 2026 08:15:42 +0000

This document summarizes the core concepts of how a CPU handles processes and threads, the mechanism of time slicing along with its trade-offs, and how popular programming languages apply these models.

1. Basic Overview: CPU, Core, Process, and Thread

To understand how a computer works, we go from hardware to software:

CPU (Central Processing Unit): The central processor. A computer can have one or multiple physical CPUs (sockets).
Core : A CPU contains multiple Cores. Each Core is an actual physical processing unit.
Process : The "container" that holds a running program. Each process has its own completely independent memory space and resources.
Thread : The smallest unit of execution that the Operating System (OS) can schedule. A process contains at least one thread (the main thread) and can contain multiple threads.

Core Truth: The CPU does not run Processes — the CPU runs Threads. A process is just an outer shell. Context switching between Processes is actually switching between Threads belonging to different Processes (which also entails switching memory spaces, making it very heavy).

2. CPU Processing Mechanism and Time Slicing

Each Core can only run exactly 1 Thread at any given time. So what happens when the number of active Threads exceeds the number of Cores?

2.1. True Parallelism

When active Threads ≤ number of Cores:

Each Thread is assigned to a separate Core. Everything runs in true parallel at the same time. There is no waiting.

2.2. Time Slicing and Context Switch

When active Threads > number of Cores (The most common scenario):

The CPU cannot process all of them simultaneously. The Operating System (OS) applies the Time Slicing mechanism.

The OS divides CPU time into very small slices (called Time Slices or Quantums, usually just a few milliseconds).
The OS alternately assigns the CPU to Thread A until its time slice runs out, then pauses A, switches to Thread B, then to Thread C, etc. The switching speed is so extremely fast that it gives us the illusion that everything is running "concurrently".

The process of switching from one Thread to another is called a Context Switch. The OS must:

Save the state (Registers, Program Counter...) of the currently running Thread.
Load the state of the next Thread to be run.

Below is a Timeline diagram illustrating the cost of Time Slicing interleaved with Context Switches:

Chart Note: The red [Overhead] blocks represent the "dead time" (cost) where the CPU does no useful work for the application other than unloading the old Thread and loading the new Thread.

3. The Nature of Time Slicing and Trade-offs

Time Slicing solves the multitasking problem, but it's not perfect. It brings massive hidden costs if abused.

3.1. The Context Switch Paradox and Minimal Execution Time

A Context Switch is not free. The act of saving/loading states consumes CPU cycles.

For the switch to be meaningful, the CPU must spend a minimum amount of time actually processing the logic of the Thread (called Minimal Execution Time ).
The Problem: When you spawn too many tasks/threads, the amount of time each thread receives in a cycle becomes smaller. If a thread's execution time shrinks and approaches the time taken just to Context Switch, the CPU falls into a state of Thrashing — meaning the CPU is 100% busy just swapping back and forth between threads, while the actual useful work processed is minimal.

3.2. Real-world Trade-offs

When you spawn too many Threads exceeding the CPU's capacity, the system suffers 3 main consequences:

Increased Latency: The more threads there are, the longer the CPU Run queue. A thread might have to wait a very long time before it's its turn to get a "time slice". This increases the overall latency of the application.
CPU Overhead: As mentioned above, the CPU wastes more resources managing schedules and Context Switching instead of running your business logic.
Cache Misses - Extremely Severe: The CPU has ultra-fast caches (L1, L2, L3 Cache). When Thread A is running, A's data is loaded into the Cache. If the CPU is forced to Context Switch to Thread B, B's data will push A's data out of the Cache (Cache Pollution). When Thread A comes back to run, the CPU can no longer find the data in the Cache ( Cache Miss ) and must go down to the RAM to read it again — reading from RAM is hundreds of times slower than Cache.

4. Real-world Application: Operational Models of Languages/Runtimes

Understanding the costs of Threads and Context Switches, backend technologies have chosen different paths:

1. PHP-FPM (Multi-Process / Pre-fork Model)

Mechanism: Each incoming request is allocated to a separate Process (containing 1 Thread inside).
Characteristics: Very safe; an error in one request won't kill another request (due to independent memory spaces).
Massive Performance Drawbacks:
- Processes are very heavy (consume a lot of RAM).
- Context Switching between Processes is vastly more expensive than between Threads (because it requires swapping Virtual Memory space / TLB flush).
- If there are 1000 concurrent requests, PHP-FPM needs 1000 Processes. The server will run out of RAM and the CPU will immediately suffer from Thrashing due to excessive Context Switching.
- Limitation: Bottlenecked by the pre-configured number of Worker Processes (e.g., pm.max_children = 50). Beyond 50 requests, subsequent requests must wait in a queue.

2. Node.js (Single Thread & Event Loop Model)

Mechanism: Node.js realizes that Context Switching is the enemy of performance. Therefore, it runs all JavaScript code on a single Main Thread (1 Process).
Characteristics:
- Completely eliminates Context Switch costs for the main logic code. CPU isn't wasted, and Cache rarely Misses.
- Handles tens of thousands of connections (I/O) concurrently thanks to the Non-blocking I/O mechanism (combined with epoll/kqueue at the OS level). When there's a File/Network read task, Node.js offloads it to the background (libuv's Thread pool) and moves on to do other things. Once finished, a callback/event notifies it.

Drawbacks: Because there is only 1 Thread, if you have a heavy computation task (CPU-bound like image compression, a billion-iteration loop), it will block the entire process — other requests will hang waiting until that task completes.

3. Golang (M:N Scheduling & Goroutines Model)

Mechanism: Go strikes a brilliant balance. It spawns a number of OS Threads (exactly equal to the number of physical CPU Cores to achieve true parallelism). On those OS Threads, it runs tens of thousands, or even millions, of Goroutines.
Characteristics (Why Go is top-tier?):
- Ultra-lightweight: A Goroutine is a User-space Thread (a virtual thread managed by the Go Runtime). It only takes about ~2KB of RAM to initialize (compared to 1-2MB for a standard OS Thread).
- Ultra-cheap Context Switch: When Goroutine A is blocked (e.g., calling a DB), the Go Runtime saves A's state and picks Goroutine B to stuff into the OS Thread to continue running. This Context Switch happens at the software level (User-space), without notifying the Operating System , thus the speed is incredibly fast and the cost is a fraction of an OS Context Switch.
- Optimized Cache & CPU: By keeping the number of OS Threads low (equal to Cores), Go completely avoids OS-level Context Switch overload and maximizes CPU Cache utilization.

Summary (The Takeaway)

CPUs run Threads, not Processes.
Time Slicing allows running more threads than cores, but brings latency, CPU overhead, and Cache data loss.
PHP is safe but heavy because it spawns many Processes.
Node.js uses 1 Thread combined with Asynchronous I/O to dodge Context Switches, excelling at I/O but fearing heavy computation.
Golang creates ultra-lightweight "virtual threads" (Goroutines) and schedules them on real Cores itself, maximizing both I/O and computation.

How the Two Most Popular Open-Source Databases (MySQL & PostgreSQL) Handle INSERT, UPDATE, DELETE

Harry Phung — Fri, 20 Mar 2026 09:11:23 +0000

When choosing a relational database for a real-world project, developers often debate surface-level features like JSON support, PostGIS, or clustering capabilities. However, the core performance determinant of a system lies at the lowest tier: how the database organizes physical storage and processes write operations (INSERT, UPDATE, DELETE).

This article will delve into the underlying architecture of InnoDB (MySQL's default engine) and PostgreSQL. This will help you clearly understand the strengths and weaknesses of each database management system based on fundamental data modification operations.

Core Differences in Physical Data Organization

Before analyzing each operation, we must understand how these two systems arrange data on the hard drive. (Practical Evidence: This difference is exactly why a table containing 1 billion records in PostgreSQL often takes up 10-20% more disk space than MySQL, due to the over-allocation of the Heap buffer).

1. MySQL (InnoDB): Clustered Index Storage (B+Tree)

In InnoDB, the table itself is a B+Tree heavily sorted by the Primary Key. The data of a row is stored directly at the leaf nodes of this tree. All Secondary Indexes point to the Primary Key of that row.

2. PostgreSQL: Heap Storage

PostgreSQL uses a free memory region called a Heap to store tuples (rows). Data pushed into the Heap does not need to be sorted by any order or Primary Key. Subsequently, all Indexes (including the Primary and Secondary Indexes) are built on a private B-Tree structure. The leaf nodes of this Index tree contain physical pointers (CTIDs) pointing directly to the exact location of the record in the Heap.

Understanding this crucial difference will reveal how they behave with I/O.

1. The INSERT Operation: The Battle of Random IDs

Analyzing the data insertion operation heavily depends on the data type of the Primary Key. (Practical Evidence: Choosing the wrong storage engine when dealing with massive amounts of Random UUIDs can severely degrade the maximum disk write speed from 50,000 IOPS down to just 2,000 IOPS because the disk is locked by the continuous restructuring of tree elements).

With Auto-Increment / Sequential IDs:

MySQL truly shines. Due to the Clustered Index characteristic, sequential data is continuously appended to the extreme right edge of the B+Tree. The write operation is extremely smooth, avoiding disk read head distraction. The physical data sits closely together, giving Range Scan queries exceptionally high speeds.

PostgreSQL also performs this task well; however, it consumes slightly more I/O because it has to write to two different places: inserting data into the empty Heap space, then updating the Index B-Tree.

With Random IDs (UUIDv4):

PostgreSQL takes absolute priority. The Heap structure is a massive advantage here. It simply finds available memory (Free Space) and randomly inserts data without breaking any memory arrays.

Conversely, this is a nightmare for MySQL. Attempting to insert random, unordered data into a B+Tree means the engine must constantly perform Page Splits to squeeze data in between. This creates severe local locks and skyrockets I/O latency under high load. Therefore, when working with MySQL, engineers are forced to switch to UUIDv7 (time-sequential) or Snowflake IDs instead of UUIDv4.

2. The UPDATE Operation: The Dark Side of MVCC Architecture

This is the area that shows the most distinct contrast in the design philosophy of the two systems. (Practical Evidence: Continuously updating driver coordinates every second in Ride-Hailing applications is the classic "Update In-place" problem, where InnoDB's hardware-protecting advantage helps the Database Server avoid suffocating under a mountain of dead data).

MySQL (InnoDB): Update In-Place

When the system receives an UPDATE command for a record, if the data size does not bloat larger than the old version, MySQL goes straight to that physical location and confidently overwrites the new data. Naturally, to solve the problem of concurrent readers while writing (MVCC - Multi-Version Concurrency Control), InnoDB uses the Undo Log. The pre-modified version is placed in the Undo Log to serve the read process of older transactions.

Benefit: Only strictly necessary data is modified; the hard drive does not generate unnecessary garbage. If the updated column is not part of any Index, the Index trees remain completely untouched, perfectly preserving hardware resources.

PostgreSQL: Update is Essentially Delete + Insert New Row

Unlike MySQL, to ensure the integrity of MVCC, PostgreSQL never overwrites old data. Every time you execute an UPDATE command, PostgreSQL treats it as a logical deletion of the current record and immediately writes the entire information of that row into a completely new physical record.

Consequences:

The first is Write Amplification. Even if you only change the value of one column, Postgres generates a whole new physical row with the exact integrity of the old row.

Next, because the newly generated row resides at a new memory address, PostgreSQL is forced to scan and update ALL the tips of the Index trees pointing to that table to reconnect the physical pointers, regardless of whether the Index pointers were actually changed or not.

To better understand the hidden price of managing MVCC in PostgreSQL, you can refer to the in-depth blog post: Internals of MVCC in Postgres: Hidden Costs of updates vs inserts. The article sharply analyzes why heavily updated systems easily bog down due to Index Thrashing (destroying I/O just to maintain Indexes).

Although PostgreSQL has a defensive technique (HOT - Heap Only Tuples) by reserving a buffer called fillfactor right inside the same data block, this technique only works if the updated column is not part of any Index structure.

3. The DELETE Operation: The Garbage Collection Process

Both MySQL and PostgreSQL utilize a Soft-delete model at the system level (marking as deleted instead of immediately sweeping the disk) to save response time, but they differ in their recovery mechanisms. (Practical Evidence: Many startups using PostgreSQL have experienced Database crashes during hot growth phases simply because their autovacuum configuration was too superficial, causing a massive accumulation of "Dead Tuples" that paralyzed the CPU and I/O bandwidth during full table scans).

MySQL (InnoDB):

At the leaf nodes of the Clustered Index, the deleted record gets a "deleted_flag" bit flipped, and that memory address is attached to a Free List. As soon as another INSERT command of matching size arrives, InnoDB immediately reuses that gap, avoiding wasted space.

PostgreSQL:

Similarly, the deleted row transforms into a "Dead Tuple" by attaching the transaction info (txid) of the recent deletion and hiding that row from subsequent arrivals.

Combined with the UPDATE in-place nightmare described above, PostgreSQL spawns an extreme amount of Dead Tuples on the hard drive. The only way to clean up and reclaim this space is to wait for the background garbage collection process (VACUUM Worker) to sweep the entire system. If the data volatility is too high and the autovacuum configuration is not well-tuned, the table will bloat out of control (Table Bloat), forcing the hard drive bandwidth to bear the burden of scanning garbage data before touching real data.

4. Conclusion via Trade-offs

Choosing a data architecture never involves the concept of an "absolutely superior technology"; rather, the core lies in the trade-offs to solve the right business domain.

To prove the importance of this architectural trade-off, you can refer to the classic technical analysis: Why Uber Engineering Switched from Postgres to MySQL. Even though modern PostgreSQL versions have been significantly upgraded and optimized to reduce the load on the garbage collection process, at the core architectural level, the root MVCC element that causes Write Amplification still fundamentally exists. As long as a record is not updated directly In-place, the system will continue to spend I/O duplicating physical rows.

In summary:

If your application leans towards static data or one-way appending (Append-only), requires complex analytical queries, polymorphic data structures (JSONB, PostGIS, Array), and the number of records subjected to In-place updates is not disastrous, PostgreSQL remains the undisputed all-in-one powerhouse.
Conversely, if the core use case of the project is Write-heavy with the frequency of data overwrite (UPDATE) on a finite number of records repeating thousands of times per second, the architecture of MySQL (InnoDB) will protect the server hardware far more perfectly and keep the I/O latency graph absolutely stable.

Hopefully, this deep dive provides you with a clearer understanding of these two prominent databases, empowering you to make informed architectural decisions in real-world applications or ace your next system design interview.

Understanding File Descriptors: The Silent Resource That Can Crash Your Linux Server

Harry Phung — Sat, 07 Mar 2026 15:25:27 +0000

In the world of Linux system administration and backend development, we often obsess over CPU usage and RAM consumption. But there is a silent resource that, when exhausted, will bring your entire service to a standstill while your monitoring dashboards still show "plenty of memory": The File Descriptor.

If you've ever seen the error EMFILE: too many open files, you've already met the limit. But what exactly is happening under the hood?

Everything is a File

One of the core philosophies of Linux is that "Everything is a file."

This doesn't just mean your text files or logs. In Linux, the following are all represented as files:

Network Sockets : Every TCP connection, Redis query, or database session.
WebSockets : Every real-time connection from your users.
Pipes : Internal communication channels between processes.
Event Listeners : Character devices and more.

A File Descriptor (FD) is simply a non-negative integer that the kernel uses as a handle to access these resources. When your program opens a file or a socket, the kernel gives it an FD. When you're done, you're supposed to close it.

The Breaking Point: Resource Limits

Linux systems have strict limits on how many FDs can be open at once—both for the entire system and for each individual process.

System-wide limits : Controlled by the kernel (visible via /proc/sys/fs/file-nr).
Process-level limits : Often defaults to 1024 on many systems (check via ulimit -n).

When a process hits its limit, it can no longer open anything new. It can't accept new users, it can't talk to the database, and it can't even write a log entry. The service becomes a "zombie"—alive but completely unresponsive.

The Anatomy of an FD Leak

A leak happens when your code opens a resource but fails to close it correctly. Common scenarios include:

Forgotten defer close(): In Go or other languages, skipping the cleanup of HTTP response bodies or file handles.
Broken Connection Pools : Database or Redis clients that don't return connections to the pool, creating a new identity with every request.
Zombie WebSockets : Connections that are half-open from the client side but never timed out or closed by the server.

Manual debugging usually involves digging into /proc/<pid>/fd/ or running complex lsof commands. But in a high-traffic production environment, you need to catch these before they hit the limit.

Best Practices for Managing File Descriptors

To keep your system healthy, follow these industry best practices:

Always close resources : Ensure every file, socket, or stream is closed as soon as it is no longer needed. In Go, use defer resource.Close(); in Java, use try-with-resources.
Avoid unnecessary FDs : Don't open files in "write" mode if you only need to read. Use the most restrictive mode possible.
Process in batches : If you are handling thousands of files, process them in smaller batches rather than opening them all at once.
Use Connection Pools : For databases and Redis, always use a connection pool or a framework that manages the lifecycle of FDs for you.
Monitor proactively : Regularly check your program's resource usage to ensure it isn't slowly climbing over time.
Know your limits : If your application legitimately needs to handle massive amounts of concurrent traffic (like a high-scale WebSocket server), ensure you increase the ulimit for that specific process.

Automating FD Awareness

Detecting these leaks in real-time is the key to stability. Instead of waiting for a crash at 3 AM, you should monitor the growth rate of your FDs. If a process starts with 50 FDs and grows to 500 over an hour without shedding them, you have a leak.

I've developed a tool called FD Detector to handle this automation. It discovers processes in Docker, Kubernetes, and PM2, analyzes their FD categories (sockets vs. files), and alerts you via Telegram before the threshold is reached.

Deep Dive into FD Monitoring

If you want to understand how to track these resources automatically and protect your infrastructure, I've open-sourced the tool and documented the architecture on GitHub.

The package provides deep insights into your process health, including growth rate tracking and leak category analysis.

Learn more about FD monitoring on GitHub : https://github.com/qpv-systems/fd-detector

Understanding the low-level resources of your OS is the first step toward building truly resilient systems.

Efficient Transaction Management in NestJS with AsyncLocalStorage

Harry Phung — Mon, 26 Jan 2026 09:16:17 +0000

In complex NestJS applications using TypeORM, managing database transactions across multiple service layers often leads to significant challenges. The main struggle is ensuring data integrity without cluttering your codebase with repetitive transaction logic.

This post introduces a solution using AsyncLocalStorage (ALS) to transparently manage transaction contexts, effectively resolving common pitfalls.

Source Code : GitHub | Package : npm

The Challenge

Developers typically face three major problems when handling transactions:

1. Data Inconsistency (No Transaction)

By default, TypeORM operations run in their own auto-commit transactions. If a business process involves multiple steps (e.g., creating an order then deducting inventory) and the last step fails, the previous steps are not rolled back. This leaves the database in an inconsistent state, such as an order existing without the corresponding stock deduction.

Example

async createOrder(data) {
  // 1. Order created successfully
  const order = await this.orderRepo.save(data);

  // 2. Inventory deduction FAILS (e.g., db connection lost)
  // CRITICAL: The order persists, but stock was not deducted!
  await this.inventoryService.deductStock(data.items); 
}

2. Complex Manual Management

To ensure data consistency, developers often resort to manual transaction management. This requires writing repetitive code to handle connections, start transactions, commit changes, and — crucially — handle errors with rollback mechanisms.

Example: Repetitive Transaction Code

async createOrder(data: CreateOrderDto) {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    const order = await queryRunner.manager.save(Order, data);
    // ... more operations
    await queryRunner.commitTransaction();
  } catch (err) {
    await queryRunner.rollbackTransaction();
    throw err;
  } finally {
    await queryRunner.release(); // Easy to forget!
  }
}

3. Service Integration Complexity (Prop-Drilling)

This is often the most intrusive issue. When multiple services need to work together within a single atomic operation (e.g., OrderService calls InventoryService), they must share the same transaction instance. This forces you to pass the QueryRunner object as a parameter to every method in the chain.

Example: Burden of Passing Parameters

// inventory.service.ts
// You must add 'queryRunner' to every method signature
async deductStock(items: Item[], queryRunner?: QueryRunner) {
    // You must check if a transaction exists
    const manager = queryRunner ? queryRunner.manager : this.defaultManager;

    // If you call another internal method, you must pass it again
    await this.updateLog(items, queryRunner);

    return manager.save(Inventory, ...);
}

Repository Method Updates:

If you use a Base Repository, you must update every method (save, update, delete) to accept an optional QueryRunner, making your code verbose and harder to maintain.

The Solution: Async Local Storage (ALS)

The nest-als-transaction library leverages Node.js AsyncLocalStorage to store the transaction context implicitly during the request lifecycle. This is similar to ThreadLocal in Java, allowing context access anywhere in the execution chain without explicit parameter passing.

Key Benefits

Automatic Context Propagation : The active QueryRunner is stored globally for the current request. Repositories can retrieve it automatically.
Intelligent Nesting : If you call executeInTransaction within an existing transaction, the library automatically creates a SAVEPOINT instead of opening a new connection. This prevents deadlocks and connection pool exhaustion.
Clean Architecture (Zero Prop-Drilling): Services and repositories do not need to know about the transaction context via arguments. They simply focus on business logic.

Integration Guide

To fully leverage this library and remove QueryRunner parameters from your code, you can integrate TransactionHelper directly into your Base Repository.

1. Setup Base Repository

Inject TransactionHelper and use it to retrieve the active QueryRunner.

// src/cores/base/base.repository.ts
import { Injectable } from '@nestjs/common';
import { Repository, DeepPartial, UpdateResult } from 'typeorm';
import { TransactionHelper } from 'nest-als-transaction';

@Injectable()
export class BaseRepository<T> {
  constructor(
    private readonly baseRepo: Repository<T>,
    private readonly txHelper: TransactionHelper
  ) {}

  /**
   * Retrieves the active EntityManager from ALS (if in a transaction)
   * or falls back to the default managers.
   */
  protected getManager() {
    const queryRunner = this.txHelper.getQueryRunner();
    return queryRunner ? queryRunner.manager : this.baseRepo.manager;
  }

  async save(entity: DeepPartial<T>): Promise<T> {
    return this.getManager().save(this.baseRepo.target, entity);
  }

  // Implement other methods utilizing getManager()...
}

2. Usage in Services

Once the repository is set up, your services become clean and focused purely on business logic.

// src/services/order.service.ts
@Injectable()
export class OrderService {
  constructor(
    private readonly txHelper: TransactionHelper,
    private readonly orderRepo: OrderRepository,
    private readonly inventoryService: InventoryService
  ) {}

  async createOrder(data: CreateOrderDto) {
    // Start a transaction scope
    return this.txHelper.executeInTransaction(async () => {

      // 1. Create Order
      // The repo automatically picks up the transaction from ALS
      const order = await this.orderRepo.save(data);

      // 2. Call other service
      // No need to pass 'queryRunner' manually.
      // The InventoryService internally uses repositories that also pick up the context.
      await this.inventoryService.deductStock(data.items);

      return order;
    });
  }
}

Installation & Setup

Install the library via npm:

npm install nest-als-transaction

Import TransactionModule in your AppModule (it is global). Ensure you have TypeOrmModule configured with a DataSource available for injection.

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { TransactionModule } from 'nest-als-transaction';

@Module({
  imports: [
    TypeOrmModule.forRoot({ ... }),
    TransactionModule,
  ],
})
export class AppModule {}

Usage Options

Option A: Fully Integrated (Recommended)

Use the Base Repository integration shown above. This keeps your code cleanest by hiding the transaction logic inside the repository layer.

Option B: Manual Access

If you do not wish to modify your Base Repository, you can still access the QueryRunner manually inside the transaction block. This is useful for incremental adoption.

this.txHelper.executeInTransaction(async (qr) => {
    // Pass 'qr' manually to legacy code that requires it
    await this.legacyService.doSomething(qr);
});

Conclusion

Using AsyncLocalStorage for transaction management in NestJS eliminates the noise of manual transaction handling. It provides a robust, clean, and scalable way to ensure data consistency across complex service interactions.

PostgreSQL Safety Lock: Double-Confirm Protection

Harry Phung — Fri, 23 Jan 2026 07:47:13 +0000

It starts like any other day in the development environment. A developer in your team is debugging a tricky issue, SQL client open. They write a quick DELETE FROM orders WHERE id = 123 to clean up some test data.

They highlight the query. They hit Execute.

But their hands were too fast, and they missed the WHERE clause. :]]]

Suddenly, DELETE 15,000 rows flashes on the screen.

In production, strict permissions and backups often save us. But in shared environments like Dev, Staging, or UAT , access is often more open to facilitate speed. A simple "muscle memory" slip by one person can inadvertently wipe out a colleague's test data or corrupt the state of a feature being demoed to a client.

We needed a way to protect the team from these moments of haste without slowing down our workflow.

1. The Solution: A Database Safety Lock

I built a lightweight, pure-SQL solution for PostgreSQL that introduces a Double-Confirmation step for destructive operations.

It works like a safety switch on a power tool. If you try to run an UPDATE or DELETE:

First Attempt: 🛑 BLOCKED. The database triggers an exception:
Second Attempt: ✅ SUCCESS. If you immediately re-run the exact same query (same SQL, same session), the lock unlocks and the query executes.

This simple friction point forces you to pause and think: "Wait, did I mean to run that?"

2. How It Works (Under the Hood)

This isn't a client-side plugin. It's a robust Server-Side Trigger (BEFORE UPDATE OR DELETE) that runs right inside PostgreSQL.

Key features:

Zero Dependencies: Just plain PL/pgSQL.
Session-Aware: It hashes your query (text) combined with your Session ID (pid). You can only confirm your own queries.
Smart Bypass: Backend services (like your API server or background workers) are automatically whitelisted via application_name, so automated tests and app logic aren't blocked.
Concurrency Safe: Multiple developers can use the system continuously without blocking each other.

3. Get the Code

The full source code, installation guide, and technical details are available on my GitHub. It's a single SQL script you can drop into any non-production database.

👉 Get PostgreSQL Safety Lock on GitHub

Give it a try and let me know if it saves your dev data (or your sanity)!

DEV Community: Harry Phung

System design: Bloom Filters & Use Cases

1. Overview: Reject Nonexistent IDs Before Reading Data

2. Requests for Missing Data Still Consume Resources

3. How Attackers Exploit Cache Misses to Overload the Database

4. A Redis Cache Miss Does Not Mean the User Does Not Exist

5. Load Examples: How Many Database Queries Can You Avoid?

5.1. One Million Requests, Half for Nonexistent Data

5.2. An Example in Requests per Second

6. Illustrated Use Cases

6.1. Profile APIs with Millions of Users

6.2. Resource Lookups: Posts, Products, and Files

6.3. Checking Username Availability During Registration

6.4. Checking Previously Crawled URLs or Processed Events

7. Considerations Before Production

7.1. Synchronizing New Users

7.2. Initial Loading and Rebuilding

7.3. Deleting Users

7.4. Negative Caching and Rate Limiting

7.5. Measuring Effectiveness

8. When Should You Use a Bloom Filter?

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

1. The Core Conflict: Repeatable Read vs. Non-Blocking Traversal

2. MySQL (InnoDB): Gap Locks and Deadlock Cycles

2.1. What is a Gap Lock?

2.2. The Conflict with SKIP LOCKED

2.3. The Resolution: Switching to READ COMMITTED

3. PostgreSQL: Snapshot Isolation and Serialization Failures

3.1. How Snapshot Isolation Works in PostgreSQL

3.2. Why REPEATABLE READ Aborts Concurrent Queries

3.3. The Resolution: READ COMMITTED and EvalPlanQual

4. Summary Matrix: MySQL vs. PostgreSQL

5. Production Configuration Rules

6. Conclusion

Common Concurrency Interview Question: Handling High-Traffic Purchases and Key Approaches

1. Approach 1: DB Constraint (CHECK > 0)

2. Approach 2: Row-Level Lock (FOR UPDATE)

3. Approach 3: Reservation Pool & SKIP LOCKED

3.1. Workflow & Architecture

3.2. Implementation

4. Isolation Level: Why READ COMMITTED

5. Summary Comparison

6. Conclusion

Realtime Config Updates for Multiple Instances with Redis Pub/Sub

Common Ways to Handle Dynamic Configuration

Another Approach: Keep Config in Memory and Use Redis Pub/Sub for Realtime Updates

Example with NestJS

1. Global Redis Publisher and Listener

2. API for Updating Notification Configuration

3. NotificationService Keeps Config in Memory

An Important Note Before Going to Production

Conclusion

The Auto-Increment Trap: Scaling Systems and the Distributed ID Problem

1. The "Disaster" Scenario: When Slave is Promoted to Master

2. The "Step k" Solution: A Temporary but Risky Lifesaver

MySQL Configuration Example:

3. Auto-increment and the Pain of "Sharding"

4. More Suitable Alternatives

Conclusion

Big O: Don't talk about speed!

1. The Deception of Hidden Constants

2. The Crossover Point: Where Theory Meets Reality

3. Hardware Reality: The Critical Cache Factor

4. Real-World Data & The Trap of Micro-Optimization

5. Final Conclusion: Developing a Pragmatic Perspective

Distributed Locking & The 'Old Overwrites New' Problem (Fencing Tokens)

The Problem: The "Awakened" Node and Data Overwrites

Case 1: Node A pauses for too long

Illustration: Case 1 (Data Overwrite due to Pause)

Case 2: Intersection Time Conflict (Race Condition)

Illustration: Case 2 (Intersection Conflict)

The Solution: Fencing Tokens

Solution Illustration (Fencing Token)

The Big Takeaway

How CPU Cores, Processes, and Threads Actually Work

1. Basic Overview: CPU, Core, Process, and Thread

2. CPU Processing Mechanism and Time Slicing

2.1. True Parallelism

2.2. Time Slicing and Context Switch

3. The Nature of Time Slicing and Trade-offs

3. `NotificationService` Keeps Config in Memory