I'm in the middle of researching migrating a moderate-sized database
(Currently 720GB) to PostgreSQL, and I stumbled across the idea of
partitions, which look to be a major win for maintainence and speed. (Our
current system actually does something similar, manually.)
I just wanted to check before going to far into re-writing structures and
data exports if it works using foreign keys as constraints?
An example (reduced to relevant columns):
CREATE TABLE 'main_info' (
'Record' char(22) NOT NULL,
'Date_In' date default NULL,
...
PRIMARY KEY ( 'Record' )
);
CREATE TABLE 'attachment_info' (
'Record' char(22) REFERENCES 'Record' ( 'main_info' ),
...
);
What I'd like to do is partition 'main_info' based on 'Date_In', and then
partition 'attachment_info' (and a few other similar tables) into matching
partitions. Can I do that directly (by constraining based on the foreign
key), or will it be a better idea to store the date in 'attachment_info'
as well and partition it on that?
Loading speed is most important here: We load several gigabytes of data
into these tables a day, and only rarely query them. (And then only for
small datasets.)
Daniel T. Staal