Thread: 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:
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
On 11/18/20 3:41 AM, Olivier Gautherot wrote:
No matter how long it takes, this is an excellent argument for partitioning Very Large Tables: many maintenance tasks are made much easier.
[snip]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.
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.
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:[snip]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.
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.
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
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;
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 improvedin 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.