One customer organization asks you to roll their data back to yesterday's state after a bad bulk import, and another asks for complete deletion of their data at the end of their contract. How does the multi-tenant storage layout - shared tables with a tenant_id column, one schema per tenant, or one database per tenant - change how you satisfy each request?

## The idea: what is the recovery unit? Backups and point-in-time recovery operate on a **database or instance**, not on a customer. Whether "restore one tenant" is easy therefore depends on whether the tenant *is* a recovery unit. This is where the tenancy models differ most sharply in daily operations, and it is a question interviewers use because it exposes whether a candidate has actually run a multi-tenant system. ## Restoring one tenant **Database per tenant.** The tenant is the recovery unit. Restore that database to the requested timestamp with ordinary point-in-time recovery; nobody else is affected, and the tenant can even have its own backup schedule and retention. Simple and fast to reason about. **Schema per tenant.** The recovery unit is the whole instance, so: restore a copy of the instance to the target time (typically into a temporary instance), export the single tenant's schema from it, then swap it into production - rename the damaged schema aside, load the recovered one, verify, drop the old. Downtime is confined to that tenant. The cost is one full restore per request: fine at low frequency, painful weekly. **Shared tables.** Hardest. Restore the whole database to a side copy at the target time, then extract that tenant's rows from every tenant-owned table and merge them back into production. The details are where it goes wrong: rows must be inserted parents-before-children to satisfy foreign keys and removed in the reverse order; sequences and surrogate ids may collide with rows created since; you must define the semantics of legitimate changes made *after* the bad event; and the operation must not lock tables for other tenants, so it runs in batches. This is why teams running pooled tables build a **tenant export/import tool** early - the same tool serves restore, offboarding export, and moving a tenant to another database. A cheaper mitigation for the common cause (a bad bulk import) is application-level soft delete or versioned import batches, so an import can be reverted logically without touching a backup at all. ## Deleting one tenant **Database per tenant:** drop the database, revoke its credentials, remove it from the routing directory. **Schema per tenant:** drop the schema, which removes its tables in one statement. **Shared tables:** DELETE from every tenant-owned table in dependency order, or rely on cascading foreign keys from a tenant root row, batched by key ranges with frequent commits so you neither hold long locks nor build one enormous transaction. Expect table and index bloat afterwards, requiring vacuum or reorganization to release space. Partitioning by tenant, where it fits, turns this into a metadata operation, but for tens of thousands of small tenants that is usually impractical. ## What every layout still owes you Deletion requests - contract exit, or a legal erasure request - are not satisfied by removing live rows alone: - **Backups** taken before the deletion still contain the data, and you cannot surgically edit them. The accepted answers are to let the documented retention window expire, or **crypto-shredding**: encrypt each tenant's data under a per-tenant key and destroy the key, rendering every copy unreadable. - **Replicas and standbys** apply the deletion automatically, but logical copies - analytics warehouse, data lake, search index - need their own deletion path. - **Derived and external stores:** caches, object storage, exported reports, message queues, and audit or application logs holding tenant payloads. - **Ordering and proof:** run deletion as a tracked job with a per-store checklist, producing an artifact you can show the customer or an auditor. ## The interview-worthy summary The restore and delete story is a first-class input to the tenancy decision, not an afterthought. If per-tenant recovery is a contractual promise or a frequent support request, that pushes hard toward a database per tenant - or toward pooled tables *plus* a purpose-built export/import tool and soft-delete semantics that make the common cases recoverable without touching a backup.