| 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: | Whole Thread | Raw Message | 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)
| 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 |