Spring Boot Interview Question - Scaling User Search Endpoint
Search at scale, concepts, example and coding ...
Scenario
You’re working at a social media platform with 50 million users.
The product team wants a feature allowing users to search for others by username.
You currently expose a simple REST endpoint backed by PostgreSQL.
Initial Implementation :
The Problem
After launch, production metrics show:
10,000 requests per second during peak hours
Database CPU consistently above 85%
Average latency ~800ms (target: <100ms)
40% of searches are for usernames that do not exist
Many searches repeat the same popular usernames
You are asked to scale this endpoint for production traffic.
What immediate database optimizations would you implement?
The biggest issue in the current query is this:
WHERE username ILIKE '%john%'The leading wildcard (%john%) prevents PostgreSQL from using a normal index, forcing a full table scan — which explains the high CPU and latency.
If we must support ILIKE substring search, the best immediate fix is a trigram index:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_username_trgm
ON users
USING GIN (username gin_trgm_ops);Why this works
Trigram indexes break strings into 3-character chunks
They allow PostgreSQL to index
LIKE,ILIKE, and fuzzy searchesThey drastically reduce scan time for substring queries
This alone can reduce query latency from hundreds of ms to single digits.
We should add pagination. Returning unlimited results is dangerous at scale.
LIMIT 20This protects memory, network, and DB CPU.
📢 Get actionable Java/Spring Boot insights every week — from practical code tips to real-world use-case based interview questions.
Join 6200+ subscribers and level up your Spring & backend skills with hand-crafted content — no fluff.
🎯 Early Supporter Offer
The first 100 paid subscribers get the annual membership at $50/year.
👉 71 already joined — only 29 spots left.
Not convinced? Check out the details of the past work
The product team reveals most searches are prefix-based. What changes we can make?
Prefix search (john%) is fundamentally different from substring search (%john%).
With prefix search:
WHERE username LIKE ‘john%’PostgreSQL can use a B-tree index, because the query can seek directly to the start of the matching range.
With substring search:
WHERE username LIKE ‘%john%’the database must scan most of the table, because it doesn’t know where matches begin.
So the indexing strategy shifts from specialized text search indexes to a simple ordered index.
The immediate solution is:
CREATE INDEX idx_users_username ON users(username);This allows the database to:
Seek directly to the first matching username
Scan only the relevant range
Stop early when results exceed the limit
This dramatically reduces CPU and latency.
From observability, we discover 40% of requests search for usernames that don’t exist, still triggering DB queries.
This is very interesting fact. This tell us that we should use bloom filter for searches.
A Bloom filter is a probabilistic data structure that tests whether an element is possibly in a set or definitely not in a set.
Internally, it uses a bit array and several hash functions.
When we add a username, each hash function sets bits in the array.
When we check a username:
If any corresponding bit is
0→ username definitely does not exist.If all bits are
1→ username may exist (possible false positive).
Key property:
No false negatives (never misses an existing username).
False positives possible (might think a non-existent username exists).
