Overview Cadence requires a persistence layer for storing workflow state, history, and metadata. This guide covers database setup for all supported databases.
Cadence supports any database with multi-row single-shard transactions:
Cassandra / ScyllaDB (NoSQL)MySQL 8.0+ (SQL)PostgreSQL 12+ (SQL)
Persistence Architecture Cadence uses two separate stores:
Default Store : Core workflow execution state, histories, task queues, and domainsVisibility Store : Workflow search and list operations
These can use the same or different database types depending on your requirements.
For advanced workflow search capabilities, consider using ElasticSearch/OpenSearch as an additional advanced visibility store.
Cadence provides dedicated schema management tools:
cadence-cassandra-tool : For Cassandra/ScyllaDBcadence-sql-tool : For MySQL/PostgreSQL
Install via Homebrew:
brew install cadence-workflow
Or build from source:
make cadence-cassandra-tool cadence-sql-tool
Cassandra Setup Installation brew install cassandra brew services start cassandra
Schema Installation
Set environment variables
export CASSANDRA_HOST = 127.0.0.1 export CASSANDRA_PORT = 9042 export CASSANDRA_USER = cassandra # if auth enabled export CASSANDRA_PASSWORD = cassandra
Create keyspaces and tables
# Install default store schema cadence-cassandra-tool \ --ep $CASSANDRA_HOST \ --port $CASSANDRA_PORT \ create \ --keyspace cadence \ --replication-factor 3 cadence-cassandra-tool \ --ep $CASSANDRA_HOST \ --port $CASSANDRA_PORT \ --keyspace cadence \ setup-schema \ --version 0.0 cadence-cassandra-tool \ --ep $CASSANDRA_HOST \ --port $CASSANDRA_PORT \ --keyspace cadence \ update-schema \ --schema-dir schema/cassandra/cadence/versioned # Install visibility store schema cadence-cassandra-tool \ --ep $CASSANDRA_HOST \ --port $CASSANDRA_PORT \ create \ --keyspace cadence_visibility \ --replication-factor 3 cadence-cassandra-tool \ --ep $CASSANDRA_HOST \ --port $CASSANDRA_PORT \ --keyspace cadence_visibility \ setup-schema \ --version 0.0 cadence-cassandra-tool \ --ep $CASSANDRA_HOST \ --port $CASSANDRA_PORT \ --keyspace cadence_visibility \ update-schema \ --schema-dir schema/cassandra/visibility/versioned
Verify schema installation
# Check installed version cadence-cassandra-tool \ --ep $CASSANDRA_HOST \ --keyspace cadence \ show-version
Cassandra Configuration persistence : defaultStore : cass-default visibilityStore : cass-visibility numHistoryShards : 1024 datastores : cass-default : nosql : pluginName : "cassandra" hosts : "127.0.0.1" keyspace : "cadence" consistency : LOCAL_QUORUM # Read/write consistency serialConsistency : LOCAL_SERIAL # Serial consistency connectTimeout : 2s timeout : 5s maxConns : 2 # Connections per host cass-visibility : nosql : pluginName : "cassandra" hosts : "127.0.0.1" keyspace : "cadence_visibility"
For production Cassandra deployments:
Use replication factor of 3 or higher
Set consistency: LOCAL_QUORUM for strong consistency
Configure multiple seed nodes in hosts
Enable authentication and encryption
MySQL Setup Installation brew install mysql brew services start mysql
User Setup Create a dedicated user for Cadence:
CREATE USER ' cadence '@ '%' IDENTIFIED BY 'cadence' ; GRANT ALL PRIVILEGES ON cadence. * TO 'cadence' @ '%' ; GRANT ALL PRIVILEGES ON cadence_visibility. * TO 'cadence' @ '%' ; FLUSH PRIVILEGES;
Schema Installation
Set environment variables
export SQL_HOST = 127.0.0.1 export SQL_PORT = 3306 export SQL_USER = cadence export SQL_PASSWORD = cadence
Create databases
# Create default database cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin mysql \ --db cadence \ create # Create visibility database cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin mysql \ --db cadence_visibility \ create
Install schema
# Setup default store schema cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin mysql \ --db cadence \ setup-schema \ --version 0.0 cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin mysql \ --db cadence \ update-schema \ --schema-dir schema/mysql/v8/cadence/versioned # Setup visibility store schema cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin mysql \ --db cadence_visibility \ setup-schema \ --version 0.0 cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin mysql \ --db cadence_visibility \ update-schema \ --schema-dir schema/mysql/v8/visibility/versioned
Verify installation
cadence-sql-tool \ --ep $SQL_HOST \ --plugin mysql \ --db cadence \ show-version
MySQL Configuration config/development_mysql.yaml
persistence : defaultStore : mysql-default visibilityStore : mysql-visibility numHistoryShards : 1024 datastores : mysql-default : sql : pluginName : "mysql" databaseName : "cadence" connectAddr : "127.0.0.1:3306" connectProtocol : "tcp" user : "cadence" password : "cadence" maxConns : 20 # Max connections per host maxIdleConns : 20 # Idle connection pool size maxConnLifetime : "1h" # Max connection lifetime connectAttributes : tx_isolation : "READ-COMMITTED" # Required for MySQL 5.6 mysql-visibility : sql : pluginName : "mysql" databaseName : "cadence_visibility" connectAddr : "127.0.0.1:3306" connectProtocol : "tcp" user : "cadence" password : "cadence" maxConns : 2 maxIdleConns : 2
MySQL 8.0+ uses READ-COMMITTED isolation level by default. The tx_isolation setting is only required for MySQL 5.6 and below.
PostgreSQL Setup Installation brew install postgresql brew services start postgresql
User Setup Create a user with superuser privileges:
CREATE USER cadence WITH PASSWORD 'cadence' ; ALTER USER cadence WITH SUPERUSER;
Schema Installation
Set environment variables
export SQL_HOST = 127.0.0.1 export SQL_PORT = 5432 export SQL_USER = cadence export SQL_PASSWORD = cadence
Create databases and install schema
# Create and setup default database cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin postgres \ --db cadence \ create cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin postgres \ --db cadence \ setup-schema \ --version 0.0 cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin postgres \ --db cadence \ update-schema \ --schema-dir schema/postgres/cadence/versioned # Create and setup visibility database cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin postgres \ --db cadence_visibility \ create cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin postgres \ --db cadence_visibility \ setup-schema \ --version 0.0 cadence-sql-tool \ --ep $SQL_HOST \ --port $SQL_PORT \ --user $SQL_USER \ --password $SQL_PASSWORD \ --plugin postgres \ --db cadence_visibility \ update-schema \ --schema-dir schema/postgres/visibility/versioned
PostgreSQL Configuration config/development_postgres.yaml
persistence : defaultStore : postgres-default visibilityStore : postgres-visibility numHistoryShards : 1024 datastores : postgres-default : sql : pluginName : "postgres" databaseName : "cadence" connectAddr : "127.0.0.1:5432" connectProtocol : "tcp" user : "cadence" password : "cadence" maxConns : 20 maxIdleConns : 20 maxConnLifetime : "1h" postgres-visibility : sql : pluginName : "postgres" databaseName : "cadence_visibility" connectAddr : "127.0.0.1:5432" connectProtocol : "tcp" user : "cadence" password : "cadence" maxConns : 2 maxIdleConns : 2
Schema Upgrades When upgrading Cadence versions, you may need to apply schema updates.
Check current version
Cassandra
MySQL/PostgreSQL
cadence-cassandra-tool --ep $HOST --keyspace cadence show-version
Review upgrade notes
Check the CHANGELOG for any special upgrade instructions.
Backup database
Always backup your database before applying schema updates: # Example for MySQL mysqldump -u cadence -p cadence > cadence_backup.sql mysqldump -u cadence -p cadence_visibility > cadence_visibility_backup.sql
Apply schema updates
Cassandra
MySQL/PostgreSQL
cadence-cassandra-tool \ --ep $HOST \ --keyspace cadence \ update-schema \ --schema-dir schema/cassandra/cadence/versioned
Verify upgrade
# Check new version cadence-sql-tool --ep $HOST --plugin mysql --db cadence show-version # Start Cadence server and verify functionality cadence-server start
Production Database Tuning Connection Pool Sizing Size connection pools based on your workload:
datastores : default : sql : maxConns : 20 # Total connections per host maxIdleConns : 20 # Idle connections to maintain maxConnLifetime : "1h" # Recycle connections hourly
Guidelines:
Start with 20 connections per service instance
Monitor connection utilization
Increase if you see connection exhaustion
Ensure database max_connections > (maxConns × service_instances)
Cassandra Tuning Consistency Levels: consistency : LOCAL_QUORUM # For multi-DC: LOCAL_QUORUM serialConsistency : LOCAL_SERIAL # For LWT operations
Timeouts: connectTimeout : 2s # Initial connection timeout timeout : 5s # Query timeout
MySQL Tuning Recommended MySQL configuration:
[mysqld] transaction-isolation = READ-COMMITTED max_connections = 500 innodb_buffer_pool_size = 4G innodb_log_file_size = 512M innodb_flush_log_at_trx_commit = 1
PostgreSQL Tuning Recommended PostgreSQL configuration:
max_connections = 500 shared_buffers = 4GB effective_cache_size = 12GB wal_buffers = 16MB default_statistics_target = 100
Monitoring Key database metrics to monitor:
Connection pool utilization : Active vs. max connectionsQuery latency : P50, P95, P99 latenciesError rates : Connection failures, query timeoutsStorage : Disk usage and growth rateReplication lag : For replicated databases
Troubleshooting Connection Issues # Test database connectivity telnet $DB_HOST $DB_PORT # For MySQL mysql -h $DB_HOST -u cadence -p # For PostgreSQL psql -h $DB_HOST -U cadence -d cadence # For Cassandra cqlsh $DB_HOST $DB_PORT
Schema Version Mismatch If server fails to start due to schema version mismatch:
# Check current version cadence-sql-tool --ep $HOST --db cadence show-version # Apply missing updates cadence-sql-tool --ep $HOST --db cadence update-schema \ --schema-dir schema/mysql/v8/cadence/versioned
Check slow query logs
Verify indexes are created properly
Monitor connection pool utilization
Review database server resources (CPU, memory, I/O)
Next Steps
Server Setup Deploy Cadence server components
Docker Deployment Run Cadence with Docker
