Re: R: Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 6:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
> On Mon, Jun 20, 2016 at 8:29 AM, Rakesh Kumar <rakeshkumar464a3(at)gmail(dot)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 <Job(at)colliniconsulting(dot)it>
>> *To:* Rakesh Kumar <rakeshkumar464a3(at)gmail(dot)com>; "
>> pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)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-owner(at)postgresql(dot)org [
>> pgsql-general-owner(at)postgresql(dot)org] per conto di Rakesh Kumar [
>> rakeshkumar464a3(at)gmail(dot)com]
>> *Inviato:* lunedì 20 giugno 2016 11.34
>> *A:* pgsql-general(at)postgresql(dot)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 <Job(at)colliniconsulting(dot)it>
>> *To:* "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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