Re: vacuum and table locking

>
> 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.

I think pg_repack does that, and hence can remove bloat without locking the
whole table. https://github.com/reorg/pg_repack

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253

On Fri, Apr 3, 2015 at 11:59 AM, John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>