Re: Add SKIP LOCKED to VACUUM and ANALYZE

From: "Bossart, Nathan" <bossartn(at)amazon(dot)com>
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, "Andres Freund" <andres(at)anarazel(dot)de>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Lyes Ameddah <lyes(dot)amd(at)gmail(dot)com>
Subject: Re: Add SKIP LOCKED to VACUUM and ANALYZE
Date: 2018-10-03 16:20:42
Message-ID: A6ED50E1-83F7-4442-82F1-175057D11E11@amazon.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 10/3/18, 12:54 AM, "Michael Paquier" <michael(at)paquier(dot)xyz> wrote:
> - Inheritance trees have the same problem, so it would be nice to
> mention them as well.

Done.

> - One workaround, which we could document (?), would be to list leaves
> of a partition tree individually so as their locks are checked one by
> one.

Done.

> + Specifies that <command>ANALYZE</command> should not wait for any
> + conflicting locks to be released: if a relation cannot be locked
>
> Perhaps it would be more precise to tell when "beginning to work on a
> relation" because the restrictions in row samplings?

Done.

Here is what I have so far for the docs:

Specifies that ANALYZE should not wait for any conflicting
locks to be released before beginning work on a relation: if a
relation cannot be locked immediately without waiting, the
relation is skipped. Note that even with this option, ANALYZE
may still block when opening the relation's indexes or when
acquiring sample rows from leaf partitions, table inheritance
children, and some types of foreign tables. Also, while
ANALYZE ordinarily processes all leaf partitions of specified
partitioned tables, this option will cause ANALYZE to skip all
leaf partitions if there is a conflicting lock on the
partitioned table. Therefore, it is recommended to list each
leaf partition individually in the ANALYZE command when using
the SKIP_LOCKED option.

Specifies that VACUUM should not wait for any conflicting
locks to be released before beginning work on a relation: if a
relation cannot be locked immediately without waiting, the
relation is skipped. Note that even with this option, VACUUM
may still block when opening the relation's indexes. Also,
while VACUUM ordinarily processes all leaf partitions of
specified partitioned tables, this option will cause VACUUM to
skip all leaf partitions if there is a conflicting lock on the
partitioned table. Therefore, it is recommended to list each
leaf partition individually in the VACUUM command when using
the SKIP_LOCKED option.

Nathan

Attachment Content-Type Size
vacuum-skip-locked-v11.patch application/octet-stream 15.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-10-03 16:22:13 Re: Performance improvements for src/port/snprintf.c
Previous Message Andres Freund 2018-10-03 16:14:54 Re: Performance improvements for src/port/snprintf.c