Postgresql partitioning problems & suggested solutions

We are presently investigating to migrate large

(>10 TB) databases from Oracle to PostgreSQL. We find the need

for table partitioning and the support of that is not good in

PgSQL. We think the problem might be important enough to reach

out to someone who might help us. Our idea is that a dedicated

partitioning function would not be necessary if:

- foreign keys could be defined to reference views (which in

theory they should, according to C.J.Date.)

- the query optimizer would be able to eliminate union clauses

from select, update and insert statements based on the

partitioning key.

- an index could be built on a view (to make a global index

accross partitions)

With these 3 requirements met, I think all we would need for a

partitioned table would be

CREATE VIEW(a, b, c) Foo AS

SELECT a, b, c FROM Foo_1

UNION ALL

SELECT a, b, c FROM Foo_2

UNION ALL

SELECT a, b, c FROM Foo_3

;

say that (a, b) is the primary key of Foo and (a) is the

primary key of each partition and c is some other column

we would like to index, we could

CREATE INDEX Foo_c_idx ON Foo(c);

Now for

SELECT * FROM Foo WHERE b='2'

it should know to access only Foo_2, I suppose it could be done

with a rule, but that should work even if b='2' is implicitly

given (not just if b = <constant>) is stated explicitly.

Do you think that can be done without too much disturbance in

the pgsql sources?

For another thing, how difficult would it be to provide for a

complete insert&update ability on views? Basically to make the

difference between a table and a view completely transparent?

There is another feature We have often wanted, and think that

can be done with such fully transparent views, i.e., ability

to define "virtual" fields, i.e., one could totally avoid

storing the partition key b (in above example) by:

CREATE VIEW(a, b, c) Foo AS

SELECT a, '1' as b, c FROM Foo_1

UNION ALL

SELECT a, '2' as b, c FROM Foo_2

UNION ALL

SELECT a, '3' as b, c FROM Foo_3

;

We have often wanted to put long constant identifiers into

such "virtual" attributes that are only stored in the metadata

and not redundantly held on disk.

Thanks
Sharmila

____________________________________________________________________________________
Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&amp;sid=396545433