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

Permanently removes a partition and all its data from a partitioned table. This operation is irreversible — dropped data cannot be recovered.

Warning

Back up the partition data before running this command. Once dropped, the data cannot be restored.

If you only need to clear data without removing the partition definition, use ALTER TABLE TRUNCATE PARTITION instead. TRUNCATE PARTITION removes all rows from the partition but keeps the partition structure intact, and it does not permanently remove the data in the same way as DROP PARTITION.

Syntax

ALTER TABLE [schema.]table_name DROP PARTITION partition_name;

Parameters

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

Usage notes

Only the table owner or a privileged user can run this command.
A partitioned table must have at least one partition. Dropping the last remaining partition is not allowed.
DROP PARTITION acquires a table-level AccessExclusiveLock, which blocks all Data Manipulation Language (DML) and most Data Definition Language (DDL) operations on the table. Run this command during off-peak hours.

Example

The following example creates a range-partitioned table, runs pre-checks, drops a partition, and verifies the result.

Set up the example table

-- 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

Pre-checks

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

Verify that the target partition exists.

-- List all partitions for the table.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- Confirm that P_2023_Q1 appears in the result.

Confirm that the partition contains the data you expect to delete.

-- Preview the data in the partition before dropping it.
SELECT * FROM sales_records WHERE sale_date < TO_DATE('2023-04-01', 'YYYY-MM-DD');
-- Expected result: rows for Q1 (for example, sale_id = 1).

(Optional) Back up the table or database. See Backup methods.

Check for active locks on the table. If long-running transactions are accessing the table, the drop operation will be blocked until they complete.

-- Check for active locks on the table.
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'SALES_RECORDS'::regclass;

Drop the partition

After completing the pre-checks, run the command during off-peak hours.

ALTER TABLE sales_records DROP PARTITION P_2023_Q1;

Verify the result

Confirm the partition was removed.

SELECT partition_name
FROM ALL_TAB_PARTITIONS
WHERE table_name = 'SALES_RECORDS' AND partition_name = 'P_2023_Q1';
-- Expected result: empty result set.

Confirm the data was deleted.

SELECT *
FROM sales_records
WHERE sale_date < TO_DATE('2023-04-01', 'YYYY-MM-DD');
-- Expected result: empty result set.

Update table statistics so the query optimizer generates accurate execution plans.
```
ANALYZE SALES_RECORDS;
```

FAQ

Why do I get `partition "..." of relation "..." does not exist`?

The partition name is case-sensitive. Query USER_TAB_PARTITIONS to find the exact name, then use double quotes for an exact match if needed.

Why do I get `permission denied for table ...` or `must be owner of table ...`?

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

Why do I get `cannot drop partition due to dependent objects`?

Other database objects — such as foreign key constraints or views — depend on this partition. The default RESTRICT behavior prevents the deletion. Identify and remove those dependent objects first, then retry.

Why is the operation taking a long time or appears stuck?

DROP PARTITION acquires an AccessExclusiveLock. If another active long-running transaction is holding a conflicting lock, the operation waits. Use the following query to identify the blocking session by its PID, then decide whether to wait or terminate the blocking transaction.

-- 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';

What's next

ALTER TABLE ADD PARTITION: Add a new partition to a partitioned table.
ALTER TABLE TRUNCATE PARTITION: Clear all data from a partition while keeping the partition definition.
ALTER TABLE SPLIT PARTITION: Split one partition into two.