3 ms·
Out of curiosity: Would appreciate if others can share what other things like AccessExclusiveLock should postgres users beware of? What I already know - Uniqu
by shivasaxena 1y ago
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`
- franckpachot 1y agoCan you provide more details? Inserting with unique indexes do not lock the table. Case statements are ok in where clause, use expression indexes to index it
- hans_castorp 1y ago> 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.
- 1a527dd5 1y agohttps://pglocks.org/?pglock=AccessExclusiveLock https://pglocks.org/?pglock=AccessExclusiveLock is my go to reference. My other reference for a slightly different problem is https://www.thatguyfromdelhi.com/2020/12/what-postgres-sql-causes-table-rewrite.html https://www.thatguyfromdelhi.com/2020/12/what-postgres-sql-c...