Question 1

How would you design the database architecture for a multi-tenant SaaS application?

Accepted Answer

Choose based on isolation needs and scale: separate database per tenant (maximum isolation, expensive at scale), shared DB with separate schemas (balance), or shared schema with tenant_id + Row-Level Security (most efficient, requires careful RLS policy design). Separate DB: easy isolation, hard to manage 10K DBs. Separate schema: PostgreSQL schemas are lightweight; pg_bouncer pools connections. Shared schema + RLS: one table, PostgreSQL RLS enforces tenant isolation at the DB layer. Choice depends on regulatory requirements (healthcare = separate DB), tenant count, and pricing model.

Question 2

How does the CAP theorem guide your database selection for a distributed system?

Accepted Answer

During a network partition you can only have C or A — not both; CP databases (ZooKeeper, HBase) reject requests to stay consistent; AP databases (Cassandra, DynamoDB) accept writes on all nodes and converge later. Network partitions happen — you must choose: CP (bank transactions, inventory — correctness over availability) or AP (shopping cart, social feed — availability over momentary inconsistency). Most real systems need 'PACELC': even without partition, there's an E-C trade-off between latency and consistency.

Question 3

How do you perform a schema migration on a large production database without downtime?

Accepted Answer

Use the expand-contract pattern: add new column as nullable → backfill data in batches → deploy new application code reading/writing both → make column NOT NULL → drop old column — use tools like Flyway, Liquibase, or gh-ost. ALTER TABLE on a 500M row table locks for hours. Expand-contract: all phases are online. gh-ost (GitHub's Online Schema change) creates a shadow table, copies data, applies binlog changes, and does an atomic rename — zero downtime. Blue-green deployments also help isolate schema changes.

Question 4

When would you choose a graph database over a relational database?

Accepted Answer

When data has deeply connected relationships and you need traversals that would require many expensive JOINs in SQL — e.g., social networks, fraud detection, recommendation engines, knowledge graphs. SQL: 'friends of friends of friends' = 3 self-joins, exponential cost. Neo4j: Cypher traversal stays O(log n) per hop. Graph DBs shine for: fraud rings (connected entity detection), recommendation graphs, network topology, access control hierarchies. Poor fit: simple CRUD, aggregations, reporting.

Question 5

What is Row-Level Security (RLS) and how does it enable multi-tenancy in PostgreSQL?

Accepted Answer

RLS enforces data access policies at the row level based on the current database user or session variable — each tenant only sees their own rows, enforced transparently by the database engine. CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::uuid). SET LOCAL app.tenant_id = '...'. Every query automatically filters by tenant. No application-level WHERE clause needed — the DB enforces it. Works with Supabase, which exposes this pattern natively.

DBMS & SQL — Tech Lead Interview Questions

More Tech Lead Topics

DBMS & SQL at Other Levels