Re: Need help to make space on my database

> 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 <adrian(dot)klaver(at)aklaver(dot)com> 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 <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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
> > <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> 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
> > > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com
> >>
> > >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>