Thread: Partitioned table limitation
Hi, Are there any limitations on number of child tables that can be use in partitioned table? I am currently having weekly partitioned tables (using partitioned view in SQL Server) that I kept for 2 years. In total, there will be 52 * 2 = 104 tables exist at one time in the partition. I am migrating from SQL Server to pg. Can pg support that number of tables? How about if I want to convert it as daily tables (356 * 2 years = 712 tables). Is this recommended? FYI, currently each weekly table storeing between 28 to 32 millions records (approx 4-5 millions recs daily) Thanks, G
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Goboxe > Sent: Monday, October 01, 2007 2:18 AM > To: pgsql-general@postgresql.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
On 10/1/07, Goboxe <hadzramin.ar@gmail.com> wrote: > Hi, > > Are there any limitations on number of child tables that can be use > in > partitioned table? > > > I am currently having weekly partitioned tables (using partitioned > view in SQL Server) that I kept for 2 years. > In total, there will be 52 * 2 = 104 tables exist at one time in the > partition. > > > I am migrating from SQL Server to pg. Can pg support that number of > tables? > > > How about if I want to convert it as daily tables (356 * 2 years = > 712 > tables). > Is this recommended? I've played around with as many as 1,000 child tables. By then, the planning time becomes noticeably longer than for a single table, but the response time is still so much faster that it's worth it. Note I'm talking only a fraction of a second planning time, even at 1,000 tables. If you are going over 100 tables, make sure you're using triggers for updating the child tables not rules, as rules are far too slow when there's over 50 or so tables to look choose from.
On Oct 2, 1:38 am, rivers.p...@gmail.com ("paul rivers") wrote:
> > -----Original Message-----
> > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> > ow...@postgresql.org] On Behalf Of Goboxe
> > Sent: Monday, October 01, 2007 2:18 AM
> > To: pgsql-gene...@postgresql.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
> > I've played around with as many as 1,000 child tables. By then, the > planning time becomes noticeably longer than for a single table, but > the response time is still so much faster that it's worth it. Note > I'm talking only a fraction of a second planning time, even at 1,000 > tables. > > If you are going over 100 tables, make sure you're using triggers for > updating the child tables not rules, as rules are far too slow when > there's over 50 or so tables to look choose from. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match- Hide quoted text - > > - Show quoted text - Scott, Could you share a snippet on how to use trigger for this? TQ, G
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Goboxe
> Sent: Monday, October 01, 2007 11:26 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Partitioned table limitation
>
> On Oct 2, 1:38 am, rivers.p...@gmail.com ("paul rivers") wrote:
> > > -----Original Message-----
> > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> > > ow...@postgresql.org] On Behalf Of Goboxe
> > > Sent: Monday, October 01, 2007 2:18 AM
> > > To: pgsql-gene...@postgresql.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
On Oct 3, 4:00 am, rivers.p...@gmail.com ("paul rivers") wrote:
> > -----Original Message-----
> > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> > ow...@postgresql.org] On Behalf Of Goboxe
> > Sent: Monday, October 01, 2007 11:26 AM
> > To: pgsql-gene...@postgresql.org
> > Subject: Re: [GENERAL]Partitionedtable limitation
>
> > On Oct 2, 1:38 am, rivers.p...@gmail.com ("paul rivers") wrote:
> > > > -----Original Message-----
> > > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> > > > ow...@postgresql.org] On Behalf Of Goboxe
> > > > Sent: Monday, October 01, 2007 2:18 AM
> > > > To: pgsql-gene...@postgresql.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