[Plproxy-users] partitioning queries

[Marko Kreen]

On 6/1/08, Steve Singer <ssinger_pg at sympatico.ca> wrote:
> On Wed, 28 May 2008, Marko Kreen wrote:
> > This seems too complex syntax for small win.
> >
> > The most help we could give for load-balancing between partitions
> > that are simple clones would allow inserting current partition
> > number ($PARTNO) and total count ($PARTCOUNT?) to queries.
> >
> > Your function would look like this then:
> >
> >  create or replace function get_payments(month_start datea,
> >      OUT amount numeric, OUT username text, OUT invoice_id integer)
> >  returns SETOF RECORD as $x$
> >   CLUSTER 'testcluster';
> >   RUN ON ALL;
> >   SELECT payment.amount, payment.username, payment.payment_id as
> invoice_id
> >     FROM payment
> >    WHERE payment_date >= month_start + interval('15 days') * $PARTNO
> >      AND payment_date <= month_start + interval('15 days') * $PARTNO
> > + interval('15 days');
> >  $x$ language plproxy;
> >
>
>
>  This isn't too bad if your partitioning the query using criteria that is a
> linear function of the partition number.  If your query partitioning
> criteria is more complicated then this can get a bit uglier.  I can see
> users having to result to complicated CASE blocks in their query.

Well, we already have full support for the most complicated queries:

  - You provide array of SQL statements to be executed on partition.
 - Each partition knows its number.
 - Each partition picks its query from array and executes it.

See?  No limitations.  Slightly simpler case - provide array of
values where each partition picks one.

> > Main problem with that is that it would not extend to function-calls.
> > The function-calls can be fixed by having partitions know their number,
> > or more generally - know their part of the work.  And that extends
> > also to SELECTs, so the need for such feature is questionable.
> >
> > Note how the balancing works for partitioned data - the load-balancing
> > of reads, writes AND single large queries happen automatically, without
> > any need of complexity in plproxy.
> >
> > So I don't see the need for complex features designed
> > for cloned-partitions case.
> >
>
>  I feel that this type of feature can make setting up/maintaining query
> parallization across cloned partitions (or having each plproxy partition
> point at the same backend) a lot easier for end-users to configure/maintain.

Now I thought about it some time and I realized the thing I've probably
said before - main reason pl/proxy is simple and robust is that it does
function-calls.  Thats means any complexity of actually querying the
data can be put into remote functions and thus no need for additional
complexity in plproxy.  (Same goes for complex pooling logic.)

The only worth the SELECT statement has is that it allows you to run
different function or with different argument list, or to remap
the result list.  The fact that it allows to query tables directly
is more like accident - to handle direct data access comfortably,
you'll want most of the plpgsql features (and more) and such path
of development is not feasible.

IOW - any feature that does not apply to default functions-calls,
but only to manual SELECTs is misguided.


I think the feature you are after is "running same query with
different arguments on different partitions."  This can be
solved in remote functions, but indeed, not comfortably.

So here's a  solution I think fits plproxy concept better:

There should be new statement (please suggest better name):

  SPREAD BY arg1 [, arg2 ...];

That takes list of argument names.  They refer function
arguments.  Each argument referred here is supposed to be ARRAY,
and if several arguments are present the arrays must be same
length.

The idea is that from single argument list, you create several
argument lists for queries, picking arguments from SPREAD BY
arrays.  The arguments that are not SPREAD BY are simply copied.

Eg, you have plproxy function:

  my_report(dates date[], span interval) returns setof .. as $$
      SPREAD BY dates;
      CLUSTER 'mycluster';
      RUN ON pick_partition(dates);
  $$ language plproxy;

Calling it as:

  SELECT * FROM
  my_report(array['2006-01-01', '2006-02-01'], '1 month');

Creates 2 argument lists:

  '2006-01-01', '1 month'
  '2006-02-01', '1 month'

Each list is processed separately by CLUSTER (as it may not be
constant) and RUN statements.  RUN ON ANY should probably have
a hack that it tries to avoid running on same partition repeatedly.

Queries are attached to connections and if all arguments are
processed, run in parallel.

Question - what to do if you get several queries to run on
single partition.  This can happen because of connection merging
or simply hash function picks one partition several times.  One
way would be to queue them and then execute serially, another
way would be join them with UNION ALL - this is possible as
result columns are supposed to be same.

Minus compared to your scheme - you cannot pick partitions
inside plproxy function body.  But is it even necessary?  You
still have int4(arg) and you can have special hash function
where you map your data to partitions.

IMHO such feature would fit better to plproxy - it adds very
minimal syntax to language and works with both functions and
manual SELECT.  It requires non-trivial code changes, but it
would be mostly refactoring existing code, amount of new code
to be added would be small.

> Getting perspectives from people with real actually deploying this type of
> setup would help validate/invalidate this.

Yes, before you start hacking some actual use cases may be good
to have.  It may be that people who request additional
functionality have just not realized how they can solve their
problem with existing functionality.  It is quite probable,
if people are not used to have functions in their database.

-- 
marko