Question 234639 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – PG_TOAST tables are creating with AccessExclusiveLock for Temp tables while executing a Postgres Transaction

In one of my Postgres Transaction (executing a procedure), I’m using more than 50 temp tables and for each temp table it is creating pg_toast_temptableOID, pg_toast_temptableOID_index with AccessExclusiveLock and fastpath = false for these pg_toast tables. It is creating slowness in my procedure execution. Please let me know how to improve the Performance.

Note: I’m using temp tables because the incoming data is huge and CTEs will make it very slow.

Tags:

2

Answers


  1. 0
    • Try to reduce the number of temp tables.
    • Use Optimized Queries
    • Use VACUUM and ANALYZE for further optimization of database.
    • Avoiding the use of unnecessary indexes.
    Login or Signup to reply.
  2. 0

    Here are some methods which might solve your problem.

    • Reduce the number of temporary tables and try to use Common table expressions (CTEs).
    • Try to use Access_Exclusive_Lock only when required.
    • Try to use database connection pooling.
    • Analyze the performance of queries by EXPLAIN.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search