vacuum vs vacuum full

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

vacuum vs vacuum full

Atul Kumar-2
Hi,

We have a table of 3113GB, and we are planning to vacuum it in non
business hours i.e. 12AM to 4AM, So my queries are:

1. What should be perform on the table Vacuum or Vacuum full ?
2. Do we need to perform Analyze also?
3. Will the operation be completed in the given time frame? how to
check the same.
4. Who acquire lock on table vacuum or vacuum full.
5. If the activity goes beyond time frame, do we have any option to do
continue doing t without acquiring lock on the table ?

If you also need the structure of the table, Please let me know.

Please help me by responding my query wise.



Regards,
Atul


Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Ron-2
On 11/18/20 2:33 AM, Atul Kumar wrote:
> Hi,
>
> We have a table of 3113GB, and we are planning to vacuum it in non
> business hours i.e. 12AM to 4AM, So my queries are:
>
> 1. What should be perform on the table Vacuum or Vacuum full ?

The documentation *clearly states* the difference between VACUUM and VACUUM
FULL.

https://www.postgresql.org/docs/9.6/sql-vacuum.html

> 2. Do we need to perform Analyze also?

I always do.

> 3. Will the operation be completed in the given time frame? how to
> check the same.

How in the heck do we know your system's hardware configuration?

> 4. Who acquire lock on table vacuum or vacuum full.

Read the docs.

> 5. If the activity goes beyond time frame, do we have any option to do
> continue doing t without acquiring lock on the table ?
>
> If you also need the structure of the table, Please let me know.
>
> Please help me by responding my query wise.


--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Olivier Gautherot-2
In reply to this post by Atul Kumar-2
Hi Atul,

On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar <[hidden email]> wrote:
Hi,

We have a table of 3113GB, and we are planning to vacuum it in non
business hours i.e. 12AM to 4AM, So my queries are:

1. What should be perform on the table Vacuum or Vacuum full ?

Vacuum full will do a complete rewrite of the table so you need to make sure that you have the necessary space. I would recommend a simple VACUUM, although it won't return the extra space to the OS.
 
2. Do we need to perform Analyze also?

It would be a good thing.
 
3. Will the operation be completed in the given time frame? how to
check the same.

Given the size of the table, it will probably take several days.
 
4. Who acquire lock on table vacuum or vacuum full.

VACUUM FULL acquires a lock on the table. VACUUM doesn't.
 
5. If the activity goes beyond time frame, do we have any option to do
continue doing t without acquiring lock on the table ?

VACUUM is a background activity. It does not block any other activity.
 

If you also need the structure of the table, Please let me know.

It would be interesting to know the number of rows updated per hour or per day to have an estimation of the needs.
 
Please help me by responding my query wise.

Regards,
Atul

Cheers
Olivier
 

Libre de virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Ron-2
On 11/18/20 3:41 AM, Olivier Gautherot wrote:
Hi Atul,

On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar <[hidden email]> wrote:
Hi,

We have a table of 3113GB, and we are planning to vacuum it in non
business hours i.e. 12AM to 4AM, So my queries are:
[snip]
 
3. Will the operation be completed in the given time frame? how to
check the same.

Given the size of the table, it will probably take several days.

No matter how long it takes, this is an excellent argument for partitioning Very Large Tables: many maintenance tasks are made much easier.



--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Thomas Kellerer-4
Ron schrieb am 18.11.2020 um 10:44:
> No matter how long it takes, this is an excellent argument for
> partitioning Very Large Tables: many maintenance tasks are made
> *much* easier.

The problem is, you can't partition every table as long as Postgres
does not support a primary key that is independent of the partitioning key
(i.e. until it has "global indexes" as they are called in Oracle)

Thomas


Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Olivier Gautherot-2
In reply to this post by Ron-2

On Wed, Nov 18, 2020 at 10:45 AM Ron <[hidden email]> wrote:
On 11/18/20 3:41 AM, Olivier Gautherot wrote:
Hi Atul,

On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar <[hidden email]> wrote:
Hi,

We have a table of 3113GB, and we are planning to vacuum it in non
business hours i.e. 12AM to 4AM, So my queries are:
[snip]
 
3. Will the operation be completed in the given time frame? how to
check the same.

Given the size of the table, it will probably take several days.

No matter how long it takes, this is an excellent argument for partitioning Very Large Tables: many maintenance tasks are made much easier.

I can only agree with this comment. The main issue I see is the available disk space, as the partitioning process will include copying the whole table. 

Libre de virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Laurenz Albe
In reply to this post by Thomas Kellerer-4
On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote:
> > No matter how long it takes, this is an excellent argument for
> > partitioning Very Large Tables: many maintenance tasks are made
> > *much* easier.
>
> The problem is, you can't partition every table as long as Postgres
> does not support a primary key that is independent of the partitioning key
> (i.e. until it has "global indexes" as they are called in Oracle)

I personally hope that we will never have global indexes.
I am not looking forward to helping customers with the problems that
they create (long duration of ATTACH/DETACH PARTITION, index fragmentation).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Paul Förster
Hi Laurenz,

> On 18. Nov, 2020, at 13:02, Laurenz Albe <[hidden email]> wrote:
>
> I personally hope that we will never have global indexes.
> I am not looking forward to helping customers with the problems that
> they create (long duration of ATTACH/DETACH PARTITION, index fragmentation).

+1.

Experience shows that global index in Oracle lead to problems when dropping a partition. rebuilding an index, or other such nice administrative stuff, often leading to unnecessarily long downtimes.

Cheers,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Ravi__Krishna

Experience shows that global index in Oracle lead to problems when dropping a partition. rebuilding an index, or other such nice administrative stuff, often leading to unnecessarily long downtimes.



I think Oracle fixed it later by allowing asynchronous update of global index after the detachment of partition.

ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME UPDATE INDEXES;

will immediately start maintenance of global index by cleaning it up asynchronously, while the index is marked valid and can be used by the applications.

DB2 also has the same feature for a long time and it works fine.

I am sure there are genuine use cases of global indexes.

Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Paul Förster
Hi Ravi,

> On 18. Nov, 2020, at 15:30, Ravi Krishna <[hidden email]> wrote:
>
> ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME UPDATE INDEXES;

IIRC the statement is

alter table <table> drop partition <partition> update *GLOBAL* indexes;

But we experienced big problems in the past which is why we changed all to local indexes. The situation may have improved in the last few years but we will not change back again. :-) Why should we?

Cheers,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

David G Johnston
In reply to this post by Atul Kumar-2
On Wed, Nov 18, 2020 at 1:33 AM Atul Kumar <[hidden email]> wrote:
Hi,

We have a table of 3113GB, and we are planning to vacuum it in non

Just making sure that isn't a typo (repeated 1s)...

business hours i.e. 12AM to 4AM, So my queries are:

1. What should be perform on the table Vacuum or Vacuum full ?

You should be vacuuming that table constantly, so why is the particular vacuum special?  What are the "last vacuum" related statistics for this table?

There is "vacuum" and there is "rebuilding the whole table from scratch", the later of which is unfortunately named "vacuum full".

If you haven't started learning/thinking about it yet you should try and get an understanding around where your system is in the process of requiring an anti-wraparound vacuum.  Or, more generally, using "vacuum freeze".

David J.

Reply | Threaded
Open this post in threaded view
|

Re: vacuum vs vacuum full

Ron-2
In reply to this post by Laurenz Albe
On 11/18/20 6:02 AM, Laurenz Albe wrote:

> On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote:
>>> No matter how long it takes, this is an excellent argument for
>>> partitioning Very Large Tables: many maintenance tasks are made
>>> *much* easier.
>> The problem is, you can't partition every table as long as Postgres
>> does not support a primary key that is independent of the partitioning key
>> (i.e. until it has "global indexes" as they are called in Oracle)
> I personally hope that we will never have global indexes.
> I am not looking forward to helping customers with the problems that
> they create (long duration of ATTACH/DETACH PARTITION, index fragmentation).

I've been using what Oracle calls "global indexes" for 20 years. They're
super useful when -- for example -- you want to partition a transaction
table by a date field, while the PK is synthetic.

Up until about two years ago, I purged old data every six months. (Then it
was migrated from the legacy RDBMS to Oracle.)

Yes, you've got to drop and rebuild the indices, but that's a small price to
pay for the simplicity of archiving (especially when the indices are built
in parallel).

--
Angular momentum makes the world go 'round.