Home > mailing lists

Thread: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Ron Johnson

Date:

09 May, 16:58:05

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.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

"David G. Johnston"

Date:

09 May, 17:06:25

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.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Ron Johnson

Date:

09 May, 17:21:18

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.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

"David G. Johnston"

Date:

09 May, 17:49:48

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.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Ron Johnson

Date:

09 May, 17:53:50

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".

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Scott Ribe

Date:

09 May, 18:12:31

> 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
detailin the command name is not really any kind of improvement, IMO.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Laurenz Albe

Date:

09 May, 23:11:55

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

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Ron Johnson

Date:

09 May, 23:39:01

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.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Rui DeSousa

Date:

09 May, 23:45:41

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> 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.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Nikolay Samokhvalov

Date:

10 May, 00:12:43

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.

Request for featu VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

"Wetmore, Matthew (CTR)"

Date:

10 May, 00:16:26

Ha! I’ve followed this all day.

VACUUM FULL should not be renamed, no matter what it actually does. Do you realize how many scripts worldwide would need to be changed? Lol.
It’s OpenSource. Grab the source, change whatever names you want, and now your PG is how you want it!

From: Nikolay Samokhvalov <samokhvalov@gmail.com>
Sent: Thursday, May 9, 2024 2:13 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

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.

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Alvaro Herrera

Date:

10 May, 14:21:08

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/

Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Ron Johnson

Date:

10 May, 14:53:06

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.

Re: Request for featu VACUUM FULL updates pg_stat_all_tables.last_vacuum

From

Wells Oliver

Date:

10 May, 21:40:37

I’ve always wanted SELECT to be renamed FETCH. What do we think?

Wells Oliver
wells.oliver@gmail.com

On Fri, May 10, 2024 at 11:37 Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com> wrote:

Ha! I’ve followed this all day.

VACUUM FULL should not be renamed, no matter what it actually does. Do you realize how many scripts worldwide would need to be changed? Lol.
It’s OpenSource. Grab the source, change whatever names you want, and now your PG is how you want it!

From: Nikolay Samokhvalov <samokhvalov@gmail.com>
Sent: Thursday, May 9, 2024 2:13 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Request for feature: VACUUM FULL updates pg_stat_all_tables.last_vacuum

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.