[pgpool-hackers: 589] Proposal: primary preference

[Tatsuo Ishii]

Hi,

In the first pgpool-II developer meeting, we discussed on enhancing
load balance. As a conclusion we decided to implement two load balance
preference types: 1) "application name" 2) database.  For example if
client connects to particular database, then the SELECT query sends to
primary server unconditionally. Note that we agreed that this should
not be called "load balance", rather "redirect" or "preference"
because this aims to route queries to particular node which meets
particular condition. Note that this feature only applicatable for
streaming replication mode.

Here is my proposal for the syntax.

1) application name

I propose a directive name "app_name_redirect_preference_list" for this
purpose. Proposed syntax is:

app_name1:node_id1,app_name2:node_id2,app_name3:node_id3...

where app_nameN is "application name". Regular expression can be
used. node_idN is the PostgreSQL server node id. Special keyword
"primary" can be used to represent the primary node.

Example:

app_name_redirect_preference_list 'psql:primary,myapp[0-4]:1,myapp[5-9]:2'

2) database

I propose a directive name "database_redirect_preference_list" for
this purpose. Proposed syntax is:

database1:node_id1,database2:node_id2,database3:node_id3...

Regular expression can be used for database. node_idN is same as above.

Example:

database_redirect_preference_list 'postgres:primary,mydb[0-4]:1,mydb[5-9]:2'

3) what if "app_name_redirect_preference_list" and
database_redirect_preference_list conflicts each other?

What if clients application name is "psql" and database is "mydb0"?
Probably we should take priority application name over database
because database is more "broad" specification. So in this case the
SELECTs will be sent to primary node, rather than node 1.

Comments and suggestions are welcome.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp