Understanding Read Replicas for Database Scaling

Read replicas are asynchronous copies of a primary database, primarily used for database scaling by offloading read-heavy workloads from the main instance. They function by replicating writes from the primary database, allowing applications to direct read queries to these replicas, thereby improving database performance and reducing the load on the primary. This horizontal scaling approach is particularly effective for applications with a high volume of read operations, as it distributes read traffic across multiple database instances.

Core Concepts of Read Replicas

Read replicas are asynchronous copies of a primary database. Their fundamental role in database scaling is to offload read operations from the primary instance. When an application sends write operations (INSERT, UPDATE, DELETE), these are directed to the primary database. The primary then asynchronously replicates these changes to all connected read replicas. This asynchronous replication means there is a small, unavoidable delay, known as replication lag, before the replicas reflect the absolute latest state of the primary database.

For instance, Amazon RDS allows up to 15 read replicas for MySQL, MariaDB, PostgreSQL, and Oracle, and 5 for SQL Server. Each replica is read-only, meaning applications can query data from them but cannot write to them. This architecture enables horizontal scaling by distributing read-heavy workloads across multiple database instances. While a primary database in a Multi-AZ deployment might have a synchronous standby replica for high availability, this standby cannot serve read traffic, unlike an asynchronous read replica. Tools like Supabase also utilize read replicas for horizontal scaling, especially when the primary database's CPU utilization consistently exceeds 70% due to read volume.

How Read Replicas Function

Read replicas operate as asynchronous copies of a primary database. When an application performs a write operation (INSERT, UPDATE, DELETE), this transaction is directed exclusively to the primary database. The primary then asynchronously transmits these changes to all configured read replicas. For instance, Amazon RDS supports up to 15 read replicas for engines like MySQL, PostgreSQL, and MariaDB, and 5 for SQL Server. Supabase also utilizes this mechanism for database scaling.

This asynchronous replication process means there is an inherent delay, known as replication lag, before the data on a replica fully reflects the primary's state. Each read replica is read-only, preventing any write operations from being directed to it. Applications send their read queries to these replicas via dedicated endpoints. This architecture offloads read-heavy workloads from the primary, improving overall database performance and alleviating potential write bottlenecks on the primary by freeing up its resources. While a Multi-AZ deployment includes a synchronous standby replica for high availability, this standby cannot serve read traffic, differentiating it from an asynchronous read replica.

Strategic Use Cases for Read Replicas

Read replicas are strategically deployed to address specific database scaling challenges, primarily focused on read-heavy workloads and distribution. One key application is horizontal scaling, where read requests are distributed across multiple replicas to enhance database performance and increase throughput. For instance, applications with user profiles, product catalogs, or dashboards that generate significant read volume can offload these queries to replicas, freeing the primary instance to handle write operations.

Another use case involves supporting analytics and reporting queries. Resource-intensive analytical workloads can be directed to dedicated read replicas, preventing them from impacting the performance of the primary database which handles transactional operations. This ensures that operational queries remain responsive.

Furthermore, read replicas facilitate enhanced geographic distribution and high availability. By deploying replicas in different Availability Zones or AWS Regions, organizations can serve read traffic closer to users, reducing latency. In scenarios where the primary DB instance becomes unavailable, read replicas can continue to serve read traffic, although they are not designed for automatic failover to become the new primary. For example, an Amazon RDS Multi-AZ deployment can have a read replica in a separate AZ, providing read access even if the primary or its synchronous standby replica is undergoing maintenance or experiencing issues. This also enables granting read-only access to specific users or applications without granting write permissions to the primary.

Limitations and Practical Considerations

While read replicas enhance database performance for read-heavy workloads, they do not address all scaling challenges. Crucially, read replicas do not scale writes; all INSERT, UPDATE, and DELETE operations must still be directed to the primary database. If write volume is the bottleneck, a read replica will not alleviate it, though offloading reads can indirectly free up primary resources. For instance, if the primary's write operations generate 300K write operations per second due to five indexes on 50K inserts/sec, adding a replica does not reduce this load on the primary.

Replication lag is an inherent limitation due to asynchronous replication. There is always a delay between a write on the primary and its appearance on a read replica. Applications must account for this eventual consistency, as querying a replica immediately after a write to the primary might return stale data.

Practical considerations include monitoring and query optimization. Supabase recommends not scaling until CPU utilization consistently exceeds 70%. Before adding replicas, ensure queries are optimized and indexes are in place, using tools like EXPLAIN ANALYZE. Amazon RDS does not support autoscaling of read replicas, meaning manual management is required to add or remove replicas based on changing read demand. While a read replica can serve as a copy of data if the primary fails, it does not provide automatic failover to become the new primary, unlike a synchronous standby in a Multi-AZ deployment.

Deciding Between Read Replicas and Vertical Scaling

The choice between read replicas and vertical scaling depends on the specific database bottleneck and workload characteristics. A key indicator for scaling is when CPU utilization consistently exceeds 70% on the primary database. Before implementing any scaling solution, ensure queries are optimized and indexes are in place, using tools like EXPLAIN ANALYZE.

If the workload is write-heavy (less than 80% reads), read replicas will not alleviate the bottleneck, as all INSERT, UPDATE, and DELETE operations must still be directed to the primary database. In such cases, vertical scaling by upgrading the primary instance's compute resources (e.g., from a Medium to a Large or XL tier on Supabase) is the appropriate solution. This provides more CPU and RAM to handle increased write volume.

Conversely, if the workload is predominantly read-heavy (80% or more reads) and the primary database is experiencing high CPU utilization despite query optimization, read replicas are beneficial. They offload read requests, distributing them across multiple copies and freeing the primary for write operations. This is particularly effective for applications with significant read volume from user profiles, product catalogs, or dashboards. If the primary is already at the maximum vertical scaling tier (e.g., Supabase's 16XL tier), read replicas become the only horizontal scaling option left for read-intensive workloads. Read replicas are also suitable for workload isolation or geo-distribution, serving read traffic closer to users.

Frequently Asked Questions

Conclusion

Read replicas are a powerful tool for scaling read-heavy applications, effectively distributing read traffic and indirectly improving write performance by freeing up primary database resources. While they introduce considerations like replication lag, their benefits for workload isolation and geo-distribution make them indispensable for many modern applications.

Sources & References

• Working with DB instance read replicas
• Read Replicas Do Not Scale Writes - Formation
• Should I distribute my database or just have read replicas?
• How to Create RDS Read Replicas for Read Scaling
• Read Replicas | Supabase Docs
• Read Replicas Don't Solve Write Bottlenecks - Tiger Data
• Read replicas | Databricks on AWS
• When to use Read Replicas vs. bigger compute - Supabase
• Scaling and high availability in Amazon RDS
• Introducing Read Replicas - Supabase