Test the Database Change and Its Actual Blast Radius
A database change can affect every SaaS customer at once, even when only a few customers see a new feature. The acceptance plan must cover the exact migration, existing data, permissions, old and new application versions, performance and recovery.
First identify the tenancy model. If customers share a table and schema, adding or changing a column is a shared schema operation. A feature flag can limit use of the new behavior; it does not turn that DDL into a five-percent schema migration. Separate tenant databases or schemas can support different rollout boundaries, but that architecture must be verified.
The procedure below is a proposed release policy for a hypothetical customer-record change. It supplies a practical acceptance brief and expected tests, not evidence that a live migration is already approved.
Build Representative Isolated Fixtures
Use an isolated database with the same relevant engine version, extensions, migration history and configuration. Confirm that its credentials and connection string cannot reach production before executing migration tests.
Synthetic data should cover actual edge cases: small and large organisations, null or unusual values, historical records, duplicate candidates, deleted relationships, long text and concurrent writes. Preserve foreign-key and tenant relationships in the fixtures. A small clean dataset can conceal the very conditions that cause a migration to fail.
If a production-derived dataset is necessary, approve and minimize it. Masking names or email addresses does not necessarily anonymize notes, attachments, identifiers or unusual combinations of data. Protect the fixture and restrict its retention. Do not paste production secrets or customer records into test logs.
Record fixture version, counts, relevant size distribution, engine version and expected values. This makes a later successful run attributable to a reproducible test environment rather than a vaguely described snapshot.
Prefer a Compatible Sequence
For a new loyalty_status field, a proposed sequence is to add a compatible optional field, deploy code that tolerates its absence or unknown state, backfill in bounded batches, verify results, and only later apply stricter constraints when evidence supports them.
Test the current application against the changed schema and the new application against each supported transitional state. Include reads, writes, exports, jobs and retry behavior. A page loading successfully does not prove that an older worker can still process queued records.
Avoid removing or renaming fields while old versions still depend on them. If a destructive change is unavoidable, define its compatibility boundary and maintenance procedure explicitly. A rollback of application code cannot recreate dropped data automatically.
For backfills, define stable batches, checkpoints and retry-safe behavior. Verify the operation affects only intended records and does not overwrite a more recent customer edit. Measure locks, query duration, write latency and worker backlog under representative load.
Keep Authorization Tests Separate From Migration Scope
RLS is an authorization mechanism, not a guarantee that an administrator's migration can touch only a canary tenant. PostgreSQL table owners normally bypass row policies; superusers and BYPASSRLS roles are exceptions too. Schema operations are also different from policy-filtered row reads. PostgreSQL row security
Inspect the migration runner's effective role and its intended write scope. Verify grants, table policies, views and functions after the schema change. Run customer allow and deny tests through the application's real credential path, including background workers with broader permissions.
Do not disable RLS on a live table as routine recovery from a failed policy test. Pause the release, reproduce the failure in isolation and repair the policy or migration while preserving the protection boundary. Where a maintenance role is required, document its authority and use it only for the approved migration.
Include a known organisation A record and a known organisation B record. A legitimate A user must retain intended access while B remains inaccessible. Tests returning no rows to every user are not sufficient.
Automate Evidence for the Exact Revision
CI can create an isolated database, apply the baseline migrations, seed fixtures, run the candidate change and execute compatibility and isolation tests. Record the commit, migration checksum, application revision, fixture version and job URL with the results.
GitHub workflow reruns use the original event's commit SHA and ref and the original triggering actor's privileges. A rerun does not automatically test a fix committed later or prove that a different owner's permissions reproduce the result. GitHub workflow rerun behavior
After changing the migration, start a run tied to the corrected revision. A rerun of the old revision may help diagnose a transient environment failure, but label it accordingly. Keep migration tests isolated from deployment jobs and avoid a test workflow that silently applies the change to production.
Collect evidence for failures as well as passes. If a retry succeeds, retain what changed between attempts; unexplained intermittent failure remains relevant to approval.
Rehearse Recovery With Its Data Boundary
Choose recovery according to the operation. A compatible forward repair can preserve new writes. An application flag can disable new behavior but cannot undo schema changes. A tested reverse migration may work for an additive change, while a restore has its own downtime and data-loss boundary.
PostgreSQL describes internally consistent SQL dumps and restoration procedures. A single-database pg_dump does not include cluster-wide role or tablespace definitions; those must be handled separately when required. Restore errors can leave a partial result, so check the full process and the resulting database. PostgreSQL SQL dump and restore
The backup chapter distinguishes dump, file-level and continuous-archiving approaches. Select a method that suits the service and verify it through a rehearsal rather than assuming any successful backup command proves recovery readiness. PostgreSQL backup approaches
Restoring a pre-migration backup can discard legitimate writes made after its recovery point. Record the latest recoverable time, expected loss window, downtime target and reconciliation procedure. Do not instruct a team to restore over the live database automatically after any failed test.
Recover into isolation first. Verify schema, selected record contents, identities, relationships, grants, policies and application read/write behavior. Check storage objects, queues and external integrations separately: a database backup does not automatically restore all service dependencies.
Filled Hypothetical Migration Acceptance Brief
Suppose a shared PostgreSQL database needs a nullable loyalty_status column and a backfill derived from an approved purchase rule. All counts and thresholds here are proposed fixtures.
| Section | Proposed plan | Evidence required |
|---|---|---|
| Change | Add compatible field, then bounded backfill | Migration checksum and reviewed SQL |
| Tenancy | Shared table and schema | DDL affects all tenants; feature flag affects behavior only |
| Fixture | 10,000 synthetic customers across several organisation sizes | Dataset version and edge-case list |
| Calculation | Approved deterministic loyalty rule | Known-input expected results |
| Compatibility | Current and candidate app revisions | Read/write, job and export tests |
| Backfill | Checkpoints and retry-safe batches | Interrupted/retried batch comparison |
| Exposure | Internal use, then proposed five-percent feature cohort | Feature flag and monitoring plan |
| Recovery | Forward repair preferred; restore rehearsed in isolation | Recovery time and loss-window evidence |
| Approval | Pending until the tested revision passes | Named release decision and supporting evidence |
The five-percent cohort is an illustrative feature exposure choice, not a provider requirement or a partial deployment of a shared schema. Choose cohort and observation period based on the operation and actual customer workload.
Acceptance Tests and Stop Conditions
| Test | Expected result | Stop or repair condition |
|---|---|---|
| Fresh baseline to candidate migration | Correct schema and deterministic data mapping | Unexpected DDL or row changes |
| Existing historical data | Nulls and edge cases handled by the approved rule | Invalid or silently coerced values |
| Current app on changed schema | Supported reads, writes and jobs still work | Compatibility failure |
| Candidate app during transition | Handles incomplete backfill safely | Requires values not yet present |
| Tenant allow and deny | Intended user sees own records; unrelated tenant denied | Cross-tenant content or metadata leak |
| Backfill interruption and retry | No duplicate effect or overwritten newer edit | Unsafe restart behavior |
| Representative load | Meets the proposed latency and lock budgets | Blocking or backlog beyond agreed limits |
| Recovery rehearsal | Approved state restored with measured time and loss boundary | Missing data, roles, files or dependencies |
| Feature cohort observation | No new unexplained integrity or authorization failures | Halt expansion and investigate |
Fill the brief with actual results after testing. Avoid an “Approved” example row that could be mistaken for signoff. A safe test plan needs both a successful path and intentional failure fixtures, such as an interrupted backfill or a foreign-key conflict.
Define the stop owner and monitoring signals before exposure. For the proposed migration, data integrity and tenant isolation failures stop expansion immediately; performance thresholds require a recorded decision. “No support tickets yet” is weak evidence because some defects remain invisible to customers.
Release and Recovery Procedure
First approve the immutable migration revision and its evidence. Confirm the backup point, maintenance permissions, observation plan and rollback or forward-repair choice. Apply only the approved shared schema change, then verify it before beginning the backfill or enabling new behavior.
If a test or observation fails, stop further exposure and preserve the failure evidence. Pause the backfill if necessary. Select the documented repair path; do not automatically disable access controls or overwrite the database from an older backup.
After recovery, compare the repaired state with the source boundary, account for writes made during the incident, and repeat compatibility and tenant tests. A green migration command or a completed restore process is only one part of acceptance.
If your business needs guidance on managing SaaS database changes, explore our SaaS development service. If you need help implementing these tests and a tailored migration acceptance brief, get in touch.
Frequently asked questions
How do we anonymise representative customer data for testing?
Prefer synthetic fixtures with representative relationships and edge cases. If using production-derived data, review all identifying and sensitive content; masking a few fields alone does not establish anonymization.
What tools can automate staged rollouts for database migrations?
Feature flags can stage application behavior. A shared-schema migration still affects that schema as a whole; independent tenant databases or schemas have different rollout options. Verify the architecture before choosing tooling.
How often should recovery procedures be tested?
Recovery tests should be performed regularly, ideally before every major migration, and after any significant infrastructure change to ensure backup integrity and restore readiness.
Can rollback scripts always restore the database to the exact previous state?
No. A reverse migration cannot necessarily recover deleted values, and restoring a backup may lose newer writes. Rehearse the selected recovery path, document its loss and downtime limits, and verify external dependencies too.
Sources
- PostgreSQL row security
- GitHub workflow reruns
- PostgreSQL backup and restore approaches
- PostgreSQL SQL dump and restore
For more on SaaS development best practices, see our SaaS development guide. To understand related website platform choices, visit CMS vs custom development. For ongoing maintenance cost insights, check website maintenance costs. Learn about user journey impacts on database changes in our user journey glossary.