Re: Partitioned table limitation

On Oct 3, 4:00 am, rivers(dot)p(dot)(dot)(dot)(at)gmail(dot)com ("paul rivers") wrote:
> > -----Original Message-----
> > From: pgsql-general-ow(dot)(dot)(dot)(at)postgresql(dot)org [mailto:pgsql-general-
> > ow(dot)(dot)(dot)(at)postgresql(dot)org] On Behalf Of Goboxe
> > Sent: Monday, October 01, 2007 11:26 AM
> > To: pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org
> > Subject: Re: [GENERAL]Partitionedtable limitation
>
> > On Oct 2, 1:38 am, rivers(dot)p(dot)(dot)(dot)(at)gmail(dot)com ("paul rivers") wrote:
> > > > -----Original Message-----
> > > > From: pgsql-general-ow(dot)(dot)(dot)(at)postgresql(dot)org [mailto:pgsql-general-
> > > > ow(dot)(dot)(dot)(at)postgresql(dot)org] On Behalf Of Goboxe
> > > > Sent: Monday, October 01, 2007 2:18 AM
> > > > To: pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org
> > > > Subject: [GENERAL]Partitionedtable limitation
>
> > > > Hi,
>
> > > > Are there any limitations on number of child tables that can be use
> > > > in
> > > >partitionedtable?
>
> > > > [snip]
>
> > > We currently use partitioning by date and id, with 1/4 a year of dates
> > and
> > > approximately 10 IDs (and slowly increasing). Each partition runs from
> > > around 1 million to 20 million rows.
>
> > > Whether it's recommended or not, I don't know. But for us, the
> > partitioning
> > > works exactly as advertised. As with anything new, I'd take the time to
> > > setup a simple test to see if it works for you, too.
>
> > > In particular, be sure to check the documentation on caveats. You'll
> > find
> > > these a little stricter than partitioning issues in Oracle or SQL
> > Server.
>
> > > HTH,
> > > Paul
>
> > Thanks Paul for your inputs.
>
> > I am not really clear when you said "partitioning by date and id, with
> > 1/4 a year of dates and
> > approximately 10 IDs". Could you give some examples of your tables?
>
> > TQ,
> > G
>
> Sure.
>
> The largest logical table has a primary key of fw_id, fw_date, fw_line_nbr.
> We partition on fw_id, fw_date.
>
> fw_date ranges from today to about 120 days ago. There are no gaps for any
> fw_id in this rolling window. Each fw_id + fw_date has between 1-20 million
> rows, though most of them tend toward the smaller end of that scale.
>
> We also generate child tables (partitions) for a few days into the future as
> part of a nightly maintenance job. We also drop ones older than the 120
> days. So all told, we have around 1400 partitions or so, and around a
> trillion rows of data, all told. The rows average about 700 bytes or so,
> wide, with date, time, inet, cidr, varchar, bigint smallint, and int types.
>
> There are a variety of different processes loading the data constantly
> during the day. This data is used for ad-hoc troubleshooting during the
> day, plus some near real-time monitoring alerts. It sees a fair amount of
> reading during the day. On a nightly basis, it is rolled up into a
> summarized format, and we keep this rollup data for years. These rollup
> tables arepartitionedtoo, but it's not on the same scale as the above
> table. The rollup data is used for all kinds of trend analysis, further
> reporting, etc.
>
> HTH,
> Paul
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/- Hide quoted text -
>
> - Show quoted text -

Thanks for sharing that.
I will give it try to convert ours to daily table and test its
performance.

G