PostgreSQL database locks

All we need is an easy explanation of the problem, so here it is.

One of our CRM applications has a PostgreSQL database. Their db guys created 10 views that queries this production PostgreSQL database

  • We plan to do a SELECT query via a PHP script on each of these views at 06:15 every morning and it takes around 1 hour to execute. Part of this process is also to save the query result into a CSV
  • 06:15 is pretty close to when the users actually start using this CRM application and pretty sure these queries on the views will at times overlap on CRM application usage by users
  • Does SELECT queries on views on PostgreSQL databases lock the tables and in turn then cause any issues on the application?
  • And also the other way around if a table is locked due to application usage will this cause any issues in terms of querying the view? Or will it just skip that row?
  • I suppose it would also be best practice to monitor server load during this time? simply use TOP command on linux instance?
  • Any other concerns?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

A query that only reads data will not block normal user activity. Readers don’t block writers and vice versa.

However, it may consume substantial resouces on the database server, which could impair performance for the users.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply