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


 SELECT a, b, c FROM Foo_2


 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


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


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


 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.


Be a better Heartthrob. Get better relationship answers from someone who knows. 
Yahoo! Answers - Check it out. 

Reply via email to