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:17 Adrian Klaver <[email protected]> 2025-03-19 17:36 ` Re: size of attributes table is too big Siraj G <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Klaver @ 2025-03-19 17:17 UTC (permalink / raw) To: Siraj G <[email protected]>; [email protected] On 3/19/25 10:06, Siraj G wrote: > Hello! > > I have a PG (v16) instance which is occupying around 1TB of storage. Out Exact version of Postgres 16, include the x in 16.x. > 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: How did you measure the sizes? pg_attribute maintains information about table columns, how many table columns do you have? Are you creating/dropping tables on a regular basis? Is autovacuum running properly? Have you run VACUUM manually on pg_attribute? > > 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. > > REgards > Siraj -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: size of attributes table is too big 2025-03-19 17:17 Re: size of attributes table is too big Adrian Klaver <[email protected]> @ 2025-03-19 17:36 ` Siraj G <[email protected]> 2025-03-19 17:48 ` Re: size of attributes table is too big Adrian Klaver <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Siraj G @ 2025-03-19 17:36 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: [email protected] Hi Adrian Used this query to find the sizes: select relname AS object_name,relkind AS object_type,pg_size_pretty(pg_relation_size(oid)) AS object_size FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog') ORDER BY pg_relation_size(oid) DESC; We have close to 2000 tables, not sure about the columns. There should be around 120 - 150 tables that are active, getting the data from DMS. We do not create/drop tables in our instance unless required. This is a cloud managed SQL and we do not have any custom setting on the vacuum part. On Wed, Mar 19, 2025 at 10:47 PM Adrian Klaver <[email protected]> wrote: > On 3/19/25 10:06, Siraj G wrote: > > Hello! > > > > I have a PG (v16) instance which is occupying around 1TB of storage. Out > > Exact version of Postgres 16, include the x in 16.x. > > > 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: > > How did you measure the sizes? > > pg_attribute maintains information about table columns, how many table > columns do you have? > > Are you creating/dropping tables on a regular basis? > > Is autovacuum running properly? > > Have you run VACUUM manually on pg_attribute? > > > > > 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. > > > > REgards > > Siraj > > -- > Adrian Klaver > [email protected] > > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: size of attributes table is too big 2025-03-19 17:17 Re: size of attributes table is too big Adrian Klaver <[email protected]> 2025-03-19 17:36 ` Re: size of attributes table is too big Siraj G <[email protected]> @ 2025-03-19 17:48 ` Adrian Klaver <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Adrian Klaver @ 2025-03-19 17:48 UTC (permalink / raw) To: Siraj G <[email protected]>; +Cc: [email protected] On 3/19/25 10:36 AM, Siraj G wrote: > Hi Adrian > > Used this query to find the sizes: > > select relname AS object_name,relkind AS > object_type,pg_size_pretty(pg_relation_size(oid)) AS object_size > FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE > nspname = 'pg_catalog') > ORDER BY pg_relation_size(oid) DESC; > > We have close to 2000 tables, not sure about the columns. There should > be around 120 - 150 tables that are active, getting the data from DMS. This would have been a good thing to have included in the original post. > We do not create/drop tables in our instance unless required. > > This is a cloud managed SQL and we do not have any custom setting on the > vacuum part. To be clear the issue is in the Google Cloud SQL instance? Or are you seeing the problem on the Postgres instance the data is coming from? > > On Wed, Mar 19, 2025 at 10:47 PM Adrian Klaver > <[email protected] <mailto:[email protected]>> wrote: > > On 3/19/25 10:06, Siraj G wrote: > > Hello! > > > > I have a PG (v16) instance which is occupying around 1TB of > storage. Out > > Exact version of Postgres 16, include the x in 16.x. > > > 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: > > How did you measure the sizes? > > pg_attribute maintains information about table columns, how many table > columns do you have? > > Are you creating/dropping tables on a regular basis? > > Is autovacuum running properly? > > Have you run VACUUM manually on pg_attribute? > > > > > 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. > > > > REgards > > Siraj > > -- > Adrian Klaver > [email protected] <mailto:[email protected]> > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-19 17:48 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:17 Re: size of attributes table is too big Adrian Klaver <[email protected]> 2025-03-19 17:36 ` Siraj G <[email protected]> 2025-03-19 17:48 ` Adrian Klaver <[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