| From: | Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> |
|---|---|
| To: | surya poondla <suryapoondla4(at)gmail(dot)com>, Sami Imseih <samimseih(dot)pg(at)gmail(dot)com>, Scott Ray <scott(at)scottray(dot)io> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: pg_xmin_horizon: a system view of everything pinning the xmin horizon |
| Date: | 2026-11-16 21:41:39 |
| Message-ID: | CALj2ACWkb76ea7rKj2HOHdo0o_6BXnfKetELkF=8_qL1NzpHiA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
On Tue, Sep 29, 2026 at 11:28 AM surya poondla <suryapoondla4(at)gmail(dot)com> wrote:
>
> Andres's 2023 sketch [1], with one row per database plus a NULL-datname row for the
> system-wide horizons.
> Tom described the other one [2]: *all* the active processes and their related
> xmins, then more entries for all active replication slots, prepared
> xacts, etc etc. Picking out the ones causing trouble is then the user's
> concern.
>
> [1] https://www.postgresql.org/message-id/20231026174136.4et3ktuegmtlgxfs@awork3.anarazel.de
> [2] https://www.postgresql.org/message-id/30712.1585854944@sss.pgh.pa.us
I have been looking at this. I tend to agree with Tom's thoughts, that
is, core emits all the processes, slots, prepared xacts etc. along
with their xmins, and the summary views could be built by the users.
I'm sure the users will need different flavors of the info such as
what is stopping vacuum per-database and per-cluster, top N blockers,
or, if the oldest blocker goes away, how far the xmin horizon moves,
those only blocking the catalog xmin horizon etc. etc., and they may
even join it with other existing views to get a nice readable format
or such. And, I don't mind optionally having a couple of common
summary SQL views on top of this in core.
I looked at v10 again and it is mostly on the above lines. However, I
think the patch is unnecessarily big at ~1480 LoC (for example, the
tests don't have to be that long, the docs seem to be overly
explaining things, and some code is unnecessarily complicated). And,
the columns it emits seem to be too confusing, there are just too many
xmins (shared, catalog and data horizon).
I think we can simplify it a lot. How about something like the
attached? I could bring it down to ~450 LoC with simplified docs, code
and tests (for the same coverage). For example, the output with this
patch is at [1] vs v10 is at [2], and one can build a summary view
such as [3].
[1]
```
SELECT * FROM pg_xmin_horizon ORDER BY xmin;
kind | pid | slot_name | gid | datid | xmin
-------------------+-------+-----------+--------+-------+------
slot_catalog_xmin | | sub_slot | | | 669
slot_catalog_xmin | | standby1 | | | 677
backend | 12122 | | | 16384 | 679
slot_xmin | | standby1 | | | 681
standby_feedback | 12060 | | | | 683
backend | 12165 | | | 5 | 685
backend | 12189 | | | 5 | 686
prepared_xact | | | order1 | 16384 | 686
backend | 12174 | | | 16384 | 687
(9 rows)
```
[2]
```
SELECT * FROM pg_xmin_horizon ORDER BY age(shared_xmin) DESC NULLS LAST;
kind | pid | slot_name | gid | datid | datname |
xid | shared_xmin | catalog_xmin | data_xmin | xact_start
------------------+-------+-----------+--------+-------+----------+-----+-------------+--------------+-----------+-------------------------------
replication_slot | | sub_slot | | 16384 | db1 |
| 669 | 669 | |
replication_slot | | standby1 | | | |
| 677 | 677 | 681 |
backend | 11880 | | | 16384 | db1 |
| 679 | 679 | 679 | 2026-11-16
11:35:15.71561-08
standby_feedback | 11818 | | | | |
| 683 | 683 | 683 |
backend | 11923 | | | 5 | postgres |
| 685 | 685 | 685 | 2026-11-16
11:35:17.323817-08
backend | 11947 | | | 5 | postgres |
| 686 | 686 | 686 | 2026-11-16
11:35:24.383231-08
prepared_xact | | | order1 | 16384 | db1 |
686 | 686 | 686 | 686 | 2026-11-16
11:35:17.834223-08
backend | 11932 | | | 16384 | db1 |
687 | 687 | 687 | 687 | 2026-11-16
11:35:17.839764-08
(8 rows)
```
[3]
```
-- Show what is stopping vacuum per-database i.e. the oldest blocker.
SELECT DISTINCT ON (d.datname) d.datname, x.kind, x.pid, x.slot_name,
x.gid, x.xmin
FROM pg_database d
JOIN pg_xmin_horizon x
ON x.datid = d.oid OR x.kind NOT IN ('backend',
'prepared_xact', 'slot_catalog_xmin')
ORDER BY d.datname, x.xmin;
datname | kind | pid | slot_name | gid | xmin
-----------+-----------+-------+-----------+-----+------
db1 | backend | 12122 | | | 679
postgres | slot_xmin | | standby1 | | 681
template0 | slot_xmin | | standby1 | | 681
template1 | slot_xmin | | standby1 | | 681
(4 rows)
```
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
| Attachment | Content-Type | Size |
|---|---|---|
| nocfbot-v11-0001-Add-pg_xmin_horizon-system-view.patch | application/x-patch | 24.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Zsolt Parragi | 2026-11-16 21:50:53 | Re: ORDER BY ALL |
| Previous Message | Matthew Mochalkin | 2026-11-16 21:00:44 | libpq: SCRAM client proofs serialize across threads on OpenSSL 3 (re-init of the HMAC per PBKDF2 iteration) |