- Release Notes and Announcements
- Release Notes
- Open-Source Plugin Privilege Escalation Has Vulnerability Risk for PostgreSQL
- Announcements
- Security Risk Statement for Creating Partial Plugins in PostgreSQL Cloud Database
- TencentDB for PostgreSQL Will Start Billing for Backup Space
- Some Extensions on Lower Kernel Version Can't Be Created and Upgraded in TencentDB for PostgreSQL
- Announcement on the Risk of Privilege Escalation Vulnerability in Cloud Database PostgreSQL Open Source Plugin
- Product Introduction
- Purchase Guide
- Getting Started
- Kernel Version Introduction
- Operation Guide
- Instance Management
- Instance Lifecycle
- Setting Instance Maintenance Time
- Modifying Instance Configuration
- Setting instance availability zone for disaster recovery or region disaster recovery
- Modifying AZs
- Terminating Instances
- Restoring Instances
- Eliminating Instances
- Restarting Instances
- Modifying Data Replication Mode
- Switch Source-Replica Instance
- Upgrading Instance
- Read-Only Instance
- Account Management
- Database Management
- Database Optimization
- Parameter Management
- Log management
- Backup and Restoration
- Backup Principles and Solutions
- Backing up Data
- Downloading Backup
- Cloning Instance
- Automatic Backup Settings
- Restoring PostgreSQL Data on CVMs
- Deleting Backup
- Viewing Backup Space
- Setting Backup Download Rules
- Use Serverless Cloud Function to Transfer Historical Backups of PostgreSQL
- Rollback to the Original Instance
- Physical Migration
- Database Audit
- Extension Management
- Network Management
- Access Management
- Data Encryption
- Tenant and Resource Isolation
- Security Groups
- Monitoring and Alarms
- Tag
- Instance Management
- PostgreSQL for Serverless
- MSSQL Compatible Version
- Practical Tutorial
- postgres_fdw Extension for Cross-database Access
- Automatically Creating Partition in PostgreSQL
- Searching in High Numbers of Tags Based on pg_roaringbitmap
- Querying People Nearby with One SQL Statement
- Configuring TencentDB for PostgreSQL as GitLab's External Data Source
- Supporting Tiered Storage Based on cos_fdw Extension
- Implement Read/Write Separation via pgpool
- Analyzing Slow SQL Using the auto_explain Plugin
- Using pglogical for Logical Replication
- Performance White Paper
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Instance APIs
- CreateInstances
- DescribeDBInstances
- DescribeDBInstanceAttribute
- DescribeEncryptionKeys
- DescribeDBInstanceHAConfig
- SwitchDBInstancePrimary
- ModifyDBInstanceName
- ModifyDBInstanceSpec
- ModifyDBInstanceDeployment
- UpgradeDBInstanceKernelVersion
- ModifyDBInstanceHAConfig
- ModifyDBInstanceChargeType
- ModifyDBInstancesProject
- ModifySwitchTimePeriod
- RenewInstance
- SetAutoRenewFlag
- RestartDBInstance
- IsolateDBInstances
- DisIsolateDBInstances
- DestroyDBInstance
- CreateDBInstances
- InitDBInstances
- UpgradeDBInstance
- UpgradeDBInstanceMajorVersion
- Read-only Replica APIs
- Backup and Recovery APIs
- DescribeAvailableRecoveryTime
- DescribeCloneDBInstanceSpec
- CloneDBInstance
- RestoreDBInstanceObjects
- DescribeBackupOverview
- DescribeBackupSummaries
- DescribeBackupPlans
- ModifyBackupPlan
- CreateBaseBackup
- DescribeBaseBackups
- ModifyBaseBackupExpireTime
- DeleteBaseBackup
- DescribeLogBackups
- DeleteLogBackup
- DescribeBackupDownloadURL
- DescribeBackupDownloadRestriction
- ModifyBackupDownloadRestriction
- DescribeDBXlogs
- DescribeDBBackups
- Parameter Management APIs
- Security Group APIs
- Performance Optimization APIs
- Account APIs
- Specification APIs
- PostgreSQL for Serverless APIs
- Network APIs
- Data Types
- Error Codes
- FAQs
- Service Agreement
- Glossary
- Contact Us
- Release Notes and Announcements
- Release Notes
- Open-Source Plugin Privilege Escalation Has Vulnerability Risk for PostgreSQL
- Announcements
- Security Risk Statement for Creating Partial Plugins in PostgreSQL Cloud Database
- TencentDB for PostgreSQL Will Start Billing for Backup Space
- Some Extensions on Lower Kernel Version Can't Be Created and Upgraded in TencentDB for PostgreSQL
- Announcement on the Risk of Privilege Escalation Vulnerability in Cloud Database PostgreSQL Open Source Plugin
- Product Introduction
- Purchase Guide
- Getting Started
- Kernel Version Introduction
- Operation Guide
- Instance Management
- Instance Lifecycle
- Setting Instance Maintenance Time
- Modifying Instance Configuration
- Setting instance availability zone for disaster recovery or region disaster recovery
- Modifying AZs
- Terminating Instances
- Restoring Instances
- Eliminating Instances
- Restarting Instances
- Modifying Data Replication Mode
- Switch Source-Replica Instance
- Upgrading Instance
- Read-Only Instance
- Account Management
- Database Management
- Database Optimization
- Parameter Management
- Log management
- Backup and Restoration
- Backup Principles and Solutions
- Backing up Data
- Downloading Backup
- Cloning Instance
- Automatic Backup Settings
- Restoring PostgreSQL Data on CVMs
- Deleting Backup
- Viewing Backup Space
- Setting Backup Download Rules
- Use Serverless Cloud Function to Transfer Historical Backups of PostgreSQL
- Rollback to the Original Instance
- Physical Migration
- Database Audit
- Extension Management
- Network Management
- Access Management
- Data Encryption
- Tenant and Resource Isolation
- Security Groups
- Monitoring and Alarms
- Tag
- Instance Management
- PostgreSQL for Serverless
- MSSQL Compatible Version
- Practical Tutorial
- postgres_fdw Extension for Cross-database Access
- Automatically Creating Partition in PostgreSQL
- Searching in High Numbers of Tags Based on pg_roaringbitmap
- Querying People Nearby with One SQL Statement
- Configuring TencentDB for PostgreSQL as GitLab's External Data Source
- Supporting Tiered Storage Based on cos_fdw Extension
- Implement Read/Write Separation via pgpool
- Analyzing Slow SQL Using the auto_explain Plugin
- Using pglogical for Logical Replication
- Performance White Paper
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Instance APIs
- CreateInstances
- DescribeDBInstances
- DescribeDBInstanceAttribute
- DescribeEncryptionKeys
- DescribeDBInstanceHAConfig
- SwitchDBInstancePrimary
- ModifyDBInstanceName
- ModifyDBInstanceSpec
- ModifyDBInstanceDeployment
- UpgradeDBInstanceKernelVersion
- ModifyDBInstanceHAConfig
- ModifyDBInstanceChargeType
- ModifyDBInstancesProject
- ModifySwitchTimePeriod
- RenewInstance
- SetAutoRenewFlag
- RestartDBInstance
- IsolateDBInstances
- DisIsolateDBInstances
- DestroyDBInstance
- CreateDBInstances
- InitDBInstances
- UpgradeDBInstance
- UpgradeDBInstanceMajorVersion
- Read-only Replica APIs
- Backup and Recovery APIs
- DescribeAvailableRecoveryTime
- DescribeCloneDBInstanceSpec
- CloneDBInstance
- RestoreDBInstanceObjects
- DescribeBackupOverview
- DescribeBackupSummaries
- DescribeBackupPlans
- ModifyBackupPlan
- CreateBaseBackup
- DescribeBaseBackups
- ModifyBaseBackupExpireTime
- DeleteBaseBackup
- DescribeLogBackups
- DeleteLogBackup
- DescribeBackupDownloadURL
- DescribeBackupDownloadRestriction
- ModifyBackupDownloadRestriction
- DescribeDBXlogs
- DescribeDBBackups
- Parameter Management APIs
- Security Group APIs
- Performance Optimization APIs
- Account APIs
- Specification APIs
- PostgreSQL for Serverless APIs
- Network APIs
- Data Types
- Error Codes
- FAQs
- Service Agreement
- Glossary
- Contact Us
Background
The primary-standby replication logic of PostgreSQL will severely slow down data recovery from the standby database if there are many DDL statements in tables. To avoid this, TencentDB for PostgreSQL is specially optimized and modified.
How It Works
PostgreSQL uses physical replication to implement primary-standby replication. After a log is synced to the standby node, the standby node will parse the WAL log to make the data consistent with that on the primary node. The standby node will perform the following operations when recovering a DROP statement:
1. Recover a system table, such as
pg_class, pg_attrbute, and pg_type, which is equivalent to removing the table metadata.2. Close the table file.
3. Traverse pages in the buffer and mark the found pages of the table as invalid, so subsequent processes can use them.
4. Send an async invalidation message to other backends to notify that the table has been deleted.
5. Delete the physical file of the table.
However, in the PostgreSQL kernel, the
DropRelFileNodesAllBuffers function will be executed in step 3. It needs to traverse the entire shard_buffer to check whether the buffer has the data of the table to be deleted and mark corresponding pages as invalid. The default size of a page in PostgreSQL is 8 KB. If the size of shard_buffer is 16 GB for example, there will be 16 GB / 8 KB = 2 million pages. Therefore, every time a table is deleted, 2 million pages need to be checked one by one. If the table has indexes, 2 million pages will be checked one by one in each index.From the perspective of business, when there are a high number of table changes and quick table deletions, as such operations can be executed concurrently on the primary database, the compromised performance is less obvious. However, as the PostgreSQL standby database recovers a table in a single thread, the primary-standby sync logs will be heaped, and data transfer will be delayed.
Solution
When TencentDB for PostgreSQL recovers a dropped table, it writes the table information into a shared hash table. If a table file is deleted, it won't be physically deleted directly; instead, TencentDB for PostgreSQL will store the actual file deletion operation as an async action in the hash table.
After invalid buffer processing is completed, the table will be removed from the hash table. In this way, if a file fails to be opened during the process, it will be sufficient to check whether the file exists in the hash table. In addition, if the queue is traversed when a file is created and a file with the same name in the hash table is undergoing invalid buffer processing, just wait.
PostgreSQL stores each table filename as a UInt32 integer and adopts the principle of "global assignment and local storage", where all databases in an instance use the same counter to generate file numbers, and generated files are stored in the databases' respective directories. When the system assigns a filename, if the current database has a file with the same name, the system will try the next name until there is no conflict. The counter will reset after it reaches the maximum value.
After optimization, the primary-standby sync performance is improved by over 30,000 times in similar scenarios.
Yes
No
Was this page helpful?