Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page
Help Center/ GaussDB(DWS)/ More Documents/ User Guide (Kuala Lumpur Region)/ FAQs/ Database Usage/ Do I Need to Run VACUUM FULL and ANALYZE on Common Tables Periodically?

Do I Need to Run VACUUM FULL and ANALYZE on Common Tables Periodically?

Updated on 2023-03-17 GMT+08:00

Yes.

For tables that are frequently added, deleted, or modified, you need to periodically perform VACUUM FULL and ANALYZE to reclaim the disk space occupied by updated or deleted data, preventing performance deterioration caused by data bloat and inaccurate statistics.

  • Generally, you are advised to perform ANALYZE after a large number of adding or modification operations are performed on a table.
  • After a table is deleted, you are advised to run VACUUM rather than VACUUM FULL. However, you can run VACUUM FULL in some particular cases, such as when you want to physically narrow a table to decrease the occupied disk space after deleting most rows of the table. For details about the differences between VACUUM and VACUUM FULL, see VACUUM and VACUUM FULL.

Syntax

Perform ANALYZE on a table.

ANALYZE table_name;

Perform ANALYZE on all tables (non-foreign tables) in the database.

ANALYZE;

Perform VACUUM on a table.

VACUUM table_name;

Perform VACUUM FULL on a table.

VACUUM FULL table_name;

For details, see in "VACUUM" and "ANALYZE | ANALYSE" in the Developer Guide.

NOTE:
  • If the physical space usage does not decrease after you run the VACUUM FULL command, check whether there were other active transactions (started before you delete data transactions and not ended before you run VACUUM FULL). If yes, run this command again when the transactions have finished.
  • In version 8.1.3 or later, VACUUM/VACUUM FULL can be invoked on the management plane. For details, see "Intelligent O&M" in the Data Warehouse Service User Guide.

VACUUM and VACUUM FULL

In GaussDB(DWS), the VACUUM operation is like a vacuum cleaner used to absorb dust. Here, "dust" means old data. If the data is not cleared in a timely manner, the database space will bloat, causing performance deterioration or even system breakdown.

Purposes of VACUUM:

  • Solve space bloat: Clear obsolete tuples and corresponding indexes, which include the tuple (and index) of a committed DELETE transaction, the old version (and index) of an UPDATE transaction, the inserted tuple (and index) of a rolled back INSERT transaction, the new version (and index) of an UPDATE transaction, and the tuple (and index) of a COPY transaction.
  • VACUUM FREEZE: Prevents system breakdown caused by transaction ID wraparound. It converts transaction IDs smaller than OldestXmin to freeze xids, update relfrozenxids in a table, and update relfrozenxids and truncate clogs in a database.
  • Update statistics: VACUUM ANALYZE updates statistics, enabling the optimizer to select a better way to execute SQL statements.

The VACUUM statement includes VACUUM and VACUUM FULL. Currently, VACUUM can only work on row-store tables. VACUUM FULL can be used to release space of column-store tables. For details, see the following table.

Table 1 VACUUM and VACUUM FULL

Item

VACUUM

VACUUM FULL

Clearing space

If the deleted record is at the end of a table, the space occupied by the deleted record is physically released and returned to the operating system. If the data is not at the end of a table, the space occupied by dead tuples in the table or index is set to be available for reuse.

Despite the position of the deleted data, the space occupied by the data is physically released and returned to the operating system. When data is inserted, a new disk page is allocated.

Lock type

Shared lock. The VACUUM operation can be performed in parallel with other operations.

Exclusive lock. All operations based on the table are suspended during execution.

Physical space

Not released

Released

Transaction ID

Not reclaimed

Reclaimed

Execution overhead

The overhead is low and the operation can be executed periodically.

The overhead is high. You are advised to perform it when the disk page space occupied by the database is close to the threshold and the data operations are few.

Effect

It improves the efficiency of operations on the table.

It greatly improves the efficiency of operations on the table.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback