pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRequest for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
12+ messages / 7 participants
[nested] [flat]
* Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 13:58 Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Ron Johnson @ 2024-05-09 13:58 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Because vacuum is vacuum. Or rename VACUUM FULL to something else, so that
no one can reasonably assume that:
1) a command with VACUUM in the name should update the last_vacuumed field,
or that
2) VACUUM FULL should be done often, since FULL is seemingly better than
not full.
Proposed new name: RECREATE TABLE <table_name> [ USING <index_name> ];
The USING clause added since CLUSTER is really a sorted VACUUM FULL,
I'm not wedded to the name RECREATE TABLE, but am wedded to the fact that
VACUUM FULL is a horrible name for what it does.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 14:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2024-05-09 14:06 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, May 9, 2024 at 6:58 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> I'm not wedded to the name RECREATE TABLE, but am wedded to the fact that
> VACUUM FULL is a horrible name for what it does.
>
>
I think there is general agreement here but your cure is arguably worse
than the disease.
A more realistic option would be to at least put "VACUUM FULL" into its own
section of the documentation instead of having FULL be an optional modifier
to the normal VACUUM command.
Are there other spots in the documentation that misrepresent the situation
to our readers?
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 14:21 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Ron Johnson @ 2024-05-09 14:21 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, May 9, 2024 at 10:07 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thu, May 9, 2024 at 6:58 AM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>>
>> I'm not wedded to the name RECREATE TABLE, but am wedded to the fact that
>> VACUUM FULL is a horrible name for what it does.
>>
>>
> I think there is general agreement here but your cure is arguably worse
> than the disease.
>
Why? RECREATE TABLE says exactly what it does: recreates the table, and
*doesn't* pretend to do something it doesn't do (vacuum the table).
Like I said, though, I'm not wedded to the name RECREATE TABLE.
> A more realistic option would be to at least put "VACUUM FULL" into its
> own section of the documentation instead of having FULL be an optional
> modifier to the normal VACUUM command.
>
That would certainly be easier and faster. But still say VACUUM.
> Are there other spots in the documentation that misrepresent the situation
> to our readers?
>
That's distracting from the question at hand.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 14:49 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: David G. Johnston @ 2024-05-09 14:49 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, May 9, 2024 at 7:21 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Thu, May 9, 2024 at 10:07 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Thu, May 9, 2024 at 6:58 AM Ron Johnson <ronljohnsonjr@gmail.com>
>> wrote:
>>
>>>
>>> I'm not wedded to the name RECREATE TABLE, but am wedded to the fact
>>> that VACUUM FULL is a horrible name for what it does.
>>>
>>>
>> I think there is general agreement here but your cure is arguably worse
>> than the disease.
>>
>
> Why? RECREATE TABLE says exactly what it does: recreates the table, and
> *doesn't* pretend to do something it doesn't do (vacuum the table).
>
>
That's distracting from the question at hand, which is whether and how to
go about changing this, not whether the alternative naming is better than
the existing one. It isn't so much better that the pain of change seems
worth it. Mostly because it isn't like the universe simply gets
reprogrammed to map the old onto the new, and having the old and new
co-exist produces a burden.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 14:53 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Ron Johnson @ 2024-05-09 14:53 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, May 9, 2024 at 10:50 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thu, May 9, 2024 at 7:21 AM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> On Thu, May 9, 2024 at 10:07 AM David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>>> On Thu, May 9, 2024 at 6:58 AM Ron Johnson <ronljohnsonjr@gmail.com>
>>> wrote:
>>>
>>>>
>>>> I'm not wedded to the name RECREATE TABLE, but am wedded to the fact
>>>> that VACUUM FULL is a horrible name for what it does.
>>>>
>>>>
>>> I think there is general agreement here but your cure is arguably worse
>>> than the disease.
>>>
>>
>> Why? RECREATE TABLE says exactly what it does: recreates the table, and
>> *doesn't* pretend to do something it doesn't do (vacuum the table).
>>
>>
> That's distracting from the question at hand, which is whether and how to
> go about changing this, not whether the alternative naming is better than
> the existing one. It isn't so much better that the pain of change seems
> worth it. Mostly because it isn't like the universe simply gets
> reprogrammed to map the old onto the new, and having the old and new
> co-exist produces a burden.
>
I requested a feature from an open source project. The appropriate
response when Those Doing The Work don't want to do that work is "we don't
want to expend the effort on that".
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 15:12 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Scott Ribe @ 2024-05-09 15:12 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On May 9, 2024, at 8:49 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> Why? RECREATE TABLE says exactly what it does: recreates the table, and doesn't pretend to do something it doesn't do (vacuum the table).
The current method is just one alternative to implement the goal: removing all dead tuples. Exposing the implementation detail in the command name is not really any kind of improvement, IMO.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 20:11 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2024-05-09 20:11 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, 2024-05-09 at 09:58 -0400, Ron Johnson wrote:
> Because vacuum is vacuum.
The problem is that the two commands do something different, so it
would be misleading. Renaming VACUUM (FULL) is a good idea in principle,
but I think that is more than 10 years too late. The compatibility
break would be too painful.
Perhaps you could write a patch to add a column "last_rewritten"
to "pg_stat_all_tables"...
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 20:39 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 12+ messages in thread
From: Ron Johnson @ 2024-05-09 20:39 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, May 9, 2024 at 4:11 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Thu, 2024-05-09 at 09:58 -0400, Ron Johnson wrote:
> > Because vacuum is vacuum.
>
> The problem is that the two commands do something different, so it
> would be misleading. Renaming VACUUM (FULL) is a good idea in principle,
> but I think that is more than 10 years too late. The compatibility
> break would be too painful.
>
Make VACUUM (FULL) a synonym for RECREATE TABLE, then say in the docs that
VACUUM (FULL) is deprecated.
Then drop it in PG 27...
Perhaps you could write a patch to add a column "last_rewritten"
> to "pg_stat_all_tables"...
>
I'm a worse C programmer than I am a DBA.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 20:45 Rui DeSousa <rui.desousa@icloud.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Rui DeSousa @ 2024-05-09 20:45 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On May 9, 2024, at 4:39 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> On Thu, May 9, 2024 at 4:11 PM Laurenz Albe <laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>> wrote:
> On Thu, 2024-05-09 at 09:58 -0400, Ron Johnson wrote:
> > Because vacuum is vacuum.
>
> The problem is that the two commands do something different, so it
> would be misleading. Renaming VACUUM (FULL) is a good idea in principle,
> but I think that is more than 10 years too late. The compatibility
> break would be too painful.
>
> Make VACUUM (FULL) a synonym for RECREATE TABLE, then say in the docs that VACUUM (FULL) is deprecated.
>
I think if you rename vacuum full then vacuum compact would be better fit than recreate table.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-09 21:12 Nikolay Samokhvalov <samokhvalov@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Nikolay Samokhvalov @ 2024-05-09 21:12 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, May 9, 2024 at 13:39 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Thu, May 9, 2024 at 4:11 PM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>
>> On Thu, 2024-05-09 at 09:58 -0400, Ron Johnson wrote:
>> > Because vacuum is vacuum.
>>
>> The problem is that the two commands do something different, so it
>> would be misleading. Renaming VACUUM (FULL) is a good idea in principle,
>> but I think that is more than 10 years too late. The compatibility
>> break would be too painful.
>>
>
> Make VACUUM (FULL) a synonym for RECREATE TABLE, then say in the docs that
> VACUUM (FULL) is deprecated.
>
> Then drop it in PG 27...
>
> Perhaps you could write a patch to add a column "last_rewritten"
>> to "pg_stat_all_tables"...
>>
>
> I'm a worse C programmer than I am a DBA.
>
It's never late.
I like the idea of RECREATE TABLE and deprecating VACUUM FULL a lot. It
always seemed to me a non-user-friendly naming choice like pg_xlog or
psql's \q, both of which are solved already.
With RECREATE TABLE, one day, we would be probably have RECREATE TABLE
CONCURRENTLY implemented, making pg_repack less needed.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-10 11:21 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Alvaro Herrera @ 2024-05-10 11:21 UTC (permalink / raw)
To: Nikolay Samokhvalov <samokhvalov@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 2024-May-09, Nikolay Samokhvalov wrote:
> With RECREATE TABLE, one day, we would be probably have RECREATE TABLE
> CONCURRENTLY implemented, making pg_repack less needed.
If we really wanted to rename VACUUM FULL, I would go for a name that
betrays its CLUSTER implementation -- maybe CLUSTER UNSORTED or so --
and document it together with regular CLUSTER. We don't really need a
new top-level command for it IMO.
As for pg_repack, I'd much rather get VACUUM SQUEEZE using pg_squeeze as
an implementation starting point (without the scheduling stuff though.)
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum
@ 2024-05-10 11:53 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 12+ messages in thread
From: Ron Johnson @ 2024-05-10 11:53 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, May 10, 2024 at 7:21 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
> On 2024-May-09, Nikolay Samokhvalov wrote:
>
> > With RECREATE TABLE, one day, we would be probably have RECREATE TABLE
> > CONCURRENTLY implemented, making pg_repack less needed.
>
> If we really wanted to rename VACUUM FULL, I would go for a name that
> betrays its CLUSTER implementation -- maybe CLUSTER UNSORTED or so --
>
I considered that, for the same reason. But there's no such thing as an
unsorted CLUSTER, since the whole purpose of CLUSTER is to sort the data.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2024-05-10 11:53 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-09 13:58 Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-09 14:06 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-09 14:21 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-09 14:49 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-09 14:53 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-09 15:12 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-09 20:11 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-09 20:39 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-09 20:45 ` Rui DeSousa <rui.desousa@icloud.com>
2024-05-09 21:12 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
2024-05-10 11:21 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-05-10 11:53 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox