←back to thread

Postgres LISTEN/NOTIFY does not scale

(www.recall.ai)
570 points davidgu | 1 comments | 07 Jul 25 14:05 UTC | HN request time: 0.208s | source
Show context
shivasaxena ◴[10 Jul 25 21:17 UTC] No.44525717[source]▶
Out of curiosity: Would appreciate if others can share what other things like AccessExclusiveLock should postgres users beware of?

What I already know

- Unique indexes slow inserts since db has to acquire a full table lock

- Case statements in Where break query planner/optimizer and require full table scans

- Read only postgres functions should be marked as `STABLE PARALLEL SAFE`

replies(3): >>44525810 #>>44528659 #>>44529294 #
1. hans_castorp ◴[11 Jul 25 05:27 UTC] No.44528659[source]▶
> Unique indexes slow inserts since db has to acquire a full table lock

An INSERT never results in a full table lock (as in "the lock would prevent other inserts or selects on the table)

Any expression used in the WHERE clause that isn't indexed will probably result in a Seq Scan. CASE expressions are no different than e.g. a function call regarding this.

A stable function marked as "STABLE" (or even immutable) can be optimized differently (e.g. can be "inlined"), so yes that's a good recommendation.