Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to vacuum a table in postgresql version 9.1 which will take between 10 to 12 minutes, and this needs to be done in real-time, in production use.

I just want to know that if any INSERT or UPDATE query comes, will they wait for the lock to lifted, or will they time out?

NOTE: The java code that will fire the query has not specified any timeout. Database is Postgresql version 9.1

share|improve this question

1 Answer 1

up vote 0 down vote accepted

The documentation says the following:

ROW EXCLUSIVE

Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

SHARE UPDATE EXCLUSIVE

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs.

Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE.

There is no conflict between the two lock types involved. This means that neither INSERT/UPDATE blocks VACUUM, nor the other way around. This means furthermore that your queries should succeed without timeouts. Most probably the I/O activity of vacuuming won't do you any harm.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.