Production database locking issue — help!

Our Postgres instance is hitting lock_timeout under peak load, and we're not sure where to start digging. Here's what we've tried: tuned max_connections, added indexes on foreign keys, but the contention persists during batch jobs. Would love a fresh pair of eyes on our schema or advice on lock escalation strategies in Postgres 15+.

First, find exactly what's blocking:

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_locks.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_locks.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.PG_locks blocking_locks ON
    blocked_locks.locktype = blocking_locks.locktype AND
    blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database AND
    blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation AND
    blocking_locks.granted = false;

Common culprits:

• Missing index on a foreign key (triggers full table locks)
• Long-running transaction holding an exclusive lock
• Upsert (ON CONFLICT) creating serialization failures under high concurrency
• Autovacuum starvation blocking writes

First, confirm which lock type it is:

• Shared (S): normal reads — multiple S locks compatible with each other
• Row Exclusive (RowExclusiveLock): UPDATE/DELETE on a row — blocks other updates to that row
• Access Exclusive (AccessExclusiveLock): ALTER TABLE ADD COLUMN / DROP INDEX — blocks everything including SELECTs

To see what's blocking right now in Postgres:

SELECT pid, state, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

And to find the chain of blockers (Postgres 9.6+):

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks

oh sorry to interrupt i know this is clearly a serious problem but i just started learning sql last week and i keep getting lock timeouts on my local machine and i can't figure out why — is it something with isolation levels or should i be using transactions differently? also what does 'deadlock detected' actually mean in practice. sorry for asking such dumb stuff here