Executive Summary and key Constraints

In enterprises, there are applications with specific high availability requirements. Online transaction processing (OLTP) applications are prime examples of applications with high-availability requirements. High availability (HA) deployment at multiple sites requires understanding how Application manages it’s state. In many cases, Database is primary persistent mechanism. Hence, high availability requires ,

  • Balancing data consistency
  • Understanding Recovery objectives
  • Managing Operational complexity in given constraints.

This post discusses alternatives for deploying mission-critical OLTP applications in a high-availability configuration.

Current State (Deployment)

Let us assume application , with high availability requirement as below,

Current Deployment Architecture

  • Application is deployed across two data centers.
  • It uses PostgreSQL as database.

Objectives and Constraints

The goal is to provide high availability with the following objectives,

  • The Applications, thus deployed, are expected to have Availability of 99.9% (~ 8.8 hours of downtime/year). Distance between these 2 is approx. 1000 KM and are connected by 1 Gbps network. This bandwidth is shared.
  • The application has write-heavy access pattern.
  • Application must adhere to SLO while achieving the target availability level.
  • Achieve a near-zero Recovery Point Objective (RPO = 0).
  • Remain cost-effective by leveraging open-source software wherever possible.
  • Minimize operational complexity.

Landscape of PostgreSQL Distributed Architectures

The Current landscape of PostgreSQL Distributed Architectures includes following options. Some of the options like “Read replicas”, “Cloud database offerings” are not listed as they are not relevant for the use case,

TypeDescriptionPros and Cons
Network-attached block storage (e.g. SAN storage)
  • Database files are stored on a different device
  • The database server typically runs in a virtual machine in a Hypervisor, which exposes a block device to the VM. Any reads and writes to the block device will result in network calls to a block storage API.
  • The durability and availability benefits of network-attached storage usually outweigh the performance downsides.
- Pros
  • Higher durability (replication)
  • Higher uptime (replace VM, reattach)
  • Fast backups and replica creation (snapshots)
  • Disk is resizable

- Cons
  • Higher disk latency (~20μs -> ~1000μs)
  • Lower IOPS (~1M -> ~10k IOPS)
  • Crash recovery on restart takes time
  • Cost can be high
Active-active (e.g. BDR)In the active-active architecture, any node can accept writes without coordination with other nodes. It is typically used with replicas in multiple sites, each of which will then see low read and write latency, and can survive failure of other sites. Active-active systems do not have a linear history, even at the row level, which makes them very hard to program against.- Pros
  • Very high read and write availability
  • Low read and write latency
  • Read throughput scales linearly
- Cons
  • Eventual read-your-writes consistency
  • No monotonic read consistency
  • No linear history (updates might conflict after commit)
  • Not offered as part of Community Edition
Transparent Sharding (e.g. Citus)Transparent sharding systems like Citus distribute tables by a shard key and/or replicate tables across multiple primary nodes. Each node shows the distributed tables as if they were regular PostgreSQL tables and queries & transactions are transparently routed or parallelized across nodes.Data is stored in shards, which are regular PostgreSQL tables.- Pros
  • Scale throughput for reads & writes (CPU & IOPS)
  • Scale memory for large working sets
  • Parallelize analytical queries, batch operations
- Cons
  • High read and write latency
  • Data model decisions have high impact on performance
  • Snapshot isolation concessions
Distributed key-value storage with SQL (e.g. yugabyteDB)These databases introduce the notion of a distributed key-value store that supports transactions across nodes (key ranges) with snapshot isolation in a scalable manner by using globally synchronized clocks. Subsequent evolutions then added a SQL layer on top, and ultimately even a PostgreSQL interface.Open source/On-prem options like CockroachDB and Yugabyte followed a similar approach without the requirement of synchronized clocks, at the cost of significantly higher latency.- Pros
  • Good read and write availability (shard-level failover)
  • Single table, single key operations scale well
  • No additional data modeling steps or snapshot isolation concessions
- Cons
  • Many internal operations incur high latency
  • No local joins in current implementations
  • Not actually PostgreSQL, and less mature and optimized
Streaming Replication/Hot StandbyWarm and hot standby servers are kept current by reading a stream of write-ahead log (WAL) records from primary. If the main server fails, the standby contains almost all of the data of the main server, and can be quickly promoted. This can be quorum based where all the standbys appearing in the list will be used as candidates for synchronous standbys. Even if one of them should fail, the other standbys will keep performing the role of candidates of synchronous standby.-Pros
  • Fail over of primary does not result in data loss within Data center.
-Cons
  • Synchronous replication usually requires carefully planing and placing of standby servers to ensure applications perform acceptably.
  • Iincautious use of synchronous replication will reduce performance for database applications because of increased response times and higher contention.

Based on above comparison, lets us go through couple of approaches in detail.

Option 1: PostgreSQL Community Edition (OSS) with Data Partitioning (a.k.a Sharding)

Brief

As of this writing, PostgreSQL Community Edition does not provide synchronous Multi-master deployment. (ref: here). This is the mode where any of server(s) can accept write requests.  

Given this limitation,a Data Partitioning (sharding) based approach can be considered. In a nutshell, Data Partitioning is one of the basic scalability technique. It works by dividing the data into smaller sets and assigning it to a Server (in this case PG Database). So a server becomes independant of others as they share nothing.

Why data partitioning/Sharding?     - The primary motivation behind data partitioning is to distribute the data set so that it could be distributed across servers. This helps ensure that that there is no data overlap.     - With no overlap, each server can safely process requests for data modifications. Additionally, this will not incur communication overhead. Also no impact on availability during partial system failures.    - PostgreSQL currently only supports single-primary architecture. All application writes must be routed to designated primary node in a data center. Data partitioning can help overcome this limitation.    - One of the possible implementations is to assign tenants to specific data centers. This helps achieve a shared-nothing architecture.

Approach

Proposed Architecture with Sharding

  • Sharding can be implemented at Application level or Database level. Both options are discussed below.
  • Sharding is often introduced to overcome limits on data volume and throughput. In this case, the primary objective is to achieve a shared-nothing architecture,

  - Sharding key - is the information that is used to decide which server is responsible for the data that you are looking for (e.g. Institution ID). This key is used to split the data across databases and then find a way to route all of your queries to the right database server. The database does not need to support sharding for application to use it.  

  • Operational Considerations,

    • Monitoring - Aggregate metrics and logs across data centers to get view of system health
    • Backup and restore - Backup and restore procedures must be planned carefully across shards. A point-in-time recovery of a single shard can introduce inconsistencies with other shards.
    • Schema changes - Data definition language (DDL) changes must be applied consistently across shards.

  • Below image shows how sharding looks like,

    Sharding Approach

Sharding at Application level

One possible implementation approach is as follows,

  • Use a modulo-based function to map a sharding key to a database identifier. Initially, each shard may exist as a logical PostgreSQL database rather than on dedicated physical hardware.
  • Provision PostgreSQL servers in an Active-Passive configuration and estimate future growth requirements.
  • For e.g., start with 2 servers with 32 databases each, each of these will be provisioned as logic databases with exact same schema on these servers. Refer below diagram,

Manual Sharding at Database

  • Implement mapping functions within the application:

    • getDbNumber() maps the sharding key (such as Institution ID) to a logical database.

    • getServerNumber() maps the logical database to a physical server.

    • As capacity requirements grow, physical servers can be added and logical databases redistributed. Only the server-mapping logic requires modification.

    • No data loss requirement requires us to use Streaming replication/Hot standy within a data center. However, this can only address failure of primary database and not the failure of Application nodes or data center itself. This confiuration can be deployed using Patroni or pg_auto_failover.

  • Challenges

  - Modulo function based approach  works for static shard counts. If shards are to be added or removed dynamically, consistent hashing or directory-based mapping are better suited for distributing and locating data. Refer here for comparison of sharding strategies. Selection of strategy will have impact on infrastructure required as well as on operational complexity,

  •  Future horizontal scaling requires shard migration procedures and corresponding application configuration updates.
  • Cross-shard queries will require additional considerations. It will require “Scatter-gather” approach to connect to multiple databases and then aggregating results.

This is one of the many approaches for sharding and will need careful consideration before finalizing it.

For the use case at hand, The load balancer is likely to route requests in round-robin or using any other algorithm, with no consideration for sharding across data centers. Hence, application in each data center will have to determine if it can process the request based on sharding key or else route the request to other data center.  

Sequence showing routing

This approach can be further enhanced as below,

  • Routing logic can be implemented as service . It can be independently deployed in redundant configuration if required.

  • This service will act as pass-through proxy. It parses the request and either forwards it to local App for processing or routes it to  other data center depending on sharding logic.

  • This service can implement health check check/Liveness probs for endpoint in other data center(s). These will be used to continuously monitor the health and to proactively respond with error(s) for requests that require such routing.

  • Drawbacks,

    • This is not a true High availability configuration. In case of disruption at any data center(s), significant percentage (i.e. up to 50% if shards are evenly distributed across data centers) of transactions may be  affected. Measures like provisioning of additional replica(s) of Application + Database servers will have to be planned . This will result in additional expenditure and operational considerations.
    • Horizontal scaling of database and movement of respective shards (logical databases) must be carefully planned to minimize down time.
    • Routing of requests between shards results in additional network round trips and will impact throughput.

   

Sharding at Database level (Automatic Sharding)

  • Application-level sharding is a great way to increase your I/O capacity and allow your application to handle more data. Hwever, a lot of challenges come with it. Code becomes much more complex, cross-shard queries are a pain point, and adding hardware and migrating data can be a challenge.

  • Within PostgreSQL Universe, Citus (ref: here), open source extension, provides sharding at database level.  It provides row-based and schema-based sharding. With schema-based sharding (ref: [here](Citus 12: Schema-based sharding for PostgreSQL - Citus Data)), the schema becomes the logical shard within the database. Multi-tenant apps can a use a schema per tenant to easily shard along the tenant dimension. Query changes are not required. Schema based can be considered for Multi-tenant or microservices based OLTP workload. Row-based sharding is claimed to be suitable for analytical workload.

  • Citus provides,

    • Schema management with appropriate transactions and locking
    • automatic zero-downtime rebalancing
    • reference tables enable more compact data models

Automatic Sharding using Citus

  • Pros and Cons
    • Leveraging open source extension backed by Microsoft.
    • Application instance still needs to decide if it can serve the request but connection management to connect to sharded schema is not needed in Application
    • Automatic rebalancing of shards
    • Additional database nodes for coordinator
    • Cross-shard queries are not natively supported.
    • Read and write latency needs to be verified against expected SLOs

Summary

  •  This approach provides workaround for the Single-master support in PostgreSQL Community Edition. This is without any proprietary/commercial software.

  • Sharding introduces significant and permanent complexity into your data architecture. It affects all aspects during the system’s lifetime. This includes development, operations, testing, query design, and failure recovery.

  •  This is not a true Active-Active setup. However, for the given the objectives,  RPO can be minimized with careful planning. Since, even in case of failure at one of the data center, tenants on other data center can still be served.

  •  Sharding logic at Application level can be achieved using Modulo function. Alternatively, it can be complex one that uses DCS (Distributed configuration store) like etcd to manage sharding information. This will be dictated by Application requirements.

  •  Application-level sharding is a great way to increase your I/O capacity and allow your application to handle more data. It introduces lot of challenges. Your code becomes much more complex, cross-shard queries are a pain point. If this concerns outweigh the benefits then sharding using Citus can be considered.

  •  Citus provides automatic sharding for PostgreSQL. It can be considered as alternative to application-level sharding.

Option 2: Using YugaByte OSS

Brief

This approach uses YugaByte OSS (YugaByte/YugabyteDB). It is an open source distributed database and is is wire compatible with PostgreSQL . Yugabyte is a distributed database. It offers horizontal scalability by auto sharding data on multiple servers. This enhances fault tolerance with no specific measures required in Application. Within a cluster, it needs to maintain quorum depending on the RTO/RPO requirements. Every table data is sharded (called tablet) and each tablet is stored across nodes in cluster. Within cluster, data is replicated synchronously. Replication occurs at tablet level. One tablet is initialized as leader and the rest are followers. It provides XCluster replication setup for setting up bi-directional replication across data centers. However, only Asynchonous replication is supported in bi-directional mode. YugabyteDB replicates data across fault domains (i.e. data center in this case) in order to tolerate faults. The replication factor (RF) is the number of copies of data in a YugabyteDB cluster. It is suggested to have minimum RF of 3.

Using Yugabyte OSS as Data store

Pros

  • No need for Application level routing/sharding as both Data centers can be in Active mode.
  • Automatic partitioning by the database
  • Xcluster bi-directional /two-way works with 2 data centers.

Cons

  • To achieve a fault tolerance of 1 data Center, the primary cluster has to be configured with a RF of at least 3 [] 2*(1) + 1].Accordingly, additional infrastructure (Bare metal or VM Servers) will be needed as well as for control pane (for monitoring, configuration etc.)
    • In bi-directional replication mode,
      • Only supports Asynchronous replication.
      • For conflict resolution, it implements “Last write wins” approach.
      • Potential data loss: In the event of a data center failure, any data that has not yet been replicated to the secondary data center will be lost.
      • Stale reads: When reading from the secondary data center, there may be a delay in data availability due to the asynchronous nature of the replication. This can result in stale reads
      • Read and write latency needs to be analyzed.
      • May lead to inconsistencies if schemas or data modifications are not carefully coordinated across clusters.
      • Tools and interfaces are only provided in Commercial version. This may make operational management (via command line tools) cumbersome.

Summary

  • This approach is based on using YugabyteDB, distributed key-value store, with support postgresql protocol for providing high availability .
  • Using yugabytedb allows for horizontal scaling within cluster though it requires additional infrastructure to confirm with intended replication factor and for control pane.
  • Since yugabytedb only supports asychronous replication for Bi-direction XCluser configuration, achieving RPO=0 will be challenging.

Option 4: Spanner Omni

Brief

Spanner Omni from Google is a self-managed, distributed database. It provides horizontal read and writes scalability of Google Spanner Cloud database. It can be deployed on VMs or Docker or Kubernetes. It requires license for commercial use. Google has stated that deployment similar to Spanner should match SLA’s. It uses Paxos. Spanner Omni automatically distribute data and maintain synchronous paxos replication across the zones. Storage layer of Spanner Omni, interfaces with each server’s attached file system thus making files available to database layer on same machine as well as other servers . Database layer on each server interfaces with Storage layer . It provides,

  • Virtually unlimited scalability
  • High availability (99.999%)
  • Strong consistency
  • Enterprise-grade security
  • Multi-model capabilities
  • 0 RTO and 0 RPO
  • Currently only Java, Python and Go libraries are available

Refer documentation for more details.

This approach is similar to YugabyteDB other than,

  • Spanner Omni is commercial offering
  • It doesn’t force to use asymmetric replication for bi-directional use cases
  • Inherits Google spanner’s credentials for high availability and performance.

Comparison of Options

Architecture MetricPostgreSQL OSS with Application level ShardingPostgresql OSS with automatic data Sharding using CitusUsing Distributed SQL (YugaByte)Spanner Omni
True Active-Active WritesNo. Active-Passive at DB level. Writes must hit a single designated Primary node.No. Active-Passive at DB Level.Yes. Distributed sharding allows concurrent writes across nodes in both DCs but with asynchronous replication.Yes. Concurrent writes are allowed on all nodes.
RPO = 0 CapabilityNo but can be minimizedNo but can be minimizedNo as Asynchronous replication between data centers might result in data lossYes
Automated FailoverManual / High Risk. Automatic promotion of replica/slave will need tools like Patroni or pg_auto_failover.Manual / High Risk. Automatic promotion of replica/slave will need tools like Patroni or pg_auto_failover.Yes.Yes.
Operational OverheadSharding will be done at Application with need for routing layer. Managing a manually sharded environment increases the difficulty of routine tasks. Schema updates, backups, and point-in-time recovery must be coordinated across multiple independent instances.Yes.Coordinater nodes to be deployed in HA mode. Requires familiarity operating Citus.Zero manual sharding needed; horizontal scaling is handled natively by the engine. More efforts needed by DBA for OSS Version as Control pane is not available for configurationSharding and data distributed is managed by database. No additional overhead