Load balancing

Overview

This document provides a general description of the PostgreSQL server load balancing and query routing features provided by the OpenProxy module in OpenSQL v3.0 and how to configure them.

---

Features

Read-Only Load Balancing

In PostgreSQL's Streaming Replication configuration, a non-primary Replica instance can only process Read-Only queries. OpenProxy provides a load-balancing feature that sends read-only queries to one of the Replica instances to process and return results, depending on the configuration.

By default, OpenProxy manages load balancing options on a per-pool basis (a group of PostgreSQL servers distinguished by their database names when connecting to OpenProxy), and you can assign different settings to different pools.

---

Configuration

Enable Query Parser

To identify and send Read-Only queries to the Replica instance, you must options in the openproxy.toml configuration.

• query_parser_enabled : Enables the Query Parsing feature. In addition to Read / Write Splitting, you need to enable the corresponding options to utilize the plugin's features.

• query_parser_read_write_splitting : Enables the Infer feature that determines whether a query can be sent to Replica (Read-Only) or can only be processed by Primary based on the parsed query.

[pools.my_pool]
pool_mode = "transaction"
query_parser_enabled = true
query_parser_read_write_splitting = true

Depending on the PostgreSQL configuration of the pool, the OpenProxy operating with the above options will parse the user query and send the user request to the server with the Replica Role for processing.

• If you are processing SELECT queries with the Simple Query protocol or parsing and processing SELECTs without explicit transaction blocks with the Extended protocol, you can route them to Replica.

• To process one or multiple statements within an explicit transaction, use the Route to Primary.

• If you also want to include the Primary node as a target for sending Read-Only queries, enable the

  primary_reads_enabled option as shown below.

[pools.my_pool]
pool_mode = "transaction"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true

This option also works in session pooling mode (where one client is processed on one PostgreSQL server connection), but because the PostgreSQL server connection obtained as a result of infer() the client's first query is persisted until the client session ends, errors can occur if you process a SELECT query first and then send a query that should be processed only on Primary.

session 풀링 모드 (하나의 클라이언트가 하나의 PostgreSQL 서버 연결에서 처리되는 방식) 에서도 해당 옵션은 동작하지만 클라이언트의 첫 쿼리를 infer() 한 결과로 얻은 PostgreSQL 서버 연결이 클라이언트 세션 종료시까지 유지되므로, SELECT 쿼리를 먼저 처리하고 이후 Primary에서만 처리되어야 하는 쿼리를 보내는 경우 에러가 발생할 수 있다.

• If you want to use query parsing and load balancing features, we recommend using transaction pooling mode (where one transaction is processed on one PostgreSQL server connection).

Load Balancing configuration

On a per-pool basis, you can set how you want to select replicas to process read-only queries.

• The random method selects one of the PostgreSQL servers at random to route the request.

• The loc method routes requests to the PostgreSQL server with the fewest number of currently active.

[pools.my_pool]
load_balancing_mode = "random" ## "random", "loc"

Prepared Statement Cache configuration

A Prepared Statement is a way of executing queries in the PostgreSQL Extended Protocol that uses a customized query as a precompiled query execution plan that is stored as an object on the DBMS server.

PostgreSQL supports the creation and processing of Prepared Statements on a per-client session basis.

In OpenProxy's session pooling mode, a single client is processed on only one PostgreSQL server connection until it terminates, so no action is required, but in transaction pooling mode, a single client can be processed on multiple PostgreSQL server connections per transaction, which can cause problems when processing Prepared Statements.

• To accomplish this, OpenProxy provides a global Prepared Statement cache when operating in transaction pooling mode.

• All Prepared Statements declared by Clients are stored in the cache, and when a Client wants to run a Prepared Statement, it fetches the Prepared Statement from the cache and checks whether it has been declared in the current PostgreSQL server session; if not, it declares it before executing it.

• To enable this global cache, you must specify a value for the Pool's prepared_statements_cache_size. The default value is 0, which disables the global cache.

• When the global cache is disabled, it only supports processing of Unnamed (unnamed by the client) Prepared Statements within the Client, meaning that if the Client tries to bind and execute more than one Prepared Statement, an error will occur.

The global cache is maintained in LRU fashion, and if the value is too small, problems may occur when processing client requests that declare many Prepared Statements.

[pools.my_pool]
pool_mode = "transaction"
prepared_statements_cache_size = 1000

---

Cautions

Unsupported features

When Query Parser is enabled in transaction pooling mode, the processing of the following SQL commands is limited.

PREPARE, EXECUTE

• This is a command that explicitly creates a Named Prepared Statement at the SQL level and instructs the user to execute it. It does not work in transaction pooling mode because each transaction can run on a different server connection.

• You can use session pooling mode because the connection between the client and server is the same for the duration of the session.

Execution of user-defined function

• If you create a custom function in Database using the CREATE FUNCTION syntax, and the function contains DDL or DML statements that should be executed only on the Primary node, execution of the function using a SELECT query might be routed to the Replica node, depending on the OpenProxy configuration settings, resulting in an error.

• You can wrap the execution of a specific statement in an explicit Transaction Block BEGIN ... COMMIT to create a Primary. You can bypass the direction to route only to the node.