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 1uBIpv-0096z8-90 for pgsql-performance@arkaria.postgresql.org; Sat, 03 May 2025 19:47:48 +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 1uBIou-00G0sC-Ix for pgsql-performance@arkaria.postgresql.org; Sat, 03 May 2025 19:46:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uBIot-00G0s4-V2 for pgsql-performance@lists.postgresql.org; Sat, 03 May 2025 19:46:45 +0000 Received: from mout.kundenserver.de ([212.227.17.10]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uBIos-000ttX-0H for pgsql-performance@lists.postgresql.org; Sat, 03 May 2025 19:46:44 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=msym.fr; s=s1-ionos; t=1746301600; x=1746906400; i=msalais@msym.fr; bh=HwuKVMEYsdJ7sH/Kxkb4Z3xhiQd6ODrJFnmY8B8D2hQ=; h=X-UI-Sender-Class:From:To:References:In-Reply-To:Subject:Date: Message-ID:MIME-Version:Content-Type:cc:content-transfer-encoding: content-type:date:from:message-id:mime-version:reply-to:subject: to; b=WktTy/St/h+9EzzfLOb3QLNajXJKtiACZMYFSdhSGj5+ruregFGmH+ex++YKTCwn LngENYhihbFmeAuMSSaBdvb2p+MYG2jAjXvvSMEnUkxavweaWxbtYWVfyMP0N1qSp NsEMCFjWUFbqG6nWODLma798Xe8ipezklnWIPDvir55JJhTyOk9ZeTWVmAP/Mo5Ul 652+CIsAJv5F/vVotQNZoxVexDcaw2pZr5VQHedd3PNaOWSlVdg6/w7FgMNMREdEI Qya/BV1fyFLRfqfzS9ml5VYlxJz3NYU2CVZblAX0fOkflvtt/bC0QXjru79XfSUGL cDlfV4qOefv1vUu1ew== X-UI-Sender-Class: 55c96926-9e95-11ee-ae09-1f7a4046a0f6 Received: from msim1 ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue109 [212.227.15.184]) with ESMTPSA (Nemesis) id 1MuluP-1v1tL23UtH-00vnVc; Sat, 03 May 2025 21:46:39 +0200 From: To: "'Leo'" , References: In-Reply-To: Subject: RE: Vacuum Questions Date: Sat, 3 May 2025 21:46:38 +0200 Message-ID: <000001dbbc64$163f99e0$42becda0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01DBBC74.D9C90620" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQKh/ZiMfV+OkgA92P515I3DSCUSiLI0zDgg Content-Language: fr X-Provags-ID: V03:K1:LdKMlQbLs3uvpCU69hbbguaj6clWZN9bKcxZ/Enm5lJ46cCbVHr 7GiRaSs6mw/iKZg99SUKGjIfpkpY9uIeI0xKFA7LXFwYdJa9slMjH0OIrNgQzYkvAYfD5Az KtkBlbAqdOejXMvYLjF6WkIE6SygXTkNMsUtPlg8xEyhoudFpsjF7kqckGAlbdFq2HU4ZqS YfptxNVrcoqdeG/FQrHFA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:L6HV6+wdZgw=;l+8Gg0WPwCiYa3mnQ5js5Ofy8tl nFUZuDT2F2OfP9a6jtPAWWlzodlRndg6sCV3/HZmTOs9GSY6777kwE0jkAGpw1F98H5ITLT6O ml8xLN/ebNBjZCI7ww2WxbIZpbn+hjYnpmYlHD5cFcHe3h+/gzgd6yLKocz4X014CUUOc1FvD nTuqMWweatjZWV6ljguL74VXfReAb9d2/qhlZNO7Mlq386E1Td9US3qdZdvjYrxrMAewLvE33 B9GnM+XzvrbDl/VZHM6Aen/QQZ4HnE3JCwivmGEvTtJww12SPqV+CueUYw4FtkOBP7aADhE9G Z8V7xVqv2sDvZA5NdkffWFJHWrPPOB/sY39x4nIybOqXRlrXxWvKZRnfFRDiEAIn9cDgStlty yJo2lbokftyBnNSV3gpQZ997T4C5PoY9qju6TjUjLjAgcc+krPEAqTnpI7a5JL5aKg36eC2mM 5SlvxxaJ1Qwdc5BXdfgoA5DHsXqIcyGYYP8nMQPuTVo+I7ahS2ZxT82uVfz1c4YEC7wQJU7wM z/2uuF/gT37UrfiY1SP2g5Tp7aYxFIJoRxp2LmNtiOHxq+7CVVPztOxOjMO+/axWZhdgcsCQZ timVqtdqFfHBliFY7R85+65Cu4PtitJgI/U+M4RN69D5v1hsEDxQc2oCBYffd1F15kib05Jp5 kYYs5HHpsdSLjVeJzepYztE5/k5Hvv4bIJoUE2e/qzhO7mNlOpMKjOBs9oBxhk0VRA/t97mQJ e01Hv/0UtpaCUoVmZ+jgiUXKFIPcx20hpXP60WdonEV9BPyWbLfW9i6N5I776YgnLQUQ4q+AF lyi21xRoBDPVrwOff+bPxg7HOGC/QU1M9D9DyWwO32lks1A37dF6/guBkvdXzvekYROXtKlKg Ma9wG9WZl6Bnphha7krgo24XQwvHgqvnU8ki2dzd6vY2PJE7SEMIcPPoT2aFyqvaECvCQeJAe JknqERQ1oSI9AB5giDC47K0WPrTMSxd8PWgsy+JFlABYq/vNsw+ddJ6hCnyaUAqSXlOlGPTdq vNk3qmsqRN8igNrnQKIDgu3ans+UY3s+acK/EoTr5CPFOHH9FrzSIvxvw0jeAROcGYAtkRs8r 4Y1BkKSryROIgSnURmGTgKmI0AA9GGQIb7Re8w/b14cldXpYdg3e4PyDuEWVHpuxtH7rHgR8D unf1/zkxMbKg8ff8fdCWYmDgUyAhROahUCGWn2fiR+u26IjyO3u09H2A0I0pVqyviWTHQw0ft 10eLSpupFFyBziu3zkeUUMY2jUFbTekX8vr2CGdjpDPxrHhjLBsdJB2UugfRUtBxA+nZSs8uw 2bfRSig8zwcftQT/QuufkwmcSRilZ4UQL3QzEWA7EuruZn56Pw4e1qnaJERYDoC9UHid4Mrh6 o0DKIIYCjCllKqlcOAtX8QuGRBlD+KOt3YepE= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0001_01DBBC74.D9C90620 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi =20 It is not your question but for such situations, you should consider = using partitioning. And more closely to your question: I would not disable autovacuum but it = must not work with default values. =20 Best regards =20 Michel SALAIS De : Leo =20 Envoy=C3=A9 : vendredi 2 mai 2025 16:23 =C3=80 : pgsql-performance@lists.postgresql.org Objet : Vacuum Questions =20 I have been working on AWS PostgreSQL RDS for a few years, but still not = very experienced when it comes to performance issues. Plus RDS is = slightly different from the pure PostgreSQL. =20 I am trying to comprehend exactly how vacuum works. =20 Here is what I am trying to do. =20 I am purging old records from a table (500 million rows, but I am doing = it in sets of 50,000,000 with a smaller loop of 100,000). That works = just fine. =20 Because of the amount of data/rows deleted, I disabled the autovacuum = for this table (I want to have control over vacuum, autovacuum does not = complete anyway due to the timeout, sizing, etc settings that I do not = want to change system wide). I will put the autovacuum back once I am = done of course. =20 The issue is when I start vacuuming. This table has 4 indexes and a PK = that I worry about. The PK takes about 30 minutes to vacuum and two of = the indexes take about an hour each. The problem comes in for the other = 2 indexes - they take 12+ hours each to vacuum: =20 psql:/home/backup/leo/fws_vacuum.sql:6: INFO: vacuuming = "public.pc_workflowlog" psql:/home/backup/leo/fws_vacuum.sql:6: INFO: launched 4 parallel = vacuum workers for index vacuuming (planned: 4) psql:/home/backup/leo/fws_vacuum.sql:6: INFO: scanned index = "pc_workflowlog_pk" to remove 50000000 row versions DETAIL: CPU: user: 191.03 s, system: 12.43 s, elapsed: 1711.22 s psql:/home/backup/leo/fws_vacuum.sql:6: INFO: scanned index = "workflowlo_u_publicid_g6uqp9lkn6e8" to remove 50000000 row versions DETAIL: CPU: user: 325.75 s, system: 19.75 s, elapsed: 2674.24 s psql:/home/backup/leo/fws_vacuum.sql:6: INFO: scanned index = "workflowlo_n_workflow_2tc9k2hdtry9v" to remove 50000000 row versions DETAIL: CPU: user: 312.17 s, system: 16.94 s, elapsed: 3097.88 s psql:/home/backup/leo/fws_vacuum.sql:6: INFO: scanned index = "workflowlo_n_frozenseti_2kjkbjgf3c6ro" to remove 50000000 row versions DETAIL: CPU: user: 41187.70 s, system: 216.14 s, elapsed: 42749.36 s psql:/home/backup/leo/fws_vacuum.sql:6: INFO: scanned index = "workflowlo_n_userid_14kqw6qdsnndw" to remove 50000000 row versions=20 DETAIL: CPU: user: 41280.66 s, system: 216.67 s, elapsed: 42832.16 s psql:/home/backup/leo/fws_vacuum.sql:6: INFO: "pc_workflowlog": removed = 50000000 row versions in 1129870 pages =20 I've increased max_parallel_maintenance_workers to 8 for the session and = it used parallel 4 (one for each index I assume) to handle it and the = two indexes were done in ~ an hour. What I am trying to figure out is = how to force the other two large indexes to be vacuumed in parallel - a = few workers going against an index. It seems it is possible to do, the = index size is large enough to kick in, but I have not been able to = figure it out yet. Most of the parameters are at default values. =20 What am I missing? =20 I have a few other questions. Does vacuum time depend on the number of = dead rows only and the size of the table, or does the entire storage = allocation (including dead tuples) also affect it? =20 Would it be more beneficial to drop the two large indexes, purge, = vacuum, and recreate the indexes after make more sense (I know it needs = to be tested)? The reason I am doing it in stages is to make sure I = have enough time to vacuum, but maybe it would not take much longer to = vacuum after the complete purge? =20 Lastly, is it better to delete all the rows (500 mil) instead of doing = it in smaller batches, and vacuum only once? =20 The current size of the table is about 1T and the indexes add another = 1.5T to it. =20 Truncate is not an option as I am only deleting rows older than 6 = months. Client was not doing purging for years, but will do it after = the clean up. =20 P.S. This is my very first post here, please advise if it is the wrong = channel. Thank you in advance. ------=_NextPart_000_0001_01DBBC74.D9C90620 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi

 

It is not your question but for such situations, you = should consider using partitioning.

And more closely to your question: I would not disable = autovacuum but it must not work with default = values.

 

Best regards

 

= Michel SALAIS

De : Leo = <leo1969@gmail.com>
Envoy=C3=A9 : vendredi 2 mai = 2025 16:23
=C3=80 : = pgsql-performance@lists.postgresql.org
Objet : Vacuum = Questions

 

I have = been working on AWS PostgreSQL RDS for a few years, but still not very = experienced when it comes to performance issues.  Plus RDS is = slightly different from the pure PostgreSQL.

 

I = am trying to comprehend exactly how vacuum = works.

 

Here is what I am trying to = do.

 

I = am purging old records from a table (500 million rows, but I am doing it = in sets of  50,000,000 with a smaller loop of 100,000).  That = works just fine.

 

Because of the amount of data/rows deleted, I = disabled the autovacuum for this table (I want to have control over = vacuum, autovacuum does not complete anyway due to the timeout, sizing, = etc settings that I do not want to change system wide).  I will put = the autovacuum back once I am done of = course.

 

The issue is when I start vacuuming.  This table = has 4 indexes and a PK that I worry about.  The PK takes about 30 = minutes to vacuum and two of the indexes take about an hour each.  = The problem comes in for the other 2 indexes - they take 12+ hours each = to vacuum:

 

psql:/hom= e/backup/leo/fws_vacuum.sql:6: INFO:  vacuuming = "public.pc_workflowlog"

psql:/hom= e/backup/leo/fws_vacuum.sql:6: INFO:  launched 4 parallel = vacuum workers for index vacuuming (planned: 4)

psql:/hom= e/backup/leo/fws_vacuum.sql:6: INFO:  scanned index = "pc_workflowlog_pk" to remove 50000000 row = versions

DETAIL:&n= bsp; CPU: user: 191.03 s, system: 12.43 s, elapsed: 1711.22 = s

psql:/hom= e/backup/leo/fws_vacuum.sql:6: INFO:  scanned index = "workflowlo_u_publicid_g6uqp9lkn6e8" to remove 50000000 row = versions

DETAIL:&n= bsp; CPU: user: 325.75 s, system: 19.75 s, elapsed: 2674.24 = s

psql:/hom= e/backup/leo/fws_vacuum.sql:6: INFO:  scanned index = "workflowlo_n_workflow_2tc9k2hdtry9v" to remove 50000000 row = versions

DETAIL:&n= bsp; CPU: user: 312.17 s, system: 16.94 s, elapsed: 3097.88 = s

psql:/hom= e/backup/leo/fws_vacuum.sql:6: INFO:  scanned index = "workflowlo_n_frozenseti_2kjkbjgf3c6ro" to remove 50000000 row = versions

DETAIL:&n= bsp; CPU: user: 41187.70 s, system: 216.14 s, elapsed: 42749.36 = s

psql:/hom= e/backup/leo/fws_vacuum.sql:6: INFO:  scanned index = "workflowlo_n_userid_14kqw6qdsnndw" to remove 50000000 row = versions 

DETAIL:&n= bsp; CPU: user: 41280.66 s, system: 216.67 s, elapsed: 42832.16 = s

psql:/hom= e/backup/leo/fws_vacuum.sql:6: = INFO:  "pc_workflowlog": removed 50000000 row = versions in 1129870 pages

 

I've increased max_parallel_maintenance_workers = to 8 for the session and it used parallel 4 (one for each index I = assume) to handle it and the two indexes were done in ~ an hour.  = What I am trying to figure out is how to force the other two large = indexes to be vacuumed in parallel - a few workers going against an = index.  It seems it is possible to do, the index size is large = enough to kick in, but I have not been able to figure it out yet.  = Most of the parameters are at default = values.

 

What am I missing?

 

I = have a few other questions.  Does vacuum time depend on the = number of dead rows only and the size of the table, or does the entire = storage allocation (including dead tuples) also affect = it?

 

Would it be more beneficial to drop the two large = indexes, purge, vacuum, and recreate the indexes after make more sense = (I know it needs to be tested)?  The reason I am doing it in stages = is to make sure I have enough time to vacuum, but maybe it would not = take much longer to vacuum after the complete = purge?

 

Lastly, is it better to delete all the rows (500 mil) = instead of doing it in smaller batches, and vacuum only = once?

 

The current size of the table is about 1T and the = indexes add another 1.5T to it.

 

Truncate is not an option as I am only deleting rows = older than 6 months.  Client was not doing purging for years, but = will do it after the clean up.

 

P.S. This is my very first post here, please advise if = it is the wrong channel.  Thank you in = advance.

------=_NextPart_000_0001_01DBBC74.D9C90620--