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)
