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 1tww1n-0018nO-FG for pgsql-general@arkaria.postgresql.org; Tue, 25 Mar 2025 04:36:39 +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 1tww1l-00FPPa-PF for pgsql-general@arkaria.postgresql.org; Tue, 25 Mar 2025 04:36:37 +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 1tww1l-00FPOP-C9 for pgsql-general@lists.postgresql.org; Tue, 25 Mar 2025 04:36:37 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tww1j-0010Wd-0S for pgsql-general@postgresql.org; Tue, 25 Mar 2025 04:36:37 +0000 Received: by mail-ej1-x629.google.com with SMTP id a640c23a62f3a-ac2a9a74d9cso1039492566b.1 for ; Mon, 24 Mar 2025 21:36:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742877394; x=1743482194; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=dFFgLIipOw4dGNSvEVyZbE6+Iw1htZD+8I8UgNjpnWM=; b=fzA/odsGAk+m/0Nfvb+ExuYHkR0F2fr5ORsRiGwR2w8P75QxGK2HMf2Hf9U1Sq3EE4 4yuWPPEWTt3sysCwbG1rmUFbuEw4LjW0gnzRzfiOIJfk8ar2aTnKPOgxdoJtBdBrSNAd qpc5JoGSyihlht92IbHkTUKnYhr8t2vvlZKFYXUNG+THHsbT2iffcDwiy9OtxIOaX9bh HhJM+uA+ZciqSZyAukdsj7hS1/DsiDAPJtoZyKVdHo760DFEJGOQk01AoHcFCvI4j0vM aHRXE+LZ0sFe1UJj7pRb+pbSQaHRRGH7KaCfot3XHC3vtCKlTXni7nlcTZufBO0aabi5 OmuA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742877394; x=1743482194; h=cc: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=dFFgLIipOw4dGNSvEVyZbE6+Iw1htZD+8I8UgNjpnWM=; b=wCryKOZhPXg1QfMWmAoM5euG0CboHYotqJs4FiX/csQ2+GuIERXSr9QXcf85itYUo6 TpycKvlqzYRsv7mcyT8TMLBFvR5u7rkbaeMrZgbDHsWP/vYS+qHlzxGIiymWG8aiT/3z vaL9Jx/UCrH8PscmrHcbPaNlXBYsocAeYbwB4QAs7a/9FjsENZaUBpVcwZaO0LEadXye 5SkT41lzLqeUm1r7KFo2DYI3nFT91AmokWQ2RsqCQQ4jr2I9Bngu1bN2GboShPTxBzEy UCSX4yhdkXxxHLr2cM6uxffj2iJtgujtCm1t0qDR7kQ+ZhGkxFbUo1B8ED4NnxzdfGRj NrUQ== X-Gm-Message-State: AOJu0YzgqTp+BPZ1H89Tb0JfLEWIA0umrTHt1VOssyVwimt5Nf8sI6jv i8y0673+ujtZ2N8QEQEyeqZADsRzn7yAGku2W6r7vATTw5DmFhGJdxGs2Fn2dvCWfUpaCqvuAOa JxGYVhLtYvDC8jpCpbOlxK0UHj0o= X-Gm-Gg: ASbGncsiNucJU/LlZkd6eZjbLamL/oVkXDtphzOoGZZhNxebGVZRtJxwUEYLffDQSNZ 9WwEKVOuneWrerzJ3k+JaHTqcBccepfio45HdRfEN8JR3rDw0wa4mv95bX366+kxspZFgMvkSRe tPuYKsZDkTcH6VUlZFpjrLm/onakw= X-Google-Smtp-Source: AGHT+IGu/4MS5ZG74mh6XgaE/28nMyKPLLTJn/T5Pc/GQ0qbCmG9rzjHlV3mOWUlVX2+pPzKZTw/02k1RFRDccxBqpA= X-Received: by 2002:a17:907:1b0d:b0:ac3:afb1:dee7 with SMTP id a640c23a62f3a-ac3f22b7e5dmr1453349966b.28.1742877393476; Mon, 24 Mar 2025 21:36:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Siraj G Date: Tue, 25 Mar 2025 10:06:22 +0530 X-Gm-Features: AQ5f1JrfixNuD8vUoJZx62EHoxz9Dd-zFZt1XDxusGdJ2RNfC1BGDGRFBt8QcDE Message-ID: Subject: Re: size of attributes table is too big To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000ece2e6063123433d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ece2e6063123433d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you! 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 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 wro= te: > >> 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 i= f >> 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 > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --000000000000ece2e6063123433d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you!

I noticed over 99% free spac= e. Now the challenge is running FULL VACUUM on 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 <ronljoh= nsonjr@gmail.com> wrote:
On Wed, Mar 19, 2025 at 1= :06=E2=80=AFPM Siraj G <tosiraj.g@gmail.com> wrote:
Hello!<= div>
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_attribute<= /div>
338 GB
<= /td>
pg_attribu= te_relid_attnam_index
117 GB
pg_attribute_relid_attnum_index
69 GB

I think this table must hav= e 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.=C2=A0 They'll tell yo= u how much bloat you have.

And tune your autovacuu= m parameters to be more aggressive.=C2=A0 These, for example, are my settin= gs:
autovacuum_analyze_scale_factor =3D 0.015
autovacuum_vacuu= m_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.
<Redact= ed> lobster!
--000000000000ece2e6063123433d--