Vacuum full: alternatives?

Any reason why you need the space back? What is wrong with space remaining constant at 4GB.

From: Job <Job@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

Am 20.06.2016 um 11:18 schrieb Job:

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.

autovaccum marks space as free, but don't give the space back to os.

I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@postgresql.org [pgsql-general-owner@postgresql.org] per conto di Rakesh Kumar [rakeshkumar464a3@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 <Job@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<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free" location?

Thank you!
Francesco

________________________________________
Da: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] per conto di Andreas Kretschmer [andreas@a-kretschmer.de]
Inviato: lunedì 20 giugno 2016 11.37
A: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Vacuum full: alternatives?

Am 20.06.2016 um 11:18 schrieb Job:

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.

autovaccum marks space as free, but don't give the space back to os.

I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 2016-06-20 at 11:43 +0200, Job wrote:

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free" location?

Yes, but you may have to make autovacuum process more aggressive. Even
then it might not cope with the frequency of your bulk I/U/D.

There are many tools to use instead of VACUUM FULL though, have a look
at pg_reorg and pgcompact for example. Do not be afraid to use an
awesome tool called Google as well ;-) (This is like the very basic
problem everyone asks about, so you'll find many more in-depth answers
and articles; the phrase you want to google for is "postgresql bloat")

Cheers,
R.

NET-A-PORTER.COM

CONFIDENTIALITY NOTICE
The information in this email is confidential and is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, you must not read, use or disseminate the information. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Net-A-Porter Group Limited.

The Net-A-Porter Group Limited is a company registered in England & Wales Number: 3820604 Registered Office: 1 The Village Offices, Westfield, Ariel Way, London, W12 7GF

Am 20.06.2016 um 11:43 schrieb Job:

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free" location?

exactly, that's the task for vacuum

Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer <andreas@a-kretschmer.de

wrote:

Am 20.06.2016 um 11:43 schrieb Job:

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not

more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free"
location?

exactly, that's the task for vacuum

Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

We do not delete everything at one (in this case the truncate woudl

resolve the problem).

Please, it is very important you provide PostgreSQL version & O/S, as
improvements to VACUUM may play a role here.

Is there any reason you cannot partition the table? Moving the data to
separate partitions
(based on a date or key field) will allow you to vacuum full only 1
partition at a time.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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@colliniconsulting.it>
To: Rakesh Kumar <rakeshkumar464a3@gmail.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Monday, June 20, 2016 5:39 AM
Subject: R: [GENERAL] Vacuum full: alternatives?

#yiv6783361115 #yiv6783361115 --P {MARGIN-BOTTOM:0px;MARGIN-TOP:0px;}#yiv6783361115 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@postgresql.org [pgsql-general-owner@postgresql.org] per conto di Rakesh Kumar [rakeshkumar464a3@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 <Job@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

On Mon, Jun 20, 2016 at 8:29 AM, Rakesh Kumar <rakeshkumar464a3@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 <Job@colliniconsulting.it>
*To:* Rakesh Kumar <rakeshkumar464a3@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-owner@postgresql.org [
pgsql-general-owner@postgresql.org] per conto di Rakesh Kumar [
rakeshkumar464a3@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 <Job@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.

On Mon, Jun 20, 2016 at 6:20 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

On Mon, Jun 20, 2016 at 8:29 AM, Rakesh Kumar <rakeshkumar464a3@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 <Job@colliniconsulting.it>
*To:* Rakesh Kumar <rakeshkumar464a3@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-owner@postgresql.org [
pgsql-general-owner@postgresql.org] per conto di Rakesh Kumar [
rakeshkumar464a3@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 <Job@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

El 20/06/16 a las 09:50, Melvin Davidson escribió:

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.

That's not entirely true. Think about a SELECT which has to scan all
child tables.

Your are also adding another layer of complexity to the system.

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 20/06/16 16:23, Martín Marqués wrote:

El 20/06/16 a las 09:50, Melvin Davidson escribió:

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.

That's not entirely true. Think about a SELECT which has to scan all
child tables.

Or any SELECT on the parent at all. The planner needs to examine the
CHECK constraints on the children and can't do it if the child is locked
in ACCESS EXCLUSIVE mode.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 20.06.2016 17:30, Vik Fearing wrote:

On 20/06/16 16:23, Martín Marqués wrote:

El 20/06/16 a las 09:50, Melvin Davidson escribió:

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.

That's not entirely true. Think about a SELECT which has to scan all
child tables.

Or any SELECT on the parent at all. The planner needs to examine the
CHECK constraints on the children and can't do it if the child is locked
in ACCESS EXCLUSIVE mode.

+1

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-06-20 11:30 GMT-03:00 Vik Fearing <vik@2ndquadrant.fr>:

On 20/06/16 16:23, Martín Marqués wrote:

That's not entirely true. Think about a SELECT which has to scan all
child tables.

Or any SELECT on the parent at all. The planner needs to examine the
CHECK constraints on the children and can't do it if the child is locked
in ACCESS EXCLUSIVE mode.

Yeah, totally skipped my mind that, so partitioning is actually a bad
idea, if that's all they are looking to solve.

Thanks Vik for showing the oversight

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jun 20, 2016 at 3:13 AM, Andreas Kretschmer
<andreas@a-kretschmer.de> wrote:

Am 20.06.2016 um 11:43 schrieb Job:

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free"
location?

exactly, that's the task for vacuum

Are you sure that that is the case with pg_bulkload specifically? It
bypasses the shared buffers, so it would not surprise me if it
bypasses the free space map as well, and thus always appends its data
to the end of the table.

Cheers,

Jeff

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer <andreas@a-kretschmer.de

wrote:

Am 20.06.2016 um 11:43 schrieb Job:

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not

more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free"
location?

exactly, that's the task for vacuum

I believe that free space is only available to UPDATE, not INSERT.

Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com&gt;*
http://openscg.com

On Mon, Jun 20, 2016 at 11:03 AM, Scott Mead <scottm@openscg.com> wrote:

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer <
andreas@a-kretschmer.de> wrote:

Am 20.06.2016 um 11:43 schrieb Job:

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not

more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free"
location?

exactly, that's the task for vacuum

I believe that free space is only available to UPDATE, not INSERT.

Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com&gt;*
http://openscg.com

Martin and Vik,

...Think about a SELECT which has to scan all child tables.

You are really digging for a corner case.
If a scan has to scan all child tables, then
A. it negates the ability to make partitions which are not used
and
B. The SELECT query is poorly crafted.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

2016-06-20 17:03 GMT+02:00 Scott Mead <scottm@openscg.com>:

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer <
andreas@a-kretschmer.de> wrote:

Am 20.06.2016 um 11:43 schrieb Job:

Hi Andreas,

I would suggest run only autovacuum, and with time you will see a not

more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free"
location?

exactly, that's the task for vacuum

I believe that free space is only available to UPDATE, not INSERT.

No, it's available for both.

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

El 20/06/16 a las 12:06, Melvin Davidson escribió:

Martin and Vik,

...Think about a SELECT which has to scan all child tables.

You are really digging for a corner case.
If a scan has to scan all child tables, then
A. it negates the ability to make partitions which are not used
and
B. The SELECT query is poorly crafted.

And you haven't read Vik's reply. :)

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general