> 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
