[pgpool-general: 1535] Re: foreign data wrappers support?

[Lonni J Friedman]

On Wed, Mar 27, 2013 at 4:31 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>> I'm mostly thinking & planning for 9.3 when a postgres FDW will be
>>>> supported ( http://www.postgresql.org/docs/devel/static/postgres-fdw.html
>>>> ).  This opens up a lot of new & interesting possibilities, especially
>>>> with respect to clustering & load balancing, as it will be (somewhat)
>>>> trivially possible to access different clusters from each other.
>>>
>>> Can you elaborate on clustering & load balancing by using pgsql_fdw
>>> with pgpool-II?
>>> --
>>
>> What I was envisioning (granted this is all theoretical, I haven't
>> tried to do any of it yet) was using pgsql_fdw to link two completely
>> separate postgres clusters, both of which would have their own pgpool
>> instances.  The 'CREATE SERVER' command would reference the port
>> number of the second pgpool instance, rather than the postgres server
>> itself.  As a result, any of the tables associated with the foreign
>> postgres server would actually have their SQL queries going directly
>> over the second pgpool instance.  I'm wondering if this should work as
>> far as pgpool is concerned?
>
> I think the configuration should work as expected because pgpool-II is
> transparent to PostgreSQL protocol and I believe pgsql_fdw does not
> invent any new PostgreSQL protocol attributes.

ok, good, thanks.

>
> BTW, is there any concrete use case for such a configuration in your
> mind? The configuration sounds pretty interesting but I cannot think
> of real world use case.

I'm hoping to use it for a form of sharding.  I have a few tables
which are orders of magnitude larger (in number of rows) than the
others, such that I'd like to move them onto their own separate
postgres instance to better manage them (in terms of performance &
resource requirements).  But doing so with other sharding techniques
seems like more complexity than its worth.