Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ye40T-0004tt-Et for pgsql-admin@arkaria.postgresql.org; Fri, 03 Apr 2015 15:59:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Ye40S-00044H-Il for pgsql-admin@arkaria.postgresql.org; Fri, 03 Apr 2015 15:59:40 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Ye40R-00044A-DS for pgsql-admin@postgresql.org; Fri, 03 Apr 2015 15:59:39 +0000 Received: from mail-yh0-x22e.google.com ([2607:f8b0:4002:c01::22e]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Ye40L-0005F1-Re for pgsql-admin@postgresql.org; Fri, 03 Apr 2015 15:59:35 +0000 Received: by yhfw71 with SMTP id w71so29123785yhf.2 for ; Fri, 03 Apr 2015 08:59:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject :content-type:content-transfer-encoding; bh=NZio4D1kcRAg76bZmRlttQUToXFvI+HRtSAd+PRS/vU=; b=E5tzHD/nPoVTfZImveBGqlzAwsXJoWjRSt9MO4ypWWfZQ2yFdtVTX9imOQ0LhVkdA0 a6UH7GYH3mT4J9eIDARvE/IfHqNc+yKRm060czQauLqy/IxuOHgM3I8bdMBfmdP+dY3U /BFDXplVF2YaMDOYeIxYgcD01/ihMB2F54o/+Wuyx6COBGJt9t6HPUc7aLKwZjoajSM0 8r2CV7CUofDM5inoSWrFGJyST4G+14CzGSNzMEu80fikWd6NJ5Gimv1pO0FWJUOwnnLm gcalOYdpWljYOTXmqDlrRsAxTo+olwB4FXACnbJifaTMcPOp5uLXNuIgySF3LELterNB I2Jw== X-Received: by 10.236.14.168 with SMTP id d28mr2817649yhd.3.1428076772223; Fri, 03 Apr 2015 08:59:32 -0700 (PDT) Received: from [192.168.254.2] (h77.89.102.166.static.ip.windstream.net. [166.102.89.77]) by mx.google.com with ESMTPSA id d16sm6562707yha.41.2015.04.03.08.59.30 for (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 03 Apr 2015 08:59:31 -0700 (PDT) Message-ID: <551EB8D4.7050406@gmail.com> Date: Fri, 03 Apr 2015 11:59:16 -0400 From: John Scalia User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.5.0 MIME-Version: 1.0 To: "pgsql-admin@postgresql.org" Subject: vacuum and table locking Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org 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 o= wn. 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 th= inking 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 t= able that occurs while the VACUUM is in operation? I hope that is clearly e= xpressed. -- Jay --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin