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 1uArIu-0033lS-4q for pgsql-performance@arkaria.postgresql.org; Fri, 02 May 2025 14:23:52 +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 1uArIq-009IiP-7i for pgsql-performance@arkaria.postgresql.org; Fri, 02 May 2025 14:23:49 +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 1uArIp-009IiD-Py for pgsql-performance@lists.postgresql.org; Fri, 02 May 2025 14:23:48 +0000 Received: from mail-pl1-x62c.google.com ([2607:f8b0:4864:20::62c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uArIo-000iMA-1s for pgsql-performance@lists.postgresql.org; Fri, 02 May 2025 14:23:48 +0000 Received: by mail-pl1-x62c.google.com with SMTP id d9443c01a7336-22c33ac23edso24411465ad.0 for ; Fri, 02 May 2025 07:23:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1746195824; x=1746800624; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=QA7iJ9vj/5xHvvZ57TN9ihYNORWfFuH8I4/dAsaYDUU=; b=fuJm32mt6PFtDx/rtdtwC4MyHcASQVPyKCUBGXSDXxMiZ9PvxF1cmlEcW4/HMC4XqZ FKs9PU2b6391aQVGiVaZa5CFRey1FNtIgfzSq4Tdb76VrMhyCKg4Zkfl3qCR/0BNTU17 kn62d/nGfr/ecfxhbT7aUtxI4qwzirSiO+XcwDLsmTI2FjnK8jKhY8GcDHnBlu0JHV52 7YYrIWxW1QHob75lb2Bysn/BN3PPZWSUjVvSDkhLfmINgAd0/FQuKCvGl6ZHBYfuQS0M iqU7hO0DtEsRGdcHdhQOVTJ8tb0OoOzMxHawWjW5fUSWElis9DB2IodYx6dR9wEyyYu9 AU6g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1746195824; x=1746800624; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=QA7iJ9vj/5xHvvZ57TN9ihYNORWfFuH8I4/dAsaYDUU=; b=ID59MiZ37SUjPZQgyo+tkubRLxbBZzgGAW8/Xe/gekSVbuuAc0vMaIV9KW7jfku/1J o3mmTG/bmt0VlJeaZP3a2ra24EA1Rc8AGkVJnajuF5+1kXhLK3lStVtMyvPSaUfmalA7 /keoJHZecbu+6aM6Wep5EJGjS7BWnYCdCUy9QhZcBxdsG7genWpXv8ehUVXSafxGPOTJ FINf05KRplFZjhE2yCIw1ZXmDA3YifAtgGeQRHwTlw7msMJB9uUYWNuKf2PIzP1VCuOV Ow/2EJAdmPh0YyzHXoHfHif9rLV+a7WSnmCJNgV7dPiqXs8I+Y/y/95fdWbR2NY6eb65 02cw== X-Gm-Message-State: AOJu0YwCFhJNrFUA/Qi+AGdtw4J6lFsVLx8Kb3ABMczpKNxcL1vZ4VaX UtJLpUjRQ3X7qjZu+fwT+v+jYXw1YxEiTCGbKsTxGIovrQECUBEVYcu4CKFNNGMk2n4lPtU6hbs vyPMYe5SDzyH0pU4Cwstu7ykO4JyzkNdd X-Gm-Gg: ASbGncv3kIGGpeKl1GmaNnS0gs7My5EDjX7iid8NgTITnobgL0Dr1TyZIQj+EV3Z634 Jv9EwoAjrb41NlnSsIslab8ih6InKZoAXIY9r8Lt+2Itmwu3ZLpt5GhheUiDeD8dp3s8yMeCR8M J5/m1LjW3TvIU4izQtB5KZjW4= X-Google-Smtp-Source: AGHT+IHgka1XP9oqLgPQG35yHRn6tR4QMC4YLHaYGCLYaA5jJbznj40xC1viso8uR8XZSgZEPpTPK27zzEjenfw9oek= X-Received: by 2002:a17:903:2352:b0:220:fb23:48df with SMTP id d9443c01a7336-22e103899a9mr46853025ad.36.1746195823424; Fri, 02 May 2025 07:23:43 -0700 (PDT) MIME-Version: 1.0 From: Leo Date: Fri, 2 May 2025 10:23:07 -0400 X-Gm-Features: ATxdqUG1yReSpP2TewOCXkGJChH-72SehqN9-NzuwSmZNw7zSsOWJKMbdVCTCKU Message-ID: Subject: Vacuum Questions To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c37e2b063427e59e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c37e2b063427e59e Content-Type: text/plain; charset="UTF-8" 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:/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 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. --000000000000c37e2b063427e59e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I have been working on AWS 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 the pure=C2=A0PostgreSQL.
I am trying=C2=A0to comprehend=C2=A0exactly how vacuum works.<= /div>

Here is what I am trying to do.

I am purging old records from a table (500 million rows, but I am do= ing it in sets of=C2=A0 50,000,000 with a smaller loop of 100,000).=C2=A0 T= hat works just fine.

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

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

=

psql:/home/backup/leo/fws_vacuum.= sql:6: INFO:=C2=A0=C2=A0vacuuming "public.pc_workflowlog"<= /p>

psql:/home/backup/leo/fws_vacuum.sql:6: I= NFO:=C2=A0=C2=A0launched 4 parallel vacuum workers for index vacuuming (pla= nned: 4)

psql:/home/backup/leo/fws= _vacuum.sql:6: INFO:=C2=A0=C2=A0scanned index "pc_workflowlog_pk"= to remove 50000000 row versions

D= ETAIL:=C2=A0=C2=A0CPU: user: 191.03 s, system: 12.43 s, elapsed: 1711.22 s<= /span>

psql:/home/backup/leo/fws_vacuum.s= ql:6: INFO:=C2=A0=C2=A0scanned index "workflowlo_u_publicid_g6uqp9lkn6= e8" to remove 50000000 row versions

DETAIL:=C2=A0=C2=A0CPU: user: 325.75 s, system: 19.75 s, elapsed: 26= 74.24 s

psql:/home/backup/leo/fws_= vacuum.sql:6: INFO:=C2=A0=C2=A0scanned index "workflowlo_n_workflow_2t= c9k2hdtry9v" to remove 50000000 row versions

DETAIL:=C2=A0=C2=A0CPU: user: 312.17 s, system: 16.94 s, el= apsed: 3097.88 s

<= span style=3D"font-variant-ligatures:no-common-ligatures">psql:/home/bac= kup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0scanned index "workflowlo_n= _frozenseti_2kjkbjgf3c6ro" to remove 50000000 row versions<= /p>

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

psql:/home/backup/leo/fws_vacuum.sql:6: INFO:=C2=A0=C2=A0sca= nned index "workflowlo_n_userid_14kqw6qdsnndw" to remove 50000000= row versions=C2=A0

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

psql:/home/backup/leo/fws_vacuum= .sql:6: INFO:=C2=A0=C2=A0"pc_workflowlog": removed 50000000 row v= ersions in 1129870 pages


I've incre= ased=C2=A0max_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.=C2=A0 What I am trying to figure out is how to forc= e the other two large indexes to be vacuumed in parallel - a few workers go= ing against an index.=C2=A0 It seems it is possible to do, the index size i= s large enough to kick in, but I have not been able to figure it out yet.= =C2=A0 Most of the parameters are at default values.

What am I missing?

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

Would it be more beneficia= l to drop the two large indexes, purge, vacuum, and recreate the indexes af= ter make more sense (I know it needs to be tested)?=C2=A0 The reason I am d= oing it in stages is to make sure I have enough time to vacuum, but maybe i= t would not take much longer to vacuum after the complete purge?
=
Lastly, is it better to delete all the rows (500 mil) instea= d 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 onl= y 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. Th= is is my very first post here, please advise if it is the wrong channel.=C2= =A0 Thank you in advance.
--000000000000c37e2b063427e59e--