Обсуждение: vacuum vs vacuum full

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

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.

Hi Atul,

On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar <akumar14871@gmail.com> 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:

 

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.

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

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

Hi Laurenz,

> On 18. Nov, 2020, at 13:02, Laurenz Albe <laurenz.albe@cybertec.at> 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
suchnice administrative stuff, often leading to unnecessarily long downtimes. 

Cheers,
Paul

Hi Ravi,

> On 18. Nov, 2020, at 15:30, Ravi Krishna <srkrishna@yahoo.com> 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
improvedin the last few years but we will not change back again. :-) Why should we? 

Cheers,
Paul

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.