pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Federated Postgresql architecture ?
Date: Fri, 27 Jun 2008 14:16:57 -0400
Message-ID: <60zlp6zqsm.fsf@dba2.int.libertyrms.com> (raw)
References: <C401CB24-5BD6-455B-A62B-9D737E44CEB3@kevinkempterllc.com>
	<200806261441.12413.josh@agliodbs.com>
	<36e682920806261440o2142783blbc38013f4f5677c2@mail.gmail.com>
	<200806261531.03892.josh@agliodbs.com>

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.
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/lisp.html
"The avalanche has started, it is too late for the pebbles to vote" 
-- Kosh, Vorlon Ambassador to Babylon 5

view thread (9+ messages)  latest in thread

Message-ID: <60zlp6zqsm.fsf@dba2.int.libertyrms.com>
Permalink:  ../60zlp6zqsm.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/60zlp6zqsm.fsf@dba2.int.libertyrms.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: Federated Postgresql architecture ?
  In-Reply-To: <60zlp6zqsm.fsf@dba2.int.libertyrms.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox