Need help to make space on my database

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

"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@aklaver.com

Please run VACUUM with ANALYZE option that will also update the DB Stats.

Regards
Kashif Zeeshan
Bitnine Global

On Mon, Apr 29, 2024 at 7:19 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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@aklaver.com
<mailto:adrian.klaver@aklaver.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&gt;

"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@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

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@aklaver.com
<mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

<mailto:adrian.klaver@aklaver.com

<mailto:adrian.klaver@aklaver.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&gt;

     <https://www.postgresql.org/docs/current/sql-vacuum.html

<https://www.postgresql.org/docs/current/sql-vacuum.html&gt;&gt;

     "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@aklaver.com <mailto:adrian.klaver@aklaver.com>

<mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

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.klaver@aklaver.com> a
écrit :

On Mon, 2024-04-29 at 15:45 +0200, Cocam' server wrote:

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)

If you don't have enough disk space for a VACUUM (FULL), your only option is
pg_dump / DROP DATABASE / CREATE DATABASE / restore.

Yours,
Laurenz Albe

On 4/29/24 08:51, Cocam' server wrote:

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

You have something against providing actual numbers?

The point is there is really nothing to be gained by doing VACUUM FULL
if the dead tuples are some small percentage of the tables.

--
Adrian Klaver
adrian.klaver@aklaver.com