Database Schema Reference

Temporal Server database schema for PostgreSQL, MySQL, and Cassandra

Temporal Server uses a database to persist workflow execution state, history, and metadata. This reference describes the schema structure for supported databases.

Supported Databases

• PostgreSQL (v12+)
• MySQL (v8+)
• SQLite (v3) - Development only
• Cassandra (v3+)

Temporal requires two logical databases:

1. Temporal Store: Core workflow and shard data
2. Visibility Store: Search and list operations

Schema Organization

Schemas are located in the source tree:

schema/
├── postgresql/
│   └── v12/
│       ├── temporal/
│       │   ├── schema.sql
│       │   └── versioned/
│       └── visibility/
│           ├── schema.sql
│           └── versioned/
├── mysql/
│   └── v8/
│       ├── temporal/
│       └── visibility/
├── sqlite/
│   └── v3/
│       ├── temporal/
│       └── visibility/
└── cassandra/
    └── temporal/

Temporal Store Schema

Namespaces

namespaces

Stores namespace configuration and metadata.

Column	Type	Description
partition_id	INTEGER	Partition identifier (always 54321)
id	BYTEA/VARBINARY	Namespace UUID
name	VARCHAR(255)	Unique namespace name
notification_version	BIGINT	Version for namespace updates
data	BYTEA/BLOB	Serialized namespace proto
data_encoding	VARCHAR(16)	Encoding type (proto3)
is_global	BOOLEAN	Whether namespace is global

Primary Key: (partition_id, id)

Unique Index: name

namespace_metadata

Global namespace metadata.

Column	Type	Description
partition_id	INTEGER	Partition identifier
notification_version	BIGINT	Current notification version

Primary Key: (partition_id)

Shards

shards

Stores history shard metadata and ownership.

Column	Type	Description
shard_id	INTEGER	Shard identifier
range_id	BIGINT	Current range ID for ownership
data	BYTEA/BLOB	Serialized shard info
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (shard_id)

Workflow Executions

executions

Stores mutable state for workflow executions.

Column	Type	Description
shard_id	INTEGER	Shard owning this execution
namespace_id	BYTEA/VARBINARY	Namespace UUID
workflow_id	VARCHAR(255)	Workflow identifier
run_id	BYTEA/VARBINARY	Run UUID
next_event_id	BIGINT	Next event ID to be assigned
last_write_version	BIGINT	Version for conflict resolution
data	BYTEA/BLOB	Mutable state proto
data_encoding	VARCHAR(16)	Encoding type
state	BYTEA/BLOB	Execution state proto
state_encoding	VARCHAR(16)	State encoding
db_record_version	BIGINT	Database record version

Primary Key: (shard_id, namespace_id, workflow_id, run_id)

current_executions

Tracks the current (latest) run for each workflow ID.

Column	Type	Description
shard_id	INTEGER	Shard ID
namespace_id	BYTEA/VARBINARY	Namespace UUID
workflow_id	VARCHAR(255)	Workflow identifier
run_id	BYTEA/VARBINARY	Current run UUID
create_request_id	VARCHAR(255)	Request ID that created execution
state	INTEGER	Execution state
status	INTEGER	Workflow status
start_version	BIGINT	Start version for conflict resolution
start_time	TIMESTAMP	When execution started
last_write_version	BIGINT	Last write version
data	BYTEA/BLOB	WorkflowExecutionState proto
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (shard_id, namespace_id, workflow_id)

current_chasm_executions

Tracks current Chasm (state machine) executions.

Column	Type	Description
shard_id	INTEGER	Shard ID
namespace_id	BYTEA/VARBINARY	Namespace UUID
business_id	VARCHAR(255)	Business identifier
archetype_id	BIGINT	Archetype identifier
run_id	BYTEA/VARBINARY	Current run UUID
create_request_id	VARCHAR(255)	Creation request ID
state	INTEGER	Execution state
status	INTEGER	Status
start_version	BIGINT	Start version
start_time	TIMESTAMP	Start time
last_write_version	BIGINT	Last write version
data	BYTEA/BLOB	ExecutionState proto
data_encoding	VARCHAR(16)	Encoding

Primary Key: (shard_id, namespace_id, business_id, archetype_id)

History Events

history_node

Stores history events in a tree structure.

Column	Type	Description
shard_id	INTEGER	Shard ID
tree_id	BYTEA/VARBINARY	History tree identifier
branch_id	BYTEA/VARBINARY	Branch identifier
node_id	BIGINT	Node ID
txn_id	BIGINT	Transaction ID
prev_txn_id	BIGINT	Previous transaction ID
data	BYTEA/BLOB	History events batch
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (shard_id, tree_id, branch_id, node_id, txn_id)

history_tree

Stores metadata about history branches.

Column	Type	Description
shard_id	INTEGER	Shard ID
tree_id	BYTEA/VARBINARY	Tree identifier
branch_id	BYTEA/VARBINARY	Branch identifier
data	BYTEA/BLOB	Branch info proto
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (shard_id, tree_id, branch_id)

Tasks

history_immediate_tasks

Immediate tasks (transfer, visibility) for workflow execution.

Column	Type	Description
shard_id	INTEGER	Shard ID
category_id	INTEGER	Task category
task_id	BIGINT	Task identifier
data	BYTEA/BLOB	Task data proto
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (shard_id, category_id, task_id)

history_scheduled_tasks

Scheduled tasks (timers, delayed tasks).

Column	Type	Description
shard_id	INTEGER	Shard ID
category_id	INTEGER	Task category
visibility_timestamp	TIMESTAMP	When task becomes visible
task_id	BIGINT	Task identifier
data	BYTEA/BLOB	Task data proto
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (shard_id, category_id, visibility_timestamp, task_id)

Task Queues

tasks_v2

Stores workflow and activity tasks with fairness scheduling.

Column	Type	Description
range_hash	BIGINT	Hash for partitioning
task_queue_id	BYTEA/VARBINARY	Task queue identifier
pass	BIGINT	Scheduling pass for fairness
task_id	BIGINT	Task identifier
data	BYTEA/BLOB	Task data
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (range_hash, task_queue_id, pass, task_id)

task_queues_v2

Task queue metadata and state.

Column	Type	Description
range_hash	BIGINT	Hash for partitioning
task_queue_id	BYTEA/VARBINARY	Task queue identifier
range_id	BIGINT	Range ID for ownership
data	BYTEA/BLOB	Task queue state
data_encoding	VARCHAR(16)	Encoding type

Primary Key: (range_hash, task_queue_id)

task_queue_user_data

User-defined task queue data including versioning.

Column	Type	Description
namespace_id	BYTEA/VARBINARY	Namespace UUID
task_queue_name	VARCHAR(255)	Task queue name
data	BYTEA/BLOB	TaskQueueUserData proto
data_encoding	VARCHAR(16)	Encoding type
version	BIGINT	Version for optimistic locking

Primary Key: (namespace_id, task_queue_name)

build_id_to_task_queue

Maps worker build IDs to task queues.

Column	Type	Description
namespace_id	BYTEA/VARBINARY	Namespace UUID
build_id	VARCHAR(255)	Build identifier
task_queue_name	VARCHAR(255)	Task queue name

Primary Key: (namespace_id, build_id, task_queue_name)

Cluster Management

cluster_metadata_info

Stores cluster configuration for multi-cluster setups.

Column	Type	Description
metadata_partition	INTEGER	Partition ID
cluster_name	VARCHAR(255)	Cluster name
data	BYTEA/BLOB	Cluster metadata proto
data_encoding	VARCHAR(16)	Encoding type
version	BIGINT	Version number

Primary Key: (metadata_partition, cluster_name)

cluster_membership

Tracks cluster members and their roles.

Column	Type	Description
membership_partition	INTEGER	Partition ID
host_id	BYTEA/VARBINARY	Host UUID
rpc_address	VARCHAR(128)	RPC address
rpc_port	SMALLINT	RPC port
role	SMALLINT	Service role
session_start	TIMESTAMP	When session started
last_heartbeat	TIMESTAMP	Last heartbeat time
record_expiry	TIMESTAMP	When record expires

Primary Key: (membership_partition, host_id)

Indexes:

• (role, host_id)
• (role, last_heartbeat)
• (rpc_address, role)
• (last_heartbeat)
• (record_expiry)

Queues

queues

Named queue metadata.

Column	Type	Description
queue_type	INT	Queue type identifier
queue_name	VARCHAR(255)	Queue name
metadata_payload	BYTEA/BLOB	Queue metadata
metadata_encoding	VARCHAR(16)	Encoding type

Primary Key: (queue_type, queue_name)

queue_messages

Messages in named queues.

Column	Type	Description
queue_type	INT	Queue type
queue_name	VARCHAR(255)	Queue name
queue_partition	BIGINT	Partition ID
message_id	BIGINT	Message identifier
message_payload	BYTEA/BLOB	Message data
message_encoding	VARCHAR(16)	Encoding type

Primary Key: (queue_type, queue_name, queue_partition, message_id)

Nexus Endpoints

nexus_endpoints

Stores Nexus endpoint configurations.

Column	Type	Description
id	BYTEA/VARBINARY	Endpoint UUID
data	BYTEA/BLOB	NexusEndpoint proto
data_encoding	VARCHAR(16)	Encoding type
version	BIGINT	Version for optimistic locking

Primary Key: (id)

nexus_endpoints_partition_status

Tracks version of nexus_endpoints table.

Column	Type	Description
id	INT	Always 0 (single row)
version	BIGINT	Table version

Primary Key: (id)

Constraint: id = 0 (single row table)

Visibility Store Schema

executions_visibility

Main visibility table for workflow search.

Column	Type	Description
namespace_id	VARCHAR(64)	Namespace UUID
workflow_id	VARCHAR(255)	Workflow identifier
run_id	VARCHAR(64)	Run UUID
start_time	TIMESTAMP	Workflow start time
close_time	TIMESTAMP	Workflow close time
workflow_type_name	VARCHAR(255)	Workflow type
status	INT	Workflow status
history_length	BIGINT	Number of history events
execution_time	TIMESTAMP	Execution timestamp
memo	BYTEA/BLOB	Memo data
encoding	VARCHAR(64)	Encoding type
task_queue	VARCHAR(255)	Task queue name
search_attributes	JSONB/JSON	Custom search attributes

Primary Key: (namespace_id, run_id)

Indexes:

• (namespace_id, close_time, status)
• (namespace_id, start_time, status)
• (namespace_id, workflow_type_name, close_time)
• Additional indexes on search attributes

Schema Versioning

Temporal uses versioned schema migrations:

• Base schema: schema.sql
• Migrations: versioned/vX.Y/*.sql

Example:

schema/postgresql/v12/temporal/
├── schema.sql (base schema)
└── versioned/
    ├── v1.0/schema.sql
    ├── v1.1/cluster_metadata.sql
    ├── v1.2/queue.sql
    └── v1.19/current_chasm_executions.sql

Setup and Migration

Initial Setup

# Create database
temporal-sql-tool --plugin postgres create-database

# Setup schema
temporal-sql-tool --plugin postgres setup-schema -v 0.0
temporal-sql-tool --plugin postgres update-schema -d schema/postgresql/v12/temporal/versioned

Upgrade Schema

# Check current version
temporal-sql-tool --plugin postgres version

# Upgrade to latest
temporal-sql-tool --plugin postgres update-schema -d schema/postgresql/v12/temporal/versioned

Best Practices

1. Separate Databases: Use separate physical databases for temporal and visibility stores
2. Connection Pooling: Configure appropriate connection pool sizes
3. Indexes: Monitor slow queries and add indexes as needed
4. Partitioning: Consider table partitioning for very large deployments
5. Backups: Regular backups are essential - test restore procedures
6. Retention: Configure appropriate retention policies
7. Monitoring: Monitor database metrics (CPU, IOPS, connections)

See Also

• Server Configuration
• CLI Commands
• Performance Tuning