Why do you need to scale?

• accommodates for growth: increased data volume and user interactions
• maintains performance: sustain swift and reliable data retrieval and interaction for good user experience
• uphold data integrity: preserves accuracy and consistency under high demand to prevent data anomalies.

How do you know when you need to scale?

• assess database performance: regularly monitor database response times and query execution speeds
• analyze data growth trends: scrutinize increments in data volume and the complexity of data integration
• scale proactively: implement scaling solutions ahead of reaching capacity to avoid performance degradation.

Scaling strategies

Vertical

Vertical scaling is the enhancing the capacity of individual resources E.g., upgrading the database server.

This should be your first solution.

Cons:

• limited by hardware capability, there is a limit to this strategy.
• potential single point of failure

SQL Databases typically employ vertical scaling. However, all scaling strategies are in fact applicable to both SQL and NoSQL databases (it's just more common).

Horizontal

(subcategories: replication, sharding, partitioning) Horizontal scaling is adding more units to the system architecture. E.g., adding new servers to a database cluster.

Cons:

• increased system complexity
• data consistency is challenging

NoSQL typically employ horizontal strategies.

Replication

Replication is a method of copying data to maintain synchronized multiple copies across different nodes and servers. It provides the following:

• enhances data availability: provides continuous data availability, reducing downtime
• facilitates load balancing: distributes read and write operations across multiple nodes to optimize performances.
• serves as a backup: acts as a safeguard to recover data in case of node failures

Replication patterns are ways to synchronize and maintain consistent data copies across multiple modes to enhance availability and read scalability.

• single primary: one node (primary) handles the writes; replicas are read only. This guarantees strong data consistency but can limit write scalability due to potential write bottlenecks
• multi primary: all nodes can independently handle writes and reads. Improves write scalability but requires sophisticated conflict-resolution mechanisms to maintain data consistency.

Replication is good for scaling reads, but not good for writes. There's normally one main database, and the replicas update based off of this main one. This is eventual consistency. This also means that there's one point of failure, but if the primary does go down, then the replica takes over as primary.

Example of replication: online bookstore with Books table containing columns BookID, Title, Author, Price

• replicate the table to multiple servers. If one server goes down, the application can continue to operate using one of the replicated servers.
• in single primary configuration, the replicas must synchronize with the primary to get the updated data.
• in the event of a primary server failure, one of the replicas can be utilized for backup and recovery purposes.

When do you employ database replication?

• high data availability is required: priority is to have continuous access to data
• read-heavy workloads: when the server predominantly handles read operations
• distributed workload: when there is a need to balance loads among several nodes.
• backup mechanism needed: having a robust recovery system is crucial to mitigate the risks of node failures.

Sharding

Database shading involves segregating a large dataset into smaller, manageable parts, called shards, and distributing them across multiple databases or node.

• enhances performance: faster queries and parallel processing because of reduced database size
• scales systems with extensive data requirements

For example, store data about local geographical region in their own geographic database.

There are different sharding types that dictate how data is distributed across nodes, affecting system balance and complexity.

• key-based: use specific key values, like user_id, for data distribution. This strategy is straightforward, but can lead to unbalanced servers.
• range-based: allocates data based on ranges of a key's value, such as dates. Idea for sequential data but prone to hotspots
• directory-based: employs a lookup service to direct queries to the right shard. Offers flexibility but adds complexity.

When do you consider database sharding?

• single server limitations: dataset is too large to be handled by a single server
• horizontal scaling is essential: when adding more machines to distribute the load is crucial for system performance
• geographical data proximity is crucial: reduce latency by positioning data closer to users
• parallel processing is essential: when distributing data for simultaneous processing.

Sharding vs replication.

• Sharding involves distributing data across multiple databases or servers for scalability and geographical data distribution. Choose this when the dataset is extensive and a single server is inadequate, or when geographical proximity is crucial.
• Replication entails maintaining multiple copies of the same data to ensure high availability, reliability, and load balancing. Choose when high data availability and reliability are crucial and when there's a need to balance the load among multiple nodes.

Partitioning

Database partitioning is segmenting a table into smaller, more manageable servers, yet all pieces remain part of the same logical table. (data remains in a single database) The benefits are:

• improves performance: enables efficient management and faster queries
• internal management: occurs within the same database system, optimizing internal structure.
• more maintainable

The types of partitioning:

• Horizontally: divide rows into smaller tables with identical columns. This is for optimizing performance via parallel processing but challenges data consistency across nodes.
• Vertically: segment columns, creating smaller tables with identical rows. This streamlines data retrieval but necessitates meticulous data consistency management.

When to consider database partitioning?

• table size affects performance: when a single table is too large, leading to bad performance
• frequent queries on recent data: when the table contains substantial historical data that is seldom accessed
• optimal node efficiency: aim to enhance the performance and manageability of each database node
• seek logical data segmentation: when data should be internally segmented yet part of the same logical entity.

Partitioning vs sharding:

• choose partitioning when dealing with large tables within a single server and aiming for enhanced manageability and performance
• choose sharding: handle extremely large datasets that cannot be accommodated by a single server or when geographic proximity of data is pivotal.

Caching

Database caching involves storing copies of files or data results in strategic locations to improve access times and reduce load on the main database. Benefits are as follows:

• enhances performance: enable faster data retrieval and reduces the need to query the main database repeatedly
• reduces database load: offloads the main database by serving the majority of read requests, prolonging its lifespan and efficiency.

Some common caching strategies:

• least recently used: discards the least recently accessed item first
• first in first out: discard the oldest cache item first
• least frequently used: discard the least often accessed items first.

Caching systems offer optimized speed and efficiency for frequently accessed transient data. Some common caching choices:

• Redis: versatile data structure server allowing for a variety of key-value pair types. Suitable for scenarios where diverse data structures like strings, hashes, lists, etc., are involved and high availability and persistence are crucial
• Memcached: general-purpose distributed memory caching system, ideal for dynamic, database-driven websites needing fast data and object caching in RAM to minimize external data source reads.

When to consider database caching:

• rapid data retrieval is essential: significantly improve data access times compared to querying the main database
• minimizing database load is crucial: maintain optimal performance and longevity of the main database by reducing the number of queries made to it.
• operational cost efficiency is a goal: must optimize database operations to reduce resource usage and operational costs are essential.

Database optimization

The focus is on enhancing database performance and reliability. Its essential for maintaining optimal operation speed and reliability as the system scales.

Strategies:

• proper indexing
• normalization/denormalization
• query optimization

Query optimization involves constructing efficient queries to reduce database load

• detailed analysis and refinement of queries yield significant performance improvements
• effective optimization leverages database features and avoids unnecessary, resource-intensive operations.

The N+1 Query Problem: when you make one query to fetch the main records and N additional queries to retrieve related data for each main record.

• Solution: optimize your query use joins.

Load Balancing

Load balancing is the process of distributing network or application traffic across multiple servers to ensure that no single server bears too much demand. This enhances performance (no single server becomes a bottleneck) and increases reliability and availability (redistributes load in case of a server failure).

Types of load balancing include:

• round robin: cyclically assign requests to a list of servers. (simple but doesn't consider server load)
• least connections: direct traffic to the server with the fewest active connection (does not account for request complexity)
• IP hash: assign a specific IP range to each server (may lead to server overload)

When to consider load balancing:

• need high availability: uninterrupted service is paramount
• handle high traffic
• optimize resource use: aim to use resources efficiently and prevents overload
• scale horizontally: add servers to enhance system capacity and performance.

Where does the load balancer go?

• between user and web server: distribute incoming network traffic across multiple web servers
• between web server and internal platform layer: handle traffic between different service layers within the architecture
• between internal platform layer and database: it can balance loads and manage requests coming to the database

Database backup

Database backup creates a secure, static, point-in-time copies of data. Allows recovery to a good state in the case of data loss, corruption, or a disaster. Backups are crucial because they safeguard data integrity and availability, preventing irreversible data loss.

Types of backup

• full: all data in the entire database. Comprehensive coverage but requires more storage and time
• Incremental: only backup the data that has changed. Faster and requires less storage, but relies on chain of previous backup
• differential backup: backs up all the data that has changed since the last full backup. Balance between full and incremental.

Backup vs Replication:

• backup is static and recoverable vs replication is dynamic, real-time, or near real time to
• backup is for protecting against data loss and corruption and replication is for enhancing availability and load balancing
• distinct purposes: replication increase availability but does not replace the need for a dedicated backup strategy.