Staged rollouts: an expensive change outside the deployment

You’ve got a change that’s right, decided, reviewed — and too expensive to run. Rebuilding a big index with page compression. Moving a full-text index to a new catalog and waiting for it to repopulate. On one database it eats most of a maintenance window. On two hundred tenant databases it eats the quarter. So the DBAs do it by hand, a few databases a window, over weeks.

And the package is stuck in the middle. Declare the new shape, and the next deploy forces the conversion on every database at once — inside the deployment window, the one thing nobody can afford. Keep the old shape, and the next deploy undoes every conversion the DBAs already finished. The usual escape is to pull the table out of source control until the rollout’s over, and hope somebody remembers to put it back.

There’s a cleaner way, and you’ve already met the lever. Let’s put both shapes in the package and let each database pick.

Declare both shapes

Here’s the SQL Server version from the lab: a reporting index on dbo.Sale, moving to PAGE compression. Two entries, same index name, each with a VariantName and a ShouldApplyExpression:

"Indexes": [
  { "Name": "PK_Sale", "PrimaryKey": true, "Clustered": true, "IndexColumns": "SaleId" },
  {
    "Name": "IX_Sale_Report",
    "VariantName": "Before conversion",
    "IndexColumns": "SoldAt",
    "IncludeColumns": "Amount",
    "ShouldApplyExpression": "EXISTS (SELECT 1 FROM sys.indexes i JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE i.object_id = OBJECT_ID('dbo.Sale') AND i.name = 'IX_Sale_Report' AND p.data_compression_desc <> 'PAGE')"
  },
  {
    "Name": "IX_Sale_Report",
    "VariantName": "Converted",
    "IndexColumns": "SoldAt",
    "IncludeColumns": "Amount",
    "CompressionType": "PAGE",
    "ShouldApplyExpression": "NOT EXISTS (SELECT 1 FROM sys.indexes i JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE i.object_id = OBJECT_ID('dbo.Sale') AND i.name = 'IX_Sale_Report' AND p.data_compression_desc <> 'PAGE')"
  }
]

Read the gates carefully, because the direction is the whole trick. Before conversion applies only where the old shape is already deployed. Converted is its exact negation — it applies everywhere else. So exactly one variant matches any database, whatever state it’s in, and each one describes what’s already there. Nothing to change. Nothing to undo.

Write them the other way round — gate the new shape on “already converted”, the old one on its negation — and a brand-new database lands on the old shape. It joins the backlog on its first deploy. Gate on what the old shape looks like, and a new database falls through to the new shape for free.

Ship it, and nothing happens

That’s the package you ship for the whole rollout. Every release in the meantime carries the rest of its changes and leaves this index alone. Let’s quench two unconverted tenants:

[localhost,11433].[cookbook_r11_t1] Successfully Quenched
[localhost,11433].[cookbook_r11_t2] Successfully Quenched

No rebuild, no drop, no index work at all. Both tenants match Before conversion, and a declaration that matches what’s deployed has nothing to strike.

The DBAs convert on their schedule

Now a DBA takes t1 in a window of their own:

ALTER INDEX IX_Sale_Report ON dbo.Sale REBUILD WITH (DATA_COMPRESSION = PAGE);

No deploy involved. Meanwhile a new customer signs up and t3 gets created. Next release goes out to the whole mixed fleet:

[localhost,11433].[cookbook_r11_t3]         Adding new table [dbo].[Sale]
[localhost,11433].[cookbook_r11_t3]         Creating constraint [dbo].[Sale].[PK_Sale]
[localhost,11433].[cookbook_r11_t3]         Creating index [dbo].[Sale].[IX_Sale_Report] (variant: Converted)

That’s the only index work in the log. t1 now matches Converted, so the DBA’s rebuild stands. t2 still matches Before conversion, so nobody converts it behind anyone’s back. And t3 was born converted — it never joins the backlog. Three tenants, two shapes, zero surprise rebuilds.

Let’s prove it — compression on each tenant:

t1: PAGE
t2: NONE
t3: PAGE

Close it out with one definition — no gate

When the rollout’s done, the package should say one thing again. Replace the two variants with the new shape, and drop its gate too:

{ "Name": "IX_Sale_Report", "IndexColumns": "SoldAt", "IncludeColumns": "Amount", "CompressionType": "PAGE" }

Don’t keep the gate “just in case.” I tried it while building the lab, and it bit. On a straggler the gate reads false, so the index is declared nowhere for that database. A component no variant declares is a component removed from the product, and the deploy drops it. The index came back on the following deploy — but for one release that tenant ran with no index at all. The last definition is unconditional. That’s what makes it the last one.

Before you deploy it, ask who still has the old shape. --WhatIf answers per database (trimmed to the statement that matters):

[localhost,11433].[cookbook_r11_t2]       ALTER INDEX [IX_Sale_Report] ON [dbo].[Sale] REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE);

Only t2 shows work. In a real fleet, that list is the DBAs’ remaining backlog. Hand it over and keep shipping the two-variant package until the preview comes back empty. Then the finished definition deploys as a no-op everywhere, and source control was accurate the whole way through.

Same pattern, every engine

The lab runs this on all four sandbox engines, each with a change that costs real time on a big table:

Engine	The expensive change	The old shape’s gate reads
SQL Server	rebuild `IX_Sale_Report` with `PAGE` compression	`sys.partitions.data_compression_desc`
PostgreSQL	move a `jsonb` GIN index to `jsonb_path_ops`	`pg_indexes.indexdef`
MySQL, MariaDB	widen `FULLTEXT ft_document` from `Title` to `Title, Body`	`information_schema.statistics`

On MySQL and MariaDB the variants live in FullTextIndexes, and the gate checks that the index exists and lacks Body:

"ShouldApplyExpression": "EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'Document' AND index_name = 'ft_document') AND NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'Document' AND index_name = 'ft_document' AND column_name = 'Body')"

The Converted gate is NOT ( that ). Same shape, different catalog. Moving a SQL Server full-text index to a new catalog is the same pattern again. The Staged rollouts guide works that example, gates on sys.fulltext_catalogs, and the four steps don’t change.

When the deploy can convert

Gating on the live shape keeps scheduling out of the package entirely: the DBAs decide, the deploy follows. If a deployment window is a safe slot for some databases, gate on a control table instead. A DBA sets a row to schedule a database, and the deploy performs the conversion. One rule: keep the new shape’s gate true once a database has converted — “the row is set or the new shape is already deployed” — or clearing the row later converts it straight back.

Check yourself: You believe every database is converted, so you delete the old variant, leave the Converted variant's gate in place, and deploy without a preview. One tenant was missed. What happens to it, and what should the finished definition look like?

On the missed tenant the old shape is still deployed, so the Converted gate evaluates false. With the old variant gone, no variant declares the index for that database, and an undeclared index is removed from the product: the deploy drops it, and the index only returns on the deploy after that. The finished definition carries no gate. It’s the only shape now, so on a straggler the deploy converts it instead of dropping it, and a --WhatIf run first lists that conversion so nobody is surprised by it.

That’s the staged rollout. An expensive change used to force a bad choice: convert everything inside one window, or lie to source control until the DBAs finished. Now the package tells the truth the whole time. Both shapes are declared, the database’s own state picks between them, and every deploy leaves the metal exactly as it found it. The DBAs set the pace. New tenants start converted. --WhatIf names whatever’s left, and one clean definition closes it out.

Got a change you’ve been parking outside source control because it’s too heavy for the window? Email me at forgebarrett@schemasmith.com — I read every one. More’s coming from the forge.

Until then, may your fleet temper one blade at a time, and no deploy undo the work already done.

— Forge

Mark this lesson complete