Re: Partitioned table limitation

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Goboxe
> Sent: Monday, October 01, 2007 11:26 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Partitioned table 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] Partitioned table limitation
> >
> > > Hi,
> >
> > > Are there any limitations on number of child tables that can be use
> > > in
> > > partitioned table?
> >
> > > [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 are partitioned too, 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