PostgreSQL database locks

postgresql

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?

Best Answer

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.

Related Solutions

Postgresql – View Postgresql Memory Usage

Memory usage in linux in general and for Postgres in particular is a pretty complex subject, a good starting point is Bruce Momjians blog covering the subject and the usage of smem. It is well worth following the links in Chris Seibenmanns blog on the subject.

PostgreSQL – How to Atomically Replace Table Data

Right, the TRUNCATE TABLE command you are performing "... acquires an ACCESS EXCLUSIVE lock on each table it operates on", so in the first SQL block you posted, any other clients attempting to access the table after that time will be blocked until your INSERT finishes and you COMMIT.

You can use the same workaround as in your MySQL-specific code; Postgres supports roughly the same syntax and will have similar locking behavior. To wit:

BEGIN;
-- You probably want to make sure that no one else is
-- INSERT / UPDATE / DELETE'ing from the original table, otherwise
-- those changes may be lost during this switchover process. One way
-- to do that would be via:
-- LOCK TABLE "table" IN SHARE ROW EXCLUSIVE mode;
CREATE TABLE "table_new" (LIKE "table");
INSERT INTO "table_new" ...;

-- The ALTER TABLE ... RENAME TO command takes an Access Exclusive lock on "table",
-- but these final few statements should be fast.
ALTER TABLE "table" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table";
DROP TABLE "table_old";

COMMIT;

Extra bonus: Postgres actually supports transactional DDL, unlike MySQL, so in case you need to ROLLBACK the above transaction, you may safely do so.

Best Answer

Related Solutions

Postgresql – View Postgresql Memory Usage

PostgreSQL – How to Atomically Replace Table Data

Related Question