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&sid=396545433
