Partitioned table limitation

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

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.

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