Re: [GENERAL] Partitioned table limitation
On Oct 3, 4:00 am, [EMAIL PROTECTED] (paul rivers) wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Goboxe Sent: Monday, October 01, 2007 11:26 AM To: [EMAIL PROTECTED] Subject: Re: [GENERAL]Partitionedtable limitation On Oct 2, 1:38 am, [EMAIL PROTECTED] (paul rivers) wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Goboxe Sent: Monday, October 01, 2007 2:18 AM To: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Partitioned table limitation
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 ---(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
Re: [GENERAL] Partitioned table limitation
On Oct 2, 1:38 am, [EMAIL PROTECTED] (paul rivers) wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Goboxe Sent: Monday, October 01, 2007 2:18 AM To: [EMAIL PROTECTED] 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 ---(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
Re: [GENERAL] Partitioned table limitation
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] 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, [EMAIL PROTECTED] (paul rivers) wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Goboxe Sent: Monday, October 01, 2007 2:18 AM To: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Partitioned table limitation
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Partitioned table limitation
On 10/1/07, Goboxe [EMAIL PROTECTED] 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. ---(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
