Re: Need help to make space on my database

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