Re: about partitioning - Mailing list pgsql-general

Hello all,


still with partitioning...

wheter I use rules or triggers is there a way for me _not_ to specify
field-by-field all the fields I wish to be redirected to the
child-table...


as example:

instead of this:
---------------------------------------------------------
create rule insert_t_1 AS ON INSERT TO teste WHERE (t>=1000 and t<2000)
DO INSTEAD INSERT INTO t_1 VALUES (NEW.t, NEW.s1, NEW.s2 NEW.s3, NEW.s4
NEW.s5, NEW.s6, NEW.s7, NEW.s8);
---------------------------------------------------------

something like this:
---------------------------------------------------------
create rule insert_t_1 AS ON INSERT TO teste WHERE (t>=1000 and t<2000)
DO INSTEAD INSERT INTO t_1 VALUES (__ALL__);
---------------------------------------------------------

of course this assumes that the child table inherits all fields from the
parent table _and_ has no extra fields which is exactly my case.

any hints.

thx
j



On Sat, 2008-09-13 at 16:48 -0400, Robert Treat wrote:
> On Thursday 11 September 2008 07:47:00 Joao Ferreira gmail wrote:
> > Hello all,
> >
> > my application is coming to a point on which 'partitioning' seems to be
> > the solution for many problems:
> >
> > - query speed up
> > - data elimination speed up
> >
> > I'dd like to get the feeling of it by talking to people who use
> > partitioning, in general..
> >
> > - good, bad,
>
> good :-)
>
> > - hard to manage, easy to manage,
>
> I think the upfront costs for managing a partitioning setup are higher with
> postgres than other systems, but there is nothing that you shouldn't be able
> to automate in a cron script (at which point management becomes easy), plus
> postgres gives you some interesting flexibility that is harder to find in
> other setups.
>
> > - processing over-head during INSERT/UPDATE,
>
> you can setup inserts to have relativly little overhead, but it requires more
> management/maintence work up front. Updates within a partition also have
> relativly little extra overhead, especially if you put in a little
> application logic to figure out how to work on a partition directly. Updates
> where you are changing the partition key value are always more problematic
> though.
>
> > - stability/compatibility of pg_dump and restore operations,
>
> no real issues here as long as your on recent enough versions to do wildcard
> table matching for individual tables.
>
> > - how many partitions would be reasonable for read _and_ write  access
> > optimal speed;
> >
>
> again, this depends on how exactly your working on the data. For example, we
> have tables with over a thousand partitions on them; in those scenarios all
> data is written into a single partition (with a new partition created daily),
> and the qeury patterns are really straightforward... last month gets a lot of
> queries, lasat three months not so much, last year barely any, and beyond
> that is pretty much just archive info. That said, we have other systems where
> that wouldnt work at all (for example, a static number of partitions, all of
> which are queried activly).
>
> For some more info, I've given at least one presentation on the topic, which
> seems to be missing from the omniti site, but I've uploaded it to
> slideshare...
> http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation
>
> HTH.
>