vacuum and table locking

From: John Scalia <jayknowsunix(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: vacuum and table locking
Date: 2015-04-03 15:59:16
Message-ID: 551EB8D4.7050406@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I have a question regarding vacuum and locking, but since I currently don't have access to my servers (maintenance activities), I can't verify on my own. I know that VACUUM has at
least four variants. OK, actually two with ability to add ANALYZE after the operation. Here we go:

VACUUM (VACUUM ANALYZE) - shouldn't need to lock the table(?). I'm looking at the Douglas' book "PostgreSQL" on page 804 where it claims the operation does not require exclusive
access.

VACUUM FULL (VACUUM FULL ANALYZE) - will need to lock the table, but I'm thinking this may only take a couple of milliseconds as I think this builds a temporary table from the one
being vacuumed and then when complete, drops the old table and renames the temporary. So the lock should only be during this move, or does it lock the table for the entirety?

Is this correct? And how does it then resolve say another deletion in the table that occurs while the VACUUM is in operation? I hope that is clearly expressed.
--
Jay

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Payal Singh 2015-04-03 16:15:12 Re: vacuum and table locking
Previous Message Alexey Klyukin 2015-04-02 15:01:11 Re: base backup from the standby without pg_basebackup