Re: Need help to make space on my database - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Need help to make space on my database |
Date | |
Msg-id | 8f942f5d-9c35-427d-9c1f-18aa405a7b8d@aklaver.com Whole thread Raw |
In response to | Need help to make space on my database ("Cocam' server" <cocamserver@gmail.com>) |
Responses |
Re: Need help to make space on my database
("Cocam' server" <cocamserver@gmail.com>)
|
List | pgsql-general |
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> > > <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@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
pgsql-general by date: