From: PFC <lists@boutiquenumerique.com>
To: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: What needs to be done for real Partitioning?
Date: Sat, 19 Mar 2005 23:24:39 +0100
Message-ID: <op.snwojdwith1vuj@localhost> (raw)
In-Reply-To: <200503191202.38089.josh@agliodbs.com>
References: <200503191202.38089.josh@agliodbs.com>
This is really great !
Think about altering the partitioning (this is quite complex) : imagine a
table split in several partitions "archive" and "current" where a row is
moved from current to archive when it will not be updated anymore.
Sometimes you can partition on a simple numeric value, or even a boolean
value in this case. Other times you'd have to partition on a date,
(current month, current year, archive...). So, how to move the partition
between the two tables so that the oldest rows in the current month table
are moved to the current year table at the end of each month ?
Some ideas :
hidden field (like oid was) to indicate in which partition the tuple is ?
On Sat, 19 Mar 2005 21:02:38 +0100, Josh Berkus <josh@agliodbs.com> wrote:
> Folks,
>
> I may (or may not) soon have funding for implementing full table
> partitioning
> in PostgreSQL. I thought it would be a good idea to discuss with people
> here
> who are already using pseudo-partitioning what things need to be added to
> Postgresql in order to make full paritioning a reality; that is, what do
> other databases do that we don't?
>
> Implementations are seperated into phases I and II, II being
> harder-and-optional-stuff that may get done later, I being essential
> features.
>
> Ph. I
> -- CREATE TABLE ... WITH PARTITION ON {expression}
> ---- should automatically create expression index on {expression}
> -- INSERT INTO should automatically create new partitions where necessary
> ---- new tables should automatically inherit all constraints,
> indexes,
> keys of "parent" table
> -- UPDATE should automatically move rows between partitions where
> applicable
> -- Query Planner/Executor should be improved to not always materialize
> paritioned tables used in subqueries and joins.
>
> Ph. II
> -- Foreign Keys to/from partitioned tables should become possible
> -- Query Planner/Executor should be improved to only join partitions
> which are
> compliant with the query's WHERE or JOIN clauses where reasonable
> -- DELETE FROM should automatically drop empty partitions
> -- setting of WITH PARTITION ON {expression} TABLESPACE should
> automatically
> create a new tablespace for each new partition and its indexes.
> -- It should be possible to create new, empty partitions via a CREATE
> TABLE
> PARTITION OF {table} ON {value} expression.
>
> All syntax above is, of course, highly debatable.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com, josh@agliodbs.com
Subject: Re: What needs to be done for real Partitioning?
In-Reply-To: <op.snwojdwith1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox