tencent cloud

TencentDB for PostgreSQL

Release Notes and Announcements
Release Notes
Product Announcements
Product Introduction
Overview
Features
Strengths
Scenarios
Information Security
Regions and AZs
Product Feature List
Large version lifecycle description
MSSQL Compatible Version
Billing
Billing Overview
Instance Type and Specification
Purchase Methods
Refund
Overdue Payments
Backup Space Billing
Database Audit Billing Overview
Getting Started
Creating TencentDB for PostgreSQL Instance
Connecting to TencentDB for PostgreSQL Instance
Managing TencentDB for PostgreSQL Instance
Importing Data
Migrating Data with DTS
Kernel Version Introduction
Kernel Version Overview
Kernel Version Release Notes
Viewing Kernel Version
Proprietary Kernel Features
Database Audit
Audit Service Description
Activating Audit Service
View Audit Logs
Modify audit services
Audit Performance Description
User Guide
Instance Management
Upgrading Instance
CPU Elastic Scaling
Read-Only Instance
Account Management
Database Management
Parameter Management
Log Management and Analysis
Backup and Restoration
Data Migration
Extension Management
Network Management
Access Management
Data Security
Tenant and Resource Isolation
Security Groups
Monitoring and Alarms
Tag
AI Practice
Using the Tencentdb_ai Plug-In to Call Large Models
Building Ai Applications with the Tencentdb Ai Plug-In
Combining Supabase to Quickly Build Backend Service Based on TencentDB for PostgreSQL
Use Cases
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
Implementing Slow SQL Analysis Using the Auto_explain Plugin
Using pglogical for Logical Replication
Using Debezium to Collect PostgreSQL Data
Set Up a Remote Disaster Recovery Environment for PostgreSQL Locally on CVM
Read-Only Instance and Read-Only Group Practical Tutorial
How to Use SCF for Scheduled Database Operations
Fix Table Bloat
Performance White Paper
Test Methods
Test Results
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Read-only Replica APIs
Backup and Recovery APIs
Parameter Management APIs
Security Group APIs
Performance Optimization APIs
Account APIs
Specification APIs
Network APIs
Data Types
Error Codes
FAQs
Service Agreement
Service Level Agreement
Terms of Service
Glossary
Contact Us
DocumentationTencentDB for PostgreSQLKernel Version IntroductionProprietary Kernel FeaturesIndex Invalidation Feature

Index Invalidation Feature

PDF
Focus Mode
Font Size
Last updated: 2026-03-23 16:16:59
TencentDB for PostgreSQL supports the index invalidation kernel feature. This document provides its description and usage examples on Tencent Cloud.

Feature Background

Database administrators require a rapid index control mechanism during release or non-routine Ops scenarios. This approach enables deactivation of specified indexes without deletion, allowing flexible assessment of index impact on query performance during upgrade validation, gray release testing, or emergency troubleshooting. It simultaneously avoids write overhead from index maintenance or triggering known bugs, ensuring system stability and data integrity throughout the debugging process while enhancing Ops efficiency.

Feature Overview

TencentDB for PostgreSQL supports the index invalidation kernel feature. This feature allows users to temporarily disable or enable indexes via standard SQL commands without requiring system table privileges. It supports transaction rollback, independent control of partitioned tables, maintains index data during deactivation, and requires no rebuilding after re-enabling.

Core Syntax

-- Disable the index (takes effect immediately, making the index invisible to the optimizer)
ALTER INDEX idx_name UNUSABLE;

-- Enable the index (without rebuilding, the index becomes visible to the optimizer again)
ALTER INDEX idx_name USABLE;

Applicable Scenarios

Scenarios where temporarily disabling or enabling indexes is required to enhance Ops efficiency.

Must-Knows

Partitioned Table Processing: The USABLE/UNUSABLE index status does not support recursive operations. For partitioned tables, subpartitions must be processed individually.
Version Upgrade: Indexes marked as UNUSABLE will be deleted during major version upgrades.
REINDEX Behavior: After a regular table is set to UNUSABLE, REINDEX will make it USABLE directly. However, the parent table of a partitioned table will remain UNUSABLE.
Integration with pg_hint_plan: Can be integrated with pg_hint_plan to specify index usage policies.

Supported Versions

Database kernel version: v11.22_r1.34, v12.22_r1.36, v13.22_r1.31, v14.19_r1.40, v15.14_r1.25, v16.10_r1.20, v17.6_r1.14, v18.1_r1.4 and later versions.

Usage Examples

Example 1: Basic Usage - Disabling and Enabling Indexes

Step 1: Create a test table.

-- Input
CREATE TABLE test_table (id int, val text);
-- Output
CREATE TABLE;

Step 2: Insert test data.

-- Input
INSERT INTO test_table SELECT i, 'val' || i FROM generate_series(1, 1000) i;
-- Output
INSERT 0 1000;

Step 3: Create an index.

-- Input
CREATE INDEX idx_test_id ON test_table (id);
-- Output
CREATE INDEX;

Step 4: Update the statistical information

-- Input
ANALYZE test_table;
-- Output
ANALYZE;

Step 5: Check the initial state of the index (indisvalid = true indicates that the index is valid)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid = 'idx_test_id'::regclass;
-- Output
 index_name  | indisvalid 
-------------+------------
 idx_test_id | t
(1 row);

Step 6: View the current query plan (using Index Scan)

-- Input
EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 100;
-- Output
              QUERY PLAN               
---------------------------------------
 Index Scan using idx_test_id on test_table
   Index Cond: (id = 100)
(2 rows);

Step 7: Disable the index

-- Input
ALTER INDEX idx_test_id UNUSABLE;
-- Output
ALTER INDEX;

Step 8: Verify that the index has been disabled (indisvalid = false, indicating that the index is invalid)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid = 'idx_test_id'::regclass;
-- Output
 index_name  | indisvalid 
-------------+------------
 idx_test_id | f
(1 row);

Step 9: View the query plan (changed to Seq Scan, no longer using index)

-- Input
EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 100;
-- Output
          QUERY PLAN           
-------------------------------
 Seq Scan on test_table
   Filter: (id = 100)
(2 rows);

Step 10: Re-enable the index

-- Input
ALTER INDEX idx_test_id USABLE;
-- Output
ALTER INDEX;

Step 11: Verify that the index is enabled (indisvalid = true)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid = 'idx_test_id'::regclass;
-- Output
 index_name  | indisvalid 
-------------+------------
 idx_test_id | t
(1 row);

Step 12: View the query plan (resume using Index Scan)

-- Input
EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 100;
-- Output
              QUERY PLAN               
---------------------------------------
 Index Scan using idx_test_id on test_table
   Index Cond: (id = 100)
(2 rows);

Example 2: Partitioned table index operation

Step 1: Create a partitioned table

-- Input
CREATE TABLE test_part (id int, val text) PARTITION BY RANGE (id);
-- Output
CREATE TABLE;

Step 2: Create partition

-- Input
CREATE TABLE test_part_p1 PARTITION OF test_part FOR VALUES FROM (1) TO (500);
CREATE TABLE test_part_p2 PARTITION OF test_part FOR VALUES FROM (500) TO (1001);
-- Output
CREATE TABLE
CREATE TABLE;

Step 3: Insert test data

-- Input
INSERT INTO test_part SELECT i, 'val' || i FROM generate_series(1, 1000) i;
-- Output
INSERT 0 1000;

Step 4: Create partitioned indexes (subindexes will be automatically created in each partition)

-- Input
CREATE INDEX idx_test_part_id ON test_part (id);
-- Output
CREATE INDEX;

Step 5: Update statistical information

-- Input
ANALYZE test_part;
-- Output
ANALYZE;

Step 6: Check the status of all indexes (both parent and child indexes are valid)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid::regclass::text LIKE '%test_part%';
-- Output
       index_name        | indisvalid 
-------------------------+------------
 idx_test_part_id        | t
 test_part_p1_id_idx     | t
 test_part_p2_id_idx     | t
(3 rows);

Step 7: Disable the parent index (note: child indexes remain unaffected)

-- Input
ALTER INDEX idx_test_part_id UNUSABLE;
-- Output
ALTER INDEX;

Step 8: Check the index status (parent index disabled, child index remains active)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid::regclass::text LIKE '%test_part%';
-- Output
       index_name        | indisvalid 
-------------------------+------------
 idx_test_part_id        | f
 test_part_p1_id_idx     | t
 test_part_p2_id_idx     | t
(3 rows);

Step 9: Disable the index for partition p1 separately

-- Input
ALTER INDEX test_part_p1_id_idx UNUSABLE;
-- Output
ALTER INDEX;

Step 10: View index status (p1 index disabled, p2 index still valid)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid::regclass::text LIKE '%test_part%';
-- Output
       index_name        | indisvalid 
-------------------------+------------
 idx_test_part_id        | f
 test_part_p1_id_idx     | f
 test_part_p2_id_idx     | t
(3 rows);

Step 11: Query the execution plan for partition p1 data (using Seq Scan)

-- Input
EXPLAIN (COSTS OFF) SELECT * FROM test_part WHERE id = 100;
-- Output
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on test_part_p1 test_part
   Filter: (id = 100)
(2 rows);

Step 12: Query the execution plan for partition p2 data (using Index Scan)

-- Input
EXPLAIN (COSTS OFF) SELECT * FROM test_part WHERE id = 900;
-- Output
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using test_part_p2_id_idx on test_part_p2 test_part
   Index Cond: (id = 900)
(2 rows);

example 3: transaction rollback support

Step 1: Ensure the index is in an enabled state

-- Input
ALTER INDEX idx_test_id USABLE;
-- Output
ALTER INDEX;

Step 2: Check the current status

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid = 'idx_test_id'::regclass;
-- Output
 index_name  | indisvalid 
-------------+------------
 idx_test_id | t
(1 row);

Step 3: Begin transaction

-- Input
BEGIN;
-- Output
BEGIN;

Step 4: Disable the index during a transaction

-- Input
ALTER INDEX idx_test_id UNUSABLE;
-- Output
ALTER INDEX;

Step 5: View the status in the transaction (index disabled)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid = 'idx_test_id'::regclass;
-- Output
 index_name  | indisvalid 
-------------+------------
 idx_test_id | f
(1 row);

Step 6: Rollback the transaction

-- Input
ROLLBACK;
-- Output
ROLLBACK;

Step 7: Check the rollback status (the index reverts to enabled)

-- Input
SELECT indexrelid::regclass AS index_name, indisvalid 
FROM pg_index 
WHERE indexrelid = 'idx_test_id'::regclass;
-- Output
 index_name  | indisvalid 
-------------+------------
 idx_test_id | t
(1 row);

Example 4: Index maintenance during DML operations

Step 1: Disable the index

-- Input
ALTER INDEX idx_test_id UNUSABLE;
-- Output
ALTER INDEX;

Step 2: Insert new data while the index is disabled

-- Input
INSERT INTO test_table VALUES (9999, 'new_value');
-- Output
INSERT 0 1;

Step 3: Re-enable the index

-- Input
ALTER INDEX idx_test_id USABLE;
-- Output
ALTER INDEX;

Step 4: Verify that new data can be queried via the index

-- Input
EXPLAIN (COSTS OFF) SELECT * FROM test_table WHERE id = 9999;
-- Output
              QUERY PLAN               
---------------------------------------
 Index Scan using idx_test_id on test_table
   Index Cond: (id = 9999)
(2 rows);

Step 5: Execute a query to confirm the existence of the data

-- Input
SELECT * FROM test_table WHERE id = 9999;
-- Output
  id  |    val    
------+-----------
 9999 | new_value
(1 row);
Note:
When the index is disabled, data inserted during this period will continue to maintain the index schema. After the index is enabled, it can be used normally without requiring REINDEX.
Previous Topic: Enable Automatic Cache Eviction Mechanism for syscache/relcacheNext Topic: Audit Service Description

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback