pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dimitri Fontaine <dfontaine@hi-media.com>
To: pgsql-performance@postgresql.org
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Jignesh K. Shah <J.K.Shah@sun.com>
Cc: Greg Smith <gsmith@gregsmith.com>
Subject: Re: Benchmark Data requested --- pgloader CE design ideas
Date: Wed, 6 Feb 2008 12:27:56 +0100
Message-ID: <200802061227.58668.dfontaine@hi-media.com> (raw)
In-Reply-To: <200802051815.25800.dfontaine@hi-media.com>
References: <1202150236.4252.521.camel@ebony.site>
	<1202221496.4252.680.camel@ebony.site>
	<200802051815.25800.dfontaine@hi-media.com>

Hi,

I've been thinking about this topic some more, and as I don't know when I'll 
be able to go and implement it I'd want to publish the ideas here. This way 
I'll be able to find them again :)

Le mardi 05 février 2008, Dimitri Fontaine a écrit :
> Le mardi 05 février 2008, Simon Riggs a écrit :
> > Much better than triggers and rules, but it will be hard to get it to
> > work.
>
> Well, I'm thinking about providing a somewhat modular approach where
> pgloader code is able to recognize CHECK constraints, load a module
> registered to the operator and data types, then use it.

Here's how I think I'm gonna implement it:

User level configuration
-=-=-=-=-=-=-=-=-=-

At user level, you will have to add a constraint_exclusion = on parameter to 
pgloader section configuration for it to bother checking if the destination 
table has some children etc.
You'll need to provide also a global ce_path parameter (where to find user 
python constraint exclusion modules) and a ce_modules parameter for each 
section where constraint_exclusion = on:
  ce_modules = columnA:module:class, columnB:module:class

As the ce_path could point to any number of modules where a single type is 
supported by several modules, I'll let the user choose which module to use.

Constraint exclusion modules
-=-=-=-=-=-=-=-=-=-=-=-=-

The modules will provide one or several class(es) (kind of a packaging issue), 
each one will have to register which datatypes and operators they know about. 
Here's some pseudo-code of a module, which certainly is the best way to 
express a code design idea:

class MyCE:
   def __init__(self, operator, constant, cside='r'):
      """ CHECK ( col operator constant ) => cside = 'r', could be 'l' """
      ...

   @classmethod
   def support_type(cls, type):
      return type in ['integer', 'bigint', 'smallint', 'real', 'double']

   @classmethod
   def support_operator(cls, op):
       return op in ['=', '>', '<', '>=', '<=', '%']

   def check(self, op, data):
      if op == '>' : return self.gt(data)
      ...

   def gt(self, data):
      if cside == 'l':
         return self.constant > data
      elif cside == 'r':
         return data > self.constant

This way pgloader will be able to support any datatype (user datatype like 
IP4R included) and operator (@@, ~<= or whatever). For pgloader to handle a 
CHECK() constraint, though, it'll have to be configured to use a CE class 
supporting the used operators and datatypes.

PGLoader constraint exclusion support
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

The CHECK() constraint being a tree of check expressions[*] linked by logical 
operators, pgloader will have to build some logic tree of MyCE (user CE 
modules) and evaluate all the checks in order to be able to choose the input 
line partition.

[*]: check((a % 10) = 1) makes an expression tree containing 2 check nodes

After having parsed pg_constraint.consrc (not conbin which seems too much an 
internal dump for using it from user code) and built a CHECK tree for each 
partition, pgloader will try to decide if it's about range partitioning (most 
common case). 

If each partition CHECK tree is AND((a>=b, a<c) or a variation of it, we have 
range partitioning. Then surely we can optimize the code to run to choose the 
partition where to COPY data to and still use the module operator 
implementation, e.g. making a binary search on a partitions limits tree.

If you want some other widely used (or not) partitioning scheme to be 
recognized and optimized by pgloader, just tell me and we'll see about it :)
Having this step as a user module seems overkill at the moment, though.

Multi-Threading behavior and CE support
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Now, pgloader will be able to run N threads, each one loading some data to a 
partitionned child-table target. N will certainly be configured depending on 
the number of server cores and not depending on the partition numbers...

So what do we do when reading a tuple we want to store in a partition which 
has no dedicated Thread started yet, and we already have N Threads running?
I'm thinking about some LRU(Thread) to choose a Thread to terminate (launch 
COPY with current buffer and quit) and start a new one for the current 
partition target.
Hopefully there won't be such high values of N that the LRU is a bad choice 
per see, and the input data won't be so messy to have to stop/start Threads 
at each new line.

Comments welcome, regards,
-- 
dim

Attachments:

  signature.asc (application/pgp-signature, 188B) download

view thread (16+ messages)  latest in thread

Message-ID: <200802061227.58668.dfontaine@hi-media.com>
Permalink:  ../200802061227.58668.dfontaine@hi-media.com/
Also on:    postgresql.org/message-id/200802061227.58668.dfontaine@hi-media.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: dfontaine@hi-media.com, simon@2ndquadrant.com, J.K.Shah@sun.com, gsmith@gregsmith.com
  Subject: Re: Benchmark Data requested --- pgloader CE design ideas
  In-Reply-To: <200802061227.58668.dfontaine@hi-media.com>
  References: <1202150236.4252.521.camel@ebony.site> <1202221496.4252.680.camel@ebony.site> <200802051815.25800.dfontaine@hi-media.com> <200802061227.58668.dfontaine@hi-media.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