Over a million developers have joined DZone.

The Performance Price of Foreign Keys in PostgreSQL

· Performance Zone

Discover 50 of the latest mobile performance statistics with the Ultimate Guide to Digital Experience Monitoring, brought to you in partnership with Catchpoint.

If you want to optimize the performance of your PostgreSQL tables, you might benefit from looking in places you wouldn't ordinarily expect. For example, your foreign keys.

According to Shaun Thomas in this recent post, foreign keys can have a considerable impact on performance. Ordinarily they're a good thing - Thomas acknowledges that - but there are certain design choices in PostgreSQL that can complicate them:

In PostgreSQL, every foreign key is maintained with an invisible system-level trigger added to the source table in the reference. At least one trigger must go here, as operations that modify the source data must be checked that they do not violate the constraint.

Each trigger, Thomas says, requires some overhead, and as the number of triggers increases, so does the cost. Thomas offers an example query to demonstrate where this kind of thing becomes an issue - not really a practical or real-world example, though - but the performance hit is substantial:

. . . after merely five foreign keys, performance of our updates drops by 28.5%. By the time we have 20 foreign keys, the updates are 95% slower!

This is not to say that you shouldn't use foreign keys, Thomas says, but that you should be careful with them and avoid abusing them. In other words, if you don't need a foreign key, you should probably just leave it out. Check out the Thomas' post for all the details.


Is your APM strategy broken? This ebook explores the latest in Gartner research to help you learn how to close the end-user experience gap in APM, brought to you in partnership with Catchpoint.

Topics:

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}