public inbox for [email protected]
help / color / mirror / Atom feedFrom: Cocam' server <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Need help to make space on my database
Date: Mon, 29 Apr 2024 17:51:36 +0200
Message-ID: <CAJyHkoh9X-nk2ebxWgBTgG+KLk84jNHazdyUA7ZEK3tiu4xuVw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAJyHkohEM4cG==cqWwLYTLzaMV-XWUVF0yb9AYfZSwVu_fO=vg@mail.gmail.com>
<[email protected]>
<CAJyHkoh6OjQ-M6BvFV6c=XRErWB1hVsimPaVo+X8qzxHrkiaiQ@mail.gmail.com>
<[email protected]>
<CAJyHkoixrJB=NOY3DwgBD7ZM1_nsJkSzgd9CZ97dejJzmvSdCQ@mail.gmail.com>
<[email protected]>
> Did the above work for each table?
Yes, except for the biggest table
> Have you done something like?:
>
> select relname, n_dead_tup from pg_stat_all_tables where relname =
> '<table_name>';
I hadn't thought of that, but it seems that some tables have dead tuples
Le lun. 29 avr. 2024 à 17:34, Adrian Klaver <[email protected]> a
écrit :
> On 4/29/24 08:04, Cocam' server wrote:
>
> When replying use Reply All to include the mailing list
> Ccing list
>
> > > How much current free space do you have available on the disk?
> > as we speak, I only have 6 GB available on the machine running the server
> >
> > > Did you VACUUM FULL a table at a time or all of them at once?
> > I tried to make a VACUUM FULL. I also tried on the biggest tables (200
> > Mb and +) but not on all of them
>
> Did the above work for each table?
>
> Have you done something like?:
>
> select relname, n_dead_tup from pg_stat_all_tables where relname =
> '<table_name>';
>
> to see if there any dead tuples to clean out.
>
> Or if you use the contrib extension pgstattuple:
>
> https://www.postgresql.org/docs/current/pgstattuple.html
>
> then:
>
> SELECT * FROM pgstattuple('<table_name>');
>
> This returns something like:
>
> -[ RECORD 1 ]------+--------
> table_len | 3940352
> tuple_count | 4310
> tuple_len | 3755414
> tuple_percent | 95.31
> dead_tuple_count | 0
> dead_tuple_len | 0
> dead_tuple_percent | 0
> free_space | 124060
> free_percent | 3.15
>
>
> >
> > The two biggest are these:
> > state_groups_state | 5475 MB
> > event_json | 2328 MB
> >
> > (I'd particularly like to make room on these two tables, which take up
> > the most space)
> >
> > By the way, excuse me if I make a few mistakes (especially when
> > replying), this is the first time I've used Postgres community support
> > directly
> >
> >
> > Le lun. 29 avr. 2024 à 16:37, Adrian Klaver <[email protected]
> > <mailto:[email protected]>> a écrit :
> >
> > On 4/29/24 07:33, Cocam' server wrote:
> >
> > Please reply to list also
> > Ccing list
> >
> > > No, the aim is also to reallocate free space to the system for
> > the other
> > > tasks it performs.(That's why I said I'd like it returned to the
> OS)
> >
> > You led with:
> >
> > "I need help to make space on my database".
> >
> > How much current free space do you have available on the disk?
> >
> > Did you VACUUM FULL a table at a time or all of them at once?
> >
> > What are the individual tables sizes?
> >
> > >
> > > Le lun. 29 avr. 2024 à 16:19, Adrian Klaver
> > <[email protected] <mailto:[email protected]>
> > > <mailto:[email protected]
> > <mailto:[email protected]>>> a écrit :
> > >
> > > On 4/29/24 06:45, Cocam' server wrote:
> > > > Hello.
> > > >
> > > > I need help to make space on my database. I have tables
> > that are
> > > several
> > > > GB in size. I used to use the VACUUM FULL VERBOSE command;
> but
> > > now, this
> > > > command is too greedy in free space to be used and I'm
> looking
> > > for a way
> > > > to make free space (given back to the OS)
> > > >
> > > > Thanks in advance to everyone who responds
> > >
> > > Per
> > >
> > > https://www.postgresql.org/docs/current/sql-vacuum.html
> > <https://www.postgresql.org/docs/current/sql-vacuum.html;
> > > <https://www.postgresql.org/docs/current/sql-vacuum.html
> > <https://www.postgresql.org/docs/current/sql-vacuum.html>;
> > >
> > > "VACUUM reclaims storage occupied by dead tuples. In normal
> > PostgreSQL
> > > operation, tuples that are deleted or obsoleted by an update
> > are not
> > > physically removed from their table; they remain present
> > until a VACUUM
> > > is done. Therefore it's necessary to do VACUUM periodically,
> > especially
> > > on frequently-updated tables.
> > >
> > > <...>
> > >
> > > Plain VACUUM (without FULL) simply reclaims space and makes it
> > > available
> > > for re-use. This form of the command can operate in parallel
> with
> > > normal
> > > reading and writing of the table, as an exclusive lock is not
> > obtained.
> > > However, extra space is not returned to the operating system
> > (in most
> > > cases); it's just kept available for re-use within the same
> > table.
> > > "
> > >
> > > So a regular VACUUM should work if all you want to do is give
> the
> > > database the ability to recycle the vacuumed tuple space.
> > >
> > > --
> > > Adrian Klaver
> > > [email protected] <mailto:[email protected]>
> > <mailto:[email protected] <mailto:[email protected]
> >>
> > >
> >
> > --
> > Adrian Klaver
> > [email protected] <mailto:[email protected]>
> >
>
> --
> Adrian Klaver
> [email protected]
>
>
view thread (7+ messages) latest in thread
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], [email protected]
Subject: Re: Need help to make space on my database
In-Reply-To: <CAJyHkoh9X-nk2ebxWgBTgG+KLk84jNHazdyUA7ZEK3tiu4xuVw@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