Re: What needs to be done for real Partitioning?

On Sat, Mar 19, 2005 at 12:02:38PM -0800, Josh Berkus 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}

ALTER TABLE might be cleaner, perhaps?

> -- 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.

Would the SELECT also look at the parent table, if it weren't empty? I can
think of cases where that'd be useful, especially if an existing table
can be partitioned with an ALTER TABLE.

This covers almost everything I'd want from table partitioning in the
short term.

> 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.

Multi-table indexes would be nice too, though that leads to some problems
when a partition is truncated or dropped, I guess.

Cheers,
Steve