Add a statistics view to track usage of deprecated features
Hi hackers,
I would like to propose a patch that adds a new cumulative statistics
view, pg_stat_deprecated_features, which tracks how often each
deprecated feature is used across the cluster.
Motivation:
When we deprecate a feature today, we have little visibility into
whether anyone is still using it. For example, MD5 password support
was marked as deprecated in v18 [1]/messages/by-id/ZwbfpJJol7lDWajL@nathan, and v19 added warnings when an
MD5 password is set or used to authenticate. Even so, there is still
no straightforward way for an administrator to answer a basic
question: is anything in my cluster still relying on MD5 passwords?
The only signals available are server log warnings (where they exist)
and ad hoc catalog queries, neither of which gives a cluster-wide,
accumulated count of actual usage.
This is useful from two directions.
For users, it makes major-version upgrades easier. Before upgrading to
a release that removes a deprecated feature, an administrator wants to
confirm that their workload does not depend on it. Today that is hard
to confirm, so the removal can turn into a surprise breakage
discovered only after the upgrade. This view lets administrators check
directly, before upgrading, whether and how often each deprecated
feature is still being used.
For hackers, it keeps the existence of deprecated features visible
instead of letting them be forgotten, and it encourages actually
removing them in time. Each deprecated feature we remove lets us
delete the code that supports it, which keeps an ever-growing codebase
more maintainable rather than carrying obsolete code indefinitely.
This proposal is inspired by similar facilities in other systems: SQL
Server's "Deprecated Features" performance object, which counts uses
of features scheduled for removal [2]https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-deprecated-features-object, and Kubernetes'
apiserver_requested_deprecated_apis metric, which reports requests
made to deprecated APIs [3]https://kubernetes.io/docs/reference/using-api/deprecation-policy/#rest-resources-aka-api-objects.
Design:
The patch adds a cumulative statistics view with one row per tracked
deprecated feature, showing cluster-wide usage. Depending on the
feature, usage is counted when the SQL using it is parsed, when the
command is executed, or when an authentication exchange relying on it
succeeds. The columns are deliberately kept to a minimal set, shown in
the example below.
Example Usage:
postgres=# SELECT * FROM pg_stat_deprecated_features;
name | usage_count | last_used |
stats_reset
------------------------+-------------+-----------------------------+-------------------------------
md5_password_set | 3 | 2026-06-27 09:02:11.114+09 |
2026-06-27 00:00:00.000+09
md5_password_auth | 1432 | 2026-06-27 10:21:54.018+09 |
2026-06-27 00:00:00.000+09
global_temporary_table | 0 | |
2026-06-27 00:00:00.000+09
local_temporary_table | 27 | 2026-06-27 10:18:42.551+09 |
2026-06-27 00:00:00.000+09
(4 rows)
Why not the custom cumulative statistics API:
- No hooks at the measurement points. Deprecated usage is detected in
the grammar (the GLOBAL/LOCAL keywords are discarded right after
parsing) and in the authentication code, where an extension cannot get
control.
- Being present by default is the point. This data can only serve as
the basis for removal decisions, which core today makes blind, if it
is in every cluster from the start. An extension installed after the
fact cannot recover the usage it missed before it was installed.
Scope of this patch:
To keep the initial submission small and reviewable, this first
version tracks only two areas: MD5 password usage (setting an MD5
password, and authenticating with one) and the deprecated GLOBAL/LOCAL
TEMPORARY table syntax. The view and the infrastructure are designed
so that more features can be added incrementally.
Many other deprecated features could be tracked in the same way, for example:
- VACUUM FULL command
- deprecated functions (getpgusername, obj_description_oid,
pg_get_viewdef_text, the txid_* family)
- deprecated operators (<^, >^, ?#, @@@)
- older DDL spellings (EXECUTE PROCEDURE in CREATE TRIGGER, PROCEDURE=
in CREATE OPERATOR, CREATE ROLE ... IN GROUP, ALTER GROUP)
Rather than adding these now, I would first like to agree on the
overall direction, namely whether this view is something we want and
what criteria decide which deprecated features belong in it. Once we
have that agreement, I will implement the rest accordingly.
The patch is attached as a small series: 0001 adds the view and the
cumulative-statistics infrastructure, and the remaining patches add
counting for MD5 password usage and for the deprecated TEMPORARY table
syntax. The patches are split this way for ease of review, so the 0001
infrastructure patch does not compile on its own, because it defines
an empty set of tracked features. The series builds once the first
counting patch is applied.
Thoughts?
[1]: /messages/by-id/ZwbfpJJol7lDWajL@nathan
[2]: https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-deprecated-features-object
[3]: https://kubernetes.io/docs/reference/using-api/deprecation-policy/#rest-resources-aka-api-objects
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
v1-0001-Add-pg_stat_deprecated_features-view.patchapplication/x-patch; name=v1-0001-Add-pg_stat_deprecated_features-view.patchDownload+459-8
v1-0002-Count-md5_password_set-in-pg_stat_deprecated_feat.patchapplication/x-patch; name=v1-0002-Count-md5_password_set-in-pg_stat_deprecated_feat.patchDownload+68-14
v1-0003-Count-md5_password_auth-in-pg_stat_deprecated_fea.patchapplication/x-patch; name=v1-0003-Count-md5_password_auth-in-pg_stat_deprecated_fea.patchDownload+33-4
v1-0004-Count-global_temporary_table-in-pg_stat_deprecate.patchapplication/x-patch; name=v1-0004-Count-global_temporary_table-in-pg_stat_deprecate.patchDownload+56-4
v1-0005-Count-local_temporary_table-in-pg_stat_deprecated.patchapplication/x-patch; name=v1-0005-Count-local_temporary_table-in-pg_stat_deprecated.patchDownload+58-5
On Mon, Jun 29, 2026 at 10:24:02AM +0900, Shinya Kato wrote:
I would like to propose a patch that adds a new cumulative statistics
view, pg_stat_deprecated_features, which tracks how often each
deprecated feature is used across the cluster.
I am unconvinced that this is useful. First, I doubt that we will
ever consider switching back to non-deprecated a feature that was
marked as deprecated, which is what this kind of counter could be used
for in terms of decision-making. To me, the important fact is not in
counting if they're used, it's in warning users that we think, as core
developers, that these features should not be used and that we have
reasons to have marked them as deprecated based on technical reasons.
GLOBAL has been parked as deprecated in c3bc76bdb0ac, back in 2012.
We keep these keywords because we may change their interpretation in
the future (see the recent business with global temporary tables from
Dean Rasheed, fancy patch set). LOCAL and GLOBAL for temp tables are
not really candidate for removal, and counting that they are used
keywords is an irrelevant fact. Keeping them for compatibility is
maintenance-free, basically.
Speaking of which, patch 0003 has the idea to touch a shared memory
state of pgstats for what is an authentication path. This is not a
good idea security-wise, first, and it can stress pgstats under
repeated authentication requests, which is worse than the potential
security aspect, performance-wise. So, no, do not do something like
that.
As a whole, there are more interesting things to do than spending a
stats kind ID on something like this kind of proposal.
--
Michael
Michael Paquier <michael@paquier.xyz> writes:
On Mon, Jun 29, 2026 at 10:24:02AM +0900, Shinya Kato wrote:
I would like to propose a patch that adds a new cumulative statistics
view, pg_stat_deprecated_features, which tracks how often each
deprecated feature is used across the cluster.
I am unconvinced that this is useful.
I'm pretty down on it as well, because it presupposes that we have
a well-defined set of deprecated features. We're not that well
organized in that area, and probably never will be.
regards, tom lane
On Mon, Jun 29, 2026 at 11:58 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Paquier <michael@paquier.xyz> writes:
On Mon, Jun 29, 2026 at 10:24:02AM +0900, Shinya Kato wrote:
I would like to propose a patch that adds a new cumulative statistics
view, pg_stat_deprecated_features, which tracks how often each
deprecated feature is used across the cluster.I am unconvinced that this is useful.
I'm pretty down on it as well, because it presupposes that we have
a well-defined set of deprecated features. We're not that well
organized in that area, and probably never will be.
Thank you both for the quick feedback.
Michael's point about GLOBAL/LOCAL made me look at the deprecated
features one by one, and it corrected a wrong assumption on my side. I
had been treating everything marked deprecated as something that would
eventually be removed. In fact "deprecated" covers at least three
different cases:
1. deprecated and documented to be removed. MD5 passwords are the
clearest, with "deprecated and will be removed in a future release".
Some are softer, such as the txid_* functions ("still supported for
backward compatibility, but may be removed from a future release") and
the old geometric operator names <^ and >^ ("deprecated and will
eventually be removed").
2. deprecated but kept deliberately for backward compatibility with no
intent to remove, such as ALTER GROUP ("obsolete ... still accepted
for backwards compatibility").
3. deprecated but more likely to be reinterpreted than removed, which
is the GLOBAL/LOCAL case Michael describes. The CREATE TABLE docs say
PostgreSQL "might adopt a more standard-compliant interpretation of
their meaning".
Only the first case is what this view was meant for. The intent was to
let an administrator confirm, before a major-version upgrade, whether
their workload still relies on a feature that will be removed. Once it
is filtered down to features the documentation actually says will or
may be removed, though, the set is very small. It is essentially MD5,
plus a few softer or niche cases. Everything else I had listed falls
into the second or third case.
For a set that small, a dedicated cumulative statistics kind and a
standing view is out of proportion, which is Michael's point about not
spending a stats kind ID on this. So I would like to withdraw this
proposal.
--
Best regards,
Shinya Kato
NTT OSS Center