Databases
TimescaleDB
Tables

TimescaleDB Tables

TimescaleDB is built on PostgreSQL and supports all PostgreSQL features. SchemaHero supports both regular tables and TimescaleDB hypertables.

Basic Structure

apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: users
spec:
  database: myapp
  name: users
  schema:
    timescaledb:
      primaryKey: [id]
      columns:
        - name: id
          type: uuid
          default: gen_random_uuid()
        - name: email
          type: varchar(255)
          constraints:
            notNull: true

Columns

columns:
  - name: id
    type: uuid
    default: gen_random_uuid()
    constraints:
      notNull: true
    attributes:
      autoIncrement: true       # For serial types

Column Types

TimescaleDB supports all PostgreSQL types:

CategoryTypes
Integersmallint, integer, bigint, serial, bigserial
Numericnumeric(p,s), decimal(p,s), real, double precision
Stringchar(n), varchar(n), text
Booleanboolean
Date/Timedate, time, timestamp, timestamptz, interval
UUIDuuid
JSONjson, jsonb
Binarybytea
Arraystext[], integer[], etc.

Default Values

# String literal (note nested quotes)
- name: status
  type: varchar(20)
  default: "'pending'"
 
# Number
- name: count
  type: integer
  default: "0"
 
# Boolean
- name: active
  type: boolean
  default: "true"
 
# Functions
- name: id
  type: uuid
  default: gen_random_uuid()
 
- name: created_at
  type: timestamptz
  default: now()

Primary Keys

schema:
  timescaledb:
    primaryKey: [id]              # Single column
    primaryKey: [time, device_id] # Composite (common for time-series)

Indexes

indexes:
  - columns: [email]
    name: idx_users_email
    isUnique: true
    type: btree                   # btree (default), hash, gin, gist
    
  - columns: [device_id, time]
    name: idx_metrics_device_time
    
  - columns: [metadata]
    type: gin                     # GIN index for JSONB

Index Types

TypeUse Case
btreeDefault, general purpose
hashEquality comparisons only
ginFull-text search, JSONB, arrays
gistGeometric data, full-text search

Foreign Keys

foreignKeys:
  - columns: [device_id]
    references:
      table: devices
      columns: [id]
    onDelete: CASCADE
    onUpdate: NO ACTION
    name: fk_metrics_device

Triggers

triggers:
  - name: update_timestamp
    events: [UPDATE]
    timing: BEFORE
    forEach: ROW
    execute:
      type: Function
      name: update_modified_column

Hypertables

See Hypertables for TimescaleDB-specific time-series table features including:

  • Automatic time-based partitioning
  • Compression policies
  • Retention policies
  • Continuous aggregates

Complete Example

Regular Table

apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: devices
spec:
  database: myapp
  name: devices
  schema:
    timescaledb:
      primaryKey: [id]
      columns:
        - name: id
          type: uuid
          default: gen_random_uuid()
        - name: name
          type: varchar(255)
          constraints:
            notNull: true
        - name: location
          type: text
        - name: metadata
          type: jsonb
          default: "'{}'::jsonb"
        - name: created_at
          type: timestamptz
          default: now()
      indexes:
        - columns: [name]
          name: idx_devices_name
          isUnique: true

Time-Series Table (for Hypertable)

apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: metrics
spec:
  database: myapp
  name: metrics
  schema:
    timescaledb:
      primaryKey: [time, device_id]
      columns:
        - name: time
          type: timestamptz
          constraints:
            notNull: true
        - name: device_id
          type: uuid
          constraints:
            notNull: true
        - name: temperature
          type: double precision
        - name: humidity
          type: double precision
        - name: metadata
          type: jsonb
      indexes:
        - columns: [device_id, time]
          name: idx_metrics_device_time
      foreignKeys:
        - columns: [device_id]
          references:
            table: devices
            columns: [id]
          onDelete: CASCADE
      isHypertable: true
      hypertable:
        timeColumnName: time
        chunkTimeInterval: 1 day

See Hypertables for the full hypertable configuration reference.

ConnectingColumn Types