CLUSTER vs. VACUUM FULL

From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: CLUSTER vs. VACUUM FULL
Date: 2024-04-21 22:07:50
Message-ID: CANzqJaD8o0UuK42F==BXZA=L380n=aXt+VJ93PN3waFbNhZHBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

PG 14.11 on RHEL8

Why is VACUUM FULL recommended for compressing a table, when CLUSTER does
the same thing (similarly doubling disk space), and apparently runs just as
fast?

My tests:
Table: CDSLBXW.public.log

Time 1 Time 2 Time 3
secs secs secs
VACUUM FULL 44.2 39.3 42.3
CLUSTER 41.7 38.9 41.3

CDSLBXW=# \d public.log
Table "public.log"
Column | Type | Collation | Nullable |
Default
-----------------+-----------------------------+-----------+----------+-------------------------------------
log_id | bigint | | not null |
nextval('log_log_id_seq'::regclass)
level | numeric(10,0) | | |
source | character varying(255) | | |
username | character varying(255) | | |
user_login_id | character varying(255) | | |
user_ip_address | character varying(255) | | |
computer | character varying(255) | | |
search_tag | character varying(4000) | | |
log_group_id | integer | | |
created_on | timestamp without time zone | | not null |
created_by | integer | | |
xml_detail | bytea | | |
Indexes:
"pk_log" PRIMARY KEY, btree (log_id)
"idx_log_attr_source" btree (source)
"idx_log_level" btree (level)
"idx_log_search_tag" btree (search_tag)

CDSLBXW=# SELECT COUNT(*) FROM public.log;
count
---------
6774664
(1 row)

CDSLBXW=# \timing
CDSLBXW=# vacuum (full) public.log;
VACUUM
Time: 44190.799 ms (00:44.191)
CDSLBXW=#
CDSLBXW=# vacuum (full) public.log;
VACUUM
Time: 39316.824 ms (00:39.317)
CDSLBXW=# vacuum (full) public.log;
VACUUM
Time: 42336.268 ms (00:42.336)

CDSLBXW=# CLUSTER public.log USING pk_log;
CLUSTER
Time: 41722.335 ms (00:41.722)
CDSLBXW=#
CDSLBXW=# CLUSTER public.log USING pk_log;
CLUSTER
Time: 38915.128 ms (00:38.915)
CDSLBXW=#
CDSLBXW=# CLUSTER public.log USING pk_log;
CLUSTER
Time: 41342.651 ms (00:41.343)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2024-04-21 22:45:14 Re: CLUSTER vs. VACUUM FULL
Previous Message Tom Lane 2024-04-21 21:21:20 Re: error in trigger creation