DB migration support for Temporal deployment

Community Support Server Deployment
, ,
1

Hi All,

We are trying to determine which DB to use for persistence and visibility with our Temporal deployment. Reading through Temporal’s documentation, several community posts and slack messages, sounds like Temporal works fine with RDBMS (persistence and advanced visibility) for work loads of a few workflow executions per second. Beyond a certain threshold, it looks Elasticsearch is recommended for visibility.

Before proceeding with production deployment of Temporal, I have the following questions

  • What do you mean by a few workflow executions?
  • If we went ahead with PostgreSQL as our persistence and advanced visibility store (with Temporal v1.20.0), are there any migration tools available to,
    • Switch to PostgreSQL for persistence and Elasticsearch for advanced visibility
      OR
    • Switch to Cassandra for persistence and Elasticsearch for advanced visibility

Our current expected load is upto 150 new workflow executions per second (during peak hours) and that may or may not go up in the future. We might be setting retention period to at least 2 years.
Roughly estimating ~20 million completed workflow executions to be stored without archiving.

2

Roughly estimating ~20 million completed workflow executions to be stored without archiving.

I think should try to estimate total size of storage needed for these executions given retention period, it will depend on your workflows too.

Server 1.20.0 added ability for advanced visibility on SQL db, see release notes here.

Cassandra for visibility is not recommended. If you need advanced visibility would go with ES, or SQL db support.

Also with server version 1.20, executions are archived as soon as completed (with a small backoff, default 5m, configurable via dynamic config history.archivalProcessorArchiveDelay) so I don’t think you necessarily need such large retention period on namespaces.

3

@tihomir Thanks for your response!

with server version 1.20, executions are archived as soon as completed

Is there any way to not archive it immediately? we want to use a single endpoint to query for active and completed workflows for the configured retention period.

also, what do you mean by archived? Does it get copied over to a different table and we’ll have to use the ListArchivedWorkflowExecutions api for it? OR does it get moved to a different data store like S3?

Also, I’m trying to find out if temporal has any migration tools to switch DBs (from postgres to cassandra) for persistence in the future and also copy over all the history along.

4

I think there are two things, archival and namespace retention.

Archival moves your execution info to a different data/blob store like s3 yes.
Completed executions when they hit namespace retention are removed from the Temporal primary persistence and visibility stores.

So the sequence of events (with 1.20 server version) is:

  1. Workflow exec completes
  2. Execution (event history and visibility data) are archived, moved to your s3 bucket for example
  3. Waiting on namespace retention
  4. Workflow execution info (event histories, visibility data) are removed from Temporal persistence+visibility stores.

Only after 4 you would need to use ListArchivedWorkflowExecutions apis (so once execution info is no longer available in Temporal persistence).

I’m trying to find out if temporal has any migration tools to switch DBs

I am not aware of such migration tool currently. Can check if there is anything and get back.

5

On migration. Just speculating wildly: what about setting up a temporal cluster with two different DB backend technologies, and then do a failover from the source to the target architecture? Finally removing the temporal source node from the cluster.