Achieving and Maintaining High
Availability in PostgreSQL

Customer demand is changing, and so is how we perceive High Availability (HA). The initial purpose of HA databases was to protect users from hardware, software, and network failures. But as technology improved and databases grew more reliable, customers and businesses didn’t just need protection – they needed “Always On” capabilities and “three 9s, four 9s, and five 9s” uptimes.

Despite advancements in hardware, network, and database technology, many organizations still risk facing detrimental database failures. A whopping 60% of data operations have experienced an outage in the past three years, with 60% of these outages having productivity disruptions between four to over 48 hours. The cost is significant: 70% of outages result in over $100,000 to over $1 million in total losses.

Ensuring your business can handle failures and recover from outages is crucial, so it’s crucial that businesses consider HA databases and implement an HA architecture that ensures maximum reliability and continuity.

Join us as we dive into achieving high availability, from the techniques and tools available today to the innovations that allow businesses to attain and maintain extreme high availability.

Achieve best-in-class High Availability for PostgreSQL, no matter the setup

While high availability architecture provides increased protection against data-tier downtime, it’s crucial that businesses first understand what they are getting into.

If your environment tolerates some downtime, high availability architecture might introduce unnecessary costs and complexity. The best option for your business use case depends on your tolerance for downtime and the respective tradeoffs of various other architectures.

Here's how you can decide what high availability option is best for your business.

• Determine the level of availability you hope to achieve

  Do you need a system that allows your business to continue functioning only during a single zone or complete region failure?

• Understand your operational budget

  To provide high availability, your infrastructure and storage costs will increase. It’s best first to determine how much you’re capable of spending.

• Know the cost to your business if there is a downtime in the data persistence tier

  A key question is what services or customers rely on your data tier. If a service caters only to occasional internal users, your system doesn’t need the HA capabilities an end-customer-facing service needs for constant continuity.

• Understand your RPO (Recovery Point Objective)

  Can you afford to lose data due to a failover? The distributed nature of HA topologies means a tradeoff between commit latency and the risk of data loss during a failure.

• Know Your RTO (Recovery Time Objective)

  Multiple HA options vary depending on how quickly the system can failover and be available to users and customers. If you know your RTO, you’ll know the right HA option to get.

Clearly defining your RPO and RTO is crucial, as both concepts go hand-in-hand. Do note that achieving the lowest amount of either tends to be the most expensive endeavor. Trying to find an acceptable balance between both will influence decisions on technology acquired and design decisions. 

The “good, fast, cheap: choose two” rule applies here. Depending on your needs, budget, and risk tolerance, your HA architecture will have tradeoffs in performance, capabilities, and cost.

keyboard_arrow_up

Fundamentally, high availability with PostgreSQL databases works in two ways:

• Streaming Replication

  A replication approach in which the replica connects to the primary and continuously receives a stream of WAL records. Streaming replication lets the replica stay more up-to-date with the primary, compared to log-shipping replication.
  
  Replica node infrastructure is independent from the primary, with each node requiring dedicated compute and storage resources. Replica nodes can be used as hot standbys to serve read-only client queries, allowing load-balancing across the primary and the replicas.

• Synchronous Streaming Replication

  Databases can also configure streaming replication as synchronous by choosing one or more replicas to be synchronous standby.
  
  In this case, the primary doesn’t confirm a transaction commit until after the replica acknowledges the transaction persistence. This method provides increased durability but leads to higher transaction latency.

What about the high availability architecture with PostgreSQL? At the most basic level, data tier HA consists of:

This underlying architecture is then combined with various tools and approaches to minimize downtime in the event of infrastructural and zonal outages. 

• A mechanism to identify if and when a primary node experiences failure
• A failover process in which a replica node is promoted to a primary node
• A method to change the query routing so that the application requests can reach the new primary node
• A way to fall back to the original architecture using pre-failover primary and replica nodes in their original capacities (this is optional, but good to have)

Important things to consider

For businesses, when choosing between approaches, it’s important to balance commit latency and durability according to their business needs. 

Similarly, businesses should select an HA architecture that can perform manual failovers or switchovers with the production infrastructure. This is because of the time and manual efforts needed to prepare a new standby environment for subsequent failover or fallback. 

Otherwise, the system can only withstand one failure, and the service won’t have protection from an SLA violation. Remember: what HA architecture you get depends on the three 9s, four 9s, or five 9s uptime you need to achieve based on your SLA. 

As discussed above, PostgreSQL users commonly rely on physical streaming replication-based high availability architecture to achieve High Availability. This is then combined with various tools such as Patroni or Repmgr to enable automatic failover or switchover, ensuring the availability of Postgres in production.  

PostgreSQL’s Native Logical Replication (or PNLR for short) has a few fundamental limitations that can affect HA systems. The examples include but are not limited to: 

• Data Definition Language (DDL) operations are not replicated

  Operations such as creating and dropping tables need to be performed independently on each node. Since transaction consistency cannot be ensured in DDL operations, additional maintenance windows, during which the database receives no traffic, are required. This detracts from the system’s ability to be highly available.

• There is no ability to failover

  If either the source or the target node goes down during a heavy period of replication traffic, there is a significant risk that the entire replication process for a given set of tables will need to be restarted, and previously replicated data will need to be resent. Therefore, PNLR servers are at greater risk than PostgreSQL physical replicas for having significant data loss when taking over as the primary data source for an application as a result of the current primary data source failing. 

• Logical replication systems require that each row in a replicated table have a primary key.

  This is to properly identify updated and deleted rows during replication. The primary key needs to be unique across all nodes in the cluster. PostgreSQL native logical replication provides no built-in mechanism to ensure that newly created and unique records on different nodes don’t have the same primary key. The risk is that completely different records in a cluster are represented as the same record. Creating the assurance of unique primary keys is entirely the responsibility of the application developer, making it more error-prone.

• PNLR is not integrated with backup and recovery solutions.

• PNLR does not come with best practices and proven architectures for achieving common tasks.

  This includes procedures to use PLNR for upgrades and maintenance operations. Such operations need to be built and extensively tested for each deployment. PNLR only replicates in one direction. Therefore, when used in an upgrade scenario, if things go wrong after the upgrade and you encounter an issue, going back to a previous version of the database software is very complex and potentially impossible if not properly planned for in advance.

• PNLR only replicates in one direction.

  Therefore, when used in an upgrade scenario, if things go wrong after the upgrade and you encounter an issue, going back to a previous version of the database software is very complex and potentially impossible if not properly planned for in advance.

Taking the above into account, this means that certain crucial activities can still compromise high availability and risk violating uptime SLAs when using physical streaming replication. They include: 

• Database maintenance operations
• Patching the system
• Major version upgrades
• Increasing resources (CPU, memory, disk space, etc)
• Unplanned outages
• Configuration and parameters changes that require a restart