Table Partitioning

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