DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Interval Partitioning in GBase 8s: Automatic Extension and Lifecycle Management

#gbase #database #数据库

Interval partitioning automatically creates new partitions when data exceeds the defined range, eliminating the need to manually add partitions. GBase 8s inherits this capability and extends it with concurrent storage distribution, NULL handling, and automated history cleanup — a complete partition management toolkit for a gbase database.

What Is Interval Partitioning?

A table starts with an initial range partition. From there, the database automatically creates new partitions at a fixed interval — for example, one per month or per million rows. Both Oracle and GBase 8s support this pattern with similar syntax.

-- Oracle syntax
CREATE TABLE orders (
    order_num   NUMBER NOT NULL,
    order_date  DATE
) PARTITION BY RANGE (order_date)
INTERVAL (NUMTODSINTERVAL(1, 'MONTH'))
( PARTITION p1 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')) );

-- GBase 8s syntax
CREATE TABLE orders (
    order_num   INTEGER NOT NULL,
    order_date  DATE
) FRAGMENT BY RANGE(order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
PARTITION p1 VALUES < '2024-02-01' IN datadbs1;
Enter fullscreen mode Exit fullscreen mode

Key Features in GBase 8s

1. Multi‑Dbspace Storage for Concurrency

By default all partitions land in the same dbspace, which can create I/O hotspots. Use STORE IN to spread automatic partitions across multiple dbspaces, improving parallel scan performance.

STORE IN (datadbs1, datadbs2, datadbs3, datadbs4)
Enter fullscreen mode Exit fullscreen mode

2. NULL Partition Handling

If the partition key allows NULLs, you must explicitly create a partition for them with VALUES IS NULL, otherwise inserting a NULL value will throw an error.

CREATE TABLE orders (
    order_num   INTEGER NOT NULL,
    order_date  DATETIME YEAR TO FRACTION(5)
) FRAGMENT BY RANGE(order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (datadbs1, datadbs2, datadbs3)
PARTITION p0 VALUES IS NULL IN datadbs1,
PARTITION p1 VALUES < '2024-02-01' IN datadbs1,
PARTITION p2 VALUES < '2024-03-01' IN datadbs2,
PARTITION p3 VALUES < '2024-04-01' IN datadbs3;
Enter fullscreen mode Exit fullscreen mode

3. Automatic History Partition Cleanup

To prevent tables from growing indefinitely, GBase 8s provides two threshold mechanisms:

  • By fragment count: ROLLING (100 FRAGMENTS) — when more than 100 partitions exist, the oldest is dropped or detached.
  • By disk usage: LIMIT TO 100000MiB DETACH — when total partition size exceeds 100 GB, cleanup starts.

You can choose between DETACH (splitting the old partition into a standalone table) and DISCARD (dropping it permanently). Both thresholds can be combined.

CREATE TABLE employee (
    emp_id    INTEGER,
    emp_name  CHAR(64),
    ssn       CHAR(12),
    basepay   FLOAT,
    varpay    FLOAT,
    dept_id   SMALLINT,
    hire_date DATE
) FRAGMENT BY RANGE(emp_id)
INTERVAL(1000)
ROLLING (100 FRAGMENTS)
LIMIT TO 100000MiB DETACH
STORE IN (dbs1, dbs2, dbs3)
PARTITION p1 VALUES < 5000 IN dbs0,
PARTITION p2 VALUES < 10000 IN dbs0,
PARTITION p3 VALUES < 20000 IN dbs4;
Enter fullscreen mode Exit fullscreen mode

The cleanup job runs daily at 00:45 by default. You can trigger an immediate purge by calling EXECUTE FUNCTION syspurge();.

Interval partitioning in GBase 8s keeps your growing tables fast and your storage manageable — a perfect fit for the GBASE China‑domestically developed database in data‑intensive environments.

Top comments (0)