Database Patterns — Series 2

Preview — 3 of 10 questions

What sequence makes this safe?

javascript

Goal: rename `users.name` to `users.full_name` while old and new
application versions run simultaneously during a rolling deploy.

ARun the rename migration first, then deploy the new code — the window is short enough not to matter

BDeploy the new code first, then rename; the old instances will fail gracefully

CRename inside a transaction, which makes it invisible to other connections until commit

DExpand, then contract: add full_name and write to both columns; backfill; switch reads to the new column; once no running version references name, drop it in a later deploy

A user is soft-deleted and tries to register again with the same email. What happens, and what is the fix?

javascript

@Column({ unique: true }) email: string;
@DeleteDateColumn() deletedAt: Date | null;

AIt succeeds — TypeORM excludes soft-deleted rows from unique checks

BIt fails: the soft-deleted row still occupies the unique index, so the constraint rejects the new registration. A partial unique index (UNIQUE (email) WHERE deleted_at IS NULL) restores the intended behaviour while keeping the historical row

CIt succeeds, and the old row is silently restored

DIt fails, and the only remedy is to hard-delete the original row

Which index serves this best?

javascript

-- hot query
SELECT id, status FROM orders
WHERE tenant_id = $1 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

ACREATE INDEX ON orders (tenant_id, status, created_at DESC) INCLUDE (id) — the leading equality columns narrow the scan, created_at DESC satisfies the ordering without a sort, and the included column lets the query be answered from the index alone

BThree separate single-column indexes on tenant_id, status and created_at

CA single index on created_at DESC, since that is what the query orders by

DNo index — LIMIT 20 makes the query cheap regardless

Answer all 10 questions (7 more), see explanations for every answer, and track your score.