This is because of PostgreSQL's Multi-Version Concurrency Control (MVCC) mechanism:
DELETEwill not physically delete rows.
UPDATEis implemented by inserting new rows, and expired data will not be physically deleted. Therefore, the used storage capacity can increase even if there is no data inserted.
autovacuum parameter is enabled by default for TencentDB, and expired data will be automatically repossessed by the kernel, so after the repossession, the used storage capacity will be released automatically. You can also run the
VACUUM command to repossess expired data (in this case, the value of used storage capacity will not decrease immediately; instead, the expired data will be repossessed, and the used storage capacity will be marked reusable). To clear the data completely, you may use the
VACUUM FULL command with parameters (this command will lock tables, so you are strongly recommended to only use it during maintenance).
For more information on the
VACUUM command, please see PostgreSQL's official documentation.
By default, PostgreSQL adopts the policy of overuse of idle resources that allows your business to preempt some idle CPU resources. Therefore, when the number of CPU cores of your instance exceeds the default value, your CPU utilization may appear to exceed 100% in the monitoring view, which is normal.
However, if your CPU load always exceeds 60%, you are recommended to upgrade your database as soon as possible.
An update can lead to a sharp increase of xlog files, which cannot be archived and deleted in time and hence take up disk capacity. Or, query operations contain sort by and join operations involving massive amounts of data, and during the process, temporary files are generated and overflow to the disk, resulting in a surge in used capacity in a short period of time.
TencentDB for PostgreSQL supports most common extensions for direct use. However, some extensions may require superuser privileges. You can enable them in the Tencent Cloud Console or contact us with the instance ID and extension name.