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 1tx02y-001kh0-48 for pgsql-general@arkaria.postgresql.org; Tue, 25 Mar 2025 08:54:08 +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 1tx02w-00104I-E9 for pgsql-general@arkaria.postgresql.org; Tue, 25 Mar 2025 08:54:06 +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 1tx02v-00104A-Rk for pgsql-general@lists.postgresql.org; Tue, 25 Mar 2025 08:54:06 +0000 Received: from mail-ot1-x333.google.com ([2607:f8b0:4864:20::333]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tx02u-0010pf-0o for pgsql-general@postgresql.org; Tue, 25 Mar 2025 08:54:04 +0000 Received: by mail-ot1-x333.google.com with SMTP id 46e09a7af769-72bb97260ceso1191930a34.1 for ; Tue, 25 Mar 2025 01:54:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742892843; x=1743497643; darn=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=TQiPUAeAHXL1+vEdhjTdr6mpqGM2hepfNGSH2HIqjqE=; b=LXST07nxbs0OdEOVmrFL/NZs8+lGn1C0XRHk343BzMIvF9vsiWr5gzQ1k+TK9bZta9 0cOOiS9V+4iWkxsPz4gTKIoTIxpwp1WGtIODZI8CaMjpSXlHLuN36rXD+6tWN1rUX+72 KWsJq8HwRTolGHVe7+yaW4NE6o+c0cvDdO7l2dqm+857tG821QlYnETMQeqzRwGy66im 4xVSX1ZPd7t9do+SwIFRAa2O2e9Vqvq8/a8wfWA3scCx6+ZeXBlu/6Ih1atXZpFeqQP/ dfpUW9eJ8WSklpkwGT60l9P4lf+Ks1OrdmDkryGKFNIu23s7xXcRCE9wWCguGb6Sipx3 eUGw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742892843; x=1743497643; 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=TQiPUAeAHXL1+vEdhjTdr6mpqGM2hepfNGSH2HIqjqE=; b=U0J9IVLZ2paMd8XjgsHR8j7R+AJmv9IxtsuXvwPmZSB8b//5nHxksu9Ep15ChUPTHf T379VyiA+z89vPW19SkOkY0jw9kSD4Q1UdU9Yyf3hlq1FwCvI5VOEtx4KvNl1dpB5rsu lX6ycNnHRmvkApN5dI3D2Xb/YcxxMBJhkeG+1PJx02kiudWEnRZJcE3cTo4xu8gVrFAx Hzugm0/GSrb6KO6UQjicFPe7Hsjet/L43y+oD9BskHp1WvwZ3aOiQgOEZ8o+b6RCEJ8c h/yw1lieM1LHWyFl46sycOzk3pMQeDbOZ98nzG5ITHiQIyXd2T5tCYVHxiwnTOPuwprE aMUw== X-Gm-Message-State: AOJu0YyYWzS3TqBojgzvcRFgkl1D8r1VAcV7dLiGt65RbGxZoPjG0Y4x HRMxCurrmR/tVurVA9SR9yXA0qn+g7jAzZM2JSo6cdXs9/4kMA9TF8g43D8WTLXeFnsf606hZKS iOVWYX8GdiHqero20Kh0HWEIdwUgMEg== X-Gm-Gg: ASbGncuPsvS2xXOhcToNYohlZYdqgHTzPoDtuME40cBqQw7OnliBQ1l0QejC5CET851 kpZVNFtPUDS84hogKPodJ/Htu8MI99S+DWk/NTnAMalMi9dPUDJzloVo3O9FaGom/YegvqdcKZf 6N1vMrsXvRYJc91zYCcQ2gu9S2n4PhyvtaRQ2e9qzG9nuWch4cIKqT+KOvTdvooslYDf8ndYg= X-Google-Smtp-Source: AGHT+IH3868bR+YJfpgFPL/tBo5w/e9/xEYuviABooz2KkFhyRiFdsWspqC0eLMwOcHzCSjVXhVKEX1W4ilv2Vld3Yo= X-Received: by 2002:a05:6830:2b24:b0:72b:84a7:47e0 with SMTP id 46e09a7af769-72c0ae4d1c4mr10592317a34.1.1742892842702; Tue, 25 Mar 2025 01:54:02 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 25 Mar 2025 04:53:51 -0400 X-Gm-Features: AQ5f1JpiJ8-OQBCO70ySG02vKq8j8Lm9roA9GGFB02TKCupI80dijXk0hUobDxY Message-ID: Subject: Re: size of attributes table is too big To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000c56512063126dc91" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c56512063126dc91 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Use "pg_repack" instead. It's an "online" CLUSTER / VACUUM FULL replacement that's in both RPM and apt repos. On Tue, Mar 25, 2025 at 12:36=E2=80=AFAM Siraj G wrot= e: > Thank you! > > I noticed over 99% free space. Now the challenge is running FULL VACUUM o= n > a table with size over 500GB. It is going to take a couple of hours I > presume. > > Also, I hope aggressive vacuuming will prevent us from this situation. > > Regards > Siraj > > > > > On Wed, Mar 19, 2025 at 11:27=E2=80=AFPM Ron Johnson > wrote: > >> On Wed, Mar 19, 2025 at 1:06=E2=80=AFPM Siraj G wr= ote: >> >>> Hello! >>> >>> I have a PG (v16) instance which is occupying around 1TB of storage. Ou= t >>> of this, around 350GB is occupied by the table pg_catalog.pg_attribute. >>> Why is the catalog table's size so big? >>> >>> Here are the sizes: >>> >>> pg_attribute >>> 338 GB >>> pg_attribute_relid_attnam_index >>> 117 GB >>> pg_attribute_relid_attnum_index >>> 69 GB >>> >>> I think this table must have tons of dead tuples. Please suggest to me >>> if we can purge any data/shrink the size of this table. >>> >>> >> Run pgstattuple and pgstatindex on them. They'll tell you how much bloa= t >> you have. >> >> And tune your autovacuum parameters to be more aggressive. These, for >> example, are my settings: >> autovacuum_analyze_scale_factor =3D 0.015 >> autovacuum_vacuum_scale_factor =3D 0.015 >> autovacuum_vacuum_insert_scale_factor =3D 0.015 >> autovacuum_vacuum_insert_threshold =3D 250 >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000c56512063126dc91 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Use "pg_repack" = instead.=C2=A0 It's an "online" CLUSTER / VACUUM FULL replace= ment that's in both RPM and apt repos.

On Tue, Mar 25, 2025 at 12:36=E2=80=AFAM Siraj G <tosiraj.g@gmail.com> wrote:
Thank you!

<= /div>
I noticed over 99% free space. Now the challenge is running FULL = VACUUM on a table with size over 500GB. It is going to take a couple of hou= rs I presume.

Also, I hope aggressive vacuuming wi= ll prevent us from this situation.

Regards
Siraj




On Wed, Mar 19, = 2025 at 11:27=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
=
On Wed, Mar 19, 2025 at 1:06=E2=80=AFPM Siraj G <tosiraj.g@gmail.com> wrot= e:
Hello!

I have a PG (v16) ins= tance which is occupying around 1TB of storage. Out of this, around 350GB i= s occupied by the table pg_catalog.pg_attribute.=C2=A0
Why is the= catalog table's size so big?

Here are the siz= es:

=
I think this table must have tons of dead tuples. Pleas= e suggest to me if we can purge any data/shrink the size of this table.


Run pgstattuple and= pgstatindex on them.=C2=A0 They'll tell you how much bloat you have.

And tune your autovacuum parameters to be more aggr= essive.=C2=A0 These, for example, are my settings:
autovacuum_ana= lyze_scale_factor =3D 0.015
autovacuum_vacuum_scale_factor =3D 0.015
= autovacuum_vacuum_insert_scale_factor =3D 0.015
autovacuum_vacuum_insert= _threshold =3D 250

--
Death to <Redacted>, and butter sauce.
Don't boil me= , I'm still alive.
<Redacted> lobster!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000c56512063126dc91--
pg_attribute
338 GB
pg_attribute_relid_attnam_index
117 GB
pg_attribute_relid_attnum_index
69 GB