Search Postgresql Archives

Re: about partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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. 
> 



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux