Scaling Read

Read scale need to follow a natural progression, simple optimisation to complex distributed system

1. Optimise read performance within your database
2. Scale your database horizontally
3. Add external caching layers

Optimise within the database

Indexing

See: Database indexing, Database index (Secondary Key)

Without indexing we will do a full table scan, with indexing, we will jump to the exact base

[!danger]
Some resources will says too many index could cause write slowdown. While this is true, with modern hardware and database engine, they design to handle index very well.

Hardware upgrade

Swapping HDD to SSD can give you 10-100x faster random I/O. Adding more RAM means more dataset sits in memory instead of disk.

This often the fastest way to buy breathing room

Denormalisation

See: Database Denormalisation

Database is often normalised Database Normalisation to save space, however query is more complex because you have to bring different joins data together.

Join will be expensive if the database is read-heavy system. In this case we can denormalise the data, store dedundant to trade storage for speed

[!danger]
Doing this may make it faster however write would be more complex, you will need to adjust in multiple places. We trading storage, write complexity for read speed

Materialized view

In postgres we can precompute expensive aggregations. Instead of computing average product ratings on every page load, we compute them once via a background process and store the result

This is powerful for analytics queries that involve complex calculations across large datasets

-- Instead of this expensive query on every page load:
SELECT p.id, AVG(r.rating) as avg_rating 
FROM products p 
JOIN reviews r ON p.id = r.product_id 
GROUP BY p.id;

-- Precompute and store the average:
CREATE MATERIALIZED VIEW product_ratings AS
SELECT p.id, AVG(r.rating) as avg_rating
FROM products p 
JOIN reviews r ON p.id = r.product_id 
GROUP BY p.id;

We can refresh by calling REFRESH MATERIALIZED VIEW product_ratings

Scale Horizontally

Rule of thumbs. If we have 50k - 100k read requests per second even with proper indexing, we need to either add a cache or scale horizontally

Read replicas

See: Read replica pattern

All write go to 1 primary, read go to replica, this distrubutes the read across multiple servers. Read replica also provide redundancy, you can promote a replica to be new primary, minimise the down time.

• There are framework that support to do this out of the box like PgBouncer, ProxySQL, AWS RDS Proxy, or we can manually do this in the application

Replication there are 2 ways

1. Synchronise replication: Primary wait for "all" replicas to be finished before saying write
   • We also have an option of only wait for 1 or a QUORUM of replicas before saying the write is good.
   • If we dont receive enough acknowledgement from the needed replica, we will not commit the change and return back to the client we fail to write
2. Asynchronise repliocation: Faster. We dont require any replica ack at all but instead the change will be delivered asynchronously
   • This is faster but might introduce some data inconsistency on read

Database sharding

Read replica wont reduce the size each database need to be handle. If the dataset beomes so large, we need to perform database sharding

Sharding help us to

1. Reduce the size of each database → faster read + queries
2. Distribute the load to multiple database

Sharding type

Functional sharding

We shard by the business domain or featuresrather than records.

Geographic sharding

Store US user in US database, European in european database. This makes user get faster read from nearby servers.

[!note]
Sharding adds significant operational complexity and is primary a write scaling technique. It helps with read as well but adding caching layers is more effective and easier to implement

[!note]
Normally the application handle which database it need to talk to, i.e if we do base on business domain, we can route it base on the business domain

Caching

Application-level caching

Invalidation strategy

1. TTL: fixed time for cache entry, potential stale data until expiration
2. Write-through invalidation: update or delete cache entries when writing to the database. Ensure consistency but add some write latency
3. Write-behind invalidation: queue to process async, reduce write latency but introduce a stale window
4. Tagged invalidation: associate cache entry with tags i.e user:123:posts invalidate all entries with specific tag when a related data changes. Good for complex dependency but need to manage tag relationships
5. Version keys: include version number, increment version on update and naturally invalidating old cahce entries. Simple and reliable but require a version tracking

Most production combine approaches - use a short TTLs (5-15 mins) as a safetyness. Critical data can go through write-through invalidation, less critical and depends on short TTLs or write-behind invalidation

CDN & Edge caching

CDN can reduce origin load by 90%. However, this only make sense when data is shared between multiple users.

Common deep dive

"What happens when your queries start taking longer as your dataset grows?"

Add index

-- Before: Full table scan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- Seq Scan on users (cost=0.00..412,000.00 rows=1)

-- Add index
CREATE INDEX idx_users_email ON users(email);

-- After: Index scan  
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- Index Scan using idx_users_email (cost=0.43..8.45 rows=1)

"How do you handle millions of concurrent reads for the same cached data?"

Request Coalescing

Perform Request Coalescing — comebine multiple requests for the same key into a single request

# Request coalescing pattern
class CoalescingCache:
    def __init__(self):
        self.inflight = {}  # key -> Future
    
    async def get(self, key):
        # Check if another request is already fetching this key
        if key in self.inflight:
            return await self.inflight[key]
        
        # No inflight request, we'll fetch it
        future = asyncio.Future()
        self.inflight[key] = future
        
        try:
            value = await fetch_from_backend(key)
            future.set_result(value)
            return value
        finally:
            del self.inflight[key]

This also help with backend as well as it reduce the load for our application

Duplicate the cache

If this doesnt work, duplicate the items into multiple cache entries instead of storing under one key. Since Redis often single threaded per shard, spreading into multiple key will lead to the request shard into multiple shard.

We can just append a random number to the key

feed:taylor-swift:1
feed:taylor-swift:123

Trade off

1. Duplicate data
2. Cache invalidation becomes more complex to clear all copies

However it's worth for read heavy system

"What happens when multiple requests try to rebuild an expired cache entry simultaneously?"

When TTL expire, if multiple request hit at the expire time in the same time, they all will try to rebuild the cache. We need some way to prevent this

Use Distributed lock

If something is building the cache, other request wait for it.

Downside:

• If it cannot rebuild successful, everyone else will get a timeout and fail read.
• The fallback logic might get very complicated

Better: Probabilistic early refresh

When it comes close to the TTL, for example the TTL is 60 mins. At 50mins, a request has 1% chance of rebuilding the cahce, At 55mins, the request have 5% chance and so on, we keep increasing

Most user will still get the cache just some unlucky one will get to trigger refresh

Critical data: Background early refresh

At critical data, we have a background refresh process continously before the expiration, this will ensure it never go stale.

The trade off is infrastructure complexity

"How do you handle cache invalidation when data updates need to be immediately visible?"

Normally a write-through (delete after write) sounds simple however at scale could have some problem

1. What if an invalidation request fail?
2. What if another quest come right in after delete an old one but putting in the new one

Better approach

Versioning

On read:

1. Read the centralied version key to fetch the latest version: event:123:version
2. From the return version, fetch the actual version: event:123:v42
3. Cache miss? fetch from database and write back using the same version key

On write:

1. Write to the db
2. Increment the versioning column of event:123:version
3. Write the new data into event:123:v43 (new version)

The old key never deleted, they just never fetched and eventually stale

Delete item cache

When versioning is not practicle — i.e you're caching a search result, use another approach of maintain a "recently deleted key"

On write:

1. Write to the db
2. Write to event:changed key 123

On read:

1. Check if 123 in event:changed
2. if it is, dont read the cache for this and read directly from db

Background process:

1. Invalidate all the current caching for event:123:* or anything realted to event:123
2. Write the new cache for event:123
3. Remove 123 from event:changed

Rebuild CDN

Using CDN API to help invalidate cache will take time to reflect to different regions. Critical updates we need to use cache headers to prevent CDN caching entirely.

Cache-Control: private, no-store

Less critical data can use shorter TTL at CDN and longer TTL at application cache.