Re: "Dynamic routing" to different databases

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of Alex Balashov
> Sent: Thursday, October 01, 2015 10:47 AM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] "Dynamic routing" to different databases 
> is a psql-level construct, not something that can be embedded in live queries.
> So, I was expecting to have to rewrite thousands of lines of stored procedures
> to take a schema parameter and construct dynamic query strings with dynamic
> table names and so on.
> 
> Assuming one can simply do:
> 
>     SET search_path TO <schema>; SELECT query_business_as_usual();
> 
> I'm in a pretty good place!

You can set the default search path on a per-role/user basis.  What you could do is map a DB user (or set of users) to
aspecific schema, then set that users search path for the appropriate schema and the queries will automatically get
routedto the appropriate schema.  Segmenting your different applications user also allows you to use the role system to
restrictaccess to the data between applications.
 

The potential downside to this approach is that you won't be able to use something like pgbouncer to share DB
connectionsbetween the different users/schemas.  This can, depending on your usage and access patterns lead to problems
withhigh connection counts to the DB being needed.
 

Brad.