From: | "Daniel Staal" <DStaal(at)usa(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Partitioning and foreign keys. |
Date: | 2009-05-04 17:13:28 |
Message-ID: | 24855.152.121.16.49.1241457208.squirrel@www.magehandbook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | pal kar | 2009-05-04 20:52:23 | hi |
Previous Message | Fred Moseley | 2009-05-01 18:07:29 | Re: Upgrading to Postgresql 8 on RHEL 3 (Release 9 Shrike) |