From: John Scalia <jayknowsunix@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: vacuum and table locking
Date: Fri, 03 Apr 2015 11:59:16 -0400
Message-ID: <551EB8D4.7050406@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com
Subject: Re: vacuum and table locking
In-Reply-To: <551EB8D4.7050406@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox