PostgreSQL tips and tricks: foreign keys

Are you using PostgreSQL in production? Have you ever experienced unexpected behaviors? At Dhatim, we are pushing PostgreSQL to the limit and we had to solve some difficult issues the hard way. (Just kidding: PostgreSQL’s limits are yet to be discovered!)

We would like to share these stories in a series of posts. Let’s start with a simple one.

Issue

While this is documented at the end of the paragraph describing foreign key constraints, it is easy to miss out:

Declaration of a foreign key constraint does not automatically create an index on the referencing columns.

When you were using MySQL – looks like more people are moving from MySQL to PostgreSQL than the other way around –, you did not have to care because referencing columns are always indexed.

If you do not carefully review your schema and forget about missing indexes on FK referencing columns, you may end up with slow queries. As always, this will probably happen when the application is running with the large production dataset.

Resolution

It may be useful to create indexes on FK referencing columns if the FK constraint defines an action for ON UPDATE or ON DELETE: the index will speed up the required reverse lookup when performing the DELETE or UPDATE action.

Of course, if the referencing columns are filtered directly or indirectly in a SELECT query, an index may help as well.

As always with indexes, they are not free and they will slow down insertions. They may not help if the number of rows is not that big. When in doubt, measure, analyze and choose wisely!

comments powered by Disqus