On Mon, Jun 20, 2016 at 6:20 PM, Melvin Davidson <melvin6...@gmail.com> wrote:
> > On Mon, Jun 20, 2016 at 8:29 AM, Rakesh Kumar <rakeshkumar46...@gmail.com> > wrote: > >> But then autovaccum avoids this. Granted it won't give back free space to >> OS, but it won't let it grow too (or am I missing something). >> >> >> ------------------------------ >> *From:* Job <j...@colliniconsulting.it> >> *To:* Rakesh Kumar <rakeshkumar46...@gmail.com>; " >> pgsql-general@postgresql.org" <pgsql-general@postgresql.org> >> *Sent:* Monday, June 20, 2016 5:39 AM >> *Subject:* R: [GENERAL] Vacuum full: alternatives? >> >> Hi Rakesh, >> >> if i do not free disk space, after some days disk can become full. >> Everyday we have a lot of pg_bulkload and delete. >> >> Thank you! >> Francesco >> >> ------------------------------ >> *Da:* pgsql-general-ow...@postgresql.org [ >> pgsql-general-ow...@postgresql.org] per conto di Rakesh Kumar [ >> rakeshkumar46...@gmail.com] >> *Inviato:* lunedì 20 giugno 2016 11.34 >> *A:* pgsql-general@postgresql.org >> *Oggetto:* Re: [GENERAL] Vacuum full: alternatives? >> >> Any reason why you need the space back? What is wrong with space >> remaining constant at 4GB. >> >> >> ------------------------------ >> *From:* Job <j...@colliniconsulting.it> >> *To:* "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> >> *Sent:* Monday, June 20, 2016 5:18 AM >> *Subject:* [GENERAL] Vacuum full: alternatives? >> >> Hello, >> >> we have a table with an heavy traffic of pg_bulkload and delete of >> records. >> The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb >> back. >> >> We have important problems on size and the only way to gain free space is >> issueing a vacuum full <table>. >> But the operation is very slow, sometimes 2/4 hours, and table is not >> available for services as it is locked. >> >> We do not delete everything at one (in this case the truncate woudl >> resolve the problem). >> >> The autovacuum is not able (same for normal vacuum) to free the spaces. >> >> Are there some suggestions or another way to manage this? >> >> Thank you! >> Francesco >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >> >> >> > > but it won't let it grow too (or am I missing something). > > Yes, you are missing something. By partioning and {Vacuum Full only the > table with data no longer needed}, the rest of the data remains available > to the users > AND space is reclaimed by the O/S, so it's the best of both worlds. > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. > --- Few things you can try : 1. Partition your table daily 2. Tune your autovacuum parameters if you think autovacuum is not keeping up with the fragmentation speed. fore.g : alter table table_name set (autovacuum_enabled=true, autovacuum_vacuum_threshold=5000, autovacuum_analyze_threshold=5000, autovacuum_vacuum_scale_factor=0.1, autovacuum_analyze_scale_factor=0.2); Read this before tuning : https://www.postgresql.org/docs/9.1/static/runtime-config-autovacuum.html 3. If you can recreate/alter your table, create/alter with a fillfactor of 20 so that your deleted rows resides in the same page.It might use extra space but you will face less fragmentation problems. link : https://www.postgresql.org/docs/8.3/static/sql-createtable.html However, i have faced one problem in past where we have streaming replication setup of one master and 4 slaves. After all these tunings , autovacuum is not able to remove dead tuples and queries are getting slower and slower. After stopping all applications and streaming replicated slaves, i was able to defrag the table properly. The doc says autovacuum will not remove any dead tuples if it has any reference to those dead tuples anywhere but i am not sure how to find those dead tuples which are still being referenced :) Thanks, Adarsh Sharma
