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 1tuxfP-00EvT2-LD for pgsql-general@arkaria.postgresql.org; Wed, 19 Mar 2025 17:57:23 +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 1tuxfN-009NAb-F3 for pgsql-general@arkaria.postgresql.org; Wed, 19 Mar 2025 17:57:21 +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 1tuxfN-009N9c-00 for pgsql-general@lists.postgresql.org; Wed, 19 Mar 2025 17:57:21 +0000 Received: from mail-oi1-x229.google.com ([2607:f8b0:4864:20::229]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tuxfL-003q8b-0O for pgsql-general@postgresql.org; Wed, 19 Mar 2025 17:57:20 +0000 Received: by mail-oi1-x229.google.com with SMTP id 5614622812f47-3fbc00143d6so3732694b6e.3 for ; Wed, 19 Mar 2025 10:57:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742407038; x=1743011838; 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=R2RSoc4OULNTQ7L4xkhCMjR7WoON/O34XmjxKuWMwJs=; b=VTCJ+NrukHH3rsI5r7nz4qjWVpP+XySAQtCndH3vHzG+jRDLkBSglHT3vJeMagp4uo IgksXXIgCHlaVxmmYWCkeg4TctLVOV7l9xiuIftrXYGsyTDlUxnpQtnj2lQ6d1AUqBG7 60BcTYiv/D8jE5zYgdpp2u0WGlx1a+8T6hST+FPfq7Xkfe/3hPH3biAnum+wqhRotobR QYe9TVk27P1YKs7pb6fD71rVXJSrehlRqP6AHcvCQL4TL0TxZ7vXNigRWZqERnKbSwuJ Hec06cs3LDshy6AwyFK5Ov0Amvc2N1JLp902rZPIHw5pAWkvCHiQuKAqUyfu8emMNHmP co1A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742407038; x=1743011838; 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=R2RSoc4OULNTQ7L4xkhCMjR7WoON/O34XmjxKuWMwJs=; b=WBcPrgEJwxENk4i3uAu/TZkGMpj6kgCF1n/Pp1fT0FbYwTCkKjd4uEIagnDOusAkxg 3pV4QF11HAMmMjQa/0e6UnnSRFO7PvRlg5KHSh1jX5sfofPyCxR9GiiHd5uSoJDOyARE 6sb9EA7iPxEi00WrjTUdULFXO7m4eOawOaZ6K4fOkmOwWyY/yM9h5ivf/tcCpT0azkSq Nes26q/1IIZjVbCBe73bT/sXr7KeHVDceHr/IIyp5/twOWs3GuR4CDs069i6SfDU7kJ3 oFmQ+/qKSSPdnJfe6Jrm60yT0Nivq93nFUh79lAvvjEZFCNDEUNlt/vQHB13b2ou7v9w RLHQ== X-Gm-Message-State: AOJu0YzuTnx0DSdNMYdL+7TOonhI52XlESK7CrQ8ykMa0LDOEE4iBNtN mnH+zM7+mkBUqVspjez9cJnCqVHBf4Iw+zIxDpuZWiTi3GrDidBZE/E7AoXqvFo1UADS8DsmvBy ygvKUV3bAB/4Br1VFJq4AxPxEYTRTR1zq X-Gm-Gg: ASbGncu68mH0eW7nW6pdHhRhwjpGEtlKOp0Cu8o1Z1gYBNN04vtbrd1vBNkMEqHGp40 S1f5qv4P1Z8Thyriw/e0N8P+0ZZgln3+PR28WdRELJJRRUd5Em6jJKCOSb6nJCJkZhCjEtLwPMp ES9peR132WdFge0YOWekd2KOG0ZHbAbHdaT39rJALoJezUglkHMdwtG3mO6+q+ X-Google-Smtp-Source: AGHT+IH0ucesykNNgxLXC1MpZG+6BkqzhiwA/EDBnSRNWILjjvbws24S3stBXZPAevXOycqRcnBkJ2r+mcFgqR0i1s0= X-Received: by 2002:a05:6808:218b:b0:3f8:4523:2697 with SMTP id 5614622812f47-3feb4b7dc94mr263897b6e.37.1742407038462; Wed, 19 Mar 2025 10:57:18 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Wed, 19 Mar 2025 13:57:06 -0400 X-Gm-Features: AQ5f1Jp6vt6-7DyHUJjOFALfKg0flaKl_6VxQnhcCPwHPw6MP-lpwXuKOfPai6Q Message-ID: Subject: Re: size of attributes table is too big To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000094f1e40630b5c0da" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000094f1e40630b5c0da Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Mar 19, 2025 at 1:06=E2=80=AFPM Siraj G wrote= : > Hello! > > I have a PG (v16) instance which is occupying around 1TB of storage. Out > 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 bloat 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 --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000094f1e40630b5c0da Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Mar 19, 2025 at 1:06=E2=80=AFPM S= iraj G <tosiraj.g@gmail.com&g= t; wrote:
Hello!

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

pg_attribut= e
338 GB
pg_attri= bute_relid_attnam_index
117 GB
pg_attribute_relid_attnum_index
69 GB

I think this table must have tons of dead tupl= es. Please suggest to me if we can purge any data/shrink the size of this t= able.


Run pgstat= tuple and pgstatindex on them.=C2=A0 They'll tell you how much bloat yo= u have.

And tune your autovacuum parameters to be = more aggressive.=C2=A0 These, for example, are my settings:
autov= acuum_analyze_scale_factor =3D 0.015
autovacuum_vacuum_scale_factor =3D = 0.015
autovacuum_vacuum_insert_scale_factor =3D 0.015
autovacuum_vacu= um_insert_threshold =3D 250

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