How to Check Username Availability with 1 Billion Users: Scalable System Design

Imagine you are building an application with 1 billion users. Whenever someone registers, you need to check whether their username already exists. At a small scale, this is a simple database query. But at a massive scale, performance, database load, concurrency, and consistency become important challenges.

In this article, we will explore a real-world scenario and solve it step by step using MySQL indexing, Redis, Bloom Filters, and database sharding.

1. Understanding the Problem

Suppose we are developing a social media application called ConnectHub. Initially, it has 10,000 users. Later, it grows to 1 billion registered users.

When a user enters himanshu123, the application must check whether the username is already taken.

• Username must be unique.
• Availability checks should be fast.
• Thousands of registration requests may arrive simultaneously.
• The database should not be overloaded with unnecessary queries.

2. Solution One: MySQL Indexing

Our first implementation uses MySQL with a unique index on the username column.

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT id
FROM users
WHERE username = 'himanshu123'
LIMIT 1;

A unique index allows MySQL to locate a matching username efficiently and prevents duplicate values from being inserted.

What Problem Did We Encounter?

Without a suitable index, MySQL may need to scan many rows. This can become expensive as the dataset grows.

With a B-tree index, MySQL can navigate the index to find the matching username instead of scanning the entire table in typical indexed lookups.

3. Solution Two: Redis Caching

As traffic increases, thousands of requests may repeatedly check the same usernames. Even indexed queries consume database resources.

Redis can cache frequently requested username availability results and reduce repeated database lookups.

use Illuminate\Support\Facades\Cache;
use App\Models\User;

$username = strtolower(trim($request->username));
$key = 'username:' . $username;

$exists = Cache::remember(
    $key,
    300,
    function () use ($username) {
        return User::where('username', $username)
            ->exists();
    }
);

return response()->json([
    'exists' => $exists,
    'available' => !$exists
]);

Redis can make repeated checks faster, but cached results may become stale. The database must remain the final authority during registration.

4. Solution Three: Bloom Filter

A Bloom Filter is a memory-efficient data structure that helps determine whether an item might exist in a set.

• Definitely absent: The username is not in the filter.
• Possibly present: The username might exist and should be checked against the database.

A standard Bloom Filter can return false positives, but it does not return false negatives for correctly inserted items when the filter is properly maintained.

Python Example

from pybloom_live import BloomFilter

users = BloomFilter(
    capacity=1000000,
    error_rate=0.001
)

users.add("himanshu123")
users.add("john_doe")

username = "himanshu123"

if username in users:
    print("Might exist - check database")
else:
    print("Definitely absent from filter")

Install the library using:

pip install pybloom-live

In production, the filter must be populated from real user data and reliably updated after new registrations. It is a preliminary check, not a replacement for the database.

5. Solution Four: Database Sharding

When a single database server no longer meets the application’s storage, throughput, or availability requirements, database sharding may help.

Sharding distributes records across multiple database servers. For username lookup, a normalized username can be hashed to select its shard.

import hashlib

def get_shard(username, total_shards):
    normalized = username.strip().lower()

    hash_value = int(
        hashlib.sha256(normalized.encode()).hexdigest(),
        16
    )

    return hash_value % total_shards

shard_number = get_shard("himanshu123", 4)
print(shard_number)

The application routes each username to its assigned shard. Each shard has its own unique index for the usernames it owns.

6. Preventing Duplicate Usernames

Imagine two users try to register the same username at nearly the same time. Both might see the username as available before either request inserts it.

This is called a race condition. An initial availability check cannot prevent it.

try {
    User::create([
        'username' => strtolower(trim($request->username)),
        'email' => $request->email
    ]);

    return response()->json([
        'success' => true,
        'message' => 'Registration successful'
    ], 201);

} catch (QueryException $e) {
    // Handle a confirmed duplicate-key violation
    throw $e;
}

In a real application, handle the specific database duplicate-key error and return an appropriate response, such as HTTP 409 Conflict. Do not treat every database exception as a duplicate username.

7. Complete Scalable Architecture

We can combine these technologies into one possible architecture:

       User Registration
               |
               v
           Laravel API
               |
               v
       Normalize Username
               |
               v
          Bloom Filter
               |
       +-------+-------+
       |               |
       v               v
 Definitely Absent  Possibly Present
       |               |
       |               v
       |          Redis Cache
       |               |
       |          Cache Miss
       |               |
       |               v
       |          Shard Router
       |               |
       |               v
       |          MySQL Shard
       |               |
       +-------+-------+
               |
               v
       Availability Result
               |
               v
      Unique DB Constraint
               |
               v
       Registration Success

This is an illustrative design. The Bloom Filter, Redis, and sharding layers should be introduced only when actual workload measurements justify them. Regardless of the availability path, the database’s unique constraint must protect registration.

8. Technology Comparison

9. Conclusion

Checking username availability in an application with 1 billion users is not just about writing a SQL query. It requires careful consideration of performance, concurrency, consistency, and infrastructure.

Start with a MySQL unique index, measure the workload, and introduce Redis, Bloom Filters, and sharding only when there is a clear need. Always keep the authoritative database responsible for enforcing username uniqueness.

Frequently Asked Questions

Can MySQL handle 1 billion users?

MySQL can support very large datasets depending on hardware, workload, indexing, and architecture. Some applications may require sharding, while others can operate efficiently on a well-designed database server.

Can Bloom Filters confirm that a username exists?

No. A positive result means the username might exist. A database lookup is needed to confirm it.

Is Redis necessary?

No. Redis is useful when caching repeated requests provides a measurable performance benefit.

How do applications prevent duplicate usernames?

They enforce a unique database constraint and handle duplicate-key errors during registration.