Syntax for partitioning

2009/10/29 Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>:

I'd like to improve partitioning feature in 8.5.
Kedar-san's previous work is wonderful, but I cannot see any updated patch.
http://archives.postgresql.org/message-id/bd8134a40906080702s96c90a9q3bbb581b9bd0d5d7@mail.gmail.com

So, I'll take over the work if there are no ones to do it.
I'm thinking to add syntax support first. Table partitioning was
proposed many times, but it is still not applied into core.
The reason is it is too difficult to make perfect partitioning
feature at once. I think syntax support is a good start.

First, I will add syntax for CREATE TABLE, ALTER TABLE ADD/DROP PARTITION.
The syntax is borrowed from from Oracle and MySQL. Their characteristics
are using "LESS THAN" in range partitioning. The keyword "PARTITION" is
added to the full-reserved keyword list to support ADD/DROP PARTITION.

Those syntax is merely a syntax sugar for INHERITS with CHECK. Declarations
are translated into CHECK constraints. I have a plan to adjust pg_dump to
dump definitions of partitioning in the correct format, but the actual
implementation will be still based on constraint exclusion. In addition,
hash partitioning is not implemented; syntax is parsed but "not implemented"
error are raised for now.

Here is syntax I propose:
----
ALTER TABLE table_name ADD PARTITION name ...;
ALTER TABLE table_name DROP PARTITION [IF EXISTS] name [CASCADE | RESTRICT];

Range partitioning:
 CREATE TABLE table_name ( columns )
   PARTITION BY RANGE ( a_expr )
   (
     PARTITION name VALUES LESS THAN [(] const [)],
     PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition
   );

List partitioning:
 CREATE TABLE table_name ( columns )
   PARTITION BY LIST ( a_expr )
   (
     PARTITION name VALUES [IN] ( const [, ...] ),
     PARTITION name VALUES [IN] [(] DEFAULT [)]       -- overflow partition
   );

Hash partitioning:
 CREATE TABLE table_name ( columns )
   PARTITION BY HASH ( a_expr )
   PARTITIONS num_partitions;

 CREATE TABLE table_name ( columns )
   PARTITION BY HASH ( a_expr )
   (
     PARTITION name,
     ...
   );

Note:
 * Each partition can have optional WITH (...) and TABLESPACE clauses.
 * '(' and ')' are optional to support both Oracle and MySQL syntax.
----

Comments welcome.

+1

Pavel

Hi,

So, I'll take over the work if there are no ones to do it.
I'm thinking to add syntax support first. Table partitioning was
proposed many times, but it is still not applied into core.
The reason is it is too difficult to make perfect partitioning
feature at once. I think syntax support is a good start.

Guess we are back to square one again on Partitioning :), but as long
as someone is willing to walk the whole nine yards with it, that would
be just great!

I had proposed Oracle style syntax a while back and had also submitted
a WIP patch then. Again then my motive was to move forward in a
piece-meal fashion on this feature. First solidify the syntax, keep
using the existing inheritance mechanism and go one step at a time. I
think a feature like Partitioning needs this kind of an approach,
because it might turn out to be a lot of work with a lot of very many
sub items.

So +1 on solidifying the syntax first and then sorting out the other
minute, intricate details later..

Regards,
Nikhils

First, I will add syntax for CREATE TABLE, ALTER TABLE ADD/DROP PARTITION.
The syntax is borrowed from from Oracle and MySQL. Their characteristics
are using "LESS THAN" in range partitioning. The keyword "PARTITION" is
added to the full-reserved keyword list to support ADD/DROP PARTITION.

Those syntax is merely a syntax sugar for INHERITS with CHECK. Declarations
are translated into CHECK constraints. I have a plan to adjust pg_dump to
dump definitions of partitioning in the correct format, but the actual
implementation will be still based on constraint exclusion. In addition,
hash partitioning is not implemented; syntax is parsed but "not implemented"
error are raised for now.

Here is syntax I propose:
----
ALTER TABLE table_name ADD PARTITION name ...;
ALTER TABLE table_name DROP PARTITION [IF EXISTS] name [CASCADE | RESTRICT];

Range partitioning:
 CREATE TABLE table_name ( columns )
   PARTITION BY RANGE ( a_expr )
   (
     PARTITION name VALUES LESS THAN [(] const [)],
     PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition
   );

List partitioning:
 CREATE TABLE table_name ( columns )
   PARTITION BY LIST ( a_expr )
   (
     PARTITION name VALUES [IN] ( const [, ...] ),
     PARTITION name VALUES [IN] [(] DEFAULT [)]       -- overflow partition
   );

Hash partitioning:
 CREATE TABLE table_name ( columns )
   PARTITION BY HASH ( a_expr )
   PARTITIONS num_partitions;

 CREATE TABLE table_name ( columns )
   PARTITION BY HASH ( a_expr )
   (
     PARTITION name,
     ...
   );

Note:
 * Each partition can have optional WITH (...) and TABLESPACE clauses.
 * '(' and ')' are optional to support both Oracle and MySQL syntax.
----

Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
http://www.enterprisedb.com

On 29 Oct 2009, at 02:15, Itagaki Takahiro wrote:

I'd like to improve partitioning feature in 8.5.
Kedar-san's previous work is wonderful, but I cannot see any updated
patch.
http://archives.postgresql.org/message-id/bd8134a40906080702s96c90a9q3bbb581b9bd0d5d7@mail.gmail.com

So, I'll take over the work if there are no ones to do it.
I'm thinking to add syntax support first. Table partitioning was
proposed many times, but it is still not applied into core.
The reason is it is too difficult to make perfect partitioning
feature at once. I think syntax support is a good start.

First, I will add syntax for CREATE TABLE, ALTER TABLE ADD/DROP
PARTITION.
The syntax is borrowed from from Oracle and MySQL. Their
characteristics
are using "LESS THAN" in range partitioning. The keyword "PARTITION"
is
added to the full-reserved keyword list to support ADD/DROP PARTITION.

Those syntax is merely a syntax sugar for INHERITS with CHECK.
Declarations
are translated into CHECK constraints. I have a plan to adjust
pg_dump to
dump definitions of partitioning in the correct format, but the actual
implementation will be still based on constraint exclusion. In
addition,
hash partitioning is not implemented; syntax is parsed but "not
implemented"
error are raised for now.

Here is syntax I propose:
----
ALTER TABLE table_name ADD PARTITION name ...;
ALTER TABLE table_name DROP PARTITION [IF EXISTS] name [CASCADE |
RESTRICT];

Range partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY RANGE ( a_expr )
(
PARTITION name VALUES LESS THAN [(] const [)],
PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow
partition
);

List partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY LIST ( a_expr )
(
PARTITION name VALUES [IN] ( const [, ...] ),
PARTITION name VALUES [IN] [(] DEFAULT [)] -- overflow
partition
);

Hash partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY HASH ( a_expr )
PARTITIONS num_partitions;

CREATE TABLE table_name ( columns )
PARTITION BY HASH ( a_expr )
(
PARTITION name,
...
);

Note:
* Each partition can have optional WITH (...) and TABLESPACE clauses.
* '(' and ')' are optional to support both Oracle and MySQL syntax.
----

Comments welcome.

+1000

Thanks !

(most anticipated feature for 8.5, here, next to replication [well, I
am interested in multi master, but that's not going to happen :P ] )

Itagaki Takahiro wrote:

The keyword "PARTITION" is
added to the full-reserved keyword list to support ADD/DROP PARTITION.

Any chance to avoid that? PARTITION seems like something people might
well use as a column or variable name. OTOH, it is reserved in SQL2008
and SQL2003.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, Oct 29, 2009 at 3:35 AM, Nikhil Sontakke
<nikhil.sontakke@enterprisedb.com> wrote:

So +1 on solidifying the syntax first and then sorting out the other
minute, intricate details later..

I like that idea as well but I have a concern. What will we do with
pg_dump. If the PARTITION commands are just syntactic sugar for
creating constraints and inherited tables then pg_dump will have to
generate the more generic commands for those objects. When we
eventually have real partitioning then restoring such a dump will not
create real partitions, just inherited tables. Perhaps we need some
kind of option to reverse-engineer partitioning commands from the
inheritance structure, but I fear having pg_dump reverse engineer
inherited tables to produce partitioning commands will be too hard and
error-prone. Hopefully that's too pessimistic though, if they were
produced by PARTITION commands they should be pretty regular.

--
greg

On Thursday 29 October 2009 18:33:22 Greg Stark wrote:

On Thu, Oct 29, 2009 at 3:35 AM, Nikhil Sontakke

<nikhil.sontakke@enterprisedb.com> wrote:

So +1 on solidifying the syntax first and then sorting out the other
minute, intricate details later..

I like that idea as well but I have a concern. What will we do with
pg_dump. If the PARTITION commands are just syntactic sugar for
creating constraints and inherited tables then pg_dump will have to
generate the more generic commands for those objects. When we
eventually have real partitioning then restoring such a dump will not
create real partitions, just inherited tables. Perhaps we need some
kind of option to reverse-engineer partitioning commands from the
inheritance structure, but I fear having pg_dump reverse engineer
inherited tables to produce partitioning commands will be too hard and
error-prone. Hopefully that's too pessimistic though, if they were
produced by PARTITION commands they should be pretty regular.

One could have a system catalog containing the partitioning information and
generate the constraints et al. from that and mark them in pg_depend...

Andres

On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote:

Range partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY RANGE ( a_expr )
(
PARTITION name VALUES LESS THAN [(] const [)],
PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition
);

Maybe this needs to mention the actual operator name instead of LESS
THAN, in case the operator is not named < or the user wants to use a
different one.

Hash partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY HASH ( a_expr )
PARTITIONS num_partitions;

CREATE TABLE table_name ( columns )
PARTITION BY HASH ( a_expr )
(
PARTITION name,
...
);

Unless someone comes up with a maintenance plan for stable hash
functions, we should probably not dare look into this yet.

On Fri, 2009-10-30 at 00:10 +0200, Peter Eisentraut wrote:

On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote:

Range partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY RANGE ( a_expr )
(
PARTITION name VALUES LESS THAN [(] const [)],
PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition
);

Maybe this needs to mention the actual operator name instead of LESS
THAN, in case the operator is not named < or the user wants to use a
different one.

I can't help but wonder if the PERIOD type might be better for
representing a partition range. It would make it easier to express and
enforce the constraint that no two partition ranges overlap ;)

Regards,
Jeff Davis

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:

The keyword "PARTITION" is
added to the full-reserved keyword list to support ADD/DROP PARTITION.

Any chance to avoid that? PARTITION seems like something people might
well use as a column or variable name. OTOH, it is reserved in SQL2008
and SQL2003.

CREATE TABLE does not require PARTITION to be a reserved keyword,
but there are conflicts in ALTER TABLE ADD/DROP PARTITION:

* ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT]
* ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT]

There are some solutions:

1. Change COLUMN not to an optional word (unlikely)
2. Change syntax of DROP PARTITION to DROP TABLE PARITION or so
3. Change ALTER TABLE ADD/DROP PARTITION to top level
=> CREATE/DROP PARTITION name ON table_name

Any better ideas?

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Thu, Oct 29, 2009 at 9:51 PM, Itagaki Takahiro
<itagaki.takahiro@oss.ntt.co.jp> wrote:

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:

The keyword "PARTITION" is
added to the full-reserved keyword list to support ADD/DROP PARTITION.

Any chance to avoid that? PARTITION seems like something people might
well use as a column or variable name. OTOH, it is reserved in SQL2008
and SQL2003.

CREATE TABLE does not require PARTITION to be a reserved keyword,
but there are conflicts in ALTER TABLE ADD/DROP PARTITION:

 * ALTER TABLE ... DROP [COLUMN]  name [CASCADE | RESTRICT]
 * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT]

There are some solutions:

 1. Change COLUMN not to an optional word (unlikely)
 2. Change syntax of DROP PARTITION to DROP TABLE PARITION or so
 3. Change ALTER TABLE ADD/DROP PARTITION to top level
     => CREATE/DROP PARTITION name ON table_name

Any better ideas?

I'm not sure if this is better, but what about:

CREATE PARTITION name ON TABLE name
DROP PARTITION name

Since partitions will live in pg_class and are in some sense "top
level" objects, it seems like it would make sense to use a syntax that
is similar to the one we use for indices... we can't say "DROP COLUMN
name", because the table must be specified. But a partition name must
be unambiguous, so making the user write it out explicitly doesn't
seem friendly.

...Robert

Peter Eisentraut <peter_e@gmx.net> wrote:

On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote:

Range partitioning:
CREATE TABLE table_name ( columns )
PARTITION BY RANGE ( a_expr )
(
PARTITION name VALUES LESS THAN [(] const [)],
PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition
);

Maybe this needs to mention the actual operator name instead of LESS
THAN, in case the operator is not named < or the user wants to use a
different one.

How about to use "sortby" or "index_elem" here?

PARTITION BY RANGE '(' sortby-or-index_elem ')' '(' RangePartitions ')'

sortby:
a_expr USING qual_all_Op opt_nulls_order
| a_expr opt_asc_desc opt_nulls_order

index_elem:
ColId opt_class opt_asc_desc opt_nulls_order
| func_expr opt_class opt_asc_desc opt_nulls_order
| '(' a_expr ')' opt_class opt_asc_desc opt_nulls_order

We should allow only btree operator class here because we need to
extract GREATER-THAN-OR-EQUAL operator from LESS THAN. In addition,
we will be able to optimize parition search in the future if we
restrict a range partition key should be comparable scalar value.

Multidimensional partitioning will be implemented with another
approach, something like "PARTITION BY GIST", because it would
require different oprimization from range partitioning.
BTW, "PARTITION BY <pg_am.amname>" crossed my mind here,
but it is not well-investigated yet.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Thu, Oct 29, 2009 at 7:14 PM, Robert Haas <robertmhaas@gmail.com> wrote:

CREATE TABLE does not require PARTITION to be a reserved keyword,
but there are conflicts in ALTER TABLE ADD/DROP PARTITION:

 * ALTER TABLE ... DROP [COLUMN]  name [CASCADE | RESTRICT]
 * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT]

There are some solutions:

Do we need a DROP PARTITION command at all? What would it even do?
Drop the partition from the parent table and throw it away in one
step? I think in actual practice people usually remove the partition
from the parent table first, then do things like archive it before
actually throwing it away.

--
greg

Greg Stark <gsstark@mit.edu> wrote:

* ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT]
* ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT]

Do we need a DROP PARTITION command at all? What would it even do?

Currently no. So, it would be good to treat PARTITION as
just a synonym of TABLE. Not only DROP PARTITION but also
ALTER PARTITION will work.

CREATE PARTITION name ON table_name
DROP PARTITION name

ALTER PARTITION name ...

We might need to specify partition keys with another syntax.
ALTER TABLE will have only one new command "PARTITION BY".
and we reuse TABLE command for PARTITION in other operations.

ALTER TABLE table_name PARTITION BY RANGE (expr) (...)

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Thu, 2009-10-29 at 15:19 -0700, Jeff Davis wrote:

I can't help but wonder if the PERIOD type might be better for
representing a partition range. It would make it easier to express and
enforce the constraint that no two partition ranges overlap ;)

I can't help but wonder if the period type might better be a generic
container for pairs of scalar, totally-ordered types.

On Thu, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote:

I'd like to improve partitioning feature in 8.5.

Nice.

Here is syntax I propose:

<snip>

Is this the same as / similar to Oracle's syntax?

IIRC Nikhil's patch was Oracle's syntax, and I prefer having that one
instead of inventing our own wheel.

Regards,
--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

PARTITION BY RANGE ( a_expr )
...
PARTITION BY HASH ( a_expr )
PARTITIONS num_partitions;

Unless someone comes up with a maintenance plan for stable hashfunctions, we should probably not dare look into this yet.

What would cover the common use case of per-day quals and drops over an extended history period, say six or nine months? You don't get quite the same locality of reference, generally, with an unpartitioned table, due to slop in the arrival of rows. Ideally, you don't want to depend on an administrator, or even an administrative script, to continually intervene in the structure of a table, as would be the case with partitioning by range, and you don't want to coalesce multiple dates, as an arbitrary hash might do. What the administrator would want would be to decide what rows were too old to keep, then process (e.g. archive, summarize, filter) and delete them.

Suppose that the number of partitions were taken as a hint rather than as a naming modulus, and that any quasi-hash function had to be specified explicitly (although storage assignment could be based on a hash of the quasi-hash output). If a_expr were allowed to include a to-date conversion of a timestamp, day-by-day partitioning would fall out naturally. If, in addition, single-parameter (?) functions were characterized as range-preserving and order-preserving, plan generation could be improved for time ranges on quasi-hash-partitioned tables, without a formal indexing requirement.

There are cases where additional partition dimensions would be useful, for eventual parallelized operation on large databases, and randomizing quasi-hash functions would help. IMHO stability is not needed, except to the extent that hash functions have properties that lend themselves to plan generation and/or table maintenance.

It is not clear to me what purpose there would be in dropping a partition. This would be tantamount to deleting all of the rows in a partition, if it were analogous to dropping a table, and would require some sort of compensatory aggregation of existing partitions (in effect, a second partitioning dimension), if it were merely structural.

Perhaps I'm missing something here.

David Hudson

On Fri, 2009-10-30 at 10:03 +0200, Peter Eisentraut wrote:

I can't help but wonder if the period type might better be a generic
container for pairs of scalar, totally-ordered types.

That would be ideal. However, it doesn't really look like our type
system was built to handle that kind of thing.

We could use typmod, I suppose, but even that won't hold a full Oid. Any
ideas/suggestions?

Regards,
Jeff Davis

Jeff Davis wrote:

On Fri, 2009-10-30 at 10:03 +0200, Peter Eisentraut wrote:

I can't help but wonder if the period type might better be a generic
container for pairs of scalar, totally-ordered types.

That would be ideal. However, it doesn't really look like our type
system was built to handle that kind of thing.

We could use typmod, I suppose, but even that won't hold a full Oid. Any
ideas/suggestions?

Wait, it doesn't? A typmod is a 32-bit integer, like Oids. Am I missing
something?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2009-10-30 at 19:12 +0200, Heikki Linnakangas wrote:

Wait, it doesn't? A typmod is a 32-bit integer, like Oids. Am I missing
something?

Oid is unsigned, typmod is signed. We might be able to get away with it,
but -1 is treated specially in some places outside of the type-specific
functions, e.g. exprTypmod().

I haven't looked at all of these places yet, so maybe a few simple
changes would allow us to treat typmod as a full 32 bits. Or perhaps it
could just be expanded to a signed 64-bit int. What do you think?

Regards,
Jeff Davis