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.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
