Re: Design of pg_stat_subscription_workers vs pgstats

On Sun, Feb 20, 2022 at 1:02 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2022-02-19 22:38:19 +0900, Masahiko Sawada wrote:
> > On Sat, Feb 19, 2022 at 5:32 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > On 2022-02-18 17:26:04 +0900, Masahiko Sawada wrote:
> > > > With this change, pg_stat_subscription_workers will be like:
> > > >
> > > > * subid
> > > > * subname
> > > > * subrelid
> > > > * error_count
> > > > * last_error_timestamp
> > >
> > > > This view will be extended by adding transaction statistics proposed
> > > > on another thread[1].
> > >
> > > I do not agree with these bits. What's the point of these per-relation stats
> > > at this poitns. You're just duplicating the normal relation pg_stats here.
> > >
> > > I really think we just should drop pg_stat_subscription_workers. Even if we
> > > don't, we definitely should rename it, because it still isn't meaningfully
> > > about workers.
> >
> > The view has stats per subscription worker (i.e., apply worker and
> > tablesync worker), not per relation. The subrelid is OID of the
> > relation that the tablesync worker is synchronizing. For the stats of
> > apply workers, it is null.
>
> That's precisely the misuse of the stats subsystem that I'm complaining about
> here. The whole design of pgstat (as it is today) only makes sense if you can
> loose a message and it doesn't matter much, because it's just an incremental
> counter increment that's lost. And to be able properly prune dead pgstat
> contents the underlying objects stats are kept around either need to be
> permanent (e.g. stats about WAL) or a record of objects needs to exist
> (e.g. stats about relations).
>
>
> Even leaving everything else aside, a key of (dboid, subid, subrelid), where
> subrelid can be NULL, but where (dboid, subid) is *not* unique, imo is poor
> relational design. What is the justification for mixing relation specific and
> non-relation specific contents in this view?

I think the current schema of the view with key (dboid, subid,
subrelid) comes from the fact that we store the same statistics for
both apply and tablesync. I think even if we have two separate views
for apply and tablesync, they would have almost the same columns
except for their keys. Also, from the relational design point of view,
pg_locks has a somewhat similar table schema; its database and
relation columns can be NULL.

>
>
> The patch you referenced [1] should just store the stats in the
> pg_stat_subscription view, not pg_stat_subscription_workers.
>
> It *does* make sense to keep stats about the number of table syncs that failed
> etc. But that should be a counter in pg_stat_subscription, not a row in
> pg_stat_subscription_workers.

We have discussed using pg_stat_subscription before but concluded it's
not an appropriate place to store error information because it ends up
keeping cumulative stats mixed with non-cumulative stats. To take a
precedent, we used to store accumulative statistics such as spill_txns
to pg_stat_replication, but then for the same reason we moved those
statistics to the new stats view, pg_stat_replication_slot. New
subscription statistics that we're introducing are cumulative
statistics whereas pg_stat_subscription is a dynamic statistics view.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/