Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ryfM4-00HQAW-JR for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 22:08:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ryfM2-00GMUX-FM for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 22:08:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ryfM2-00GMUN-2v for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 22:08:10 +0000 Received: from mail-oa1-x34.google.com ([2001:4860:4864:20::34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ryfLv-002EVq-Fm for pgsql-general@postgresql.org; Sun, 21 Apr 2024 22:08:09 +0000 Received: by mail-oa1-x34.google.com with SMTP id 586e51a60fabf-233e41de0caso1979280fac.0 for ; Sun, 21 Apr 2024 15:08:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713737281; x=1714342081; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=9xBnATMDXMbcHmIlZ4/ss7C/5U0KRvAw6kyhbMAzv6I=; b=TOA2DSI4htTTlzwkLt0FtLN4B4gVkM06Bk8MJuZRnnTH7aWoQJ1qPTuzqMzyO/sAnB l94ggHCkdmUXMjVkUaAgCJqvBFbOOk4cNG4V2KMg7iRBR4Qf/sti9bsiWBOJ+2sEX6sp 91tYFTljcc+qdAWvQ0VIPEhX5b/6WG32jMP9zNAIzS9mP694Uy9W94fjoPU/gcCBwo5b Tm2RvX/8FPR55tsi71fakmyuylDHfjNkSRABH0qC55IVLvd9z23w+sWaaXvlFKZyzx49 16Y65Cyl3OlqesWp84nXH/y/aNTzODXpV+Rx17Trii4Ve0DCxtD0Nzj5GrvV03HXDGUB 9WIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713737281; x=1714342081; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=9xBnATMDXMbcHmIlZ4/ss7C/5U0KRvAw6kyhbMAzv6I=; b=pm8wBejg1kn4qjgpBAKtg0rRdQvjZmYDdd9AncW9UuAlN+4L9NLS08PLOUP2Nr7CwI 2kESPU9aFKY2xXnNJApQPd1a+wFrTG4N4fBuADeyN0x1lhWwmKrAw0SkUHT12gkMYtow eZoMbfcrZighTIPQzy+qrHFIBxVPRL95rBl/bzJC/uwXBshAxjLSh+YEba7UUnDF9y/L DNDbrazN8UzQAs3Rjn3LRA1szc15ZYS78QW4Qp0zC33GOCYkAmo8RcSAWsPyHNQrzQDs +K8HGUjn3A1yp36ajdP0iNadHwGhodgnDUyKO0Y8bpptbMWj1SyhbXmgwJHUfPZjuU66 fWeg== X-Gm-Message-State: AOJu0Yy3hwSqm+/4brilyTmHkOJDEwREvt5JNJpuRy9c4P2eA5/Qc1Lj idaTTG9G4koImi+BOQn4rdr3KmH7hv5kzGohnQqAdLtvrowwt11bG8W03mrwCQr1wctKfyMxGpi V5FExKe8PKEsrfrcCkjwFcR9m2Gi84+mp X-Google-Smtp-Source: AGHT+IFFdBJvq+w5VWuP0SV7igx3t4v2WlGayzaMAyfN2tlxDWMWwfDbU9kDmsTmSND14b2vAA6hF2Zz5mUc6zFp90c= X-Received: by 2002:a05:6870:cb8e:b0:21e:a957:efef with SMTP id ov14-20020a056870cb8e00b0021ea957efefmr11247461oab.19.1713737281248; Sun, 21 Apr 2024 15:08:01 -0700 (PDT) MIME-Version: 1.0 From: Ron Johnson Date: Sun, 21 Apr 2024 18:07:50 -0400 Message-ID: Subject: CLUSTER vs. VACUUM FULL To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000e325880616a28de9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e325880616a28de9 Content-Type: text/plain; charset="UTF-8" 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) --000000000000e325880616a28de9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: base64 PGRpdiBkaXI9Imx0ciI+UEcgMTQuMTEgb24gUkhFTDg8ZGl2Pjxicj48L2Rpdj48ZGl2PldoeSBp cyBWQUNVVU0gRlVMTCByZWNvbW1lbmRlZCBmb3IgY29tcHJlc3NpbmcgYSB0YWJsZSwgd2hlbiBD TFVTVEVSIGRvZXMgdGhlIHNhbWUgdGhpbmcgKHNpbWlsYXJseSBkb3VibGluZyBkaXNrIHNwYWNl KSwgYW5kIGFwcGFyZW50bHkgcnVucyBqdXN0IGFzIGZhc3Q/wqDCoDwvZGl2PjxkaXY+PGJyPjwv ZGl2PjxkaXY+TXkgdGVzdHM6PC9kaXY+PGRpdj48Zm9udCBmYWNlPSJtb25vc3BhY2UiPlRhYmxl OiBDRFNMQlhXLnB1YmxpYy5sb2c8YnI+PGJyPsKgIMKgIMKgIMKgIMKgIMKgIFRpbWUgMSDCoFRp bWUgMiDCoFRpbWUgMzxicj7CoCDCoCDCoCDCoCDCoCDCoCBzZWNzIMKgIMKgc2VjcyDCoCDCoHNl Y3M8YnI+VkFDVVVNIEZVTEwgNDQuMiDCoCDCoDM5LjMgwqAgwqA0Mi4zPGJyPkNMVVNURVIgwqAg wqAgNDEuNyDCoCDCoDM4LjkgwqAgwqA0MS4zPGJyPjxicj5DRFNMQlhXPSMgXGQgcHVibGljLmxv Zzxicj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC oCDCoCDCoCDCoCDCoCDCoFRhYmxlICZxdW90O3B1YmxpYy5sb2cmcXVvdDs8YnI+wqAgwqAgwqBD b2x1bW4gwqAgwqAgwqB8IMKgIMKgIMKgIMKgIMKgIMKgVHlwZSDCoCDCoCDCoCDCoCDCoCDCoCB8 IENvbGxhdGlvbiB8IE51bGxhYmxlIHwgwqAgwqAgwqAgwqAgwqAgwqAgwqAgRGVmYXVsdCDCoCDC oCDCoCDCoCDCoCDCoCDCoCA8YnI+LS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tPGJyPsKgbG9nX2lkIMKgIMKgIMKgIMKgIMKgfCBiaWdpbnQgwqAgwqAg wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCBu ZXh0dmFsKCYjMzk7bG9nX2xvZ19pZF9zZXEmIzM5Ozo6cmVnY2xhc3MpPGJyPsKgbGV2ZWwgwqAg wqAgwqAgwqAgwqAgfCBudW1lcmljKDEwLDApIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAg wqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoHwgPGJyPsKgc291cmNlIMKgIMKgIMKgIMKgIMKgfCBj aGFyYWN0ZXIgdmFyeWluZygyNTUpIMKgIMKgIMKgfCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKg IMKgIMKgfCA8YnI+wqB1c2VybmFtZSDCoCDCoCDCoCDCoHwgY2hhcmFjdGVyIHZhcnlpbmcoMjU1 KSDCoCDCoCDCoHwgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoHwgPGJyPsKgdXNlcl9s b2dpbl9pZCDCoCB8IGNoYXJhY3RlciB2YXJ5aW5nKDI1NSkgwqAgwqAgwqB8IMKgIMKgIMKgIMKg IMKgIHwgwqAgwqAgwqAgwqAgwqB8IDxicj7CoHVzZXJfaXBfYWRkcmVzcyB8IGNoYXJhY3RlciB2 YXJ5aW5nKDI1NSkgwqAgwqAgwqB8IMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqB8IDxi cj7CoGNvbXB1dGVyIMKgIMKgIMKgIMKgfCBjaGFyYWN0ZXIgdmFyeWluZygyNTUpIMKgIMKgIMKg fCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgfCA8YnI+wqBzZWFyY2hfdGFnIMKgIMKg IMKgfCBjaGFyYWN0ZXIgdmFyeWluZyg0MDAwKSDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgwqAg wqAgwqAgwqAgwqB8IDxicj7CoGxvZ19ncm91cF9pZCDCoCDCoHwgaW50ZWdlciDCoCDCoCDCoCDC oCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqB8IDxi cj7CoGNyZWF0ZWRfb24gwqAgwqAgwqB8IHRpbWVzdGFtcCB3aXRob3V0IHRpbWUgem9uZSB8IMKg IMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCA8YnI+wqBjcmVhdGVkX2J5IMKgIMKgIMKgfCBpbnRl Z2VyIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAgfCDCoCDC oCDCoCDCoCDCoHwgPGJyPsKgeG1sX2RldGFpbCDCoCDCoCDCoHwgYnl0ZWEgwqAgwqAgwqAgwqAg wqAgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgfCA8 YnI+SW5kZXhlczo8YnI+wqAgwqAgJnF1b3Q7cGtfbG9nJnF1b3Q7IFBSSU1BUlkgS0VZLCBidHJl ZSAobG9nX2lkKTxicj7CoCDCoCAmcXVvdDtpZHhfbG9nX2F0dHJfc291cmNlJnF1b3Q7IGJ0cmVl IChzb3VyY2UpPGJyPsKgIMKgICZxdW90O2lkeF9sb2dfbGV2ZWwmcXVvdDsgYnRyZWUgKGxldmVs KTxicj7CoCDCoCAmcXVvdDtpZHhfbG9nX3NlYXJjaF90YWcmcXVvdDsgYnRyZWUgKHNlYXJjaF90 YWcpPGJyPjxicj5DRFNMQlhXPSMgU0VMRUNUIENPVU5UKCopIEZST00gcHVibGljLmxvZzs8YnI+ wqAgY291bnQgwqA8YnI+LS0tLS0tLS0tPGJyPsKgNjc3NDY2NDxicj4oMSByb3cpPGJyPjxicj48 L2ZvbnQ+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5Om1vbm9zcGFjZSI+Q0RTTEJYVz0jIFx0aW1p bmc8L3NwYW4+PGZvbnQgZmFjZT0ibW9ub3NwYWNlIj48YnI+PC9mb250PjwvZGl2PjxkaXY+PGZv bnQgZmFjZT0ibW9ub3NwYWNlIj5DRFNMQlhXPSMgdmFjdXVtIChmdWxsKSBwdWJsaWMubG9nOzxi cj5WQUNVVU08YnI+VGltZTogNDQxOTAuNzk5IG1zICgwMDo0NC4xOTEpPGJyPkNEU0xCWFc9IyA8 YnI+Q0RTTEJYVz0jIHZhY3V1bSAoZnVsbCkgcHVibGljLmxvZzs8YnI+VkFDVVVNPGJyPlRpbWU6 IDM5MzE2LjgyNCBtcyAoMDA6MzkuMzE3KTxicj5DRFNMQlhXPSMgdmFjdXVtIChmdWxsKSBwdWJs aWMubG9nOzxicj5WQUNVVU08YnI+VGltZTogNDIzMzYuMjY4IG1zICgwMDo0Mi4zMzYpPGJyPjxi cj5DRFNMQlhXPSMgQ0xVU1RFUiBwdWJsaWMubG9nIFVTSU5HIHBrX2xvZzs8YnI+Q0xVU1RFUjxi cj5UaW1lOiA0MTcyMi4zMzUgbXMgKDAwOjQxLjcyMik8YnI+Q0RTTEJYVz0jIDxicj5DRFNMQlhX PSMgQ0xVU1RFUiBwdWJsaWMubG9nIFVTSU5HIHBrX2xvZzs8YnI+Q0xVU1RFUjxicj5UaW1lOiAz ODkxNS4xMjggbXMgKDAwOjM4LjkxNSk8YnI+Q0RTTEJYVz0jIDxicj5DRFNMQlhXPSMgQ0xVU1RF UiBwdWJsaWMubG9nIFVTSU5HIHBrX2xvZzs8YnI+Q0xVU1RFUjxicj5UaW1lOiA0MTM0Mi42NTEg bXMgKDAwOjQxLjM0Myk8L2ZvbnQ+PGJyPjwvZGl2PjxkaXY+PGZvbnQgZmFjZT0ibW9ub3NwYWNl Ij48YnI+PC9mb250PjwvZGl2PjwvZGl2Pg0K --000000000000e325880616a28de9--