Best Practices for Postgres Database Replication

Written by Carlo Mencarelli

Scaling your database can be daunting, but with Postgres replicas, it doesn't have to be. Database replication ensures high availability and helps keep your applications running smoothly, even under the strain of unexpected traffic spikes or server failures.

While you can maintain your replica on-site in the same data center, ideally, the replica would be in a geographically distinct location in case of a critical outage or disaster. 

A database replica is not only for disaster recovery, though. While it is the best way to ensure you can keep during a data center outage, replica nodes are also great for scaling and splitting workloads across multiple instances or bringing data closer to your users.

What Is a Database Replica?

We’ve written about database replicas before. At the core, a database replica is a copy of your database. It can be a read replica, a hot standby, or a testing and performance node. When configuring replication, you designate one node as the primary and the others as the replicas. The write-ahead logs (WAL) maintain synchronicity by recording all changes (Inserts, Updates, and Deletes) made to the database. 

Replicas can improve disaster resilience by keeping an entire copy of your database in a separate data center or availability zone. During an outage, the administrator can failover to the replica and continue operations. PostgreSQL doesn’t offer native automatic failover, so this is a critical use. Timescale can help offload this responsibility in various ways depending on the failure scenario.

Another excellent use case for replicas is splitting the database system's workload. For example, using a replica for all read traffic reduces the potential impact on the application if there is a sudden influx of update operations like inserting or backfilling large amounts of data.

Ultimately, this benefits end users by increasing reliability. There is no distance limitation for replicas, so you can offer users improved performance via a read replica no matter what continent they are on.

An often-forgotten use for database replicas is leveraging them as testing environments. Maintaining multiple different datasets depending on the environment can be challenging for organizations. It might be easier to use a replica of the production environment database to benchmark load and performance changes to the application.

Replicas in non-production environments can also be great for tuning query performance in a safe environment before merging the changes to production.

How PostgreSQL Replication Works

PostgreSQL replication is a powerful feature that enables real-time data copying from one database instance (the primary server) to one or more standby servers. This process ensures high availability, fault tolerance, and load balancing in production systems.

Let’s break down the key elements of PostgreSQL replication, focusing on the WAL and streaming replication, which together form the backbone of this architecture.

WAL streaming and replication

At the core of PostgreSQL replication is the write-ahead log. The WAL is a crucial mechanism that records changes to the database before they are written to disk. This ensures data consistency and durability, as it allows PostgreSQL to recover from crashes by replaying the log. The same WAL logs are used to ship changes to replica servers.

In WAL streaming replication, the primary server continuously sends chunks of its WAL to one or more standby servers, which then apply these logs to their own data files. This creates an almost real-time copy of the primary database. The standby server is in a read-only mode, capable of serving read queries (read scaling) while it replicates the data changes.

How the write-ahead log works for replication

Here’s a simplified breakdown of the WAL's role in replication:

1. Transaction record: Whenever a transaction occurs (e.g., an insert, update, or delete), PostgreSQL writes this transaction to the WAL before committing it to the database files. This ensures that even if the server crashes, the transaction can be recovered from the log.

2. WAL shipping: The WAL segments are sent to the standby servers, either as files or via streaming in small chunks. This process is asynchronous by default but can be configured for synchronous replication to ensure no data is lost during a failure.

3. WAL replay: The standby server receives the WAL segments and applies them to its own data files, keeping its state in sync with the primary. This happens continuously, which minimizes the delay between the primary and standby.

By using this architecture, PostgreSQL ensures data durability and enables fault tolerance. If the primary server fails, the standby can take over with minimal downtime.

Here’s a simple diagram illustrating how PostgreSQL WAL streaming replication works:

In this setup:

• The primary server handles both read and write operations, sending the WAL logs to the standby server.

• The standby server receives and replays the WAL logs, keeping its data synchronized with the primary.

• Clients can connect to the standby for read-only queries, helping distribute the load and improve performance.

This basic replication setup ensures that in the event of a failure, the standby server can quickly be promoted to act as the new primary, reducing downtime and data loss risks.

Types of Postgres Database Replicas

There are two types of replication in PostgreSQL: logical and physical. Both have pros and cons, as well as best practices. Logical replication copies the data in the database and the changes that happen and sends them to the replica to be applied. Physical replication operates by sending complete WAL files to the replica or streaming the changes directly to the replica using the changes recorded in the WAL as the source.

As mentioned earlier, there’s also the concept of a “hot standby” or read replica. The read replica is great for running read-only queries while maintaining a database that can be a failover node in an emergency.

Logical replicas copy the entire database structure and then make each change that happens on the source database to the replica. Logical replication is great for testing major version upgrades or limiting users’ access to specific subsets of data.

Physical replicas are precisely what they sound like: a copy of the entire database on another physical machine. As noted, this is typically accomplished by sending WAL files or streaming changes to the destination database. These replicas are excellent choices for offsite backups and read replicas. Since the changes can be replicated at the disk level, you can be sure that the changes that happen to each database will be the same. 

Whether you use physical or logical replication, you can choose between asynchronous, synchronous write, or synchronous apply replication. Depending on the risk level, you may choose a different replication mode. For example, you only need eventual read consistency but need to support a high number of writes. In that case, you may choose asynchronous replication but understand that mode carries a level of data loss due to the nature of how the replication is done.

Here's a summary of the different replication options:

1. Asynchronous replication

• Description:

  • In asynchronous replication, the primary server does not wait for confirmation from the standby server before committing a transaction and returning success to the client. WAL records are sent to the standby server, but there is no guarantee they have been received or applied by the standby at the time of commit.

• Risks:

  • Risk of data loss: In the event of a primary server crash, any transactions that were committed on the primary but not yet replicated to the standby could be lost.

  • Low availability risk: While the standby remains in sync most of the time, there’s no guarantee of immediate synchronization, which could lead to slight data inconsistencies between the primary and standby during a failover.

• Use Case: Suitable for systems where high performance is prioritized over strict durability, and some level of data loss in case of failure is acceptable.

2. Synchronous write replication

• Description:

  • In synchronous write replication, the primary server waits until the WAL records have been sent to and written to disk (but not necessarily applied) by the standby server before confirming the transaction to the client. The transaction is guaranteed to be written to the standby’s WAL.

• Risks:

  • Minimal data loss risk: If the primary fails, transactions that were confirmed are already on the standby’s disk, so there is no risk of losing confirmed transactions.

  • Performance impact: Waiting for confirmation from the standby can increase latency, especially if the network between primary and standby has high latency or low bandwidth.

  • Risk of standby lagging behind: Even though the WAL is written on the standby, it may not have been applied yet, meaning the standby might not be immediately usable for a failover until the logs are replayed.

• Use Case: Suitable for systems where ensuring no data loss is critical, but some delay in failover readiness is acceptable.

3. Synchronous apply replication

• Description:

  • In synchronous apply replication, the primary server waits for the WAL records to be both written and applied (replayed to the database files) on the standby server before confirming the transaction to the client. This ensures that the standby is completely synchronized with the primary at the time of commit.

• Risks:

  • No data loss: There is no risk of losing any confirmed transactions because both the primary and standby have applied the transaction by the time the commit is confirmed.

  • Higher performance impact: This mode introduces the most latency, as the primary waits for the standby to not only receive and write the WAL logs but also apply them to its database files. This adds extra delay to the commit process, especially in cases where the standby is slower or under heavy load.

  • Guaranteed failover readiness: Since the standby has already applied all committed transactions, it is ready to take over immediately in the event of a failover.

• Use Case: Suitable for mission-critical systems where both data durability and immediate failover readiness are required, and performance impact is secondary to consistency and high availability.

Check out our blog on streaming replication if you need help choosing between these different modes.

Do’s and Don’ts of Database Replication

Replication can be complex, but fortunately, we can share some best practices and pitfalls to avoid reducing the difficulty of managing the process.

Best practices for replication

When setting up replication for PostgreSQL, it's crucial to follow best practices to ensure high availability, data integrity, and optimal performance.

Failover replicas

One of the most important aspects is implementing an automatic failover system. Failover protocols are essential for maintaining uptime during emergencies, such as hardware failures or network outages.

By having a robust failover mechanism in place, you can minimize downtime and ensure that your application remains operational despite unexpected issues. Additionally, combining failover with regular backups provides a two-layer protection system, allowing you to recover from data loss or corruption. Failover allows for stability during planned downtime, such as software updates or maintenance tasks.

It's worth noting that PostgreSQL does not have a built-in failover tool. However, several third-party solutions are available to help you set up and manage failover in your PostgreSQL environment. Timescale, for example, offers a replication and failover implementation built on top of PostgreSQL's native replication capabilities, providing a seamless and reliable solution for high availability.

Choosing a replication mode

Another critical consideration is selecting the correct replication mode for your specific use case. Different replication modes offer varying levels of efficiency and consistency, and selecting the appropriate mode can significantly impact your system's performance.

For example, if your primary focus is write efficiency, you may opt for an asynchronous replication mode. This mode allows the primary server to commit transactions without waiting for the replicas to acknowledge receipt, resulting in faster write performance. On the other hand, if you require a balance between write performance and data consistency, synchronous write mode may be more suitable. In this mode, the primary server waits for at least one replica to acknowledge receipt before committing the transaction, ensuring consistent data across nodes.

Synchronous apply mode is the best choice for applications that demand maximum read consistency. In this mode, the primary server waits for all replicas to apply the transaction before considering it committed, guaranteeing that all nodes have the same data state. It's important to note that you can change the replication mode on a per-transaction basis, allowing you to fine-tune your replication setup based on the specific requirements of each operation.

Setting replication parameters

When optimizing your replication setup, it's essential to understand and adjust key replication parameters. These parameters can significantly impact the performance and behavior of your replicas, so you should conduct in-depth testing before deploying any of them to production. Some crucial parameters to consider include max_wal_size, hot_standby, max_wal_senders, wal_keep_size, and hot_standby_feedback.

The max_wal_size parameter determines the maximum size of the WAL files before automatic WAL file recycling occurs. Adjusting this value can affect the storage space and time needed for crash recovery.

The hot_standby parameter enables read-only queries on replica servers, allowing you to offload read workloads from the primary server. max_wal_senders sets the maximum number of concurrent connections from replicas while wal_keep_size specifies the minimum size of WAL files to retain for replica servers. Increasing these values can support more replicas but may consume more resources on the primary server. Finally, hot_standby_feedback allows replicas to send feedback to the primary server about their current state, helping to avoid query cancellations due to conflicts.

Monitoring replicas

Monitoring your replicas is crucial to ensure they perform optimally and that emergency protocols are in place. Effective monitoring helps you identify and resolve issues before they impact your application's availability or performance. PostgreSQL provides various built-in monitoring tools and commands.

For example, the EXPLAIN command can help you analyze query performance by providing insights into the query execution plan. The pg_stat_activity view offers information about the current activity in the database, including running queries and their associated resources.

In addition to built-in tools, several third-party monitoring solutions are available. These tools often provide more advanced features and visualizations, making monitoring and troubleshooting your replication setup easier. Timescale offers monitoring dashboards that comprehensively overview your database's health, performance, and replication status. These dashboards can help you quickly identify bottlenecks, detect anomalies, and make informed decisions about scaling and optimization.

Replication pitfalls

When implementing replication, there are several pitfalls to be aware of that can impact the performance and consistency of your replicas.

Heavy write loads

One common issue is the impact of heavy write loads on the replication process. When the primary server experiences a high concentration of write queries, it can slow down the replicas as they struggle to keep up with the incoming changes.

In such cases, the replicas may either become desynchronized, leading to inconsistent data, or wait for the WAL replication to catch up, resulting in increased latency. To mitigate this issue, breaking up large write queries into smaller batches is the way to go, allowing the replicas to process the changes more efficiently and maintain better synchronization with the primary server.

Excessive locks

Another potential pitfall is exclusive locks on source tables. When a query acquires a ACCESS EXCLUSIVE lock on a table in the primary server, the replicas must wait until the lock is released before they can replay the changes. This locking can lead to significant delays in the replication process, especially if the lock lasts for an extended period.

To identify and address such delays, you can use the pg_locks view to monitor lock activity and adjust your queries accordingly. You can prevent replication delays and maintain a more responsive replica environment by minimizing exclusive locks and releasing them promptly.

Read desynchronization

Read replication desynchronization is another common pitfall, mainly when using read replicas. If a read replica is not configured correctly or experiences frequent delays, it may provide the application with stale or inconsistent data. Such inconsistencies can lead to incorrect query results and a poor user experience.

You can avoid this issue by configuring your read replicas correctly. If your application requires near-real-time data consistency, there may be better choices than a read replica. In such cases, exploring alternative replication strategies or adjusting your application’s tolerance for slightly stale data may be necessary.

How Timescale Uses Database Replication

Timescale offers a range of tools and features that simplify database replication and enhance the overall experience. With Timescale, creating replicas is a straightforward process, thanks to intuitive tools and documentation. We have a comprehensive guide on PostgreSQL database replication that walks users through setting up and managing replicas. As mentioned, Timescale offers native failover capabilities, ensuring high availability and minimizing downtime in the event of a primary server failure.

Monitoring replicas is crucial for maintaining a healthy and performant replication setup. Timescale provides a user-friendly replica monitoring interface that allows users to track their replicas’ status and performance easily. Metrics on the replica, such as CPU, memory utilization, replication lag, and more, are easily accessible.

Timescale also leverages logical replication to facilitate seamless platform transitions and migrations. Using logical replication allows users to replicate data between different database versions, making it an ideal tool for migrating databases with minimal downtime. This blog post on migrating a terabyte-scale PostgreSQL database to Timescale demonstrates how easy it is to migrate without downtime.

Try it for free today to see how easy replication is with Timescale.