Hologres:CREATE PARTITION TABLE

When to use partitioned tables

Use partitioned tables when the benefit of partition elimination or bulk data operations clearly outweighs the overhead of managing multiple child tables.

Use partitioned tables when:

• The daily data volume for a single partition exceeds 100 million rows (for date-based partitioning).

• You need to bulk-replace or delete an entire partition's data using TRUNCATE or DROP—both operations complete instantly on a child table and avoid full scans.

Avoid partitioned tables when:

• The data source is a database system. Excessive partitions waste I/O resources. Instead, specify the commonly used filter columns as the segment key to enable index-based query acceleration.

• The daily data volume is below 100 million rows per partition. Small partitions provide negligible query acceleration. Use a coarser granularity instead.

Keep in mind: each child table is stored as a separate set of files and is equivalent to a non-partitioned table in metadata storage. A large number of child tables increases metadata volume and creates many small files and fragments.

Limitations

• Data must be written to a child table, not to the parent table directly.

  Realtime Compute for Apache Flink can write data to a parent table in real time. For details, see Write data to a partitioned Hologres sink table in real time.

• Hologres supports only list partitioning (PARTITION BY LIST).

• The partition key must be a single column.

• Each partitioning rule can be used to create only one partitioned table.

• If the parent table has a primary key, the partition key column must be part of that primary key.

Create a partitioned table

A partitioned table consists of a parent table (which defines the schema and partition rule) and one or more child tables (each covering specific partition values). Child tables must be created before data can be written.

Alternatively, enable dynamic partitioning to let Hologres create child tables automatically based on rules you configure.

-- Create the parent table
CREATE TABLE [IF NOT EXISTS] [<schema_name>.]<table_name> (
  {
    <column_name> <column_type> [<column_constraints> [, ...]]
    | <table_constraints>
    [, ...]
  }
)
PARTITION BY LIST (<column_name>);

-- Create a child table
CREATE TABLE [IF NOT EXISTS] [<schema_name>.]<table_name>
  PARTITION OF <parent_table>
  FOR VALUES IN (<string_literal>);

BEGIN;
CREATE TABLE public.hologres_parent (
    a TEXT,
    b INT,
    c TIMESTAMP,
    d TEXT
)
PARTITION BY LIST (a)
WITH (orientation = 'column');
CREATE TABLE public.hologres_child1 PARTITION OF public.hologres_parent FOR VALUES IN ('v1');
CREATE TABLE public.hologres_child2 PARTITION OF public.hologres_parent FOR VALUES IN ('v2');
CREATE TABLE public.hologres_child3 PARTITION OF public.hologres_parent FOR VALUES IN ('v3');
COMMIT;

BEGIN;
CREATE TABLE public.hologres_parent(
  a TEXT,
  b INT,
  c TIMESTAMP,
  d TEXT
)
PARTITION BY LIST(a);
CALL set_table_property('public.hologres_parent', 'orientation', 'column');
CREATE TABLE public.hologres_child1 PARTITION OF public.hologres_parent FOR VALUES IN('v1');
CREATE TABLE public.hologres_child2 PARTITION OF public.hologres_parent FOR VALUES IN('v2');
CREATE TABLE public.hologres_child3 PARTITION OF public.hologres_parent FOR VALUES IN('v3');
COMMIT;

BEGIN;
CREATE TABLE public.hologres_parent_2 (
    a TEXT,
    b INT,
    c TIMESTAMP,
    d TEXT,
    ds TEXT,
    PRIMARY KEY (ds, b)
)
PARTITION BY LIST (ds)
WITH (orientation = 'column');
CREATE TABLE public.holo_child_1 PARTITION OF public.hologres_parent_2 FOR VALUES IN ('20201215');
CREATE TABLE public.holo_child_2 PARTITION OF public.hologres_parent_2 FOR VALUES IN ('20201216');
CREATE TABLE public.holo_child_3 PARTITION OF public.hologres_parent_2 FOR VALUES IN ('20201217');
COMMIT;

BEGIN;
CREATE TABLE public.hologres_parent_2(
  a TEXT,
  b INT,
  c TIMESTAMP,
  d TEXT,
  ds TEXT,
  PRIMARY KEY (ds, b)
)
PARTITION BY LIST(ds);
CALL set_table_property('public.hologres_parent_2', 'orientation', 'column');
CREATE TABLE public.holo_child_1 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201215');
CREATE TABLE public.holo_child_2 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201216');
CREATE TABLE public.holo_child_3 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201217');
COMMIT;

List child tables

List all child tables of a parent table using HoloWeb or the following SQL statement. Replace parent_table_name with the actual parent table name.

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent          ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child           ON pg_inherits.inhrelid  = child.oid
    JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
    JOIN pg_namespace nmsp_child  ON nmsp_child.oid  = child.relnamespace
WHERE parent.relname = 'parent_table_name';

Property inheritance rules

When you create a child table using CREATE TABLE ... PARTITION OF, the child table inherits most properties from the parent table. The table below shows which properties must match and which can differ.

• Must match: Attaching a child table with a different value for these properties returns an error. To avoid this, create child tables using CREATE TABLE LIKE, which copies the parent's property settings.

• Can differ: If not explicitly set on the child table, the child inherits the parent's value. If explicitly set, the child's value takes effect.

• Must include parent's indexed columns: The child table's indexed columns must be a superset of the parent's. Additional indexed columns can be added on the child table.

What's next

• Dynamic partitioning — automatically create and manage child tables based on configured rules, without manually creating each partition upfront.

• ALTER PARTITION TABLE — modify partitions after creation.

• DROP PARTITION TABLE — drop partitioned tables.