Database engineer system design interview questions

As asked

You need to split a customer_name column into first_name and last_name on a large production table. How do you migrate without downtime?

Sample answer outline

Use an expand and contract migration. Add nullable new columns first, deploy application code that dual-writes old and new fields, and backfill in small batches with throttling and observability. After validating parity, switch reads to the new columns, keep dual-write briefly, then remove the old column in a later migration. Discuss locks, replication lag, rollback, and how to handle ambiguous names or bad historical data. Interviewers look for sequencing discipline; candidates often propose a single blocking ALTER and backfill that would hurt production.

Expect these follow-ups

• How would this change if the table receives 10,000 writes per second?
• What validation query proves the backfill is correct enough?
• When do you need a trigger instead of application dual-write?

As asked

Design a PostgreSQL schema to store per-second readings from 100,000 IoT sensors, each producing a (sensor_id, timestamp, value) tuple. The system must support efficient last-value queries and 30-day range aggregations. What does your schema look like and how do you handle scale?

Sample answer outline

Partition the table by time range (monthly or weekly) so old partitions can be detached and archived. Create a compound index on (sensor_id, timestamp DESC) within each partition so last-value queries are index-only scans. For aggregations, maintain a materialized rollup table at 1-minute granularity using a trigger or background job, reducing 30-day queries from 2.6 billion rows to 43 million. For extreme scale (100k sensors, 1Hz), consider TimescaleDB hypertables with compression, or a dedicated time-series store like InfluxDB or VictoriaMetrics.

Expect these follow-ups

• How does TimescaleDB's chunk exclusion improve on native PostgreSQL partitioning for time-series queries?

As asked

A product needs sub-100ms writes from users in both the US and EU without routing EU users to US servers. How do you design the database layer to support active-active writes in two regions while keeping data consistent?

Sample answer outline

True active-active with strong consistency is very hard at cross-region latency. A pragmatic approach is to partition data by user region so each region owns its subset (geo-partitioning), with cross-region reads only for cross-tenant data. For global data (e.g., feature flags), accept eventual consistency with a CRDTs or last-write-wins strategy. Spanner or CockroachDB offer globally consistent transactions but add commit latency due to Paxos consensus across regions. The candidate should address conflict resolution for the cases where global consistency is not enforced.

Expect these follow-ups

• How does CockroachDB's leaseholder model affect write latency for a key whose leaseholder is in the wrong region?

As asked

You have a primary PostgreSQL instance and three streaming replicas. Design the routing layer that decides which queries go to replicas versus the primary, handles replica lag, and fails gracefully if a replica falls behind or crashes.

Sample answer outline

A connection proxy (PgBouncer, HAProxy, or a custom application-layer router) routes reads to replicas and writes to primary. Each replica's lag is polled periodically via SELECT NOW() - pg_last_xact_replay_timestamp(). If lag exceeds a threshold (e.g., 5 seconds), the replica is removed from the read pool and reads fall back to the primary. Stale reads can be tolerated for most reporting queries; for read-your-own-writes consistency, the application can pin a session to the primary for a brief window after a write. Health checks should detect replica crashes and remove them within one check interval.

Expect these follow-ups

• How would you handle a read that must see a write committed less than 1 second ago?

As asked

A data warehouse team wants a near-real-time copy of changes from a production PostgreSQL database. Design a CDC pipeline that is low-overhead on the source and delivers changes to Snowflake within 30 seconds.

Sample answer outline

Use PostgreSQL logical decoding (pgoutput plugin) via a replication slot to stream WAL changes. Debezium or a managed CDC tool (Airbyte, Fivetran) reads from the replication slot and publishes row-level changes to a Kafka topic. A Kafka consumer reads the topic and writes micro-batches to Snowflake using the Snowflake Kafka connector or a custom COPY INTO job. Key concerns: replication slot lag (unreplicated WAL blocks vacuuming on the source), exactly-once delivery to Snowflake (use Kafka offsets plus a deduplication key), and schema evolution (handle DDL changes in the Debezium schema registry).

Expect these follow-ups

• What happens to the replication slot if the Kafka consumer stops for 6 hours?

As asked

Design a PostgreSQL HA setup that automatically promotes a replica within 30 seconds of primary failure, avoids split-brain, and requires no manual steps from an on-call engineer.

Sample answer outline

Use Patroni with a distributed consensus store (etcd or Consul) as the DCS for leader election. Patroni monitors the primary and, on failure, initiates a leader election in etcd; only the replica that wins the election promotes itself, preventing split-brain. HAProxy or a floating VIP (using keepalived) routes client connections to the current primary. Synchronous replication to at least one replica ensures no data loss during promotion. The candidate should discuss quorum requirements for etcd and the impact of DCS unavailability on the cluster.

Expect these follow-ups

• What is the difference between synchronous_commit=on and synchronous_standby_names=1 in the context of zero RPO failover?

As asked

Design a per-user API rate limiter that allows 1,000 requests per minute. It must work across 50 application servers with millisecond overhead per request and must not block a user who is under the limit.

Sample answer outline

Use a sliding window counter in Redis: store a sorted set per user keyed by timestamp, use ZADD to add the current request, ZREMRANGEBYSCORE to drop entries older than 60 seconds, and ZCARD to count. Run these three commands in a Lua script for atomicity. The response includes the count and the earliest timestamp in the set to compute a retry-after header. For lower latency, use a token bucket implemented with a hash: store last_refill timestamp and tokens_remaining, and update atomically with a Lua script. Redis pipelines minimize round trips.

Expect these follow-ups

• How does this design behave if the Redis primary fails mid-request?

As asked

You need to add a NOT NULL column with a default to a 500-million-row table on a live PostgreSQL instance that cannot afford more than 1 second of write unavailability. Design the migration sequence.

Sample answer outline

Step 1: add the column as nullable with no default (instant metadata change, no table rewrite). Step 2: backfill in batches of 10,000 rows using UPDATE ... WHERE id BETWEEN ... with a short sleep between batches to avoid I/O saturation. Step 3: add the NOT NULL constraint with NOT VALID (skips existing rows, fast). Step 4: VALIDATE CONSTRAINT in a separate transaction (takes a ShareUpdateExclusive lock, not an exclusive lock). Step 5: in a subsequent deploy, the application writes the default value for new rows, making the column effectively NOT NULL in practice. The candidate should also mention pg_repack for table rewrites that would otherwise require ACCESS EXCLUSIVE.

Expect these follow-ups

• What is the lock level required by ALTER TABLE ADD COLUMN ... NOT NULL DEFAULT in PostgreSQL 11 versus PostgreSQL 10?

As asked

An API endpoint runs an expensive PostgreSQL query on every request. You want to cache results in Redis for 60 seconds, but the underlying data can be updated by any of 20 microservices. How do you design cache invalidation that is consistent without coupling every service to the cache?

Sample answer outline

Use a CDC/event-driven approach: each microservice publishes a domain event (e.g., ProductUpdated) to a message bus. A cache invalidation service subscribes to these events, identifies which cache keys are affected using a key registry or tag-based invalidation, and deletes or re-computes those keys. Alternatively, use PostgreSQL LISTEN/NOTIFY: triggers on the relevant tables send notifications that the API layer listens for and uses to expire specific cache keys. The candidate should address the thundering herd problem post-invalidation and mention using a lock to ensure only one request repopulates the cache.

Expect these follow-ups

• How does tag-based cache invalidation work and what are its limits at scale?

As asked

You are building a social platform expecting 500 million users. Design a sharding architecture for the users table that allows efficient single-user lookups, supports cross-shard fan-out for queries like 'find mutual friends', and can add shards without full resharding.

Sample answer outline

Shard on user_id using consistent hashing to distribute users across N shards and allow adding shards with only 1/N data migration. A shard directory service (or the hashing function) maps user_id to shard. For single-user lookups, one shard is queried directly. For cross-shard queries (mutual friends), the application queries each relevant shard and merges results (scatter-gather). Maintain a denormalized friendship adjacency table per user on that user's shard to reduce fan-out. Use a virtual sharding layer (e.g., 1024 virtual shards mapped to N physical shards) to allow rebalancing by remapping virtual-to-physical without changing application routing logic.

Expect these follow-ups

• How does Vitess manage shard routing and cross-shard transactions for MySQL?

As asked

A financial platform must log every read and write to customer data, retain logs for 7 years, support point-in-time reconstruction of any record, and allow compliance officers to query logs without impacting the OLTP database. How do you design this?

Sample answer outline

Use a separate append-only audit database or table (never in the same OLTP schema) populated via triggers or application-level hooks. Each audit row captures entity_type, entity_id, operation, old_value (JSONB), new_value (JSONB), actor_id, and timestamp. Partition the audit table by month and archive old partitions to cold object storage (S3 Glacier) in Parquet format for cost efficiency. For point-in-time reconstruction, replay the audit log from the record's creation. For compliance queries, expose the audit data via a read replica or the archived Parquet files through Athena or BigQuery to avoid OLTP load.

Expect these follow-ups

• How do you prevent the audit log from being tampered with by a database administrator?