All quizzesHard
Soft Deletes & Multitenancy — Series 3
Preview — 3 of 10 questions
Beyond row-level (shared tables, a tenant_id column) and schema-level (one PostgreSQL schema per tenant, shared database) multitenancy, what does database-per-tenant offer, and at what cost?
AIt's identical to schema-level multitenancy; "database" and "schema" are interchangeable terms in PostgreSQL
BDatabase-per-tenant is strictly worse than row-level in every dimension and is never used in practice
CDatabase-per-tenant only works with a NoSQL backend, not PostgreSQL
DDatabase-per-tenant gives the strongest possible isolation — separate connection pools, separate backup/restore boundaries, and a tenant's heavy load or a corrupted database can't directly affect another tenant at all — at the cost of the highest operational overhead: migrations must run against every database individually, cross-tenant reporting queries can't just JOIN across tenants, and connection/resource overhead scales with tenant count far more than the other two strategies
users has email TEXT UNIQUE and a soft-delete deleted_at TIMESTAMPTZ. A user deletes their account (soft-deleted), then tries to sign up again with the same email. What happens, and what's the standard fix?
ANothing needs fixing — soft-deleted rows are automatically excluded from UNIQUE constraint checks
BUNIQUE constraints automatically become case-insensitive once soft deletes are introduced, resolving the conflict
CThe fix is to permanently delete the old row before allowing a new signup, defeating the purpose of soft deletes
DThe new signup fails with a unique-violation error, because the old (soft-deleted) row still occupies that email value in the plain UNIQUE index, which doesn't know or care about deleted_at. The fix is a partial unique index instead of a plain UNIQUE constraint: CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL — this only enforces uniqueness among currently-active rows, letting a soft-deleted row's email be reused
Beyond the adjacency list (parent_id) and nested set models, what does a closure table store, and what does it make efficient?
AIt stores the same information as an adjacency list, just under a different table name
BIt replaces the need for a parent_id column, storing the tree purely as a JSON blob
CIt stores only the root and leaf nodes of the tree, discarding intermediate levels entirely
DA separate table storing every ancestor-descendant pair in the hierarchy — including a row for each node paired with itself at depth 0 — not just direct parent-child links. This makes "find all descendants of X" or "find all ancestors of X" a simple, fast indexed lookup (WHERE ancestor_id = X) with no recursion needed, at the cost of O(n log n)-ish extra storage and more complex insert/delete/move maintenance than a plain adjacency list
Sign up free to play
Answer all 10 questions (7 more), see explanations for every answer, and track your score.