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 1uAssX-003OjI-D8 for pgsql-performance@arkaria.postgresql.org; Fri, 02 May 2025 16:04:46 +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 1uAssU-009r0x-V5 for pgsql-performance@arkaria.postgresql.org; Fri, 02 May 2025 16:04:44 +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 1uAssU-009r0p-Fg for pgsql-performance@lists.postgresql.org; Fri, 02 May 2025 16:04:43 +0000 Received: from mail-pg1-x534.google.com ([2607:f8b0:4864:20::534]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uAssT-000j83-1Y for pgsql-performance@lists.postgresql.org; Fri, 02 May 2025 16:04:43 +0000 Received: by mail-pg1-x534.google.com with SMTP id 41be03b00d2f7-b12b984e791so2026885a12.2 for ; Fri, 02 May 2025 09:04:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1746201878; x=1746806678; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=rTvQYtOOzReZxGcvQTyikV6zO1cXz4IVXAMScSnrU70=; b=SYLy3RXxoC0dnVqJba99gZOllbbuYaTiEzvM3vT2aDaI/rCj/6fZREE6JIpvcO0L3j tXPr74XCGP8D3mMv20vYVku2G52o574/YlNFGPfSTIeksMnbPQ33qyv7oWB42YCPl/KT Ftt7fVtmIUxoaslMgbDQivtt8UuVNJrs6CAxbBhWQX7KipwV8yg0NvdaXdFrp6AyGnO0 M1p7zcleOBhhDg7L7/S47vA8cpfEs3KwEaiN3uDsZ8gWd2n565b1dwIPNyzZEgtkkU5Q umFWsmcLldNfDqvC8zDoy9j343+DKFk4caE0CSPQNZRQyV34q5YhO7eOc4PdrTpriv5h IePQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1746201878; x=1746806678; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=rTvQYtOOzReZxGcvQTyikV6zO1cXz4IVXAMScSnrU70=; b=ll0BIx0v0sTKHa6GUMGP1q32qurcXMV8VQ3ztPWKXU/KYAIxqS6iJ66QW+rcQrjR6G kaCcyngUA8XXpNXGulMOIey+UL2dbsuQhYXMdR4VjoHka4laGtoFV7+nHp5O7/3dWXGA XQCeiJd/rUhGagYEg8wa/hBNWOQ94uNg5wrJ2JIXrPZWEZNqSTHx1UjjMiOcYTDNK2w1 wJZiJhLJTCL/+GOxiBRkG8dXQaXgn9kPfFzW+YLxIqFpQoHdDrzWTZ2Vms4GJc8hCmIF tTD1JDH0tfFUT5usyUvc/gGGnA+VZvAibkBCizIIHr5oDdcVDmu1srgznYa+s2MRwQHT 4Q5w== X-Gm-Message-State: AOJu0YwS7sqPoKKa/wuypFn8Zrw2H0mP8bdd8XZmktINxZfJHFOKoe5R FNMYdj5Y2ZDPY/pkuh7kP681qAMdjT7NDnGERuHadbkpT86yMvZi2z/yOf7YU0svW7Sgcg1qI4j 8WYEwNS5QagubnaTQ+M3SLiQjuPbNMw== X-Gm-Gg: ASbGnctQ80dsqmtbZTsl/zMfo1W8wTiIXHQIsaB08mafrv79woMW1bnU/mnZi3E6PcS dWNw23hf8YhYB1fasKKUwvP4woTTSiELYcYNcR7YbSpZLLHOz+vVhOWpld9YS9SabgQUt8/N/sY VNp2v856J+L+1iRsPmT/S3pg== X-Google-Smtp-Source: AGHT+IG/QFAw+h5NlLmXUXcMGfiewkNwaJgtdXAr3jL3iAqB4Sze0lQMxvUb9MnIoeMLjELvjIIiutgq44CLju7NS3U= X-Received: by 2002:a17:90b:514b:b0:301:1bce:c258 with SMTP id 98e67ed59e1d1-30a4e69f29cmr4286356a91.22.1746201878126; Fri, 02 May 2025 09:04:38 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Leo Date: Fri, 2 May 2025 12:04:01 -0400 X-Gm-Features: ATxdqUGBEM42UzRk727AtaegMXR30LN413Y4e3p_WtvJm5o4IjSsdlGuBGqxRhU Message-ID: Subject: Re: Vacuum Questions To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a6e6590634294ef1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a6e6590634294ef1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Also, is there a way to estimate the vacuum execution? Something like explain plan - without actually vacuuming, just to see how it will perform it - like a degree of parallelism? On Fri, May 2, 2025 at 10:23=E2=80=AFAM Leo wrote: > 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 slight= ly > 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 i= t > 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 wa= nt > 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 t= he > indexes take about an hour each. The problem comes in for the other 2 > indexes - they take 12+ hours each to vacuum: > > 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 * > > *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 > > 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 ou= t > 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 enoug= h > time to vacuum, but maybe it would not take much longer to vacuum after t= he > 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.5= T > 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. > --000000000000a6e6590634294ef1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Also, is there a way to estimate the vacuum execution?=C2= =A0 Something like explain plan - without=C2=A0actually vacuuming, just to = see how it will perform it - like a degree of parallelism?

On Fri, May 2, 2025 at 10:23=E2=80=AFAM Leo <leo1969@gmail.com> wrote:
I have been working on AW= S PostgreSQL RDS for a few years, but still not very experienced=C2=A0when = it comes to performance issues.=C2=A0 Plus RDS is slightly different from t= he pure=C2=A0PostgreSQL.

I am trying=C2=A0to comprehend= =C2=A0exactly how vacuum works.

Here is what I am = trying to do.

I am purging old records from a tabl= e (500 million rows, but I am doing it in sets of=C2=A0 50,000,000 with a s= maller loop of 100,000).=C2=A0 That works just fine.

Because of the amount of data/rows deleted, I disabled=C2=A0the autovacu= um 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).=C2=A0 I will put the autovacuum back once I am don= e of course.

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

= psql:/home/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0vacuuming "pu= blic.pc_workflowlog"

psql:/ho= me/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0launched 4 parallel vacuum= workers for index vacuuming (planned: 4)

psql:/home/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0scanned in= dex "pc_workflowlog_pk" to remove 50000000 row versions

DETAIL:=C2=A0=C2=A0CPU: user: 191.03 s, sys= tem: 12.43 s, elapsed: 1711.22 s

p= sql:/home/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0scanned index "= ;workflowlo_u_publicid_g6uqp9lkn6e8" to remove 50000000 row versions

DETAIL:=C2=A0=C2=A0CPU: user: 325.7= 5 s, system: 19.75 s, elapsed: 2674.24 s

psql:/home/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0scanned ind= ex "workflowlo_n_workflow_2tc9k2hdtry9v" to remove 50000000 row v= ersions

DETAIL:=C2=A0=C2=A0CPU: us= er: 312.17 s, system: 16.94 s, elapsed: 3097.88 s

psql:/home/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2= =A0scanned index "workflowlo_n_frozenseti_2kjkbjgf3c6ro" to remov= e 50000000 row versions

DET= AIL:=C2=A0=C2=A0CPU: user: 41187.70 s, system: 216.14 s, elapsed: 42749.36 = s

psql:/home/backup/leo/fw= s_vacuum.sql:6: INFO:=C2=A0=C2=A0scanned index "workflowlo_n_userid_14= kqw6qdsnndw" to remove 50000000 row versions=C2=A0

DETAIL:=C2=A0=C2=A0CPU: user: 41280.66 s, syst= em: 216.67 s, elapsed: 42832.16 s

psql:/home/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0"pc_workf= lowlog": removed 50000000 row versions in 1129870 pages


I've increased=C2=A0max_parallel_maintenance_work= ers to 8 for the session and it used parallel 4 (one for each index I assum= e) to handle it and the two indexes were done in ~ an hour.=C2=A0 What I am= trying to figure out is how to force the other two large indexes to be vac= uumed in parallel - a few workers going against an index.=C2=A0 It seems it= is possible to do, the index size is large enough to kick in, but I have n= ot been able to figure it out yet.=C2=A0 Most of the parameters are at defa= ult values.

What am I missing?

I have a few other questions.=C2=A0 Does vacuum=C2=A0time depend on t= he number of dead rows only and the size of the table, or does the entire s= torage allocation (including dead tuples) also affect it?

Would it be more beneficial to drop the two large indexes, purge, v= acuum, and recreate the indexes after make more sense (I know it needs to b= e tested)?=C2=A0 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 af= ter the complete purge?

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

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

Tru= ncate is not an option as I am only deleting rows older than 6 months.=C2= =A0 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 a= dvise if it is the wrong channel.=C2=A0 Thank you in advance.
--000000000000a6e6590634294ef1--