Help Center> Relational Database Service> Best Practices> PostgreSQL> Read/Write Splitting with PostgreSQL Proxy
View PDF

Read/Write Splitting with PostgreSQL Proxy

Scenarios

If the processing capability of a single PostgreSQL DB instance reaches the bottleneck and cannot bear higher read and write pressure, services will be affected. In read-intensive scenarios, you can create read replicas to offload read pressure from primary DB instances through PostgreSQL proxy so that primary DB instances are dedicated to processing write requests Read replicas not only elastically scale the read capability of primary DB instances, but also increase the number of concurrent database connections.

How It Works

PostgreSQL Proxy is a middleware that implements read/write splitting for PostgreSQL databases. It sits between the PostgreSQL server and PostgreSQL client. You application connects to the PostgreSQL proxy, as shown in Figure 1.

Figure 1 PostgreSQL Proxy principle

Advantages

PostgreSQL Proxy has the following advantages:

  • It is fully compatible with the PostgreSQL protocol and is widely used.
  • A unified read/write splitting address is provided to simplify the complexity of application development and database O&M.

    Your application connects to the PostgreSQL Proxy through the unified read/write splitting address and the PostgreSQL Proxy splits requests to the primary DB instance and read replicas, therefore delivering better performance and application scalability.

  • Read replicas can be added online

    without any application modifications.

  • Read weights can be set online to suit requirements in different scenarios.
  • The heartbeat of read replicas is automatically and periodically detected to improve database availability.

    In case of a failure, such as a breakdown or a long latency that exceeds the threshold, PostgreSQL Proxy will no longer send read requests to the faulty read replica until it is recovered.

Enabling Read/Write Splitting

  1. Learn about billing and constraints first.
  2. Enable read/write splitting by referring to Enabling Read/Write Splitting.
  3. Configure the delay threshold and read weight by referring to Configuring Delay Threshold and Distributing Read Weight.
  4. Verify the read/write splitting effect by referring to Testing Read/Write Splitting Performance.
Parent topic: PostgreSQL