public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Cocam' server <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Need help to make space on my database
Date: Mon, 29 Apr 2024 08:34:40 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJyHkoixrJB=NOY3DwgBD7ZM1_nsJkSzgd9CZ97dejJzmvSdCQ@mail.gmail.com>
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>
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: <[email protected]>
* 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