public inbox for [email protected]help / color / mirror / Atom feed
Need help to make space on my database 7+ messages / 4 participants [nested] [flat]
* Need help to make space on my database @ 2024-04-29 13:45 Cocam' server <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Cocam' server @ 2024-04-29 13:45 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help to make space on my database @ 2024-04-29 14:19 Adrian Klaver <[email protected]> parent: Cocam' server <[email protected]> 1 sibling, 2 replies; 7+ messages in thread From: Adrian Klaver @ 2024-04-29 14:19 UTC (permalink / raw) To: Cocam' server <[email protected]>; [email protected] 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 [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help to make space on my database @ 2024-04-29 14:25 Kashif Zeeshan <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Kashif Zeeshan @ 2024-04-29 14:25 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Cocam' server <[email protected]>; [email protected] 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 <[email protected]> wrote: > 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 > [email protected] > > > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help to make space on my database @ 2024-04-29 14:37 Adrian Klaver <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2024-04-29 14:37 UTC (permalink / raw) To: Cocam' server <[email protected]>; +Cc: pgsql-general 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]>> 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; > > "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]> > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help to make space on my database @ 2024-04-29 15:34 Adrian Klaver <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2024-04-29 15:34 UTC (permalink / raw) To: Cocam' server <[email protected]>; +Cc: 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 <[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] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help to make space on my database @ 2024-04-29 15:51 Cocam' server <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Cocam' server @ 2024-04-29 15:51 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general > 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 <[email protected]> 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 <[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] > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help to make space on my database @ 2024-04-29 17:34 Laurenz Albe <[email protected]> parent: Cocam' server <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Laurenz Albe @ 2024-04-29 17:34 UTC (permalink / raw) To: Cocam' server <[email protected]>; [email protected] 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-04-29 17:34 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-29 13:45 Need help to make space on my database Cocam' server <[email protected]> 2024-04-29 14:19 ` Adrian Klaver <[email protected]> 2024-04-29 14:25 ` Kashif Zeeshan <[email protected]> 2024-04-29 14:37 ` Adrian Klaver <[email protected]> 2024-04-29 15:34 ` Adrian Klaver <[email protected]> 2024-04-29 15:51 ` Cocam' server <[email protected]> 2024-04-29 17:34 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox