Thread: Table Partitioning
Folks, I noticed that we have some nice new speed optimizations (more properly, de-pessimizations) for partitioned tables in 9.1. Anybody care to look over the table partitioning stuff on the wiki and check it for relevance? http://wiki.postgresql.org/wiki/Table_partitioning I think I may be able to get my employer to put some people on this... Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Mon, Jun 20, 2011 at 5:42 PM, David Fetter <david@fetter.org> wrote: > I noticed that we have some nice new speed optimizations (more > properly, de-pessimizations) for partitioned tables in 9.1. /me sticks tongue out at dfetter. > Anybody care to look over the table partitioning stuff on the wiki and > check it for relevance? > > http://wiki.postgresql.org/wiki/Table_partitioning Itagaki Takahiro had a patch for this about a year ago, but I wasn't happy with the system catalog representation he chose and I think there were some other issues as well. Still, I think a pretty clear way forward here is to try to figure out a way to add some explicit syntax for range partitions, so that you can say... foo_a is for all rows where foo starts with 'a' foo_b is for all rows where foo starts with 'b' ... foo_xyz is for all rows where foo starts with 'xyz' If we have that data represented explicitly in the system catalog, then we can look at doing built-in INSERT-routing and UPDATE-handling.For an added bonus, it's a more natural syntax. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Jun 21, 2011 at 01:07:17PM -0400, Robert Haas wrote: > On Mon, Jun 20, 2011 at 5:42 PM, David Fetter <david@fetter.org> wrote: > > I noticed that we have some nice new speed optimizations (more > > properly, de-pessimizations) for partitioned tables in 9.1. > > /me sticks tongue out at dfetter. > > > Anybody care to look over the table partitioning stuff on the wiki and > > check it for relevance? > > > > http://wiki.postgresql.org/wiki/Table_partitioning > > Itagaki Takahiro had a patch for this about a year ago, but I wasn't > happy with the system catalog representation he chose and I think > there were some other issues as well. In particular, I'm noticing things labeled 8.4 and 9.0 as future. > Still, I think a pretty clear > way forward here is to try to figure out a way to add some explicit > syntax for range partitions, so that you can say... > > foo_a is for all rows where foo starts with 'a' > foo_b is for all rows where foo starts with 'b' > ... > foo_xyz is for all rows where foo starts with 'xyz' > > If we have that data represented explicitly in the system catalog, > then we can look at doing built-in INSERT-routing and UPDATE-handling. > For an added bonus, it's a more natural syntax. Does someone else have such a syntax? Does The Standard™ have anything to say? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Jun 21, 2011 at 1:52 PM, David Fetter <david@fetter.org> wrote: >> Still, I think a pretty clear >> way forward here is to try to figure out a way to add some explicit >> syntax for range partitions, so that you can say... >> >> foo_a is for all rows where foo starts with 'a' >> foo_b is for all rows where foo starts with 'b' >> ... >> foo_xyz is for all rows where foo starts with 'xyz' >> >> If we have that data represented explicitly in the system catalog, >> then we can look at doing built-in INSERT-routing and UPDATE-handling. >> For an added bonus, it's a more natural syntax. > > Does someone else have such a syntax? Does The Standard™ have > anything to say? Yes, and I don't know, respectively. There have been previous discussions of this. You might want to start by reading the discussion around the previous patch. https://commitfest.postgresql.org/action/patch_view?id=266 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> wrote: > David Fetter <david@fetter.org> wrote: >> Does The Standard* have anything to say? > > I don't know I dug around in the standard a bit and didn't find anything. Given that the standard doesn't even touch the issue of indexes because that a performance tuning implementation detail, it would seem out of character for them to define table partitioning. -Kevin