size of attributes table is too big

Hello

On 2025-Mar-19, Siraj G wrote:

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?

Heavy use of temp tables is a known cause of this.

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.

Yeah, I'd also bet that there are tons of dead tuples, or just unused
free space. To purge it you would use VACUUM FULL, though that would
need to lock all accesses to the table.

Does your instance run with autovacuum disabled perchance?

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree. (Don Knuth)

st 19. 3. 2025 v 18:14 odesílatel Álvaro Herrera <alvherre@alvh.no-ip.org>
napsal:

Hello

On 2025-Mar-19, Siraj G wrote:

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?

Heavy use of temp tables is a known cause of this.

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.

Yeah, I'd also bet that there are tons of dead tuples, or just unused
free space. To purge it you would use VACUUM FULL, though that would
need to lock all accesses to the table.

Does your instance run with autovacuum disabled perchance?

or long unclosed transactions, maybe forgotten replication slots, ...

It is very strange so with this size it is still usable. Today hardware is
unbelievable strong

Regards

Pavel

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
adrian.klaver@aklaver.com

Hello Pavel

The SQL instance is a target of google DMS and it does have a physical
replica.

A couple of weeks back we did have a performance issue and vacuum was run
at that time to fix the problem. Very soon we may run into the same problem
I presume.

Regards
Siraj

On Wed, Mar 19, 2025 at 10:47 PM Pavel Stehule <pavel.stehule@gmail.com>
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.
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 <adrian.klaver@aklaver.com>
wrote:

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
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Mar 19, 2025 at 1:06 PM Siraj G <tosiraj.g@gmail.com> 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!

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 <ronljohnsonjr@gmail.com>
wrote:

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 <tosiraj.g@gmail.com> 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 <ronljohnsonjr@gmail.com>
wrote:

On Wed, Mar 19, 2025 at 1:06 PM Siraj G <tosiraj.g@gmail.com> 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!