Intermediate LevelPostgreSQL
How does foreign key validation lock parent vs child tables during updates?
❓ Interview Prompt: Explain Foreign Key locking behavior, row-level locks on parent table, locking child tables without indexes, and avoiding deadlocks.
Updating or deleting primary key rows in a parent table requires PostgreSQL to check referencing foreign keys in child tables:
Lock Contention Pitfall:
If foreign key columns in child tables are NOT INDEXED, PostgreSQL must execute a sequential scan on the child table while holding row locks, causing severe deadlocks and lock contention under concurrent writes!
Code Implementation Example
sql
-- ALWAYS create indexes on child table foreign key columns!
CREATE INDEX idx_orders_customer_id ON orders(customer_id);💡Creating indexes on foreign key columns to prevent table lock contention.
🎯 Interview Key Takeaway
Always index foreign key columns in child tables to prevent sequential table scans and lock contention during parent updates.
#Foreign Keys#Locking#Deadlocks#Indexes#Intermediate
Back to all PostgreSQL Questions→