Soft Deletes & Multitenancy

Preview — 3 of 10 questions

What is the soft delete pattern and what column is typically used to implement it?

javascript

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
-- Query active users only:
SELECT * FROM users WHERE deleted_at IS NULL;

AAdding a deleted_at TIMESTAMPTZ column that is NULL for active records and set to the deletion timestamp for deleted ones

BMoving deleted rows to a separate archive table immediately on delete

CMarking rows with status = 'deleted' and using a CHECK constraint to prevent further updates

DUsing cascading deletes with ON DELETE SET NULL on all child foreign keys

Which index is most efficient for a table with soft deletes, where most queries filter on deleted_at IS NULL?

javascript

-- Partial index — only indexes active users
CREATE INDEX idx_users_active_email ON users (email) WHERE deleted_at IS NULL;

AA standard B-tree index on deleted_at

BA GIN index on deleted_at

CA partial index: CREATE INDEX ON users (email) WHERE deleted_at IS NULL

DNo index is needed since NULL comparisons are inherently fast

In single-table inheritance (STI), how are multiple entity types (e.g., Employee, Manager, Contractor) stored?

javascript

CREATE TABLE employees (
  id            SERIAL PRIMARY KEY,
  type          TEXT NOT NULL CHECK (type IN ('employee', 'manager', 'contractor')),
  name          TEXT NOT NULL,
  department_id INT,   -- only for employees/managers
  contract_end  DATE   -- only for contractors
);

AEach type has its own table with no shared columns

BAll types are stored in one table with a discriminator column (e.g., type) and type-specific columns that are NULL for rows of other types

CA junction table maps each entity row to its attribute set

DEach type inherits columns from a parent table using PostgreSQL table inheritance syntax

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