[pgpool-hackers: 595] Re: 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.

I would like to add a new keyword "standby". This represents any of
standby servers. This is more handy than specifying the each node
id. Also by using this, when you want to add new node later on, you
don't need to add new node id to the list.

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

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

> 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
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers