public inbox for [email protected]
help / color / mirror / Atom feedRe: Track skipped tables during autovacuum and autoanalyze
17+ messages / 4 participants
[nested] [flat]
* Re: Track skipped tables during autovacuum and autoanalyze
@ 2026-03-24 14:58 Sami Imseih <[email protected]>
2026-03-25 02:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Sami Imseih @ 2026-03-24 14:58 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: pgsql-hackers
Hi,
Thanks for the patch!
> The attached patch add the following fields to pg_stat_all_tables:
> - last_skipped_autovacuum
> - last_skipped_autoanalyze
> - skipped_autovacuum_count
> - skipped_autoanalyze_count
>
> Are there any concerns about exposing this in pg_stat_all_tables, or suggestions
> for a better approach?
I am not sure about the timestamp columns. I am not saying they will
not be useful,
but I think it will be better to just start with counters for this.
The way the views get
used, a dashboard built for tracking the deltas of the counters can easily spot
when there is a spike of skipped autovacuum/autoanalyze count.
Also, for tables that are being autovacuumed and skipped quickly,
the timestamps will just be overwritten.
So, I am +1 on the counters, -1 on the timestamps.
Out of scope for this patch, but I also wonder if we should add another counter,
autovacuum_started_count. If there are other types of failure scenarios such as
corrupt indexes, checksum failures, etc. which terminate the
autovacuum in flight,
we would be able to catch this by looking at the number of autovacuums
started vs completed. The skipped counters in this patch and a started
counter would capture different stages of the autovacuum lifecycle;
skipped means
"never started" (lock contention), while a started-minus-completed delta means
"started but failed." Both are useful signals, but for different reasons.
In terms of the patch:
1/
+ if (AmAutoVacuumWorkerProcess())
+ pgstat_report_skipped_vacuum(relid);
Any reason why this should not also include manual vacuum/analyze?
If someone has a vacuum/analyze script that uses SKIP_LOCKED, and
the operation gets skipped, this should be included in the counter.
this can be done with separate counter fields for autovacuum/autoanalyze and
vacuum/analyze
2/
+ pg_stat_get_skipped_autovacuum_count(C.oid) AS
skipped_autovacuum_count,
How about a name like "autovacuum_lock_skip_count"?
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-03-25 02:07 ` Michael Paquier <[email protected]>
2026-03-25 17:12 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Michael Paquier @ 2026-03-25 02:07 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Sami Imseih <[email protected]>; pgsql-hackers
On Wed, Mar 25, 2026 at 01:28:47AM +0900, Yugo Nagata wrote:
> Although the timestamps are overwritten on each skipped autovacuum or
> autoanalyze, they still indicate when the last attempt was made. This
> can help users confirm that autovacuum is actively attempting to run,
> and that the issue is due to repeated skips rather than inactivity.
>
> While counters can indicate overall activity, they do not reveal when
> the last skip occurred. With timestamps, users can immediately see the
> most recent attempt, even without a separate dashboard or historical
> tracking.
>
> Therefore, counters are useful for monitoring overall activity, but
> timestamps give additional, complementary information, so it seems
> worthwhile to include them too.
Hmm.. I can buy this argument for the timestamps, especially for
database with many relations of various sizes that could take a
various amount of time to process. The timestamps could offer hints
about the time it takes between the skips, even if snapshots of the
stats data are not taken at a very aggressive frequency.
This is v20 material at this stage, of course..
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-25 02:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
@ 2026-03-25 17:12 ` Sami Imseih <[email protected]>
2026-03-25 23:26 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Sami Imseih @ 2026-03-25 17:12 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers
> > On Wed, Mar 25, 2026 at 01:28:47AM +0900, Yugo Nagata wrote:
> > > Although the timestamps are overwritten on each skipped autovacuum or
> > > autoanalyze, they still indicate when the last attempt was made. This
> > > can help users confirm that autovacuum is actively attempting to run,
> > > and that the issue is due to repeated skips rather than inactivity.
> > >
> > > While counters can indicate overall activity, they do not reveal when
> > > the last skip occurred. With timestamps, users can immediately see the
> > > most recent attempt, even without a separate dashboard or historical
> > > tracking.
> > >
> > > Therefore, counters are useful for monitoring overall activity, but
> > > timestamps give additional, complementary information, so it seems
> > > worthwhile to include them too.
> >
> > Hmm.. I can buy this argument for the timestamps, especially for
> > database with many relations of various sizes that could take a
> > various amount of time to process. The timestamps could offer hints
> > about the time it takes between the skips, even if snapshots of the
> > stats data are not taken at a very aggressive frequency.
I'm fine with adding timestamps, as there seem to be convincing
reasons to add them.
My other concern is bloat of the pg_stat_all_tables view. This patch
adds 4 columns, or
8 if we also include manual vacuum and analyze (which I think we should).
Given that, should we also start thinking about splitting the vacuum
activity related
columns into a dedicated view and out of pg_stat_all_tables for v20?
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-25 02:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-25 17:12 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-03-25 23:26 ` Michael Paquier <[email protected]>
2026-03-26 01:31 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Michael Paquier @ 2026-03-25 23:26 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Yugo Nagata <[email protected]>; pgsql-hackers
On Wed, Mar 25, 2026 at 12:12:35PM -0500, Sami Imseih wrote:
> I'm fine with adding timestamps, as there seem to be convincing
> reasons to add them.
> My other concern is bloat of the pg_stat_all_tables view. This patch
> adds 4 columns, or
> 8 if we also include manual vacuum and analyze (which I think we should).
>
> Given that, should we also start thinking about splitting the vacuum
> activity related
> columns into a dedicated view and out of pg_stat_all_tables for v20?
PgStat_StatTabEntry is shared between indexes and tables. A bunch of
its fields apply only to tables, not indexes (aka all the vacuum and
analyze ones). Few fields apply only to indexes, not tables. Not
that many are shared between both. I would advocate for a clean split
between indexes and tables, as a start, with a new variable-sized
stats kind dedicated to indexes.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-25 02:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-25 17:12 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-25 23:26 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
@ 2026-03-26 01:31 ` Michael Paquier <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Michael Paquier @ 2026-03-26 01:31 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Sami Imseih <[email protected]>; pgsql-hackers
On Thu, Mar 26, 2026 at 10:18:39AM +0900, Yugo Nagata wrote:
> I'm not sure this would significantly reduce the size of
> PgStat_StatTabEntry. Could you elaborate on the expected benefits?
The point is that this reduces the shmem footprint for indexes (well,
it's also benefitial for tables, just less), on top of being cleaner
because the stats views would only need to store and query the fields
they care about for each relkind.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-03-26 23:07 ` Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Michael Paquier @ 2026-03-26 23:07 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Sami Imseih <[email protected]>; pgsql-hackers
On Thu, Mar 26, 2026 at 07:22:03PM +0900, Yugo Nagata wrote:
> To handle the possibility that the table is dropped between
> RangeVarGetRelid() and the lock attempt, SearchSysCacheExists1() is
> used after acquiring the lock.
(Noticed while skimming through my emails this morning..)
+void
+pgstat_report_skipped_vacuum_analyze(Oid relid, bool vacuum, bool analyze,
+ bool autovacuum)
I'd recommend to replace this interface with three booleans with a set
of three bitwise flags. That would be less error prone for the
callers of this function, or we could finish by aggregating counters
we don't want to.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
@ 2026-03-27 16:48 ` Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Sami Imseih @ 2026-03-27 16:48 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers
> I've attached a revised patch reflecting this change, and it also includes
> the documentation.
Thanks fo the update!
I have some comments:
1/
+pgstat_report_skipped_vacuum_analyze(Oid relid, bits8 flags)
using bit8 is fine here, but I would have just used int. For this
case, it's just a matter of prefernace.
2/
+/* flags for pgstat_flush_backend() */
+#define PGSTAT_REPORT_SKIPPED_VACUUM (1 << 0) /* vacuum is skipped */
+#define PGSTAT_REPORT_SKIPPED_ANALYZE (1 << 1) /* analyze is skipped */
+#define PGSTAT_REPORT_SKIPPED_AUTOVAC (1 << 2) /* skipped
during autovacuum/autoanalyze */
+#define PGSTAT_REPORT_SKIPPED_ANY (PGSTAT_REPORT_SKIPPED_VACUUM |
PGSTAT_REPORT_SKIPPED_ANALYZE)
can we just have 4 flags, SKIPPED_VACUUM, SKIPPED_ANALYZE,
SKIPPED_AUTOVACUUM, SKIPPED_AUTOANALYZE,
which can then remove the nested if/else and makes the mapping more obvious
+ if (flags & PGSTAT_REPORT_SKIPPED_AUTOVAC)
+ {
+ if (flags & PGSTAT_REPORT_SKIPPED_VACUUM)
+ {
+ tabentry->last_skipped_autovacuum_time = ts;
+ tabentry->skipped_autovacuum_count++;
+ }
+ if (flags & PGSTAT_REPORT_SKIPPED_ANALYZE)
+ {
+ tabentry->last_skipped_autoanalyze_time = ts;
+ tabentry->skipped_autoanalyze_count++;
+ }
+ }
+ else
+ {
+ if (flags & PGSTAT_REPORT_SKIPPED_VACUUM)
+ {
+ tabentry->last_skipped_vacuum_time = ts;
+ tabentry->skipped_vacuum_count++;
+ }
+ if (flags & PGSTAT_REPORT_SKIPPED_ANALYZE)
+ {
+ tabentry->last_skipped_analyze_time = ts;
+ tabentry->skipped_analyze_count++;
+ }
+ }
3/
For the sake of consistency, can we rename the fields from
skipped_vacuum_count to vacuum_skipped_count, etc. ? to be similar
to fields like vacuum_count
4/
field documentation could be a bit better to match existing phrasing
For example, the timestamp fields:
- Last time a manual vacuum on this table was attempted but skipped due to
- lock unavailability (not counting <command>VACUUM FULL</command>)
+ The time of the last manual vacuum on this table that was skipped
+ due to lock unavailability (not counting <command>VACUUM FULL</command>)
and the counter fields
- Number of times vacuums on this table have been attempted but skipped
+ Number of times a manual vacuum on this table has been skipped
5/
Partitioned table asymmetry between vacuum_count and vacuum_skipped_count.
vacuum_count never increments on a the parenttable, because the parent is never
pocessed. On the other hand, if the manual VACUUM/ANALYZE is on the
parent table,
then we will skip all the children. So, we should still report the skip on the
parent table, but we should add a Notes section in the docs perhaps to
document this caveat?
6/
It would be nice to add a test for this, but this requires concurrency and I'm
not sure it's woth it.
Also, can you create a CF entry in
https://commitfest.postgresql.org/59/, please.
Thanks!
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-04-13 08:05 ` Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Yugo Nagata @ 2026-04-13 08:05 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Sami Imseih <[email protected]>; Michael Paquier <[email protected]>; pgsql-hackers
Hello Sami Imseih,
On Sat, 28 Mar 2026 16:18:02 +0900
Yugo Nagata <[email protected]> wrote:
> On Fri, 27 Mar 2026 11:48:27 -0500
> Sami Imseih <[email protected]> wrote:
>
> > > I've attached a revised patch reflecting this change, and it also includes
> > > the documentation.
> >
> > Thanks fo the update!
> >
> > I have some comments:
> >
> > 1/
> > +pgstat_report_skipped_vacuum_analyze(Oid relid, bits8 flags)
> >
> > using bit8 is fine here, but I would have just used int. For this
> > case, it's just a matter of prefernace.
>
> That makes sense, since using int for flags seems common in other
> places in the code. I'm not sure how much it affects performance,
> though.
>
> > 2/
> > +/* flags for pgstat_flush_backend() */
> > +#define PGSTAT_REPORT_SKIPPED_VACUUM (1 << 0) /* vacuum is skipped */
> > +#define PGSTAT_REPORT_SKIPPED_ANALYZE (1 << 1) /* analyze is skipped */
> > +#define PGSTAT_REPORT_SKIPPED_AUTOVAC (1 << 2) /* skipped
> > during autovacuum/autoanalyze */
> > +#define PGSTAT_REPORT_SKIPPED_ANY (PGSTAT_REPORT_SKIPPED_VACUUM |
> > PGSTAT_REPORT_SKIPPED_ANALYZE)
> >
> > can we just have 4 flags, SKIPPED_VACUUM, SKIPPED_ANALYZE,
> > SKIPPED_AUTOVACUUM, SKIPPED_AUTOANALYZE,
> > which can then remove the nested if/else and makes the mapping more obvious
>
> I am fine with that. In that case, the nested logic would move to the
> caller side.
>
> > 3/
> > For the sake of consistency, can we rename the fields from
> >
> > skipped_vacuum_count to vacuum_skipped_count, etc. ? to be similar
> > to fields like vacuum_count
>
> Hmm, I think skipped_vacuum_count is more consistent with
> fields like last_vacuum and total_vacuum_time, where the modifier
> comes before vacuum/analyze. What do you think about that?
>
> > 4/
> > field documentation could be a bit better to match existing phrasing
> >
> > For example, the timestamp fields:
> >
> > - Last time a manual vacuum on this table was attempted but skipped due to
> > - lock unavailability (not counting <command>VACUUM FULL</command>)
> > + The time of the last manual vacuum on this table that was skipped
> > + due to lock unavailability (not counting <command>VACUUM FULL</command>)
>
> I intended to keep consistency with the existing last_vacuum:
>
> Last time at which this table was manually vacuumed (not counting VACUUM FULL)
>
> although "at which" was accidentally omitted. Your suggestion seems
> simpler and more natural to me. Should we prioritize that over consistency?
>
> > and the counter fields
> >
> > - Number of times vacuums on this table have been attempted but skipped
> > + Number of times a manual vacuum on this table has been skipped
>
> The "a munual" was also accidentally omitted, so I'll fix it.
>
> > 5/
> > Partitioned table asymmetry between vacuum_count and vacuum_skipped_count.
> >
> > vacuum_count never increments on a the parenttable, because the parent is never
> > pocessed. On the other hand, if the manual VACUUM/ANALYZE is on the
> > parent table,
> > then we will skip all the children. So, we should still report the skip on the
> > parent table, but we should add a Notes section in the docs perhaps to
> > document this caveat?
>
> Yeah, we cannot report skips on the children when a manual
> vacuum/analyze on the parent table is skipped. (It might be possible
> to obtain child information with NoLock, but that would not be safe.)
>
> Therefore, I agree that the best we can do here is to add a note to the
> documentation of last_skipped_vacuum/analyze and skipped_vacuum/analyze_count.
>
> For example:
>
> When a manual vacuum or analyze on a parent table in an inheritance
> or partitioning hierarchy is skipped, the statistics are recorded
> only for the parent table, not for its children.
>
> > 6/
> > It would be nice to add a test for this, but this requires concurrency and I'm
> > not sure it's woth it.
>
> I'm not sure what meaningful tests we could add for these statistics.
> I couldn't find any existing tests for fields like last_vacuum.
I've attached a patch reflecting your comments on items 1, 2, and 5.
As for items 3, 4, and 6, I am waiting for your comments, so the patch
is left unchanged for now.
Regards,
Yugo Nagata
--
Yugo Nagata <[email protected]>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
@ 2026-04-22 12:49 ` Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Sami Imseih @ 2026-04-22 12:49 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers
Thanks for the updated patch!
> I've attached a patch reflecting your comments on items 1, 2, and 5.
> As for items 3, 4, and 6, I am waiting for your comments, so the patch
> is left unchanged for now.
A few more comments:
1/
+ relid = RangeVarGetRelid(vrel->relation, NoLock, false);
Should this be called with "true" as the 3rd (missing_ok) argument, otherwise
we end up with an error instead of a "--- relation no longer exists" log. right?
2/
Can the isolation tests
src/test/isolation/specs/vacuum-skip-locked.spec be updated
to check pg_stat_user_tables as well?
3/ comment fix:
This:
* Relation could not be opened hence generate if possible a log
Should be:
* Relation could not be opened, hence generate if possible a log
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-04-27 11:32 ` Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Yugo Nagata @ 2026-04-27 11:32 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers
On Wed, 22 Apr 2026 07:49:55 -0500
Sami Imseih <[email protected]> wrote:
Thank you for your comments!
>
> 1/
>
> + relid = RangeVarGetRelid(vrel->relation, NoLock, false);
>
> Should this be called with "true" as the 3rd (missing_ok) argument, otherwise
> we end up with an error instead of a "--- relation no longer exists" log. right?
No, it should be false. If missing_ok is true, VACUUM (SKIP_LOCKED) on a not-existing
table would emit a "skipping vacuum of ... --- relation no longer exists" message, but
it should be "relation ... does not exist".
> 2/
>
> Can the isolation tests
> src/test/isolation/specs/vacuum-skip-locked.spec be updated
> to check pg_stat_user_tables as well?
Yes, we can. I've attached an updated patch including that test.
While working on the test, I noticed that skipped FULL VACUUM was counted
in the previous patch, so I fixed it not to avoid counting those cases.
> 3/ comment fix:
>
> This:
> * Relation could not be opened hence generate if possible a log
>
> Should be:
> * Relation could not be opened, hence generate if possible a log
Fixed.
The names of the new fields are still open. The current pattern is
"last_skipped_..." and "skipped_..._count". Alternatively, we could use
"..._last_skip" and "..._skip_count", which would be consistent with
slotsync_skip_count and slosync_last_skip.
Which do you think is better?
Regards,
Yugo Nagata
--
Yugo Nagata <[email protected]>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
@ 2026-05-04 20:44 ` Sami Imseih <[email protected]>
2026-05-12 09:47 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-05-14 21:46 ` Re: Track skipped tables during autovacuum and autoanalyze Zsolt Parragi <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Sami Imseih @ 2026-05-04 20:44 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers
Thanks for the update!
> >
> > 1/
> >
> > + relid = RangeVarGetRelid(vrel->relation, NoLock, false);
> >
> > Should this be called with "true" as the 3rd (missing_ok) argument, otherwise
> > we end up with an error instead of a "--- relation no longer exists" log. right?
>
> No, it should be false. If missing_ok is true, VACUUM (SKIP_LOCKED) on a not-existing
> table would emit a "skipping vacuum of ... --- relation no longer exists" message, but
> it should be "relation ... does not exist".
Yeah you are right.
But, after looking more into this, I still think the
expand_vacuum_rel() changes can be
improved. The branching
- */
- if (!OidIsValid(relid))
+ if (!(options & VACOPT_SKIP_LOCKED))
{
- if (options & VACOPT_VACUUM)
- ereport(WARNING,
-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
- errmsg("skipping
vacuum of \"%s\" --- lock not available",
-
vrel->relation->relname)));
- else
- ereport(WARNING,
-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
- errmsg("skipping
analyze of \"%s\" --- lock not available",
+ relid = RangeVarGetRelidExtended(vrel->relation,
+
AccessShareLock,
+
0, NULL, NULL);
+ if (!OidIsValid(relid))
+ return vacrels;
+ }
+ else
+ {
+ /* Get relid for reporting before taking a lock */
+ relid = RangeVarGetRelid(vrel->relation, NoLock, false);
+
+ if (!ConditionalLockRelationOid(relid, AccessShareLock))
is not needed. We can continue just using RangeVarGetRelidExtended()
with the rvr_opts and an AccessExclusiveLock, and once we need to
report that we cannot obtain the lock, RangeVarGetRelid() can be
called at that point for the purpose of calling
pgstat_report_skipped_vacuum_analyze(). This is safer than calling
ConditionalLockRelationOid() on a relid obtained with NoLock. See
attached v6.
>> 2/
>>
>> Can the isolation tests
>> src/test/isolation/specs/vacuum-skip-locked.spec be updated
>> to check pg_stat_user_tables as well?
> Yes, we can. I've attached an updated patch including that test.
> While working on the test, I noticed that skipped FULL VACUUM was counted
> in the previous patch, so I fixed it not to avoid counting those cases.
The tests looks good to me.
> The names of the new fields are still open. The current pattern is
> "last_skipped_..." and "skipped_..._count". Alternatively, we could use
> "..._last_skip" and "..._skip_count", which would be consistent with
> slotsync_skip_count and slosync_last_skip.
> Which do you think is better?
I think last_skipped_* is better since we use last_vacuum, last_autovacuum, etc.
--
Sami
Attachments:
[application/octet-stream] v6-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch (39.6K, 2-v6-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch)
download | inline diff:
From 13e2d6b8390ea48cb4fd3e604a46bde02b750bde Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Tue, 24 Mar 2026 13:09:00 +0900
Subject: [PATCH v6 1/1] Track skipped vacuum and analyze activity per relation
This commit adds eight fields to the relation statistics that track
the last time vacuum or analyze has been attempted but skipped due to
lock unavailability, along with their counts:
- last_skipped_vacuum
- last_skipped_autovacuum
- last_skipped_analyze
- last_skipped_autoanalyze
- skipped_vacuum_count
- skipped_autovacuum_count
- skipped_analyze_count
- skipped_autoanalyze_count
These field can help users confirm that autovacuum is actively attempting
to run on a table that has not been vacuumed or analyzed for a long time,
and that the lack of progress is due to repeated skips rather than inactivity.
---
doc/src/sgml/monitoring.sgml | 88 ++++++
src/backend/catalog/system_views.sql | 8 +
src/backend/commands/vacuum.c | 31 +++
src/backend/utils/activity/pgstat_relation.c | 64 +++++
src/backend/utils/adt/pgstatfuncs.c | 24 ++
src/include/catalog/pg_proc.dat | 32 +++
src/include/pgstat.h | 17 ++
.../isolation/expected/vacuum-skip-locked.out | 260 ++++++++++++++++--
.../isolation/specs/vacuum-skip-locked.spec | 41 +--
src/test/regress/expected/rules.out | 24 ++
10 files changed, 555 insertions(+), 34 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..a9b579d87a9 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4387,6 +4387,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_vacuum</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a manual vacuum on this table was attempted but skipped due to
+ lock unavailability (not counting <command>VACUUM FULL</command>)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_autovacuum</structfield> <type>timestamp with time zone</type>
@@ -4397,6 +4407,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_autovacuum</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a vacuum on this table by the autovacuum daemon was attempted
+ but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_analyze</structfield> <type>timestamp with time zone</type>
@@ -4406,6 +4426,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_analyze</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a manual analyze on this table was attempted but skipped due to
+ lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_autoanalyze</structfield> <type>timestamp with time zone</type>
@@ -4416,6 +4446,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_autoanalyze</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time at which an analyze on this table by the autovacuum was
+ attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>vacuum_count</structfield> <type>bigint</type>
@@ -4426,6 +4466,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_vacuum_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times manual vacuums on this table have been attempted but skipped
+ due to lock unavailability (not counting <command>VACUUM FULL</command>)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>autovacuum_count</structfield> <type>bigint</type>
@@ -4436,6 +4486,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_autovacuum_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times vacuums on this table by the autovacuum daemon have been
+ attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>analyze_count</structfield> <type>bigint</type>
@@ -4445,6 +4505,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_analyze_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times manual analyzes on this table have been attempted but
+ skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>autoanalyze_count</structfield> <type>bigint</type>
@@ -4455,6 +4525,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_autoanalyze_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times analyzes on this table by the autovacuum daemon have
+ been attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>total_vacuum_time</structfield> <type>double precision</type>
@@ -4510,6 +4590,14 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</tgroup>
</table>
+ <note>
+ <para>
+ When a manual vacuum or analyze on a parent table in an inheritance or
+ partitioning hierarchy is skipped, the statistics are recorded only for
+ the parent table, not for its children.
+ </para>
+ </note>
+
</sect2>
<sect2 id="monitoring-pg-stat-autovacuum-scores-view">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 73a1c1c4670..f509fc7876b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -736,13 +736,21 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
+ pg_stat_get_last_skipped_vacuum_time(C.oid) as last_skipped_vacuum,
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
+ pg_stat_get_last_skipped_autovacuum_time(C.oid) as last_skipped_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
+ pg_stat_get_last_skipped_analyze_time(C.oid) as last_skipped_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_skipped_autoanalyze_time(C.oid) as last_skipped_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
+ pg_stat_get_skipped_vacuum_count(C.oid) AS skipped_vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
+ pg_stat_get_skipped_autovacuum_count(C.oid) AS skipped_autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
+ pg_stat_get_skipped_analyze_count(C.oid) AS skipped_analyze_count,
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+ pg_stat_get_skipped_autoanalyze_count(C.oid) AS skipped_autoanalyze_count,
pg_stat_get_total_vacuum_time(C.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time,
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 99d0db82ed7..51c5fc2fdaf 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -793,8 +793,26 @@ vacuum_open_relation(Oid relid, RangeVar *relation, uint32 options,
rel = try_relation_open(relid, NoLock);
else
{
+ int flags = 0;
+
rel = NULL;
rel_lock = false;
+
+ if ((options & VACOPT_VACUUM) != 0 && (options & VACOPT_FULL) == 0)
+ {
+ if (AmAutoVacuumWorkerProcess())
+ flags |= PGSTAT_REPORT_SKIPPED_AUTOVACUUM;
+ else
+ flags |= PGSTAT_REPORT_SKIPPED_VACUUM;
+ }
+ if ((options & VACOPT_ANALYZE) != 0)
+ {
+ if (AmAutoVacuumWorkerProcess())
+ flags |= PGSTAT_REPORT_SKIPPED_AUTOANALYZE;
+ else
+ flags |= PGSTAT_REPORT_SKIPPED_ANALYZE;
+ }
+ pgstat_report_skipped_vacuum_analyze(relid, flags);
}
/* if relation is opened, leave */
@@ -930,6 +948,8 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
*/
if (!OidIsValid(relid))
{
+ int flags = 0;
+
if (options & VACOPT_VACUUM)
ereport(WARNING,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
@@ -940,6 +960,17 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
errmsg("skipping analyze of \"%s\" --- lock not available",
vrel->relation->relname)));
+
+ /* Get relid for statistics reporting */
+ relid = RangeVarGetRelid(vrel->relation, NoLock, true);
+
+ if ((options & VACOPT_VACUUM) != 0 && (options & VACOPT_FULL) == 0)
+ flags |= PGSTAT_REPORT_SKIPPED_VACUUM;
+ if ((options & VACOPT_ANALYZE) != 0)
+ flags |= PGSTAT_REPORT_SKIPPED_ANALYZE;
+
+ pgstat_report_skipped_vacuum_analyze(relid, flags);
+
return vacrels;
}
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index b2ca28f83ba..21d1b382ba0 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -17,12 +17,14 @@
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/twophase_rmgr.h"
#include "access/xact.h"
#include "catalog/catalog.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -367,6 +369,68 @@ pgstat_report_analyze(Relation rel,
(void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
}
+/*
+ * Report that the table was skipped during vacuum or/and analyze.
+ */
+void
+pgstat_report_skipped_vacuum_analyze(Oid relid, int flags)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
+ TimestampTz ts;
+ HeapTuple classTup;
+ bool isshared;
+
+ if (!pgstat_track_counts || !flags)
+ return;
+
+ classTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(classTup))
+ return; /* somebody deleted the rel, forget it */
+ isshared = ((Form_pg_class) GETSTRUCT(classTup))->relisshared;
+ ReleaseSysCache(classTup);
+
+ /* Store the data in the table's hash table entry. */
+ ts = GetCurrentTimestamp();
+
+ /* block acquiring lock for the same reason as pgstat_report_autovac() */
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ isshared ? InvalidOid : MyDatabaseId,
+ relid, false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ if (flags & PGSTAT_REPORT_SKIPPED_VACUUM)
+ {
+ tabentry->last_skipped_vacuum_time = ts;
+ tabentry->skipped_vacuum_count++;
+ }
+ else if (flags & PGSTAT_REPORT_SKIPPED_AUTOVACUUM)
+ {
+ tabentry->last_skipped_autovacuum_time = ts;
+ tabentry->skipped_autovacuum_count++;
+ }
+
+ if (flags & PGSTAT_REPORT_SKIPPED_ANALYZE)
+ {
+ tabentry->last_skipped_analyze_time = ts;
+ tabentry->skipped_analyze_count++;
+ }
+ else if (flags & PGSTAT_REPORT_SKIPPED_AUTOANALYZE)
+ {
+ tabentry->last_skipped_autoanalyze_time = ts;
+ tabentry->skipped_autoanalyze_count++;
+ }
+
+ pgstat_unlock_entry(entry_ref);
+
+ /* see pgstat_report_vacuum() */
+ pgstat_flush_io(false);
+ (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
+}
+
/*
* count a tuple insertion of n tuples
*/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7a9dfa9ba3b..aef291217b6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -84,6 +84,18 @@ PG_STAT_GET_RELENTRY_INT64(mod_since_analyze)
/* pg_stat_get_numscans */
PG_STAT_GET_RELENTRY_INT64(numscans)
+/* pg_stat_get_skipped_analyze_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_analyze_count)
+
+/* pg_stat_get_skipped_autoanalyze_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_autoanalyze_count)
+
+/* pg_stat_get_skipped_autovacuum_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_autovacuum_count)
+
+/* pg_stat_get_skipped_vacuum_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_vacuum_count)
+
/* pg_stat_get_tuples_deleted */
PG_STAT_GET_RELENTRY_INT64(tuples_deleted)
@@ -170,6 +182,18 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time)
/* pg_stat_get_lastscan */
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan)
+/* pg_stat_get_last_skipped_analyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_analyze_time)
+
+/* pg_stat_get_last_skipped_autoanalyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autoanalyze_time)
+
+/* pg_stat_get_last_skipped_autovacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autovacuum_time)
+
+/* pg_stat_get_last_skipped_vacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_vacuum_time)
+
/* pg_stat_get_stat_reset_time */
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat_reset_time)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fa9ae79082b..32debb34863 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5680,6 +5680,38 @@
proargmodes => '{o,o,o,o,o,o,o,o,o,o}',
proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score,do_vacuum,do_analyze,for_wraparound}',
prosrc => 'pg_stat_get_autovacuum_scores' },
+{ oid => '8142', descr => 'statistics: last skipped vacuum time for a table',
+ proname => 'pg_stat_get_last_skipped_vacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_vacuum_time' },
+{ oid => '8143', descr => 'statistics: last skipped auto vacuum time for a table',
+ proname => 'pg_stat_get_last_skipped_autovacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_autovacuum_time' },
+{ oid => '8144', descr => 'statistics: last skipped analyze time for a table',
+ proname => 'pg_stat_get_last_skipped_analyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_analyze_time' },
+{ oid => '8145', descr => 'statistics: last skipped auto analyze time for a table',
+ proname => 'pg_stat_get_last_skipped_autoanalyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_autoanalyze_time' },
+{ oid => '8146', descr => 'statistics: number of skipped vacuum for a table',
+ proname => 'pg_stat_get_skipped_vacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_vacuum_count' },
+{ oid => '8147', descr => 'statistics: number of skipped auto vacuum for a table',
+ proname => 'pg_stat_get_skipped_autovacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_autovacuum_count' },
+{ oid => '8148', descr => 'statistics: number of skipped analyzes for a table',
+ proname => 'pg_stat_get_skipped_analyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_analyze_count' },
+{ oid => '8149', descr => 'statistics: number of skipped auto analyzes for a table',
+ proname => 'pg_stat_get_skipped_autoanalyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_autoanalyze_count' },
{ oid => '1936', descr => 'statistics: currently active backend IDs',
proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index dfa2e837638..54b9fc60bc3 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -479,6 +479,15 @@ typedef struct PgStat_StatTabEntry
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+ TimestampTz last_skipped_vacuum_time; /* user initiated vacuum */
+ PgStat_Counter skipped_vacuum_count;
+ TimestampTz last_skipped_autovacuum_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autovacuum_count;
+ TimestampTz last_skipped_analyze_time; /* user initiated */
+ PgStat_Counter skipped_analyze_count;
+ TimestampTz last_skipped_autoanalyze_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autoanalyze_count;
+
PgStat_Counter total_vacuum_time; /* times in milliseconds */
PgStat_Counter total_autovacuum_time;
PgStat_Counter total_analyze_time;
@@ -703,6 +712,14 @@ extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
+/* flags for pgstat_flush_backend() */
+#define PGSTAT_REPORT_SKIPPED_VACUUM (1 << 0) /* vacuum is skipped */
+#define PGSTAT_REPORT_SKIPPED_ANALYZE (1 << 1) /* analyze is skipped */
+#define PGSTAT_REPORT_SKIPPED_AUTOVACUUM (1 << 2) /* autovacuum is skipped */
+#define PGSTAT_REPORT_SKIPPED_AUTOANALYZE (1 << 3) /* autoanalyze is
+ * skipped */
+extern void pgstat_report_skipped_vacuum_analyze(Oid relid, int flags);
+
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
* pgstat_assoc_relation() to do so. See its comment for why this is done
diff --git a/src/test/isolation/expected/vacuum-skip-locked.out b/src/test/isolation/expected/vacuum-skip-locked.out
index 99db281a159..e2cff175b7c 100644
--- a/src/test/isolation/expected/vacuum-skip-locked.out
+++ b/src/test/isolation/expected/vacuum-skip-locked.out
@@ -1,6 +1,6 @@
Parsed test spec with 2 sessions
-starting permutation: lock_share vac_specified commit
+starting permutation: lock_share vac_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -10,8 +10,22 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -20,8 +34,22 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
+
-starting permutation: lock_share analyze_specified commit
+starting permutation: lock_share analyze_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -31,8 +59,22 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share analyze_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_share analyze_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -41,8 +83,22 @@ step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_share vac_analyze_specified commit
+starting permutation: lock_share vac_analyze_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -52,8 +108,22 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_analyze_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_analyze_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -62,8 +132,22 @@ step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_share vac_full_specified commit
+starting permutation: lock_share vac_full_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -73,8 +157,22 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_full_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_full_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -83,8 +181,22 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_specified commit
+
+starting permutation: lock_access_exclusive vac_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -94,8 +206,22 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_all_parts commit
+
+starting permutation: lock_access_exclusive vac_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -104,8 +230,22 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive analyze_specified commit
+
+starting permutation: lock_access_exclusive analyze_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -115,8 +255,22 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_access_exclusive analyze_all_parts commit
+starting permutation: lock_access_exclusive analyze_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -126,8 +280,22 @@ step commit:
COMMIT;
step analyze_all_parts: <... completed>
-
-starting permutation: lock_access_exclusive vac_analyze_specified commit
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 0| 1| 0
+part2 | 0| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_analyze_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -137,8 +305,22 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_analyze_all_parts commit
+
+starting permutation: lock_access_exclusive vac_analyze_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -148,8 +330,22 @@ step commit:
COMMIT;
step vac_analyze_all_parts: <... completed>
-
-starting permutation: lock_access_exclusive vac_full_specified commit
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 1| 0| 1| 0
+part2 | 1| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_full_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -159,8 +355,22 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_access_exclusive vac_full_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_full_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -169,3 +379,17 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
+
diff --git a/src/test/isolation/specs/vacuum-skip-locked.spec b/src/test/isolation/specs/vacuum-skip-locked.spec
index 3fad6e1c92a..b0da75d4b6d 100644
--- a/src/test/isolation/specs/vacuum-skip-locked.spec
+++ b/src/test/isolation/specs/vacuum-skip-locked.spec
@@ -33,6 +33,15 @@ step commit
COMMIT;
}
+step check_stat
+{
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+}
+
session s2
step vac_specified { VACUUM (SKIP_LOCKED) part1, part2; }
step vac_all_parts { VACUUM (SKIP_LOCKED) parted; }
@@ -43,19 +52,19 @@ step vac_analyze_all_parts { VACUUM (ANALYZE, SKIP_LOCKED) parted; }
step vac_full_specified { VACUUM (SKIP_LOCKED, FULL) part1, part2; }
step vac_full_all_parts { VACUUM (SKIP_LOCKED, FULL) parted; }
-permutation lock_share vac_specified commit
-permutation lock_share vac_all_parts commit
-permutation lock_share analyze_specified commit
-permutation lock_share analyze_all_parts commit
-permutation lock_share vac_analyze_specified commit
-permutation lock_share vac_analyze_all_parts commit
-permutation lock_share vac_full_specified commit
-permutation lock_share vac_full_all_parts commit
-permutation lock_access_exclusive vac_specified commit
-permutation lock_access_exclusive vac_all_parts commit
-permutation lock_access_exclusive analyze_specified commit
-permutation lock_access_exclusive analyze_all_parts commit
-permutation lock_access_exclusive vac_analyze_specified commit
-permutation lock_access_exclusive vac_analyze_all_parts commit
-permutation lock_access_exclusive vac_full_specified commit
-permutation lock_access_exclusive vac_full_all_parts commit
+permutation lock_share vac_specified commit check_stat
+permutation lock_share vac_all_parts commit check_stat
+permutation lock_share analyze_specified commit check_stat
+permutation lock_share analyze_all_parts commit check_stat
+permutation lock_share vac_analyze_specified commit check_stat
+permutation lock_share vac_analyze_all_parts commit check_stat
+permutation lock_share vac_full_specified commit check_stat
+permutation lock_share vac_full_all_parts commit check_stat
+permutation lock_access_exclusive vac_specified commit check_stat
+permutation lock_access_exclusive vac_all_parts commit check_stat
+permutation lock_access_exclusive analyze_specified commit check_stat
+permutation lock_access_exclusive analyze_all_parts commit check_stat
+permutation lock_access_exclusive vac_analyze_specified commit check_stat
+permutation lock_access_exclusive vac_analyze_all_parts commit check_stat
+permutation lock_access_exclusive vac_full_specified commit check_stat
+permutation lock_access_exclusive vac_full_all_parts commit check_stat
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a65a5bf0c4f..9b2075d3373 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1835,13 +1835,21 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
+ pg_stat_get_last_skipped_vacuum_time(c.oid) AS last_skipped_vacuum,
pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
+ pg_stat_get_last_skipped_autovacuum_time(c.oid) AS last_skipped_autovacuum,
pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
+ pg_stat_get_last_skipped_analyze_time(c.oid) AS last_skipped_analyze,
pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
+ pg_stat_get_last_skipped_autoanalyze_time(c.oid) AS last_skipped_autoanalyze,
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
+ pg_stat_get_skipped_vacuum_count(c.oid) AS skipped_vacuum_count,
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
+ pg_stat_get_skipped_autovacuum_count(c.oid) AS skipped_autovacuum_count,
pg_stat_get_analyze_count(c.oid) AS analyze_count,
+ pg_stat_get_skipped_analyze_count(c.oid) AS skipped_analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
+ pg_stat_get_skipped_autoanalyze_count(c.oid) AS skipped_autoanalyze_count,
pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
@@ -2346,13 +2354,21 @@ pg_stat_sys_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_skipped_vacuum,
last_autovacuum,
+ last_skipped_autovacuum,
last_analyze,
+ last_skipped_analyze,
last_autoanalyze,
+ last_skipped_autoanalyze,
vacuum_count,
+ skipped_vacuum_count,
autovacuum_count,
+ skipped_autovacuum_count,
analyze_count,
+ skipped_analyze_count,
autoanalyze_count,
+ skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
@@ -2401,13 +2417,21 @@ pg_stat_user_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_skipped_vacuum,
last_autovacuum,
+ last_skipped_autovacuum,
last_analyze,
+ last_skipped_analyze,
last_autoanalyze,
+ last_skipped_autoanalyze,
vacuum_count,
+ skipped_vacuum_count,
autovacuum_count,
+ skipped_autovacuum_count,
analyze_count,
+ skipped_analyze_count,
autoanalyze_count,
+ skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-05-12 09:47 ` Yugo Nagata <[email protected]>
2026-05-12 14:50 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Yugo Nagata @ 2026-05-12 09:47 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers
On Mon, 4 May 2026 15:44:57 -0500
Sami Imseih <[email protected]> wrote:
Thank you for your review!
> > > 1/
> > >
> > > + relid = RangeVarGetRelid(vrel->relation, NoLock, false);
> > >
> > > Should this be called with "true" as the 3rd (missing_ok) argument, otherwise
> > > we end up with an error instead of a "--- relation no longer exists" log. right?
> >
> > No, it should be false. If missing_ok is true, VACUUM (SKIP_LOCKED) on a not-existing
> > table would emit a "skipping vacuum of ... --- relation no longer exists" message, but
> > it should be "relation ... does not exist".
>
> Yeah you are right.
>
> But, after looking more into this, I still think the
> expand_vacuum_rel() changes can be
> improved. The branching
> - */
> - if (!OidIsValid(relid))
> + if (!(options & VACOPT_SKIP_LOCKED))
> {
> - if (options & VACOPT_VACUUM)
> - ereport(WARNING,
> -
> (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
> - errmsg("skipping
> vacuum of \"%s\" --- lock not available",
> -
> vrel->relation->relname)));
> - else
> - ereport(WARNING,
> -
> (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
> - errmsg("skipping
> analyze of \"%s\" --- lock not available",
> + relid = RangeVarGetRelidExtended(vrel->relation,
> +
> AccessShareLock,
> +
> 0, NULL, NULL);
> + if (!OidIsValid(relid))
> + return vacrels;
> + }
> + else
> + {
> + /* Get relid for reporting before taking a lock */
> + relid = RangeVarGetRelid(vrel->relation, NoLock, false);
> +
> + if (!ConditionalLockRelationOid(relid, AccessShareLock))
>
> is not needed. We can continue just using RangeVarGetRelidExtended()
> with the rvr_opts and an AccessExclusiveLock, and once we need to
> report that we cannot obtain the lock, RangeVarGetRelid() can be
> called at that point for the purpose of calling
> pgstat_report_skipped_vacuum_analyze(). This is safer than calling
> ConditionalLockRelationOid() on a relid obtained with NoLock. See
> attached v6.
It seems good to me.
Initially, I was concerned that something might go wrong if a concurrent
session performed DROP TABLE or ALTER TABLE RENAME between RangeVarGetRelidExtended()
and RangeVarGetRelid(), but I could not find any actual issue. Even when the table
name is changed, the correct statistics entry is updated correctly.
So I'm fine with your version.
Regards,
Yugo Nagata
> >> 2/
> >>
> >> Can the isolation tests
> >> src/test/isolation/specs/vacuum-skip-locked.spec be updated
> >> to check pg_stat_user_tables as well?
>
> > Yes, we can. I've attached an updated patch including that test.
>
> > While working on the test, I noticed that skipped FULL VACUUM was counted
> > in the previous patch, so I fixed it not to avoid counting those cases.
>
> The tests looks good to me.
>
> > The names of the new fields are still open. The current pattern is
> > "last_skipped_..." and "skipped_..._count". Alternatively, we could use
> > "..._last_skip" and "..._skip_count", which would be consistent with
> > slotsync_skip_count and slosync_last_skip.
>
> > Which do you think is better?
>
> I think last_skipped_* is better since we use last_vacuum, last_autovacuum, etc.
>
> --
> Sami
--
Yugo Nagata <[email protected]>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-05-12 09:47 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
@ 2026-05-12 14:50 ` Sami Imseih <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Sami Imseih @ 2026-05-12 14:50 UTC (permalink / raw)
To: Yugo Nagata <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-hackers
> So I'm fine with your version.
Thanks!
I marked the CF entry as RFC.
--
Sami
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-05-14 21:46 ` Zsolt Parragi <[email protected]>
2026-05-15 00:59 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Zsolt Parragi @ 2026-05-14 21:46 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Yugo Nagata <[email protected]>; Michael Paquier <[email protected]>; pgsql-hackers
Hello!
+ TimestampTz last_skipped_vacuum_time; /* user initiated vacuum */
+ PgStat_Counter skipped_vacuum_count;
+ TimestampTz last_skipped_autovacuum_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autovacuum_count;
+ TimestampTz last_skipped_analyze_time; /* user initiated */
+ PgStat_Counter skipped_analyze_count;
+ TimestampTz last_skipped_autoanalyze_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autoanalyze_count;
+
Doesn't these also require a PGSTAT_FILE_FORMAT_ID change?
There's also an asymmetric case for the skipped counters, is that intentional?
| Command | `skipped_vacuum_count` |
`skipped_analyze_count` |
|-----------------------------------------|------------------------|-------------------------|
| `VACUUM (FULL, ANALYZE, SKIP_LOCKED) t` | 0 | 1
|
| `VACUUM (ANALYZE, SKIP_LOCKED) t` | 1 | 1
|
| `VACUUM (FULL, SKIP_LOCKED) t` | 0 | 0
|
> Initially, I was concerned that something might go wrong if a concurrent
> session performed DROP TABLE or ALTER TABLE RENAME between RangeVarGetRelidExtended()
> and RangeVarGetRelid(), but I could not find any actual issue. Even when the table
> name is changed, the correct statistics entry is updated correctly.
A DROP TABLE can cause a missed skip in statistics, which is
reproducible with a custom injection point and tap test, see the
attached patch. The race window is quite minimal, but it exists.
Attachments:
[application/octet-stream] 0001-DROP-TABLE-race-in-expand_vacuum_rel-skip-lock-path.patch.nocfbot (3.1K, 2-0001-DROP-TABLE-race-in-expand_vacuum_rel-skip-lock-path.patch.nocfbot)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-05-14 21:46 ` Re: Track skipped tables during autovacuum and autoanalyze Zsolt Parragi <[email protected]>
@ 2026-05-15 00:59 ` Sami Imseih <[email protected]>
2026-05-15 05:03 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Sami Imseih @ 2026-05-15 00:59 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Yugo Nagata <[email protected]>; Michael Paquier <[email protected]>; pgsql-hackers
> Doesn't these also require a PGSTAT_FILE_FORMAT_ID change?
right. that was missed. Fixed in the attached.
> There's also an asymmetric case for the skipped counters, is that intentional?
>
> | Command | `skipped_vacuum_count` |
> `skipped_analyze_count` |
> |-----------------------------------------|------------------------|-------------------------|
> | `VACUUM (FULL, ANALYZE, SKIP_LOCKED) t` | 0 | 1
> |
> | `VACUUM (ANALYZE, SKIP_LOCKED) t` | 1 | 1
> |
> | `VACUUM (FULL, SKIP_LOCKED) t` | 0 | 0
Yeah, this is because vacuum_count and last_vacuum also skip VACUUM FULL.
That was mentioned earlier in the thread.
> > Initially, I was concerned that something might go wrong if a concurrent
> > session performed DROP TABLE or ALTER TABLE RENAME between RangeVarGetRelidExtended()
> > and RangeVarGetRelid(), but I could not find any actual issue. Even when the table
> > name is changed, the correct statistics entry is updated correctly.
>
> A DROP TABLE can cause a missed skip in statistics, which is
> reproducible with a custom injection point and tap test, see the
> attached patch. The race window is quite minimal, but it exists.
If the table is dropped, there are no stats to update. right?
--
Sami
Attachments:
[application/octet-stream] v7-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch (39.8K, 2-v7-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch)
download | inline diff:
From 6f5294f1c09edf3f5cd11d9732d49ef74061f963 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Tue, 24 Mar 2026 13:09:00 +0900
Subject: [PATCH v7 1/1] Track skipped vacuum and analyze activity per relation
This commit adds eight fields to the relation statistics that track
the last time vacuum or analyze has been attempted but skipped due to
lock unavailability, along with their counts:
- last_skipped_vacuum
- last_skipped_autovacuum
- last_skipped_analyze
- last_skipped_autoanalyze
- skipped_vacuum_count
- skipped_autovacuum_count
- skipped_analyze_count
- skipped_autoanalyze_count
These field can help users confirm that autovacuum is actively attempting
to run on a table that has not been vacuumed or analyzed for a long time,
and that the lack of progress is due to repeated skips rather than inactivity.
---
doc/src/sgml/monitoring.sgml | 88 ++++++
src/backend/catalog/system_views.sql | 8 +
src/backend/commands/vacuum.c | 31 +++
src/backend/utils/activity/pgstat_relation.c | 64 +++++
src/backend/utils/adt/pgstatfuncs.c | 24 ++
src/include/catalog/pg_proc.dat | 32 +++
src/include/pgstat.h | 19 +-
.../isolation/expected/vacuum-skip-locked.out | 260 ++++++++++++++++--
.../isolation/specs/vacuum-skip-locked.spec | 41 +--
src/test/regress/expected/rules.out | 24 ++
10 files changed, 556 insertions(+), 35 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..a9b579d87a9 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4387,6 +4387,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_vacuum</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a manual vacuum on this table was attempted but skipped due to
+ lock unavailability (not counting <command>VACUUM FULL</command>)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_autovacuum</structfield> <type>timestamp with time zone</type>
@@ -4397,6 +4407,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_autovacuum</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a vacuum on this table by the autovacuum daemon was attempted
+ but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_analyze</structfield> <type>timestamp with time zone</type>
@@ -4406,6 +4426,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_analyze</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a manual analyze on this table was attempted but skipped due to
+ lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_autoanalyze</structfield> <type>timestamp with time zone</type>
@@ -4416,6 +4446,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_skipped_autoanalyze</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time at which an analyze on this table by the autovacuum was
+ attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>vacuum_count</structfield> <type>bigint</type>
@@ -4426,6 +4466,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_vacuum_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times manual vacuums on this table have been attempted but skipped
+ due to lock unavailability (not counting <command>VACUUM FULL</command>)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>autovacuum_count</structfield> <type>bigint</type>
@@ -4436,6 +4486,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_autovacuum_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times vacuums on this table by the autovacuum daemon have been
+ attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>analyze_count</structfield> <type>bigint</type>
@@ -4445,6 +4505,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_analyze_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times manual analyzes on this table have been attempted but
+ skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>autoanalyze_count</structfield> <type>bigint</type>
@@ -4455,6 +4525,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>skipped_autoanalyze_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times analyzes on this table by the autovacuum daemon have
+ been attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>total_vacuum_time</structfield> <type>double precision</type>
@@ -4510,6 +4590,14 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</tgroup>
</table>
+ <note>
+ <para>
+ When a manual vacuum or analyze on a parent table in an inheritance or
+ partitioning hierarchy is skipped, the statistics are recorded only for
+ the parent table, not for its children.
+ </para>
+ </note>
+
</sect2>
<sect2 id="monitoring-pg-stat-autovacuum-scores-view">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 73a1c1c4670..f509fc7876b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -736,13 +736,21 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
+ pg_stat_get_last_skipped_vacuum_time(C.oid) as last_skipped_vacuum,
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
+ pg_stat_get_last_skipped_autovacuum_time(C.oid) as last_skipped_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
+ pg_stat_get_last_skipped_analyze_time(C.oid) as last_skipped_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_skipped_autoanalyze_time(C.oid) as last_skipped_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
+ pg_stat_get_skipped_vacuum_count(C.oid) AS skipped_vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
+ pg_stat_get_skipped_autovacuum_count(C.oid) AS skipped_autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
+ pg_stat_get_skipped_analyze_count(C.oid) AS skipped_analyze_count,
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+ pg_stat_get_skipped_autoanalyze_count(C.oid) AS skipped_autoanalyze_count,
pg_stat_get_total_vacuum_time(C.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time,
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index a4abb29cf64..019cfa03679 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -793,8 +793,26 @@ vacuum_open_relation(Oid relid, RangeVar *relation, uint32 options,
rel = try_relation_open(relid, NoLock);
else
{
+ int flags = 0;
+
rel = NULL;
rel_lock = false;
+
+ if ((options & VACOPT_VACUUM) != 0 && (options & VACOPT_FULL) == 0)
+ {
+ if (AmAutoVacuumWorkerProcess())
+ flags |= PGSTAT_REPORT_SKIPPED_AUTOVACUUM;
+ else
+ flags |= PGSTAT_REPORT_SKIPPED_VACUUM;
+ }
+ if ((options & VACOPT_ANALYZE) != 0)
+ {
+ if (AmAutoVacuumWorkerProcess())
+ flags |= PGSTAT_REPORT_SKIPPED_AUTOANALYZE;
+ else
+ flags |= PGSTAT_REPORT_SKIPPED_ANALYZE;
+ }
+ pgstat_report_skipped_vacuum_analyze(relid, flags);
}
/* if relation is opened, leave */
@@ -930,6 +948,8 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
*/
if (!OidIsValid(relid))
{
+ int flags = 0;
+
if (options & VACOPT_VACUUM)
ereport(WARNING,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
@@ -940,6 +960,17 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
errmsg("skipping analyze of \"%s\" --- lock not available",
vrel->relation->relname)));
+
+ /* Get relid for statistics reporting */
+ relid = RangeVarGetRelid(vrel->relation, NoLock, true);
+
+ if ((options & VACOPT_VACUUM) != 0 && (options & VACOPT_FULL) == 0)
+ flags |= PGSTAT_REPORT_SKIPPED_VACUUM;
+ if ((options & VACOPT_ANALYZE) != 0)
+ flags |= PGSTAT_REPORT_SKIPPED_ANALYZE;
+
+ pgstat_report_skipped_vacuum_analyze(relid, flags);
+
return vacrels;
}
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index b2ca28f83ba..21d1b382ba0 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -17,12 +17,14 @@
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/twophase_rmgr.h"
#include "access/xact.h"
#include "catalog/catalog.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -367,6 +369,68 @@ pgstat_report_analyze(Relation rel,
(void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
}
+/*
+ * Report that the table was skipped during vacuum or/and analyze.
+ */
+void
+pgstat_report_skipped_vacuum_analyze(Oid relid, int flags)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
+ TimestampTz ts;
+ HeapTuple classTup;
+ bool isshared;
+
+ if (!pgstat_track_counts || !flags)
+ return;
+
+ classTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(classTup))
+ return; /* somebody deleted the rel, forget it */
+ isshared = ((Form_pg_class) GETSTRUCT(classTup))->relisshared;
+ ReleaseSysCache(classTup);
+
+ /* Store the data in the table's hash table entry. */
+ ts = GetCurrentTimestamp();
+
+ /* block acquiring lock for the same reason as pgstat_report_autovac() */
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ isshared ? InvalidOid : MyDatabaseId,
+ relid, false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ if (flags & PGSTAT_REPORT_SKIPPED_VACUUM)
+ {
+ tabentry->last_skipped_vacuum_time = ts;
+ tabentry->skipped_vacuum_count++;
+ }
+ else if (flags & PGSTAT_REPORT_SKIPPED_AUTOVACUUM)
+ {
+ tabentry->last_skipped_autovacuum_time = ts;
+ tabentry->skipped_autovacuum_count++;
+ }
+
+ if (flags & PGSTAT_REPORT_SKIPPED_ANALYZE)
+ {
+ tabentry->last_skipped_analyze_time = ts;
+ tabentry->skipped_analyze_count++;
+ }
+ else if (flags & PGSTAT_REPORT_SKIPPED_AUTOANALYZE)
+ {
+ tabentry->last_skipped_autoanalyze_time = ts;
+ tabentry->skipped_autoanalyze_count++;
+ }
+
+ pgstat_unlock_entry(entry_ref);
+
+ /* see pgstat_report_vacuum() */
+ pgstat_flush_io(false);
+ (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
+}
+
/*
* count a tuple insertion of n tuples
*/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 6f9c9c72de5..c45126f18c3 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -84,6 +84,18 @@ PG_STAT_GET_RELENTRY_INT64(mod_since_analyze)
/* pg_stat_get_numscans */
PG_STAT_GET_RELENTRY_INT64(numscans)
+/* pg_stat_get_skipped_analyze_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_analyze_count)
+
+/* pg_stat_get_skipped_autoanalyze_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_autoanalyze_count)
+
+/* pg_stat_get_skipped_autovacuum_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_autovacuum_count)
+
+/* pg_stat_get_skipped_vacuum_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_vacuum_count)
+
/* pg_stat_get_tuples_deleted */
PG_STAT_GET_RELENTRY_INT64(tuples_deleted)
@@ -170,6 +182,18 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time)
/* pg_stat_get_lastscan */
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan)
+/* pg_stat_get_last_skipped_analyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_analyze_time)
+
+/* pg_stat_get_last_skipped_autoanalyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autoanalyze_time)
+
+/* pg_stat_get_last_skipped_autovacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autovacuum_time)
+
+/* pg_stat_get_last_skipped_vacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_vacuum_time)
+
/* pg_stat_get_stat_reset_time */
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat_reset_time)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..d72921a1626 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5681,6 +5681,38 @@
proargmodes => '{o,o,o,o,o,o,o,o,o,o}',
proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score,do_vacuum,do_analyze,for_wraparound}',
prosrc => 'pg_stat_get_autovacuum_scores' },
+{ oid => '8142', descr => 'statistics: last skipped vacuum time for a table',
+ proname => 'pg_stat_get_last_skipped_vacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_vacuum_time' },
+{ oid => '8143', descr => 'statistics: last skipped auto vacuum time for a table',
+ proname => 'pg_stat_get_last_skipped_autovacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_autovacuum_time' },
+{ oid => '8144', descr => 'statistics: last skipped analyze time for a table',
+ proname => 'pg_stat_get_last_skipped_analyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_analyze_time' },
+{ oid => '8145', descr => 'statistics: last skipped auto analyze time for a table',
+ proname => 'pg_stat_get_last_skipped_autoanalyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_autoanalyze_time' },
+{ oid => '8146', descr => 'statistics: number of skipped vacuum for a table',
+ proname => 'pg_stat_get_skipped_vacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_vacuum_count' },
+{ oid => '8147', descr => 'statistics: number of skipped auto vacuum for a table',
+ proname => 'pg_stat_get_skipped_autovacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_autovacuum_count' },
+{ oid => '8148', descr => 'statistics: number of skipped analyzes for a table',
+ proname => 'pg_stat_get_skipped_analyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_analyze_count' },
+{ oid => '8149', descr => 'statistics: number of skipped auto analyzes for a table',
+ proname => 'pg_stat_get_skipped_autoanalyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_autoanalyze_count' },
{ oid => '1936', descr => 'statistics: currently active backend IDs',
proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index dfa2e837638..e31871ed0cc 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -218,7 +218,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBC
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBD
typedef struct PgStat_ArchiverStats
{
@@ -479,6 +479,15 @@ typedef struct PgStat_StatTabEntry
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+ TimestampTz last_skipped_vacuum_time; /* user initiated vacuum */
+ PgStat_Counter skipped_vacuum_count;
+ TimestampTz last_skipped_autovacuum_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autovacuum_count;
+ TimestampTz last_skipped_analyze_time; /* user initiated */
+ PgStat_Counter skipped_analyze_count;
+ TimestampTz last_skipped_autoanalyze_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autoanalyze_count;
+
PgStat_Counter total_vacuum_time; /* times in milliseconds */
PgStat_Counter total_autovacuum_time;
PgStat_Counter total_analyze_time;
@@ -703,6 +712,14 @@ extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
+/* flags for pgstat_flush_backend() */
+#define PGSTAT_REPORT_SKIPPED_VACUUM (1 << 0) /* vacuum is skipped */
+#define PGSTAT_REPORT_SKIPPED_ANALYZE (1 << 1) /* analyze is skipped */
+#define PGSTAT_REPORT_SKIPPED_AUTOVACUUM (1 << 2) /* autovacuum is skipped */
+#define PGSTAT_REPORT_SKIPPED_AUTOANALYZE (1 << 3) /* autoanalyze is
+ * skipped */
+extern void pgstat_report_skipped_vacuum_analyze(Oid relid, int flags);
+
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
* pgstat_assoc_relation() to do so. See its comment for why this is done
diff --git a/src/test/isolation/expected/vacuum-skip-locked.out b/src/test/isolation/expected/vacuum-skip-locked.out
index 99db281a159..e2cff175b7c 100644
--- a/src/test/isolation/expected/vacuum-skip-locked.out
+++ b/src/test/isolation/expected/vacuum-skip-locked.out
@@ -1,6 +1,6 @@
Parsed test spec with 2 sessions
-starting permutation: lock_share vac_specified commit
+starting permutation: lock_share vac_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -10,8 +10,22 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -20,8 +34,22 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
+
-starting permutation: lock_share analyze_specified commit
+starting permutation: lock_share analyze_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -31,8 +59,22 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share analyze_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_share analyze_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -41,8 +83,22 @@ step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_share vac_analyze_specified commit
+starting permutation: lock_share vac_analyze_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -52,8 +108,22 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_analyze_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_analyze_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -62,8 +132,22 @@ step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_share vac_full_specified commit
+starting permutation: lock_share vac_full_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -73,8 +157,22 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_full_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_full_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -83,8 +181,22 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_specified commit
+
+starting permutation: lock_access_exclusive vac_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -94,8 +206,22 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_all_parts commit
+
+starting permutation: lock_access_exclusive vac_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -104,8 +230,22 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive analyze_specified commit
+
+starting permutation: lock_access_exclusive analyze_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -115,8 +255,22 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_access_exclusive analyze_all_parts commit
+starting permutation: lock_access_exclusive analyze_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -126,8 +280,22 @@ step commit:
COMMIT;
step analyze_all_parts: <... completed>
-
-starting permutation: lock_access_exclusive vac_analyze_specified commit
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 0| 1| 0
+part2 | 0| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_analyze_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -137,8 +305,22 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_analyze_all_parts commit
+
+starting permutation: lock_access_exclusive vac_analyze_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -148,8 +330,22 @@ step commit:
COMMIT;
step vac_analyze_all_parts: <... completed>
-
-starting permutation: lock_access_exclusive vac_full_specified commit
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 1| 0| 1| 0
+part2 | 1| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_full_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -159,8 +355,22 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_access_exclusive vac_full_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_full_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -169,3 +379,17 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+(3 rows)
+
diff --git a/src/test/isolation/specs/vacuum-skip-locked.spec b/src/test/isolation/specs/vacuum-skip-locked.spec
index 3fad6e1c92a..b0da75d4b6d 100644
--- a/src/test/isolation/specs/vacuum-skip-locked.spec
+++ b/src/test/isolation/specs/vacuum-skip-locked.spec
@@ -33,6 +33,15 @@ step commit
COMMIT;
}
+step check_stat
+{
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+}
+
session s2
step vac_specified { VACUUM (SKIP_LOCKED) part1, part2; }
step vac_all_parts { VACUUM (SKIP_LOCKED) parted; }
@@ -43,19 +52,19 @@ step vac_analyze_all_parts { VACUUM (ANALYZE, SKIP_LOCKED) parted; }
step vac_full_specified { VACUUM (SKIP_LOCKED, FULL) part1, part2; }
step vac_full_all_parts { VACUUM (SKIP_LOCKED, FULL) parted; }
-permutation lock_share vac_specified commit
-permutation lock_share vac_all_parts commit
-permutation lock_share analyze_specified commit
-permutation lock_share analyze_all_parts commit
-permutation lock_share vac_analyze_specified commit
-permutation lock_share vac_analyze_all_parts commit
-permutation lock_share vac_full_specified commit
-permutation lock_share vac_full_all_parts commit
-permutation lock_access_exclusive vac_specified commit
-permutation lock_access_exclusive vac_all_parts commit
-permutation lock_access_exclusive analyze_specified commit
-permutation lock_access_exclusive analyze_all_parts commit
-permutation lock_access_exclusive vac_analyze_specified commit
-permutation lock_access_exclusive vac_analyze_all_parts commit
-permutation lock_access_exclusive vac_full_specified commit
-permutation lock_access_exclusive vac_full_all_parts commit
+permutation lock_share vac_specified commit check_stat
+permutation lock_share vac_all_parts commit check_stat
+permutation lock_share analyze_specified commit check_stat
+permutation lock_share analyze_all_parts commit check_stat
+permutation lock_share vac_analyze_specified commit check_stat
+permutation lock_share vac_analyze_all_parts commit check_stat
+permutation lock_share vac_full_specified commit check_stat
+permutation lock_share vac_full_all_parts commit check_stat
+permutation lock_access_exclusive vac_specified commit check_stat
+permutation lock_access_exclusive vac_all_parts commit check_stat
+permutation lock_access_exclusive analyze_specified commit check_stat
+permutation lock_access_exclusive analyze_all_parts commit check_stat
+permutation lock_access_exclusive vac_analyze_specified commit check_stat
+permutation lock_access_exclusive vac_analyze_all_parts commit check_stat
+permutation lock_access_exclusive vac_full_specified commit check_stat
+permutation lock_access_exclusive vac_full_all_parts commit check_stat
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a65a5bf0c4f..9b2075d3373 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1835,13 +1835,21 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
+ pg_stat_get_last_skipped_vacuum_time(c.oid) AS last_skipped_vacuum,
pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
+ pg_stat_get_last_skipped_autovacuum_time(c.oid) AS last_skipped_autovacuum,
pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
+ pg_stat_get_last_skipped_analyze_time(c.oid) AS last_skipped_analyze,
pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
+ pg_stat_get_last_skipped_autoanalyze_time(c.oid) AS last_skipped_autoanalyze,
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
+ pg_stat_get_skipped_vacuum_count(c.oid) AS skipped_vacuum_count,
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
+ pg_stat_get_skipped_autovacuum_count(c.oid) AS skipped_autovacuum_count,
pg_stat_get_analyze_count(c.oid) AS analyze_count,
+ pg_stat_get_skipped_analyze_count(c.oid) AS skipped_analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
+ pg_stat_get_skipped_autoanalyze_count(c.oid) AS skipped_autoanalyze_count,
pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
@@ -2346,13 +2354,21 @@ pg_stat_sys_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_skipped_vacuum,
last_autovacuum,
+ last_skipped_autovacuum,
last_analyze,
+ last_skipped_analyze,
last_autoanalyze,
+ last_skipped_autoanalyze,
vacuum_count,
+ skipped_vacuum_count,
autovacuum_count,
+ skipped_autovacuum_count,
analyze_count,
+ skipped_analyze_count,
autoanalyze_count,
+ skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
@@ -2401,13 +2417,21 @@ pg_stat_user_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_skipped_vacuum,
last_autovacuum,
+ last_skipped_autovacuum,
last_analyze,
+ last_skipped_analyze,
last_autoanalyze,
+ last_skipped_autoanalyze,
vacuum_count,
+ skipped_vacuum_count,
autovacuum_count,
+ skipped_autovacuum_count,
analyze_count,
+ skipped_analyze_count,
autoanalyze_count,
+ skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-05-14 21:46 ` Re: Track skipped tables during autovacuum and autoanalyze Zsolt Parragi <[email protected]>
2026-05-15 00:59 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
@ 2026-05-15 05:03 ` Yugo Nagata <[email protected]>
2026-05-15 09:02 ` Re: Track skipped tables during autovacuum and autoanalyze Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Yugo Nagata @ 2026-05-15 05:03 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Michael Paquier <[email protected]>; pgsql-hackers
On Thu, 14 May 2026 19:59:18 -0500
Sami Imseih <[email protected]> wrote:
> > Doesn't these also require a PGSTAT_FILE_FORMAT_ID change?
>
> right. that was missed. Fixed in the attached.
Thank you for updating the patch.
>
> > There's also an asymmetric case for the skipped counters, is that intentional?
> >
> > | Command | `skipped_vacuum_count` |
> > `skipped_analyze_count` |
> > |-----------------------------------------|------------------------|-------------------------|
> > | `VACUUM (FULL, ANALYZE, SKIP_LOCKED) t` | 0 | 1
> > |
> > | `VACUUM (ANALYZE, SKIP_LOCKED) t` | 1 | 1
> > |
> > | `VACUUM (FULL, SKIP_LOCKED) t` | 0 | 0
>
> Yeah, this is because vacuum_count and last_vacuum also skip VACUUM FULL.
> That was mentioned earlier in the thread.
Right.
> > > Initially, I was concerned that something might go wrong if a concurrent
> > > session performed DROP TABLE or ALTER TABLE RENAME between RangeVarGetRelidExtended()
> > > and RangeVarGetRelid(), but I could not find any actual issue. Even when the table
> > > name is changed, the correct statistics entry is updated correctly.
> >
> > A DROP TABLE can cause a missed skip in statistics, which is
> > reproducible with a custom injection point and tap test, see the
> > attached patch. The race window is quite minimal, but it exists.
>
> If the table is dropped, there are no stats to update. right?
In my analysis, even if the table is dropped or renamed just before calling RangeVarGetRelid()
(at the injection point you added), RangeVarGetRelid() still returns the table's OID. So it
seems possible that the statistics entry is updated locally, but it would be released shortly
afterward and thus eventually becomes invisible.
However, I'm not entirely sure whether this behavior is always guaranteed.
Could anyone clarify this?
Regards,
Yugo Nagata
--
Yugo Nagata <[email protected]>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Track skipped tables during autovacuum and autoanalyze
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-26 23:07 ` Re: Track skipped tables during autovacuum and autoanalyze Michael Paquier <[email protected]>
2026-03-27 16:48 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-13 08:05 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-04-27 11:32 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-05-14 21:46 ` Re: Track skipped tables during autovacuum and autoanalyze Zsolt Parragi <[email protected]>
2026-05-15 00:59 ` Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-05-15 05:03 ` Re: Track skipped tables during autovacuum and autoanalyze Yugo Nagata <[email protected]>
@ 2026-05-15 09:02 ` Zsolt Parragi <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Zsolt Parragi @ 2026-05-15 09:02 UTC (permalink / raw)
To: [email protected]
> If the table is dropped, there are no stats to update. right?
Ops, right. I focused too much on "all warnings should be visible in
the statistic, so the sum of warnings and statistics should match",
but of course that's not the case.
> However, I'm not entirely sure whether this behavior is always guaranteed.
> Could anyone clarify this?
There's another different corner-case if I move the injection point
inside pgstat_report_skipped_vacuum_analyze, after releasing the
syscache.
If the drop happens at that point, we insert an orphaned record into
the statistics, and it will be visible with the internal functions
(e.g. pg_stat_get_skipped_autoanalyze_count).
It is still invisible in the pg_stat_all_tables view, but now that
I've looked more at the code, I think internally it will stay there
permanently, even surviving pg_stat_reset?
> RangeVarGetRelid() still returns the table's OID
Yes, I also reached the same conclusion, I started testing because I
tried to see if I could break the double relid retrieval by some
scenarios (alter rename + create, drop-create etc), but it's not
possible. The ereports can execute CHECK_FOR_INTERRUPTS, but that
never calls ProcessCatchupInterrupt, and because of that it never runs
AccceptInvalidationMessages. And that's when I noticed that the
warning isn't visible in the statistics at all, and got distracted...
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2026-05-15 09:02 UTC | newest]
Thread overview: 17+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-24 14:58 Re: Track skipped tables during autovacuum and autoanalyze Sami Imseih <[email protected]>
2026-03-25 02:07 ` Michael Paquier <[email protected]>
2026-03-25 17:12 ` Sami Imseih <[email protected]>
2026-03-25 23:26 ` Michael Paquier <[email protected]>
2026-03-26 01:31 ` Michael Paquier <[email protected]>
2026-03-26 23:07 ` Michael Paquier <[email protected]>
2026-03-27 16:48 ` Sami Imseih <[email protected]>
2026-04-13 08:05 ` Yugo Nagata <[email protected]>
2026-04-22 12:49 ` Sami Imseih <[email protected]>
2026-04-27 11:32 ` Yugo Nagata <[email protected]>
2026-05-04 20:44 ` Sami Imseih <[email protected]>
2026-05-12 09:47 ` Yugo Nagata <[email protected]>
2026-05-12 14:50 ` Sami Imseih <[email protected]>
2026-05-14 21:46 ` Zsolt Parragi <[email protected]>
2026-05-15 00:59 ` Sami Imseih <[email protected]>
2026-05-15 05:03 ` Yugo Nagata <[email protected]>
2026-05-15 09:02 ` Zsolt Parragi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox