public inbox for [email protected]help / color / mirror / Atom feed
Re: size of attributes table is too big 3+ messages / 2 participants [nested] [flat]
* Re: size of attributes table is too big @ 2025-03-19 17:57 Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ron Johnson @ 2025-03-19 17:57 UTC (permalink / raw) To: pgsql-general On Wed, Mar 19, 2025 at 1:06 PM Siraj G <[email protected]> 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 = 0.015 autovacuum_vacuum_scale_factor = 0.015 autovacuum_vacuum_insert_scale_factor = 0.015 autovacuum_vacuum_insert_threshold = 250 -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: size of attributes table is too big @ 2025-03-25 04:36 Siraj G <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Siraj G @ 2025-03-25 04:36 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general 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 PM Ron Johnson <[email protected]> wrote: > On Wed, Mar 19, 2025 at 1:06 PM Siraj G <[email protected]> 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 = 0.015 > autovacuum_vacuum_scale_factor = 0.015 > autovacuum_vacuum_insert_scale_factor = 0.015 > autovacuum_vacuum_insert_threshold = 250 > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: size of attributes table is too big @ 2025-03-25 08:53 Ron Johnson <[email protected]> parent: Siraj G <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Ron Johnson @ 2025-03-25 08:53 UTC (permalink / raw) To: pgsql-general 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 AM Siraj G <[email protected]> wrote: > 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 PM Ron Johnson <[email protected]> > wrote: > >> On Wed, Mar 19, 2025 at 1:06 PM Siraj G <[email protected]> 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 = 0.015 >> autovacuum_vacuum_scale_factor = 0.015 >> autovacuum_vacuum_insert_scale_factor = 0.015 >> autovacuum_vacuum_insert_threshold = 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> lobster! ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-25 08:53 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-19 17:57 Re: size of attributes table is too big Ron Johnson <[email protected]> 2025-03-25 04:36 ` Siraj G <[email protected]> 2025-03-25 08:53 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox