Обсуждение: [PGSQL 8.3.5] How to handle FKs with partitioning?
Hi all. I need to implement something very similar to temporal table partitioning as described in the documentation at chapter 5.9. My issues come from the fact that I have other tables that references (FKs) to the table(s) to be partitioned. Those references are enforced by means of DRI statements (REFERENCES ...). As the table containing the referenced data will not be a single table, will I be forced to drop DRI? The referencing table(s) don't need to be partitioned, though and have also other FKs to other tables. Is there any other solution as I would keep DRI? Thanks in advance. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
On Fri, Dec 19, 2008 at 6:04 AM, Reg Me Please <regmeplease@gmail.com> wrote: > Hi all. > > I need to implement something very similar to temporal table partitioning as > described in the documentation at chapter 5.9. > > My issues come from the fact that I have other tables that references (FKs) to > the table(s) to be partitioned. Those references are enforced by means of DRI > statements (REFERENCES ...). > > As the table containing the referenced data will not be a single table, will I > be forced to drop DRI? > The referencing table(s) don't need to be partitioned, though and have also > other FKs to other tables. > > Is there any other solution as I would keep DRI? Write a trigger. merlin
My idea is that DRI will help during the the JOINs I'll need to make later. Creating a trigger to check the consistence would not help for that case, unless my idea is wrong. In which case I'd follow the great Merlin's hint. So the question is now: do DRI impact on JOINs efficiency? What'd be the gain? The table in question should easily go 20+M rows, possibly up to 50+M a year. The partitioning would ensure about 2M rows per partition and the trigger should work accordingly to this (dynamic) schema. So, along with the loss of efficiency due to the trigger I also would get some other loss because of an external table needed for the partitioning. On Friday December 19 2008 17:15:56 Merlin Moncure wrote: > On Fri, Dec 19, 2008 at 6:04 AM, Reg Me Please <regmeplease@gmail.com> wrote: > > Hi all. > > > > I need to implement something very similar to temporal table partitioning > > as described in the documentation at chapter 5.9. > > > > My issues come from the fact that I have other tables that references > > (FKs) to the table(s) to be partitioned. Those references are enforced by > > means of DRI statements (REFERENCES ...). > > > > As the table containing the referenced data will not be a single table, > > will I be forced to drop DRI? > > The referencing table(s) don't need to be partitioned, though and have > > also other FKs to other tables. > > > > Is there any other solution as I would keep DRI? > > Write a trigger. > > merlin -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
> On Friday December 19 2008 17:15:56 Merlin Moncure wrote: >> On Fri, Dec 19, 2008 at 6:04 AM, Reg Me Please <regmeplease@gmail.com> >> > I need to implement something very similar to temporal table partitioning >> > as described in the documentation at chapter 5.9. >> > >> > My issues come from the fact that I have other tables that references >> > (FKs) to the table(s) to be partitioned. Those references are enforced by >> > means of DRI statements (REFERENCES ...). >> >> Write a trigger. >> On Sat, Dec 20, 2008 at 3:51 AM, Reg Me Please <regmeplease@gmail.com> wrote: > My idea is that DRI will help during the the JOINs I'll need to make later. > > Creating a trigger to check the consistence would not help for that case, > unless my idea is wrong. In which case I'd follow the great Merlin's hint. > > So the question is now: do DRI impact on JOINs efficiency? What'd be the gain? > > The table in question should easily go 20+M rows, possibly up to 50+M a year. > The partitioning would ensure about 2M rows per partition and the trigger > should work accordingly to this (dynamic) schema. > So, along with the loss of efficiency due to the trigger I also would get some > other loss because of an external table needed for the partitioning. DRI (referential integrity) is not a performance option. It is strictly for correctness. PostgreSQL will be able to do joins strictly looking at the data types and the indexes that may be on them. I'm not a huge fan of the built in replication...it can create as many problems as it solves. The main reason to do it is for faster VACUUMs and things like that, or perhaps if you need to frequently add/remove records in bulk (like a log rotation system). merlin