Load Balancing

5.7.1. Condition for Load Balancing

For a query to be load balanced, all the following requirements must be met:

• PostgreSQL version 7.4 or later

• either in replication mode or master slave mode

• the query must not be in an explicitly declared transaction (i.e. not in a BEGIN ~ END block)

  • However, if following conditions are met, load balance is possible even if in an explicit transaction

    • transaction isolation level is not SERIALIZABLE

    • transaction has not issued a write query yet (until a write query is issued, load balance is possible. Here "write query" means non SELECT DML or DDL. SELECTs having write functions as specified in black or white function list is not regarded as a write query. This may be changed in the future.)

    • If black and white function list is empty, SELECTs having functions is regarded as a read only query.

• it's not SELECT INTO

• it's not SELECT FOR UPDATE nor FOR SHARE

• it starts with "SELECT" or one of COPY TO STDOUT, EXPLAIN, EXPLAIN ANALYZE SELECT... ignore_leading_white_space = true will ignore leading white space. (Except for SELECTs using writing functions specified in black_function_list or white_function_list )

• in master slave mode, in addition to above, following conditions must be met:

  • does not use temporary tables

  • does not use unlogged tables

  • does not use system catalogs

5.7.2. Load Balancing in Streaming Replication

While using Streaming replication and Hot Standby, it is important to determine which query can be sent to the primary or the standby, and which one should not be sent to the standby. Pgpool-II 's Streaming Replication mode carefully takes care of this.

We distinguish which query should be sent to which node by looking at the query itself.

• These queries should be sent to the primary node only

  • INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE, CREATE, DROP, ALTER, COMMENT

  • SELECT ... FOR SHARE | UPDATE

  • SELECT in transaction isolation level SERIALIZABLE

  • LOCK command more strict than ROW EXCLUSIVE MODE

  • DECLARE, FETCH, CLOSE

  • SHOW

  • Some transactional commands:

    • BEGIN READ WRITE, START TRANSACTION READ WRITE

    • SET TRANSACTION READ WRITE, SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

    • SET transaction_read_only = off

  • Two phase commit commands: PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED

  • LISTEN, UNLISTEN, NOTIFY

  • VACUUM

  • Some sequence functions (nextval and setval)

  • Large objects creation commands

• These queries can be sent to both the primary node and the standby node. If load balancing is enabled, these types of queries can be sent to the standby node. However, if delay_threshold is set and the replication delay is higher than delay_threshold , queries are sent to the primary node.

  • SELECT not listed above

  • COPY TO

• These queries are sent to both the primary node and the standby node

  • SET

  • DISCARD

  • DEALLOCATE ALL

In an explicit transaction:

• Transaction starting commands such as BEGIN are sent to the primary node.

• Following SELECT and some other queries that can be sent to both primary or standby are executed in the transaction or on the standby node.

• Commands which cannot be executed on the standby such as INSERT are sent to the primary. After one of these commands, even SELECTs are sent to the primary node, This is because these SELECTs might want to see the result of an INSERT immediately. This behavior continues until the transaction closes or aborts.

In the extended protocol, it is possible to determine if the query can be sent to standby or not in load balance mode while parsing the query. The rules are the same as for the non extended protocol. For example, INSERTs are sent to the primary node. Following bind, describe and execute will be sent to the primary node as well.

Lastly, queries that Pgpool-II 's parser thinks to be an error are sent to the primary node.

5.7.3. Load Balancing Settings