proxies and multiplexing

[Stuart Bishop]

On 17 June 2014 22:58, Stuart Bishop <stuart.bishop at canonical.com> wrote:
> On 17 June 2014 20:37, John Meinel <john at arbash-meinel.com> wrote:
>
>> I don't know quite how that would work with postgres masters vs postgres
>> slaves, but it does mean that postgres charm is involved in its own proxying
>> (it knows when it is being proxied, and changes its behavior accordingly).
>>
>> I haven't quite worked through how all that interacts with your proposal
>> that not all units of postgresql are equal. Nor how it interacts with the
>> Azure model of only giving out one IP address for all the units of postgres.
>
> This model looks very promising, thanks. It had not occurred to me
> that the data doesn't have to flow slavishly along the relation path.
> It solves the master/slave unit issue elegantly by not solving it at
> all - each client unit still has a relation to each PostgreSQL unit
> and can select which PostgreSQL unit to use based on its role, rather
> than trying to stuff a new service in the middle and multiplex
> everything through it.

Now I've thought about it more, the downsides of this model are:

 - you can only have one proxy service attached to the service.
 - all clients must go via the proxy.

So in the case of PostgreSQL (server) and PgBouncer (proxy/connection
pool), PgBouncer can be running in one of 3 modes (session,
transaction, statement). If a client service needs to use database
resources that cross transaction boundaries, it needs to connect via
PgBouncer in session mode or directly to the database. If a client
service does not need these sorts of database resources, it should
connect via PgBouncer running in transaction or statement mode
reducing db load. Commonly, you will have a web front end that uses
transaction mode, and some reporting jobs using temporary tables that
require session mode.

In this case, I can work around it by extending the PG db interface,
allowing clients to specify the pooling model. This will be ignored
when there is no pgbouncer proxy attached, or used to redirect to a
proxy running in the correct mode when there is a proxy attached.

-- 
Stuart Bishop <stuart.bishop at canonical.com>