1

I am using Postgresql on CloudSQL (GCP), in version 15. (Running on 4vCPU, 16Gb RAM, with SSD disk)

We are observing an unexpected issue during index creation. While creating an index on a large-ish table (~10M rows), we observe a spike of lock wait on write transactions (not on read) on completely unrelated tables. By "unrelated", I mean that this happens even if the index is created on a table in a different database (same instance) than the tables where lock wait is observed on writes - so presumably they share only low-level postgres ressources as well as the infrastructure ressources.

Essentially I am interested in knowing if this happened to others, and in which conditions. Initially we thought that "lock wait" in the query analytics interface was reported only for transaction locks, but this issue seems to indicate that it also includes wait time for IO ressources. I would be happy if anyone can confirm or point me to a better hypothesis.

Improve this question
3
  • 1) This is the wrong site for this question you should be asking on dba.stackexchange.com. 2) This is probably something you will need to take up with Google tech support, as they control the infrastructure. Commented May 16 at 15:18
  • Good point, I'll try dba.stackexchange. Google tech support is always more tricky, unless you pay for the prohibitive support packages. Commented May 16 at 15:35
  • Be sure to add the SSD size you configured. Cloud SQL storage IOPS&throughput depend on that. Commented May 16 at 19:16
Related questions
191
How to write DataFrame to postgres table
0
Is Aggressive Auto vacuum impacting Query performance?
2
CloudSQL Postgres confused around table grants and owners
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.