how to use alter table…drop partition to delete partitions and data - PolarDB

Drops a specified partition and all its data from a partitioned table. This action is irreversible. Before you run this command, ensure the target partition is no longer needed or has been backed up.

Syntax

ALTER TABLE [schema.]table_name DROP PARTITION partition_name;

Parameters

Parameter	Required	Description	Example
`table_name`	Yes	The name of the target partitioned table from which to drop a partition.	`sales_records`
`partition_name`	Yes	The name of the partition to drop.	`p_2023_q1`

Notes

You must be the table owner or a privileged user to run this command.
You cannot drop a table's last partition, because a partitioned table must have at least one.
The DROP PARTITION statement acquires a table-level exclusive lock AccessExclusiveLock. This lock blocks all Data Manipulation Language (DML) and most Data Definition Language (DDL) operations on the table. Run this statement during off-peak hours and allow sufficient time for it to complete to avoid blocking other critical operations on the table.

Examples

Preparations

-- Create a table partitioned by sales date range.
CREATE TABLE sales_records (
    sale_id    INT NOT NULL,
    product_id INT NOT NULL,
    sale_date  DATE NOT NULL
)
PARTITION BY RANGE (sale_date) (
    PARTITION P_2023_Q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION P_2023_Q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

-- Create a globally unique index.
CREATE UNIQUE INDEX idx_sale_id_global ON sales_records(sale_id) GLOBAL;

-- Insert data.
INSERT INTO sales_records VALUES (1, 101, TO_DATE('2023-01-15', 'YYYY-MM-DD')); -- q1
INSERT INTO sales_records VALUES (2, 102, TO_DATE('2023-04-20', 'YYYY-MM-DD')); -- q2

Perform pre-checks

Before dropping a partition, perform these checks to prevent data loss and service disruptions.

Verify that the partition exists.

-- Check the partition information.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- Confirm that p_2023_q1 is in the list.

(Optional) Create a data backup.
To prevent accidental deletion, back up the table or the entire database before dropping the partition. See Backup methods.
Assess the operational impact.
Check for long-running transactions or critical workloads currently accessing the table. The drop operation acquires a table-level exclusive lock, which blocks all read and write requests.
```
-- Check for active locks.
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'SALES_RECORDS'::regclass;
```

Run the command

After completing the pre-checks, run the ALTER TABLE ... DROP PARTITION command during off-peak hours.

ALTER TABLE sales_records DROP PARTITION P_2023_Q1;

Verify the result

After the operation is complete, verify that the partition and its data have been dropped as expected.

Check that the partition was removed

Query the metadata view to confirm that the dropped partition is no longer in the partition list.

SELECT partition_name
FROM ALL_TAB_PARTITIONS
WHERE table_name = 'SALES_RECORDS' and partition_name = 'P_2023_Q1';
-- Expected result: The command returns an empty result set.

Confirm that the data was deleted.

Query the data from the dropped partition to confirm it has been deleted.

SELECT * 
FROM sales_records 
WHERE sale_date < '2023-04-01';
-- Expected result: The command returns an empty result set.

Update table statistics.
After dropping a partition, immediately update the table statistics to ensure that the query optimizer can generate accurate execution plans.
```
ANALYZE SALES_RECORDS;
```

FAQ

Q1: Why do I receive a partition "..." of relation "..." does not exist error after running the command?

The partition name does not exist or has a casing mismatch. Partition names are case-sensitive. Query the USER_TAB_PARTITIONS view to confirm the exact partition name, or use double quotation marks ("") for an exact match.

Q2: Why do I receive a permission denied for table ... or must be owner of table ... error?

The current user lacks sufficient permissions. Switch to the table owner or a privileged user to run the command.

Q3: Why do I receive a cannot drop partition due to dependent objects error?

Other database objects, such as foreign key constraints or views, depend on this partition. The default RESTRICT behavior prevents the deletion from being dropped. You must first identify and remove these dependent objects.

Q4: Why is the operation taking a long time or appearing to be stuck?

The DROP PARTITION statement acquires an AccessExclusiveLock. If this lock is blocked by another active, long-running transaction, the operation must wait, which can cause delays or timeouts. You can identify the blocking session by checking for active locks. Then, use the (blocking_pid from that query to find more details about the session with the following statement:

-- Query user information of the blocking thread based on the PID.
SELECT 
    pid,
    usename AS username,           -- Database username
    application_name,              -- Application name (such as JDBC, psql)
    client_addr,                   -- Client IP address
    client_hostname,               -- Client hostname
    client_port,                   -- Client port
    backend_start,                 -- Time when the connection started
    xact_start,                    -- Time when the transaction started
    query_start,                   -- Time when the current query started
    state_change,                  -- Time when the status last changed
    state,                         -- Status (active, idle, idle in transaction)
    wait_event_type,               -- Wait event type (such as Lock, IO)
    wait_event,                    -- Specific wait event
    query                          -- Currently running or most recently run SQL statement
FROM 
    pg_stat_activity
WHERE 
    pid in 'blocking_pid';

Related SQLs

ALTER TABLE ADD PARTITION: Adds a new partition to a partitioned table.
ALTER TABLE TRUNCATE PARTITION: Clears all data from a partition while keeping the partition definition.
ALTER TABLE SPLIT PARTITION: Splits one partition into two partitions.