Date: Mon, 13 Apr 2026 17:05:51 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Yugo Nagata <nagata@sraoss.co.jp>
Cc: Sami Imseih <samimseih@gmail.com>, Michael Paquier
 <michael@paquier.xyz>, Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Track skipped tables during autovacuum and autoanalyze
Message-Id: <20260413170551.5ec43ba5a2c848f0d46c6a0b@sraoss.co.jp>
In-Reply-To: <20260328161802.f35b5a3e739566ffd7c1053b@sraoss.co.jp>
References: <20260324151133.7940a5c1f2ebd594d54da481@sraoss.co.jp>
	<CAA5RZ0snnePNW1NKGKh+NyJ1CY26T5F_6-tTq+BHWM2kj1fN1g@mail.gmail.com>
	<20260325012847.e026ba1860c07288efe3e97d@sraoss.co.jp>
	<20260326192203.e6dbb8d80f8d27dc15ceee59@sraoss.co.jp>
	<acW8MW-Gb8Aj_TH2@paquier.xyz>
	<20260327163549.b5df519c0099970ddbb3412d@sraoss.co.jp>
	<CAA5RZ0tNh03LLgJVMA=PXSdY8YVoui4_GyfbfTrYK5cka3Q9Rw@mail.gmail.com>
	<20260328161802.f35b5a3e739566ffd7c1053b@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Mon__13_Apr_2026_17_05_51_+0900_T4n8olu8FNI1Va/O"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 2
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?JTE0rkwqgJ4R0yq0sUs8RlpM1Zuh+bPkEbWM9JPW43+JesgwEOKlAphQzwd4?=
 =?us-ascii?Q?AmhbWbCUF1l0Oqo30KCc+ggGaT8aldx+mRJIX+0VpmoCjon/r2CpjJJuGgog?=
 =?us-ascii?Q?yOSHej6+FDSiy/QP+vWsCVBNfGIo9CwTi+CZQ9uS683WZFGUYYFrA17IZ8F2?=
 =?us-ascii?Q?QzXjNK4xm+jfXtH5XFP1rj0gyXlVoAOacBw65FgFSOfsr+PnIWQrOzO5jFWT?=
 =?us-ascii?Q?DNUXZdbIAqEdxJCYd61Mi+4deecTumsusGUVsuJPFtGQWoQZsxqUu3zA2U9K?=
 =?us-ascii?Q?ZTgoYtNuEnm+voeu4KMyxNVxqqlpV+4k1lEaQel/P74MPM8I8GuqoIYPmrs1?=
 =?us-ascii?Q?yLhFRoUKLnQ9K4Ws6jDguKjSd0rqiMRNrSg104JF62j0XbnYNNIP8OoMBW5d?=
 =?us-ascii?Q?4LfnTQOHgswoNVjycplQr/Yz9o2LjxO5CmKgYB5mUXSlXNk4d8Trrq8+9/9j?=
 =?us-ascii?Q?twGNw6EGXj3L4biLcxf2nWjYxQXqACiRy1U34Z7gSc3OFVicFho7jHMQrQ/Y?=
 =?us-ascii?Q?KfVuD9SHmvCehvFiloPbZ8wqQ42BMpONYEU3R3exQzj8yZUMrvIspzUt/LGM?=
 =?us-ascii?Q?btx7jXiOSlizl0sREXGbBam4gKfSwswaQCM3aL4h1/jOwovtbYwCFTz+6iNu?=
 =?us-ascii?Q?Pp+J9Vt/6RIP0tj3WmzTmuZnAi9f84rIjFy20KS5aeVrnEu1kdRntadwxOQt?=
 =?us-ascii?Q?SWFDzgVGzcldXd883FVvXQlrrNB4FgqjuaQfE6PJHXDcd1eOiOtJKrwvdzP/?=
 =?us-ascii?Q?DTHFkFT91C06cMiIv+nLaKlv5u0vm8krtn3pZeXDbNr1Sk4/ec43W45aq4lY?=
 =?us-ascii?Q?eEmetWSYTBITcTa26tqEYmh2J442oWuS7F8DkIQb8ZJHD8xpLZ1VaADSrr1H?=
 =?us-ascii?Q?Y2UWw4oumcvmpXsX2rUbfue4mM6zhXNUee3LQD1GDOaKpQEwlzX3euZ8VTKk?=
 =?us-ascii?Q?D5tL4B56ZD3VW3OE9cYJW0Xw9qsukKaBfSyBv+h0Z6pXJlkKHJ4g2u3JFxaH?=
 =?us-ascii?Q?F/hg8bk9oLfs0KoERxJQ8IWc+nbN6HGWZ9IPQ0o/5yZgZgUPi6EkRXvrukmG?=
 =?us-ascii?Q?IWdTq/i5PzXdzisEoNYlPUT6EsGhWJctaFfXjfl7fBLDX0bKsetcJiqY3ALX?=
 =?us-ascii?Q?4SfD+F5e6qmkMoreiQzLm7uE+duvbOct5/xHq40ZQQBlRW+M7wUkpXcRdkMT?=
 =?us-ascii?Q?z/ZSNzeiQCl1IJU1Mkbba9Ny+ZQqu/N9HNHTPt7kVsOO7Zn3TD1/rDhYBQpy?=
 =?us-ascii?Q?DjYw16a4ZL3wTJxMzg/6IKIKeZQrGjBsA+IGULeg+jWeQzxyf0I6//6MMi2b?=
 =?us-ascii?Q?0zHjeUFrHVNz/GhSSqrymA+4jhgK8mmcicA29VVwQ0vPGpv372FWenjjyF8d?=
 =?us-ascii?Q?riit0fWF6a+vwfHz+mhOD/U8SF4ZuGi5GoRC9Crmxn6uta34SOS/oOSlhCq+?=
 =?us-ascii?Q?F6oVIZxXGIXhCyJEdi2L2V5vk8E1AwNU8DSzzlks/+QI08y688wN1vmQ6QAs?=
 =?us-ascii?Q?6o3MYwaPRSIFVWFYZlWGFM2p2OtOnKPyVjDaNOMKZqAwzJ9O5XhPwP498IyG?=
 =?us-ascii?Q?Eq4kOHWtxhHimnuNPt1DAtwNjzgMtmb9lfClhnPmBFP8xJS0+mCTh3T5M0G/?=
 =?us-ascii?Q?Q3wShoWGP9SbxI0u+1+rI7ArlTCiLxpH+6yKeQLhwEDZmHuZKVjytu6QCc9b?=
 =?us-ascii?Q?TvCWGZN3izPj+X7s01tl2RTpBkJvAYQH5sEcfhAS27hhi30H9Pzo/3ZAITqw?=
 =?us-ascii?Q?ePsRa5hds4LyOliWYmKtryXb7ocQy8xoqqn68gQOhkaJ3qZmWjRGVk4VcBlU?=
X-MS-Exchange-AntiSpam-MessageData-1: E/g9NUJW10NzFg==
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 387d627f-832b-4b67-0b0f-08de99337ba4
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 13 Apr 2026 08:05:53.2882
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 CWwWrVe3/IpT/wI1cOGhW2pf8aC+NyECTfqVY+tr0a3Ib0bXLkn/pb7xIe+Zv9PmgMf1Cpyrkjrz2vdrJzQfRA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS7P286MB6522
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/20260413170551.5ec43ba5a2c848f0d46c6a0b%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Mon__13_Apr_2026_17_05_51_+0900_T4n8olu8FNI1Va/O
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hello Sami Imseih,

On Sat, 28 Mar 2026 16:18:02 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

> On Fri, 27 Mar 2026 11:48:27 -0500
> Sami Imseih <samimseih@gmail.com> 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 <nagata@sraoss.co.jp>

--Multipart=_Mon__13_Apr_2026_17_05_51_+0900_T4n8olu8FNI1Va/O
Content-Type: text/x-diff;
 name="v4-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch"
Content-Disposition: attachment;
 filename="v4-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch"
Content-Transfer-Encoding: 7bit