Create and manage table partitions - AnalyticDB

Table partitioning splits a large table into smaller, independently managed segments based on a column value. For fact tables or large tables, partitioning speeds up data deletion and data loading by letting you operate on an entire partition at once instead of scanning individual rows.

Drop all data in a partition with ALTER TABLE DROP PARTITION — far faster than deleting rows one by one.
Load a new batch of data through partition exchange with ALTER TABLE EXCHANGE PARTITION, bypassing full-table writes.

When to use table partitioning

Before partitioning a table, confirm the following:

The table is large. Millions or billions of rows are strong candidates. For tables with a few thousand rows, the administrative overhead typically outweighs the performance gains.
Queries filter on a specific column. Partitioning is effective when your WHERE clauses consistently reference the partition key, allowing the query planner to skip irrelevant partitions.
You manage data by time or category. Regular operations such as purging last month's data or loading a daily batch map naturally onto partition boundaries.

If most queries do full-table scans without filtering on the partition key, partitioning adds overhead without improving performance.

Partition types

AnalyticDB for PostgreSQL supports three partition types:

Type	When to use
Range partitioning	Partition by a continuous range of numeric or date values — for example, one partition per month for time-series data.
List partitioning	Partition by an explicit list of discrete values — for example, one partition per sales region or product category.
Composite partition	Combine range and list strategies for multi-dimensional data — for example, partition first by year, then by region within each year.

Range partitioning only supports numeric and date/time column types.

Create a partitioned table

The following example creates a LINEITEM table partitioned by ship date. Each partition covers one calendar month from January 1992 through January 2000.

CREATE TABLE LINEITEM (
    L_ORDERKEY      BIGINT NOT NULL,
    L_PARTKEY       BIGINT NOT NULL,
    L_SUPPKEY       BIGINT NOT NULL,
    L_LINENUMBER    INTEGER,
    L_QUANTITY      FLOAT8,
    L_EXTENDEDPRICE FLOAT8,
    L_DISCOUNT      FLOAT8,
    L_TAX           FLOAT8,
    L_RETURNFLAG    CHAR(1),
    L_LINESTATUS    CHAR(1),
    L_SHIPDATE      DATE,
    L_COMMITDATE    DATE,
    L_RECEIPTDATE   DATE,
    L_SHIPINSTRUCT  CHAR(25),
    L_SHIPMODE      CHAR(10),
    L_COMMENT       VARCHAR(44)
) WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576, OIDS=false)
DISTRIBUTED BY (l_orderkey)
PARTITION BY RANGE (L_SHIPDATE)
    (START (date '1992-01-01') INCLUSIVE END (date '2000-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'));

Key parameters in this example:

Parameter	Value	Description
`ORIENTATION`	`column`	Column-oriented storage, optimized for analytical queries
`COMPRESSTYPE`	`zlib`	zlib compression to reduce storage footprint
`COMPRESSLEVEL`	`5`	Compression level (1–9; higher = smaller size, slower write)
`BLOCKSIZE`	`1048576`	Block size in bytes (1 MB)
`DISTRIBUTED BY`	`l_orderkey`	Distribution key for spreading data across segments
`PARTITION BY RANGE`	`L_SHIPDATE`	Partition column — must be a numeric or date/time type

Maintain partitions

Drop an old partition

To remove all data in a partition, drop the partition directly. This is far faster than a DELETE statement because it removes the partition's storage without scanning individual rows.

ALTER TABLE LINEITEM DROP PARTITION FOR (date '1992-01-01');

Load data through partition exchange

Use the ALTER TABLE EXCHANGE PARTITION command to add a new data partition. This lets you load a batch of data into the partitioned table without performing a full-table write.

ALTER TABLE LINEITEM EXCHANGE PARTITION FOR (date '1999-12-01')
    WITH TABLE lineitem_staging;