Re: New partitioning WAS: Check constraints on partition parents only?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: New partitioning WAS: Check constraints on partition parents only?
Date: 2011-07-26 23:58:48
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


> That's why I'd be opposed to any partitioning scheme that removed the ability to have different fields in different children. We've found that ability to be very useful. Likewise, I think we need to have intelligent plans involving a parent table that's either completely empty or mostly empty.

Well, I don't think that anyone is proposing making constraint exclusion
go away. However, we also need a new version of partitioning which
happens "below" the table level. I don't agree that the new
partitioning needs -- at least at the start -- the level of flexibility
which CE gives the user. In order to get simplicity, we have to
sacrifice flexibility.

In fact, I'd suggest extreme simplicity for the first version of this,
with just key partitioning. That is:

CREATE TABLE <table_name> (
... cols ... )
PARTITION ON <key_expression>

... where <key_expression> can be any immutable expression on one or
more columns of some_table. This actually covers range partitioning as
well, provided that the ranges can be expressed as the results of an
expression (e.g. EXTRACT ('month' FROM date_processed ) ).

For the optional AUTOMATIC CREATE phrase, new values for key_expression
would result in the automatic creation of new partitions when they
appear (this has some potential deadlocking issues, so it's not ideal
for a lot of applications). Otherwise, you'd create partitions manually:

CREATE PARTITION ON <table_name> KEY <key_value>;
DROP PARTITION ON <table_name> KEY <key_value>;

... where <key_value> is some valid value which could result from

Yes, this is a very narrow and simplistic partitioning spec. However,
it would cover 80% of the use cases I see in the field or on IRC, while
being 80% simpler than CE. And CE would still be there for those who
need it.

Josh Berkus
PostgreSQL Experts Inc.

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Kupershmidt 2011-07-27 00:38:09 Re: psql: display of object comments
Previous Message Jim Nasby 2011-07-26 23:30:11 Re: storing TZ along timestamps