PostgreSQL Discussion - Foreign key / performance question

Author

Foreign key / performance question

Nico Callewaert

2006-03-29, 1:28 pm

Hi !,

Is it wise to define foreign keys for referential entegrity ?
Example : I have a customer table with 40 fields. Out of that 40 fields, 10 fields contain information linked to other tables. So, is defining foreign keys for these 10 fields a good idea ? Because from what I understand, for every foreign key, ther
e is an index defined. So, all these indexes has to be maintained. Is that killing performance ? What's the best practise : defining foreign keys or not ?

Thanks a lot,
Nico Callewaert

---------------------------------
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.

Vivek Khera

2006-03-29, 1:28 pm

On Mar 29, 2006, at 12:35 PM, Nico Callewaert wrote:

> Because from what I understand, for every foreign key, there is an
> index defined. So, all these indexes has to be maintained. Is
> that killing performance ? What's the best practise : defining
> foreign keys or not ?

If your application is 100% perfect and you never do any manual
updates to the DB, then who needs FK's?

Show me that your app is 100% perfect first... which there is no way
you can do unless it is a trivial amount of code.

But just defining FK's won't define an index. You have to do that
manually else suffer performance. However, if the referenced table
is only a few hundred rows or less, there is not much point in an
index on that column for that table.

Personally, I live by FKs since I value my data to be correct.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Stephan Szabo

2006-03-29, 1:28 pm

On Wed, 29 Mar 2006, Nico Callewaert wrote:

> Is it wise to define foreign keys for referential entegrity ?

> Example : I have a customer table with 40 fields. Out of that 40
> fields, 10 fields contain information linked to other tables. So, is
> defining foreign keys for these 10 fields a good idea ? Because from
> what I understand, for every foreign key, there is an index defined.
> So, all these indexes has to be maintained. Is that killing
> performance ? What's the best practise : defining foreign keys or not
> ?

The referencing side of the constraint doesn't need an index, although
it's useful for speeding up deletes or updates to the referenced table
(so, if those operations don't happen or are significantly rare, having
those have to do a sequential scan may be better than the maintenance
cost of the index on the referencing side). The referenced side does need
an index, however that's theoretically the same index that's used to
guarantee the required unique/primary key constraint.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Scott Marlowe

2006-03-29, 1:28 pm

On Wed, 2006-03-29 at 11:35, Nico Callewaert wrote:
> Hi !,
>
> Is it wise to define foreign keys for referential entegrity ?
> Example : I have a customer table with 40 fields. Out of that 40
> fields, 10 fields contain information linked to other tables. So, is
> defining foreign keys for these 10 fields a good idea ? Because from
> what I understand, for every foreign key, there is an index defined.
> So, all these indexes has to be maintained. Is that killing
> performance ? What's the best practise : defining foreign keys or not
> ?

While a foreign key has to point to a field(s) with a unique index on
it, the foreign key itself doesn't require an index. That said,
performance is usually better with it than without it.

If performance is your only consideration, then an SQL database is
probably not your best choice. There are plenty of solutions that can
run faster. They just may not guarantee you data stays coherent. And
sometimes, that's ok. Sometimes you have a margin of error in your data
that means you can lost a few bits here and there and the system is
still allright. (i.e. weather monitoring and such)

However, if your data is critical, and even a single error is a bad
thing (i.e. accounting, airline reservations, medical, and so on) or
possibly even deadly.

>From a performance perspective, I haven't found that FK/PK is the
problem so much as extreme normalization. When you have to join 100+
tables for every request, your performance may not be as fast as you'd
like. Setting up fk/pk relations for these 100 tables, however, almost
never makes them slower, unless they're set up poorly.

Occasionally you'll see someone used mismatched types in a FK/PK
relationship (i.e. int -> text) or other strange things. That can cause
issues.

I'd suggest benchmarking your issue, and seeing what kind of performance
you get in your schema with and without fk/pk references.

and if you do decide that going without fks are fine, then don't forget
to factor in your daily / weekly / monthly / yearly data cleaning
festivals...

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links