Search Postgresql Archives

CLUSTER vs. VACUUM FULL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux