vacuum vs vacuum full

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:

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

<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
<#m_-5414522580965465877_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On 11/18/20 3:41 AM, Olivier Gautherot wrote:

Hi Atul,

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

[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.

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, Nov 18, 2020 at 10:45 AM Ron <ronljohnsonjr@gmail.com> wrote:

On 11/18/20 3:41 AM, Olivier Gautherot wrote:

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:

[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.

<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

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 such nice administrative stuff, often leading to unnecessarily long downtimes.

Cheers,
Paul

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.

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 improved in the last few years but we will not change back again. :-) Why should we?

Cheers,
Paul

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

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.