Database Scaling and Indexing

• Scaling databases becomes essential to maintain smooth operations and ensure a good user experience as applications grow, handling more data and serving more users

Situations That Require Database Scaling

• A startup experiencing viral growth needs database scaling to manage millions of requests and maintain app stability
• E-commerce platforms, such as Amazon, require a scalable database to smoothly handle peak loads during events like holiday sales

Indexing

• Indexes help locate specific information quickly without scanning every page, similar to an index in a book
• Indexing allows customer service representatives to quickly pull up order histories based on order ID or customer ID in an online retail customer database
• B-tree indexes keep data sorted, which is ideal for a wide range of queries and allows for fast insertion, deletion, and lookup operations
• B-tree indexes are effective for range queries, such as finding orders within a specific date range or retrieving customer records alphabetically by last name
• Indexes reduce query execution time, preventing simple search queries from turning into full table scans
• While indexes improve read performance, they can slow down write operations because the index needs updating when data is modified

Materialized Views

• Materialized views are pre-computed snapshots of data stored for faster access and are useful for complex queries
• Materialized views in business intelligence platforms, such as Tableau, store pre-computed sales data, enabling quick and efficient generation of daily sales reports
• Materialized views improve performance by reducing the computational load on databases
• Materialized views must be refreshed periodically to ensure data remains up-to-date; this operation can be resource-intensive

Denormalization

• Denormalization involves storing redundant data to reduce database query complexity, increasing retrieval speed
• Social media platforms like Facebook denormalize data to store user posts and information in the same table, minimizing the need for complex joins
• Denormalization enhances read performance by simplifying query execution
• Storing redundant data requires careful management and updates to maintain consistency across the database

Vertical Scaling

• Vertical scaling involves adding more resources—CPU, RAM, or storage—to an existing database server to handle increased load
• An online marketplace experiencing rapid growth upgrades its database server with more powerful CPUs, increased RAM, and expanded storage to process more transactions quickly
• Vertical scaling is often the first step because it's straightforward and requires no changes to the application architecture
• There are limits to vertical scaling: you can reach the maximum hardware capacity, and costs of further upgrades become prohibitive
• Vertical scaling doesn't address redundancy; a single server failure can still bring down the database

Caching

• Caching involves storing frequently accessed data in a faster storage layer to reduce database load and speed up response times
• Online streaming services, such as Netflix, retrieve movie metadata from a cache rather than querying the database each time a user browses movie titles
• Caching can be implemented at various levels, such as in-memory caches using tools like Redis or Memcached, or at the application level with built-in mechanisms
• A major challenge with caching is cache invalidation, which ensures the cache remains up-to-date with the most recent data
• Strategies for refreshing caches include time-based expiration or event-driven updates

Replication

• Replication involves creating copies of a primary database on different servers to improve availability, distribute load, and enhance fault tolerance
• Synchronous replication copies data to replica servers simultaneously, ensuring immediate consistency, but can introduce latency because the primary server waits for all replicas to confirm the write operation
• Asynchronous replication doesn't wait for replicas to confirm the write, which improves performace but may lead to temporary inconsistencies
• Replication increases storage, maintenance overhead, and complexity in maintaining data consistency in distributed systems

Sharding

• Sharding is a database architecture that splits a large database into smaller pieces called shards
• Instagram shards its database by user ID, meaning each user's data is stored on a specific shard to distribute workload across multiple servers
• Performance and reliability are improved with sharding
• Sharding is effective for scaling databases horizontally by adding more servers to distribute the load
• Correctly deciding on the sharding key is crucial for an even distribution of data and workload across shards
• Querying across multiple shards can be complex and requires changes to an application's query logic
• Re-sharding, which involves redistributing data when shards become imbalanced, can be challenging and resource-intensive