Release History
Help

YMatrix

Quick Start

Deployment

Data Usage

Manage Clusters

Security

Best Practice

Technical Principles

Advanced Features

Graph Database

Guide

SQL Reference

FAQ

MARS3 DIMS Dimension Column Declaration

The DIMS clause declares dimension columns when creating MARS3 tables. Dimension columns are usually logical identification axes in time-series data, such as timestamps, device IDs, or business tags. By explicitly declaring dimension columns, YMatrix can persist dimension metadata in system catalogs for later inspection, migration, and tool integration.

Dimension columns are still ordinary table columns. They can be used in queries, distribution keys, sort keys, and partition keys. The DIMS clause adds dimension semantics to these columns. It does not automatically create indexes, nor does it replace the design of DISTRIBUTED BY, ORDER BY, or PARTITION BY.

Features

After dimension columns are declared with DIMS, the following capabilities are available:

  • Persistent dimension metadata: Dimension column markers are stored in system catalogs.
  • Backup and restore compatibility: Dimension metadata is preserved when table definitions are migrated using pg_dump / pg_restore.
  • psql display enhancement: When you use \d+ to inspect a table, the output includes Dimensions, which lists the table's dimension columns.
  • Consistent modeling semantics: In time-series, device monitoring, and IoT scenarios, metric columns and dimension columns can be explicitly distinguished, making table schemas easier to understand and maintain.

Syntax

CREATE TABLE table_name (
    data_column_1 type1,
    data_column_2 type2,
    ...
)
USING MARS3
  [WITH (...)]
  DIMS (dim_column_1 type1 [, dim_column_2 type2, ...])
  DISTRIBUTED BY (dist_key)
  ORDER BY (sort_keys);

DIMS accepts a parenthesized column definition list, similar to the main column list in CREATE TABLE.

DIMS (column_name data_type [, column_name data_type, ...])

Only column names, data types, and column-level constraints can be defined inside DIMS. The LIKE clause and table-level constraints are not supported.

Basic Usage

The following example creates a MARS3 table and declares tag as a dimension column:

CREATE TABLE metrics (
    ts timestamp,
    value float8
)
USING MARS3
  DIMS (tag text)
  DISTRIBUTED BY (tag)
  ORDER BY (ts);

After creation, the metrics table contains three columns: tag, ts, and value. The tag column is marked as a dimension column.

Note!
Dimension columns become actual table columns. Therefore, they can be referenced like ordinary columns in DISTRIBUTED BY, ORDER BY, queries, and write statements.

Declaring Multiple Dimension Columns

You can declare multiple dimension columns in DIMS:

CREATE TABLE sensor_data (
    reading numeric
)
USING MARS3
  DIMS (device_id int, ts timestamp)
  DISTRIBUTED BY (device_id)
  ORDER BY (device_id, ts);

In this example, both device_id and ts are declared as dimension columns. They can also be used as distribution keys and sort keys.

Using Dimension Columns as Sort Keys

Dimension columns can be referenced in ORDER BY to define the MARS3 table sort key:

CREATE TABLE logs (
    level int,
    msg text
)
USING MARS3
  DIMS (ts timestamp)
  DISTRIBUTED BY (level)
  ORDER BY (ts);

In this example, ts is both a dimension column and the sort key.

Using DIMS with Unique Mode

DIMS can be used together with MARS3 Unique Mode:

CREATE TABLE latest_reading (
    value int NOT NULL
)
USING MARS3
  WITH (uniquemode=true)
  DIMS (device_id int NOT NULL, ts timestamp NOT NULL)
  DISTRIBUTED BY (device_id)
  ORDER BY (device_id, ts);

In Unique Mode, if dimension columns are also part of the unique key or sort key, explicitly declare NOT NULL constraints according to the business semantics.

Note!
When Unique Mode is enabled, the first column in the ORDER BY clause must be defined as NOT NULL.

Using Quoted Identifiers

If a dimension column name contains spaces, hyphens, or case-sensitive characters, use double quotes:

CREATE TABLE data (
    id int
)
USING MARS3
  DIMS ("capture time" timestamp, "sensor-ID" int)
  DISTRIBUTED BY (id)
  ORDER BY (id);

When querying these columns later, you must also reference them with double quotes:

SELECT "capture time", "sensor-ID"
FROM data;

Viewing Dimension Columns

Using psql

Use \d+ to view dimension column information for a MARS3 table:

\d+ my_table

Example output:

                      Table "public.my_table"
 Column |            Type             | ...
--------+-----------------------------+-----
 ts     | timestamp without time zone | ...
 id     | integer                     | ...
 value  | numeric                     | ...
Distributed by: (id)
Order by: (id)
Options: dim_columns=ts,id, mars3options=nbuckets=3
Dimensions: ts, id

The Dimensions field lists the dimension columns declared for the table.

Querying System Catalogs

You can also query dimension columns from system catalogs:

SELECT attname
FROM pg_attribute
WHERE attrelid = 'my_table'::regclass
  AND attnum > 0
  AND attoptions @> ARRAY['is_dimension=true']
ORDER BY attnum;

Column Order

When a table is created with DIMS, dimension columns appear before regular columns in the physical column order.

Example:

CREATE TABLE t (
    a int,
    b text
)
USING MARS3
  DIMS (c int, d text)
  DISTRIBUTED BY (c)
  ORDER BY (c);

The physical column order is:

c, d, a, b

Therefore, if an INSERT statement does not explicitly specify column names, values must follow the physical column order:

INSERT INTO t VALUES (1, 'x', 10, 'hello');

It is recommended to explicitly specify column names to avoid incorrect writes caused by column order:

INSERT INTO t (c, d, a, b) VALUES (1, 'x', 10, 'hello');

Limitations

The DIMS clause has the following limitations:

Limitation Description
Storage engine DIMS is supported only for tables created with USING MARS3. HEAP, AO, and other storage engines are not supported.
External tables DIMS is not supported for external tables.
Empty list DIMS () is invalid syntax.
Definition content Only column definitions are allowed inside DIMS. The LIKE clause and table-level constraints are not supported.
Name conflicts Dimension column names cannot duplicate regular column names or other dimension column names.
Behavior DIMS only declares dimension semantics. It does not automatically create indexes or change data distribution, sorting, or partitioning behavior.

Recommendations

  • Declare stable fields that are commonly used to filter or identify data subjects as dimension columns, such as device IDs, collection timestamps, and business tags.
  • If dimension columns are frequently used in point lookups, range queries, or aggregate filters, consider using them as distribution keys, sort keys, or partition keys based on query patterns.
  • For time-series tables, a common design is to declare both the device identifier and time field as dimension columns, and then choose appropriate DISTRIBUTED BY and ORDER BY clauses according to the query pattern.
  • Explicitly specify column names in write statements to avoid value misalignment caused by dimension columns being placed before regular columns in physical column order.
← Previous
Introduction
Next →
Distribution Strategy