PostgreSQL Foreign Keys and Performance

Foreign keys are one of the most important features in PostgreSQL because they protect referential integrity.

They stop your database from drifting into nonsense like:

• orders pointing to customers that do not exist
• tickets pointing to deleted accounts
• child records surviving after the parent relationship stopped making sense
• or application bugs silently creating orphaned rows that you only discover months later

That integrity is usually worth a lot.

But foreign keys are not free.

They add real work during:

• inserts into the child table
• updates to key values
• deletes from the parent table
• and cascading actions such as ON DELETE CASCADE

That is why performance questions around foreign keys are not about:

• “should we avoid foreign keys?”

They are about:

• “how do we keep foreign keys without making writes, deletes, and cascades painfully slow?”

This guide explains exactly that.

The Short Answer

If you want the practical answer first:

• keep foreign keys in most real PostgreSQL systems
• index the referencing columns when deletes, joins, updates, or cascades need them
• do not assume PostgreSQL creates that child-side index automatically
• be especially careful with large delete operations and ON DELETE CASCADE
• and treat foreign key design as both a data-integrity decision and a write-performance decision

That is the core of it.

What PostgreSQL Requires for a Foreign Key

A PostgreSQL foreign key must reference columns that are:

• a primary key
• a unique constraint
• or a non-partial unique index

That matters because the referenced side is always backed by an index-capable uniqueness rule.

So PostgreSQL can efficiently check:

• “does the parent row exist?”

This is why the referenced columns are already indexed in practice when the foreign key is valid.

Example

create table accounts (
  id bigint primary key,
  name text not null
);

create table projects (
  id bigint primary key,
  account_id bigint not null references accounts(id),
  name text not null
);

In this example:

• accounts(id) is automatically indexed because it is a primary key
• projects(account_id) is not automatically indexed just because it is a foreign key

That distinction is the source of many foreign-key performance problems.

The Most Important Performance Rule

Here is the rule that matters most:

PostgreSQL does not automatically create an index on the referencing columns.

It is often a very good idea to create one yourself.

Why?

Because when a parent row is:

• deleted
• or its referenced key is updated

PostgreSQL must find matching rows in the child table.

If the child table is not indexed on the foreign key columns, PostgreSQL may need to scan the child table to find those matches.

That can become very expensive on large tables.

Why Child-Side Indexes Matter So Much

Suppose you have this schema:

create table customers (
  id bigint primary key,
  name text not null
);

create table invoices (
  id bigint primary key,
  customer_id bigint not null references customers(id),
  total_cents bigint not null
);

Now imagine you run:

delete from customers
where id = 42;

PostgreSQL must check:

• are there any invoices referencing customer 42?
• if yes, what should happen based on the foreign key action?

If invoices(customer_id) is not indexed, that check can require a scan of the invoices table.

That is exactly why child-side indexing matters.

Stronger version

create index idx_invoices_customer_id
on invoices (customer_id);

That one index can make parent deletes, parent key updates, and many joins much more efficient.

Do Foreign Keys Slow Down Inserts?

Yes, but usually not in the dramatic way people fear.

When you insert into the child table, PostgreSQL must check that the referenced parent row exists.

Example:

insert into invoices (id, customer_id, total_cents)
values (1, 42, 109900);

PostgreSQL checks:

• does customers(id = 42) exist?

Because the parent key is backed by a primary key or unique index, that lookup is normally efficient.

So child inserts do have foreign-key overhead, but the parent-side existence check is usually not the part that hurts most in well-designed systems.

Where insert overhead becomes more noticeable

• extremely write-heavy systems
• bulk loads
• very large batches
• systems with many overlapping foreign keys on the same write path
• workloads where every row touches many related tables

Even then, the question is usually not:

• “should we remove the foreign keys?”

It is more often:

• “can we batch writes better?”
• “can we defer validation where appropriate?”
• “can we design the write path more carefully?”

Deletes Are Often the Real Performance Pain Point

Foreign key overhead becomes much more visible on deletes from the parent table.

That is because PostgreSQL must check whether matching child rows exist and then apply the configured rule:

• NO ACTION
• RESTRICT
• CASCADE
• SET NULL
• SET DEFAULT

Example

delete from customers
where id = 42;

Depending on the constraint, PostgreSQL may need to:

• reject the delete
• delete child rows
• set child values to null
• or update them to defaults

All of those require finding the matching child rows.

That is why child-side foreign key indexes are so important.

ON DELETE CASCADE Is Convenient, But Not Free

ON DELETE CASCADE is one of the most useful foreign-key actions in PostgreSQL.

It can be exactly right for relationships like:

• account → sessions
• project → tasks
• order → order_items
• post → post_comments

Example:

create table order_items (
  id bigint primary key,
  order_id bigint not null references orders(id) on delete cascade,
  sku text not null
);

Now if you delete an order, PostgreSQL automatically deletes the matching order items.

That is very useful.

But it is not free.

On large datasets, cascades can:

• touch many rows
• create large write bursts
• generate WAL
• trigger more foreign-key checks further down the tree
• and create lock and latency pressure if the cascade graph is large

Practical rule

Use ON DELETE CASCADE when the lifecycle is truly dependent.

But still index the child foreign key columns, and be careful with:

• large parent deletes
• bulk cleanup jobs
• and deep cascade chains

ON UPDATE CASCADE Needs the Same Respect

Developers think about delete cost more often than update cost, but parent key updates can also be expensive.

If the referenced key changes, PostgreSQL must find the child rows and apply the update behavior.

Example:

create table users (
  id bigint primary key
);

create table api_keys (
  id bigint primary key,
  user_id bigint not null references users(id) on update cascade
);

If users.id changes, PostgreSQL has to update the child rows too.

In many systems, parent keys should rarely change anyway. That is one reason surrogate keys are so popular.

Practical lesson

If the referenced key is stable, foreign key maintenance gets easier. If the referenced key changes often, foreign-key update cost becomes more relevant.

Foreign Keys and Joins

Foreign keys do not directly make joins faster. But the same indexes that help foreign-key checks often help joins too.

Example:

select i.id, i.total_cents, c.name
from invoices i
join customers c on c.id = i.customer_id
where i.customer_id = 42;

If invoices(customer_id) is indexed, that often helps both:

• foreign-key enforcement patterns
• and query performance patterns

This is another reason the child-side index is so often the right choice. It pulls double duty:

• integrity-sensitive write paths
• and read-side join/filter paths

The Classic Mistake

A very common PostgreSQL schema mistake is this:

create table parent (
  id bigint primary key
);

create table child (
  id bigint primary key,
  parent_id bigint not null references parent(id)
);

and then stopping there.

The foreign key exists. Integrity is protected. But the child side has no index.

That looks harmless until:

• parent deletes become slow
• parent key updates become slow
• join-heavy queries degrade
• or cascades start scanning large child tables

Better version

create index idx_child_parent_id
on child (parent_id);

That simple addition often prevents a lot of future pain.

Composite Foreign Keys Need Composite Thinking

If your foreign key spans multiple columns, the index design should usually match that shape.

Example:

create table accounts (
  tenant_id bigint not null,
  id bigint not null,
  primary key (tenant_id, id)
);

create table projects (
  tenant_id bigint not null,
  id bigint not null,
  account_id bigint not null,
  primary key (tenant_id, id),
  foreign key (tenant_id, account_id) references accounts(tenant_id, id)
);

In this case, a matching child-side index often looks like:

create index idx_projects_tenant_account
on projects (tenant_id, account_id);

That is usually much better than indexing only one of the columns.

Practical rule

For composite foreign keys, index the child side in the same logical order as the foreign key when that is also how your queries and parent-side checks will access it.

Foreign Keys and Multi-Tenant PostgreSQL Design

This matters a lot in SaaS systems.

If your schema is tenant-scoped, foreign keys often look like:

• (tenant_id, account_id)
• (tenant_id, user_id)
• (tenant_id, project_id)

That is good because it helps make tenant boundaries explicit.

But it also means your indexes need to reflect tenant-aware access patterns.

Example

create index idx_tasks_tenant_project
on tasks (tenant_id, project_id);

This helps:

• foreign key validation and parent deletes
• tenant-scoped joins
• tenant-scoped lookups
• and safer query design overall

For SaaS systems, foreign-key performance and multi-tenant indexing strategy are tightly connected.

Are Foreign Keys Bad for Bulk Loads?

Not necessarily, but they do add overhead during large imports.

When loading large volumes of child rows, PostgreSQL still needs to validate that the referenced parents exist.

That means bulk loads with many foreign keys can be slower than raw unconstrained loading.

Practical approaches

Depending on the situation, teams may use:

• staged loading tables
• ordered load sequences
• adding constraints later
• or NOT VALID plus later validation on existing data

A very useful PostgreSQL feature

When adding a foreign key to an existing large table, PostgreSQL supports:

alter table child
add constraint child_parent_fkey
foreign key (parent_id) references parent(id)
not valid;

This skips the initial full-table validation scan of existing rows, while still enforcing the constraint for new inserts and updates going forward.

Later, you can validate it:

alter table child
validate constraint child_parent_fkey;

This is extremely useful when adding foreign keys to large live tables.

Why NOT VALID Can Be a Big Deal

On a large production table, adding a fully validated foreign key can be disruptive.

PostgreSQL’s ALTER TABLE docs make two important points here:

• NOT VALID skips the expensive verification of existing rows at add time
• ADD FOREIGN KEY requires only SHARE ROW EXCLUSIVE lock, not the harsher lock many people assume

That makes phased foreign-key rollout much more realistic on big systems.

Practical use cases

• retrofitting integrity on legacy schemas
• production migrations on large tables
• cleaning up schemas that started too loosely
• gradually improving data correctness without a huge one-shot validation event

Foreign Keys Can Be Deferred

Foreign keys can also be declared DEFERRABLE.

That means their checks can be postponed until transaction commit instead of being enforced at the end of every statement.

Example:

create table child (
  id bigint primary key,
  parent_id bigint not null,
  constraint child_parent_fkey
    foreign key (parent_id)
    references parent(id)
    deferrable initially immediate
);

Inside a transaction, you can then do:

set constraints child_parent_fkey deferred;

When this is useful

Deferred foreign keys are especially useful when:

• the transaction temporarily violates the relationship mid-flight
• circular or mutually dependent insert/update flows exist
• batch changes need to settle into a valid final state by commit

Performance note

This is usually more about correctness and transaction design than speed, but it is still an important performance-adjacent feature because it changes when the work is done.

Foreign Keys and Locking

Foreign keys also matter for locking behavior.

They are not usually the biggest locking story in a system, but they do participate in:

• parent/child write interactions
• delete behavior
• cascading actions
• constraint-trigger work
• migration-time constraint changes

This matters most when:

• large parent deletes happen concurrently with child writes
• cascading operations touch many rows
• or large integrity changes are introduced on a live system

The main practical lesson is:

• foreign keys are part of write-path design
• not just schema semantics

When You Might Skip a Child-Side Index

Most of the time, indexing the child foreign key is a good idea. But not every foreign key column must be indexed.

You might choose not to index it when:

• the child table is tiny
• parent deletes are extremely rare
• parent key updates never happen
• joins on that key are rare
• or the workload genuinely does not justify the write and storage cost

Practical rule

Do not index foreign key columns by superstition. Index them because:

• deletes, updates, joins, or cascades need help

That said, in real production systems, many child-side foreign key columns do end up being worth indexing.

Common PostgreSQL Foreign Key Performance Problems

1. Missing child-side index

This is the most common problem by far.

2. Large cascades without planning

Convenient schema design becomes expensive operational behavior.

3. Bulk parent deletes on unindexed child tables

This is a classic slow-delete trap.

4. Composite foreign keys with weak or partial indexing

The access path does not match the relationship shape.

5. Assuming PostgreSQL created the child index automatically

It did not.

6. Using mutable referenced keys too casually

Parent key changes are more expensive than stable surrogate key patterns.

Practical Design Patterns

Pattern 1: Standard parent-child relationship

create table customers (
  id bigint primary key
);

create table orders (
  id bigint primary key,
  customer_id bigint not null references customers(id)
);

create index idx_orders_customer_id
on orders (customer_id);

This is the safe default shape for many systems.

Pattern 2: Cascade relationship with proper indexing

create table projects (
  id bigint primary key
);

create table tasks (
  id bigint primary key,
  project_id bigint not null references projects(id) on delete cascade
);

create index idx_tasks_project_id
on tasks (project_id);

If you are going to cascade, make sure the child lookup is efficient.

Pattern 3: Multi-tenant composite foreign key

create table accounts (
  tenant_id bigint not null,
  id bigint not null,
  primary key (tenant_id, id)
);

create table users (
  tenant_id bigint not null,
  id bigint not null,
  account_id bigint not null,
  primary key (tenant_id, id),
  foreign key (tenant_id, account_id) references accounts(tenant_id, id)
);

create index idx_users_tenant_account
on users (tenant_id, account_id);

This is a strong SaaS-oriented design because the tenant boundary stays explicit and indexable.

How to Think About the Trade-Off

Foreign keys trade a bit of write cost for a lot of integrity value.

For most PostgreSQL systems, that trade is worth it.

The question is not whether foreign keys have overhead. They do.

The real question is whether the schema and indexing are designed so that overhead stays acceptable.

That usually means:

• referenced side already properly unique
• referencing side indexed when the workload needs it
• cascade behavior intentional
• delete/update patterns understood
• and large migrations added carefully

FAQ

Do foreign keys slow down PostgreSQL?

Yes, a little, because PostgreSQL must check referential integrity during writes. But the overhead is usually acceptable, and the biggest performance problems usually come from missing indexes on the referencing table rather than from the foreign key itself.

Should I index foreign key columns in PostgreSQL?

Usually yes on the referencing side when parent deletes, parent key updates, joins, or cascades are common. PostgreSQL does not create that index automatically, so you normally add it yourself.

Does PostgreSQL automatically index foreign keys?

PostgreSQL automatically indexes the referenced side through the required primary key, unique constraint, or non-partial unique index. It does not automatically create an index on the referencing columns.

Are foreign keys bad for SaaS or high-scale PostgreSQL apps?

No. They are still valuable in SaaS and high-scale systems, but they need good indexing, careful delete behavior, and awareness of how multi-tenant queries and cascades will behave under load.

Conclusion

Foreign keys are not the enemy of PostgreSQL performance.

Unplanned foreign keys are.

If you keep the integrity benefits but forget the write-path reality, you get:

• slow deletes
• expensive cascades
• weak join performance
• and confusing production behavior

But if you design them properly, foreign keys are usually a strength.

That means:

• keep them
• index the child side when the workload needs it
• respect cascade cost
• use NOT VALID and later validation for large retrofits
• and treat foreign key design as part of performance design, not separate from it

That is the PostgreSQL foreign key performance mindset that usually holds up best over time.