>
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
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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
> > <mailto:adrian.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
> <mailto:adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>>
> >
>
> --
> Adrian Klaver
> adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
