public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: size of attributes table is too big
Date: Tue, 25 Mar 2025 04:53:51 -0400
Message-ID: <CANzqJaCd+WNj=wgmfaTWZ3f_WDGk1pB++QL7FdY3L1OyetNoYQ@mail.gmail.com> (raw)
In-Reply-To: <CAC5iy61UBi3gQh3bWL0f13Ouxg7BgKDbASscpy1whzr7c=ZkDw@mail.gmail.com>
References: <CAC5iy61zUBySCMFH3JSKd6W922nV3fHMpXi+qUuPvXQ3shuOBg@mail.gmail.com>
<CANzqJaAxC71J3wQjvPv2N37X1cDfnD_x+F3+tcsjG69PorSMWA@mail.gmail.com>
<CAC5iy61UBi3gQh3bWL0f13Ouxg7BgKDbASscpy1whzr7c=ZkDw@mail.gmail.com>
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!
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: size of attributes table is too big
In-Reply-To: <CANzqJaCd+WNj=wgmfaTWZ3f_WDGk1pB++QL7FdY3L1OyetNoYQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox