Horton

Re: Vacuum statistics

Alena Rybakina <a.rybakina@postgrespro.ru>

View thread
From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Andrei Lepikhov <lepihov@gmail.com>, Alexander Korotkov <aekorotkov@gmail.com>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>, Andrei Zubkov <zubkov@moonset.ru>, Alena Rybakina <lena.ribackina@yandex.ru>, pgsql-hackers <pgsql-hackers@postgresql.org>, jian he <jian.universality@gmail.com>
Date: 2025-05-09T12:31:11Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Move wal_buffers_full from PgStat_PendingWalStats to WalUsage

Hi!

On 22.04.2025 21:23, Andrei Lepikhov wrote:
> On 10/28/24 14:40, Alexander Korotkov wrote:
>> On Sun, Aug 25, 2024 at 6:59 PM Alena Rybakina
>>> If I missed something or misunderstood, can you explain in more detail?
>>
>> Actually, I mean why do we need a possibility to return statistics for
>> all tables/indexes in one function call?  User anyway is supposed to
>> use pg_stat_vacuum_indexes/pg_stat_vacuum_tables view, which do
>> function calls one per relation.  I suppose we can get rid of
>> possibility to get all the objects in one function call and just
>> return a tuple from the functions like other pgstatfuncs.c functions
>> do.
> I suppose it was designed this way because databases may contain 
> thousands of tables and indexes - remember, at least, partitions. But 
> it may be okay to use the SRF_FIRSTCALL_INIT / SRF_RETURN_NEXT API. I 
> think by registering a prosupport routine predicting cost and rows of 
> these calls, we may let the planner build adequate plans for queries 
> involving those stats - people will definitely join it with something 
> else in the database.
>
I think we can add this, but first we need to answer the main question - 
are there cases when we have statistics for a relation that are not in 
pg_class? After all, we have views that show vacuum statistics for all 
relations for objects stored in pg_class.

+CREATE VIEW pg_stat_vacuum_tables AS
...
FROM pg_class rel
+  JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
+  LATERAL*pg_stat_get_vacuum_tables(rel.oid)*  stats
+WHERE rel.relkind = 'r';

I tend to think that such a case will happen because to solve the 
problem with the memory consumed for storing vacuum statistics, we need 
to store them separately from the relations' statistics (I already wrote 
the code here [0]), so
the approach with the output of all statistics from a snapshot, as we 
did here [1] and removed this approach here [2] and this approach now 
makes sense and it is worth organizing it as you suggest.

I can add the code if no one is against it.


[0] 
https://www.postgresql.org/message-id/2a04ad18-5572-4633-848b-eb57209e7ac0%40postgrespro.ru

[1] 
https://www.postgresql.org/message-id/995657bc-9966-47c0-b085-4c5e8886d249%40postgrespro.ru

[2] 
https://www.postgresql.org/message-id/CAPpHfdvSo3mfH%3D2m4ADCHAuN%3D22SnBY3TrPaPbGKTw3r_Jaw7Q%40mail.gmail.com

-- 
Regards,
Alena Rybakina
Postgres Professional