[p10jkle]

In general, if you're changing database schemas, you need a code version running that supports old and new. If you have some old code running to serve in-flight requests, you probably need to wait for those requests to complete. This is one of the main areas that makes me feel that requests > a few hours are a real problem (and that we shouldn't be writing workflows this way)

[DaiPlusPlus]

I'm of the opinion that actual database schemas should be a private implementation detail of any given service within an organization, whereas workflows are not services themselves, but instead are clients of the services and so should only ever interact with services via their API (be it a web-service or otherwise (no love for CORBA, eh?) and never directly talking to an RDBMS.

(obviously the rules are different for KV/object/blob-stores and don't apply here).

-----

That said, if you need a stopgap solution for a problem like that, then take advantage of existing compatibility-shim features in a decent RDBMS: things like VIEWs and SYNONOYMs exist for this reason (and yes, you can INSERT INTO-a-VIEW).

-----

Another option is to constrain your DB designs such that all DDL/schema changes are always backwards-compatible (i.e. disallow dropping anything, disallow alter-column except to widen an existing column (e.g. int-to-bigint is okay but not string-to-UUID), all domain-model attributes must be initially modelled with a m:m multiplicity whereever possible, etc etc) - and you can enforce these rules using a DDL trigger too - and any changes made by a workflow's DML can be repaired by a background job.

----

(Just throwing some ideas around)

[pavel_pt]

+1 on views, for those things than need direct DB access.

One effective pattern I’ve seen in large DB deployments is to separate the write schema from the read schema. That is, treat what is allowed to be written into the DB separately from the shapes of any views that exist. The views themselves are a tightly coupled client of the DB log - by constraining writers, you can migrate/rebuild views, then point services to read from the migrated views, and retire old views.

This allows you to keep accepting writes - you never have to shut down the write path. If you’re introducing new shapes or the DB, you’d prepare a new view, the “widen” the write schema, and begin accepting writes in the new shape, and only then re-point clients to read from the new view.

To drop elements of your read schema, you do the dance in reverse. First, constrain your writes. Then, build new views that don’t require the elements you removed. Gradually, update application code to work on the new, reduced views. When you’re done reading from the original views, you can drop them.

This is inherently much less efficient than online or offline DB migrations. But it’s a sane strategy for wrangling very large systems with very low risk.

Versioned workflows are in practice distributed entities that interact with their peers, themselves defined by versioned interfaces. By tracking the versions of handlers touched by any given execution, we can imagine a similar experience for deployed code - including garbage-collecting unused versions.