> 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.kla...@aklaver.com> 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.kla...@aklaver.com > > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > <mailto:adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com > >> > > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
