DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=cJCuNj9hdIAbG2OXnY4fP5Mexa5RHSo288fVdfC9cUCLJ35E5R23cdjVpVVMkkCrcD
	mTKdOJBQap0u/JL7eeHmLwiOTmLmpiDG5fh3Ab30STWuaeTUyZ5d4XTbIYoQQyF/y1Pt
	bqqdKHT73lFvCQZfmWT5N/XUWhSTvbdc+yzPw=
Message-ID: <e51f66da0806300616q47a3433bt52f78716427c5665@mail.gmail.com>
Date: Mon, 30 Jun 2008 16:16:26 +0300
From: "Marko Kreen" <markokr@gmail.com>
To: "Chris Browne" <cbbrowne@acm.org>
Subject: Re: Federated Postgresql architecture ?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <60zlp6zqsm.fsf@dba2.int.libertyrms.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <C401CB24-5BD6-455B-A62B-9D737E44CEB3@kevinkempterllc.com>
	<200806261441.12413.josh@agliodbs.com>
	<36e682920806261440o2142783blbc38013f4f5677c2@mail.gmail.com>
	<200806261531.03892.josh@agliodbs.com>
	<60zlp6zqsm.fsf@dba2.int.libertyrms.com>

On 6/27/08, Chris Browne <cbbrowne@acm.org> wrote:
> josh@agliodbs.com (Josh Berkus) writes:
>  > Jonah,
>  >
>  >> Hmm, I didn't think the Skype tools could really provide federated
>  >> database functionality without a good amount of custom work.  Or, am I
>  >> mistaken?
>  >
>  > Sure, what do you think pl/proxy is for?
>
>
> Ah, but the thing is, it changes the model from a relational one,
>  where you can have fairly arbitrary "where clauses," to one where
>  parameterization of queries must be predetermined.
>
>  The "hard part" of federated database functionality at this point is
>  the [parenthesized portion] of...
>
>   select * from table@node [where criterion = x];
>
>  What we'd like to be able to do is to ascertain that [where criterion
>  = x] portion, and run it on the remote DBMS, so that only the relevant
>  tuples would come back.
>
>  Consider...
>
>  What if table@node is a remote table with 200 million tuples, and
>  [where criterion = x] restricts the result set to 200 of those.
>
>  If you *cannot* push the "where clause" down to the remote node, then
>  you're stuck with pulling all 200 million tuples, and filtering out,
>  on the "local" node, the 200 tuples that need to be kept.
>
>  To do better, with pl/proxy, requires having a predetermined function
>  that would do that filtering, and if it's missing, you're stuck
>  pulling 200M tuples, and throwing out nearly all of them.
>
>  In contrast, with the work David Fetter's looking at, the [where
>  criterion = x] clause would get pushed to the node which the data is
>  being drawn from, and so the query, when running on "table@node,"
>  could use indices, and return only the 200 tuples that are of
>  interest.
>
>  It's a really big win, if it works.

I agree that for doing free-form queries on remote database,
the PL/Proxy is not the right answer.  (Although the recent patch
to support dynamic records with AS clause at least makes them work.)

But I want to clarify it's goal - it is not to run "pre-determined
queries."  It is to run "pre-determined complex transactions."

And to make those work in a "federated database" takes huge amount
of complexity that PL/Proxy simply sidesteps.  At the price of
requiring function-based API.  But as the function-based API has
other advantages even without PL/Proxy, it seems fine tradeoff.

-- 
marko