Companies › Meta › Database Engineer
Meta Database Engineer interview questions
100 real Meta Database Engineer interview questions with model answers, key talking points, and common pitfalls — free prep for your Meta interview.
Paste the job description and your resume into SkillFitly's free resume checker to see your match score and missing skills, then practice with timed interview quizzes.
1Design the database layer for social graph storage at Meta Platforms with focus on logical schema design and normalization/denormalization. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on logical schema design and normalization/denormalization, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
2A critical query for ads delivery platforms becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on logical schema design and normalization/denormalization, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
3How would you handle correctness, transactions, isolation, idempotency, and recovery for logical schema design and normalization/denormalization in a distributed environment supporting syncing a VR device?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on logical schema design and normalization/denormalization, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
4Explain your strategy for scaling social graph storage: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on logical schema design and normalization/denormalization, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
5What operational risks such as abuse traffic spike, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on logical schema design and normalization/denormalization, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
6Design the database layer for messaging metadata at Meta Platforms with focus on indexing strategy and query access patterns. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on indexing strategy and query access patterns, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
7A critical query for messaging reliability systems becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on indexing strategy and query access patterns, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
8How would you handle correctness, transactions, isolation, idempotency, and recovery for indexing strategy and query access patterns in a distributed environment supporting loading an Instagram feed?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on indexing strategy and query access patterns, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
9Explain your strategy for scaling messaging metadata: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on indexing strategy and query access patterns, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
10What operational risks such as misinformation spread, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on indexing strategy and query access patterns, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
11Design the database layer for ad campaign and delivery records at Meta Platforms with focus on query optimization and execution-plan analysis. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on query optimization and execution-plan analysis, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
12A critical query for content moderation pipelines becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on query optimization and execution-plan analysis, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
13How would you handle correctness, transactions, isolation, idempotency, and recovery for query optimization and execution-plan analysis in a distributed environment supporting sending a WhatsApp message?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on query optimization and execution-plan analysis, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
14Explain your strategy for scaling ad campaign and delivery records: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on query optimization and execution-plan analysis, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
15What operational risks such as messaging delay, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on query optimization and execution-plan analysis, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
16Design the database layer for social graph storage at Meta Platforms with focus on transactions, isolation levels, and consistency. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on transactions, isolation levels, and consistency, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
17A critical query for Reality Labs device services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on transactions, isolation levels, and consistency, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
18How would you handle correctness, transactions, isolation, idempotency, and recovery for transactions, isolation levels, and consistency in a distributed environment supporting serving a targeted ad?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on transactions, isolation levels, and consistency, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
19Explain your strategy for scaling social graph storage: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on transactions, isolation levels, and consistency, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
20What operational risks such as ad-delivery regression, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on transactions, isolation levels, and consistency, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
21Design the database layer for messaging metadata at Meta Platforms with focus on replication, failover, and read/write splitting. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on replication, failover, and read/write splitting, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
22A critical query for social feed ranking services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on replication, failover, and read/write splitting, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
23How would you handle correctness, transactions, isolation, idempotency, and recovery for replication, failover, and read/write splitting in a distributed environment supporting moderating harmful content?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on replication, failover, and read/write splitting, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
24Explain your strategy for scaling messaging metadata: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on replication, failover, and read/write splitting, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
25What operational risks such as privacy-sensitive social graph exposure, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on replication, failover, and read/write splitting, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
26Design the database layer for ad campaign and delivery records at Meta Platforms with focus on sharding, partitioning, and hot-key mitigation. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on sharding, partitioning, and hot-key mitigation, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
When one node can't hold the data or throughput, you partition. Vertical partitioning splits columns/tables by access; horizontal partitioning (sharding) splits rows across nodes by a shard key. The shard key choice is critical: it must distribute load evenly and keep related data together. Hot keys — a few keys with disproportionate traffic — defeat sharding and need special handling.
Hash sharding for even distribution, and salting a hot key:
NUM_SHARDS = 16
def shard_for(key):
return hash(key) % NUM_SHARDS # even spread, avoids range hot spots
# hot-key mitigation: split a viral key across sub-shards, fan-in on read
def write_hot(key, val):
sub = random.randint(0, 9)
put(f'{key}#{sub}', val) # spread writes
def read_hot(key):
return merge(get(f'{key}#{s}') for s in range(10)) # gather on readPrefer a high-cardinality, evenly-accessed shard key; avoid monotonic keys (timestamps) that create range hot spots. The tradeoff: sharding scales writes but makes cross-shard queries, joins, and transactions expensive, so design the schema so the common queries stay within a single shard.
27A critical query for ads delivery platforms becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on sharding, partitioning, and hot-key mitigation, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
28How would you handle correctness, transactions, isolation, idempotency, and recovery for sharding, partitioning, and hot-key mitigation in a distributed environment supporting syncing a VR device?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on sharding, partitioning, and hot-key mitigation, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
When one node can't hold the data or throughput, you partition. Vertical partitioning splits columns/tables by access; horizontal partitioning (sharding) splits rows across nodes by a shard key. The shard key choice is critical: it must distribute load evenly and keep related data together. Hot keys — a few keys with disproportionate traffic — defeat sharding and need special handling.
Hash sharding for even distribution, and salting a hot key:
NUM_SHARDS = 16
def shard_for(key):
return hash(key) % NUM_SHARDS # even spread, avoids range hot spots
# hot-key mitigation: split a viral key across sub-shards, fan-in on read
def write_hot(key, val):
sub = random.randint(0, 9)
put(f'{key}#{sub}', val) # spread writes
def read_hot(key):
return merge(get(f'{key}#{s}') for s in range(10)) # gather on readPrefer a high-cardinality, evenly-accessed shard key; avoid monotonic keys (timestamps) that create range hot spots. The tradeoff: sharding scales writes but makes cross-shard queries, joins, and transactions expensive, so design the schema so the common queries stay within a single shard.
29Explain your strategy for scaling ad campaign and delivery records: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on sharding, partitioning, and hot-key mitigation, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
30What operational risks such as abuse traffic spike, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on sharding, partitioning, and hot-key mitigation, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
31Design the database layer for social graph storage at Meta Platforms with focus on backup, restore, point-in-time recovery, and drills. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on backup, restore, point-in-time recovery, and drills, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
A backup strategy combines periodic full backups with continuous transaction-log (WAL) archiving, which together enable point-in-time recovery (PITR) — restoring to any moment, e.g. just before an accidental DELETE. The cardinal rule: a backup you haven't tested restoring isn't a backup. Regular restore drills verify the backups are valid and measure your actual recovery time.
Point-in-time recovery to just before a bad change:
# Restore the latest base backup, then replay WAL up to a target time
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-31 09:14:00' # 1 minute before the bad DELETE
# start server -> it replays archived WAL to that exact point, then stops
pg_ctl start # DB now reflects state at the target timeStore backups in a separate region/account so a single failure or ransomware can't take both primary and backups. The tradeoff: frequent backups and long retention cost storage and add I/O, and tight RPO needs continuous archiving — size retention and backup frequency to your recovery objectives and compliance requirements.
32A critical query for messaging reliability systems becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on backup, restore, point-in-time recovery, and drills, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
33How would you handle correctness, transactions, isolation, idempotency, and recovery for backup, restore, point-in-time recovery, and drills in a distributed environment supporting loading an Instagram feed?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on backup, restore, point-in-time recovery, and drills, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
A backup strategy combines periodic full backups with continuous transaction-log (WAL) archiving, which together enable point-in-time recovery (PITR) — restoring to any moment, e.g. just before an accidental DELETE. The cardinal rule: a backup you haven't tested restoring isn't a backup. Regular restore drills verify the backups are valid and measure your actual recovery time.
Point-in-time recovery to just before a bad change:
# Restore the latest base backup, then replay WAL up to a target time
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-31 09:14:00' # 1 minute before the bad DELETE
# start server -> it replays archived WAL to that exact point, then stops
pg_ctl start # DB now reflects state at the target timeStore backups in a separate region/account so a single failure or ransomware can't take both primary and backups. The tradeoff: frequent backups and long retention cost storage and add I/O, and tight RPO needs continuous archiving — size retention and backup frequency to your recovery objectives and compliance requirements.
34Explain your strategy for scaling social graph storage: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on backup, restore, point-in-time recovery, and drills, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
35What operational risks such as misinformation spread, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on backup, restore, point-in-time recovery, and drills, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
36Design the database layer for messaging metadata at Meta Platforms with focus on high availability, disaster recovery, and RPO/RTO design. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on high availability, disaster recovery, and RPO/RTO design, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
HA and DR are distinct: HA keeps the service running through common failures (a node dies) within a region, usually via automatic failover to a standby; DR recovers from a whole-region disaster. Both are designed to two targets — RPO (max acceptable data loss) and RTO (max acceptable downtime) — which dictate the architecture and cost, from synchronous multi-AZ replicas to cross-region standbys.
Matching architecture to RPO/RTO targets:
RPO=0, RTO~seconds : synchronous multi-AZ replicas + auto-failover (high cost)
RPO~sec, RTO~minutes: async cross-region replica, promote on disaster
RPO~hrs, RTO~hours : periodic backups restored in DR region (lowest cost)
=> pick the row your business (and budget) actually requires, then test itRun DR game days so RTO is a measured fact, not a hope. The tradeoff is unavoidable: near-zero RPO/RTO requires synchronous replication and standby capacity that roughly doubles cost, so reserve the strongest tier for data whose loss or downtime is genuinely intolerable.
37A critical query for content moderation pipelines becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on high availability, disaster recovery, and RPO/RTO design, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
38How would you handle correctness, transactions, isolation, idempotency, and recovery for high availability, disaster recovery, and RPO/RTO design in a distributed environment supporting sending a WhatsApp message?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on high availability, disaster recovery, and RPO/RTO design, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
HA and DR are distinct: HA keeps the service running through common failures (a node dies) within a region, usually via automatic failover to a standby; DR recovers from a whole-region disaster. Both are designed to two targets — RPO (max acceptable data loss) and RTO (max acceptable downtime) — which dictate the architecture and cost, from synchronous multi-AZ replicas to cross-region standbys.
Matching architecture to RPO/RTO targets:
RPO=0, RTO~seconds : synchronous multi-AZ replicas + auto-failover (high cost)
RPO~sec, RTO~minutes: async cross-region replica, promote on disaster
RPO~hrs, RTO~hours : periodic backups restored in DR region (lowest cost)
=> pick the row your business (and budget) actually requires, then test itRun DR game days so RTO is a measured fact, not a hope. The tradeoff is unavoidable: near-zero RPO/RTO requires synchronous replication and standby capacity that roughly doubles cost, so reserve the strongest tier for data whose loss or downtime is genuinely intolerable.
39Explain your strategy for scaling messaging metadata: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on high availability, disaster recovery, and RPO/RTO design, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
40What operational risks such as messaging delay, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on high availability, disaster recovery, and RPO/RTO design, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
41Design the database layer for ad campaign and delivery records at Meta Platforms with focus on online migrations and schema versioning. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on online migrations and schema versioning, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Changing a schema on a live, large table without downtime requires care — a naive ALTER can lock the table for minutes. The safe pattern is expand-and-contract: make additive, backward-compatible changes first (add a nullable column), backfill in batches, deploy code that writes both old and new, then contract (drop the old) once nothing uses it. Migrations are versioned and applied in order, forward-only.
A safe, batched backfill during an expand-contract migration:
-- 1. expand: add new column (fast, non-blocking if nullable/no default rewrite)
ALTER TABLE users ADD COLUMN email_normalized TEXT;
-- 2. backfill in small batches to avoid a long lock / replication lag
UPDATE users SET email_normalized = lower(email)
WHERE id BETWEEN :lo AND :hi AND email_normalized IS NULL;
-- 3. (later) app reads new column; 4. contract: drop old once unusedTest migrations on production-sized data and keep them reversible or forward-only-with-a-plan. The tradeoff: expand-contract is more steps and temporary dual-writes than a single ALTER, but it's the price of zero downtime on a large table where a blocking change would cause an outage.
42A critical query for Reality Labs device services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on online migrations and schema versioning, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
43How would you handle correctness, transactions, isolation, idempotency, and recovery for online migrations and schema versioning in a distributed environment supporting serving a targeted ad?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on online migrations and schema versioning, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Changing a schema on a live, large table without downtime requires care — a naive ALTER can lock the table for minutes. The safe pattern is expand-and-contract: make additive, backward-compatible changes first (add a nullable column), backfill in batches, deploy code that writes both old and new, then contract (drop the old) once nothing uses it. Migrations are versioned and applied in order, forward-only.
A safe, batched backfill during an expand-contract migration:
-- 1. expand: add new column (fast, non-blocking if nullable/no default rewrite)
ALTER TABLE users ADD COLUMN email_normalized TEXT;
-- 2. backfill in small batches to avoid a long lock / replication lag
UPDATE users SET email_normalized = lower(email)
WHERE id BETWEEN :lo AND :hi AND email_normalized IS NULL;
-- 3. (later) app reads new column; 4. contract: drop old once unusedTest migrations on production-sized data and keep them reversible or forward-only-with-a-plan. The tradeoff: expand-contract is more steps and temporary dual-writes than a single ALTER, but it's the price of zero downtime on a large table where a blocking change would cause an outage.
44Explain your strategy for scaling ad campaign and delivery records: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on online migrations and schema versioning, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
45What operational risks such as ad-delivery regression, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on online migrations and schema versioning, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
46Design the database layer for social graph storage at Meta Platforms with focus on OLTP versus OLAP, warehouse, and lakehouse design. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on OLTP versus OLAP, warehouse, and lakehouse design, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
OLTP and OLAP have opposite shapes. OLTP serves many small, low-latency read/write transactions on a normalized, row-oriented store. OLAP scans huge volumes for aggregate analytics, favoring denormalized star schemas and columnar storage that reads only the needed columns. You don't run heavy analytics on the OLTP database — you pipe data into a warehouse (or lakehouse combining a data lake with warehouse features) via ELT.
A columnar warehouse fact/dimension model for fast aggregation:
-- Star schema: a wide fact table + small dimensions, columnar storage
CREATE TABLE fct_sales (
date_key INT, product_key INT, store_key INT,
quantity INT, revenue_cents BIGINT
); -- columnar: SUM(revenue) scans one column, not whole rows
SELECT d.month, SUM(f.revenue_cents)
FROM fct_sales f JOIN dim_date d ON f.date_key = d.date_key
GROUP BY d.month;Match storage to workload: row-oriented for OLTP point lookups, columnar for OLAP scans. The tradeoff: separating systems adds an ELT pipeline and some data latency, but running analytics on the OLTP store would cripple transactional performance — which is exactly why the two are kept apart.
47A critical query for social feed ranking services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on OLTP versus OLAP, warehouse, and lakehouse design, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
48How would you handle correctness, transactions, isolation, idempotency, and recovery for OLTP versus OLAP, warehouse, and lakehouse design in a distributed environment supporting moderating harmful content?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on OLTP versus OLAP, warehouse, and lakehouse design, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
OLTP and OLAP have opposite shapes. OLTP serves many small, low-latency read/write transactions on a normalized, row-oriented store. OLAP scans huge volumes for aggregate analytics, favoring denormalized star schemas and columnar storage that reads only the needed columns. You don't run heavy analytics on the OLTP database — you pipe data into a warehouse (or lakehouse combining a data lake with warehouse features) via ELT.
A columnar warehouse fact/dimension model for fast aggregation:
-- Star schema: a wide fact table + small dimensions, columnar storage
CREATE TABLE fct_sales (
date_key INT, product_key INT, store_key INT,
quantity INT, revenue_cents BIGINT
); -- columnar: SUM(revenue) scans one column, not whole rows
SELECT d.month, SUM(f.revenue_cents)
FROM fct_sales f JOIN dim_date d ON f.date_key = d.date_key
GROUP BY d.month;Match storage to workload: row-oriented for OLTP point lookups, columnar for OLAP scans. The tradeoff: separating systems adds an ELT pipeline and some data latency, but running analytics on the OLTP store would cripple transactional performance — which is exactly why the two are kept apart.
49Explain your strategy for scaling social graph storage: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on OLTP versus OLAP, warehouse, and lakehouse design, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
50What operational risks such as privacy-sensitive social graph exposure, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on OLTP versus OLAP, warehouse, and lakehouse design, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
51Design the database layer for messaging metadata at Meta Platforms with focus on NoSQL, document, wide-column, and key-value modeling. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on NoSQL, document, wide-column, and key-value modeling, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
NoSQL stores trade relational flexibility for scale and predictable performance by modeling around access patterns, not entities. Key-value is a simple, ultra-fast dictionary; document stores keep nested JSON so an aggregate loads in one read; wide-column (Cassandra/DynamoDB) partitions by key for massive write throughput. The design rule inverts SQL: know your queries first, then model tables to serve them with single-partition reads.
A DynamoDB-style single-table design keyed to the access pattern:
// Access pattern: get a user and all their orders in one query
// Partition key = USER#id, Sort key groups the item types
{ "PK": "USER#42", "SK": "PROFILE", "name": "Alex" }
{ "PK": "USER#42", "SK": "ORDER#1001", "total": 4200 }
{ "PK": "USER#42", "SK": "ORDER#1002", "total": 1500 }
// Query PK=USER#42 -> profile + all orders in one partition readDenormalize and duplicate freely, and add secondary indexes for alternate access paths. The tradeoff: NoSQL scales and is fast for known patterns but is poor at ad-hoc queries and cross-entity joins, and you own consistency in the app — so use it when access patterns are well-defined and scale demands it, not as a default.
52A critical query for ads delivery platforms becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on NoSQL, document, wide-column, and key-value modeling, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
53How would you handle correctness, transactions, isolation, idempotency, and recovery for NoSQL, document, wide-column, and key-value modeling in a distributed environment supporting syncing a VR device?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on NoSQL, document, wide-column, and key-value modeling, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
NoSQL stores trade relational flexibility for scale and predictable performance by modeling around access patterns, not entities. Key-value is a simple, ultra-fast dictionary; document stores keep nested JSON so an aggregate loads in one read; wide-column (Cassandra/DynamoDB) partitions by key for massive write throughput. The design rule inverts SQL: know your queries first, then model tables to serve them with single-partition reads.
A DynamoDB-style single-table design keyed to the access pattern:
// Access pattern: get a user and all their orders in one query
// Partition key = USER#id, Sort key groups the item types
{ "PK": "USER#42", "SK": "PROFILE", "name": "Alex" }
{ "PK": "USER#42", "SK": "ORDER#1001", "total": 4200 }
{ "PK": "USER#42", "SK": "ORDER#1002", "total": 1500 }
// Query PK=USER#42 -> profile + all orders in one partition readDenormalize and duplicate freely, and add secondary indexes for alternate access paths. The tradeoff: NoSQL scales and is fast for known patterns but is poor at ad-hoc queries and cross-entity joins, and you own consistency in the app — so use it when access patterns are well-defined and scale demands it, not as a default.
54Explain your strategy for scaling messaging metadata: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on NoSQL, document, wide-column, and key-value modeling, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
55What operational risks such as abuse traffic spike, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on NoSQL, document, wide-column, and key-value modeling, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
56Design the database layer for ad campaign and delivery records at Meta Platforms with focus on search, graph, and vector database patterns. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on search, graph, and vector database patterns, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Specialized stores fit specialized queries. Search engines (Elasticsearch) invert text into terms for fast full-text and relevance ranking. Graph databases store nodes and edges for traversal queries (shortest path, friends-of-friends) that are painful in SQL. Vector databases index embeddings for approximate nearest-neighbor search powering semantic search and recommendations. Each is a purpose-built index you keep in sync with your source of truth.
Approximate nearest-neighbor search in a vector store:
-- pgvector: index embeddings, query by similarity
CREATE TABLE docs (id BIGINT, embedding vector(768));
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
-- find the 5 docs most similar to a query embedding
SELECT id FROM docs
ORDER BY embedding <=> :query_embedding -- cosine distance
LIMIT 5;Treat these as derived indexes rebuilt/updated from the primary store, and accept approximate results for speed. The tradeoff: each specialized store adds operational surface and a sync pipeline, so introduce one only when a query pattern (text relevance, traversal, similarity) is genuinely poorly served by your primary database.
57A critical query for messaging reliability systems becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on search, graph, and vector database patterns, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
58How would you handle correctness, transactions, isolation, idempotency, and recovery for search, graph, and vector database patterns in a distributed environment supporting loading an Instagram feed?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on search, graph, and vector database patterns, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Specialized stores fit specialized queries. Search engines (Elasticsearch) invert text into terms for fast full-text and relevance ranking. Graph databases store nodes and edges for traversal queries (shortest path, friends-of-friends) that are painful in SQL. Vector databases index embeddings for approximate nearest-neighbor search powering semantic search and recommendations. Each is a purpose-built index you keep in sync with your source of truth.
Approximate nearest-neighbor search in a vector store:
-- pgvector: index embeddings, query by similarity
CREATE TABLE docs (id BIGINT, embedding vector(768));
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
-- find the 5 docs most similar to a query embedding
SELECT id FROM docs
ORDER BY embedding <=> :query_embedding -- cosine distance
LIMIT 5;Treat these as derived indexes rebuilt/updated from the primary store, and accept approximate results for speed. The tradeoff: each specialized store adds operational surface and a sync pipeline, so introduce one only when a query pattern (text relevance, traversal, similarity) is genuinely poorly served by your primary database.
59Explain your strategy for scaling ad campaign and delivery records: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on search, graph, and vector database patterns, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
60What operational risks such as misinformation spread, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on search, graph, and vector database patterns, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
61Design the database layer for social graph storage at Meta Platforms with focus on CDC, streaming ingestion, and event sourcing. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on CDC, streaming ingestion, and event sourcing, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Change Data Capture (CDC) streams a database's row-level changes (from its transaction log) to downstream systems in near real time, keeping caches, search indexes, and warehouses in sync without dual-writes. Event sourcing goes further: the log of events IS the source of truth, and current state is derived by replaying them — giving a full audit trail and time-travel at the cost of more complex reads.
CDC via log-based capture feeding downstream consumers:
// Debezium-style CDC event emitted from the DB's WAL/binlog
{
"op": "u", // insert/update/delete
"source": {"table": "orders"},
"before": {"id": 1001, "status": "pending"},
"after": {"id": 1001, "status": "shipped"},
"ts_ms": 1756630440000
}
// consumers update search index / cache / warehouse from this streamEnsure consumers are idempotent since CDC delivery is at-least-once. The tradeoff: log-based CDC is low-overhead and complete but adds a streaming pipeline to operate, while event sourcing gives auditability and replay at the cost of eventual-consistency reads and rebuild complexity — adopt each only where its benefits are needed.
62A critical query for content moderation pipelines becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on CDC, streaming ingestion, and event sourcing, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
63How would you handle correctness, transactions, isolation, idempotency, and recovery for CDC, streaming ingestion, and event sourcing in a distributed environment supporting sending a WhatsApp message?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on CDC, streaming ingestion, and event sourcing, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Change Data Capture (CDC) streams a database's row-level changes (from its transaction log) to downstream systems in near real time, keeping caches, search indexes, and warehouses in sync without dual-writes. Event sourcing goes further: the log of events IS the source of truth, and current state is derived by replaying them — giving a full audit trail and time-travel at the cost of more complex reads.
CDC via log-based capture feeding downstream consumers:
// Debezium-style CDC event emitted from the DB's WAL/binlog
{
"op": "u", // insert/update/delete
"source": {"table": "orders"},
"before": {"id": 1001, "status": "pending"},
"after": {"id": 1001, "status": "shipped"},
"ts_ms": 1756630440000
}
// consumers update search index / cache / warehouse from this streamEnsure consumers are idempotent since CDC delivery is at-least-once. The tradeoff: log-based CDC is low-overhead and complete but adds a streaming pipeline to operate, while event sourcing gives auditability and replay at the cost of eventual-consistency reads and rebuild complexity — adopt each only where its benefits are needed.
64Explain your strategy for scaling social graph storage: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on CDC, streaming ingestion, and event sourcing, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
65What operational risks such as messaging delay, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on CDC, streaming ingestion, and event sourcing, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
66Design the database layer for messaging metadata at Meta Platforms with focus on database security, encryption, RBAC, and secrets. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on database security, encryption, RBAC, and secrets, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Database security is layered: encrypt data in transit (TLS) and at rest, enforce least-privilege access with role-based access control (RBAC) so each service/user has only the grants it needs, and never embed credentials in code — fetch them from a secrets manager with rotation. Column-level encryption or masking protects the most sensitive fields even from users who can query the table.
Least-privilege RBAC grants for an application role:
CREATE ROLE app_readwrite;
GRANT SELECT, INSERT, UPDATE ON orders TO app_readwrite; -- no DELETE, no DDL
GRANT SELECT ON customers TO app_readwrite; -- read-only where enough
REVOKE ALL ON schema_migrations FROM app_readwrite; -- deny sensitive tables
-- app connects as app_readwrite with credentials from the secrets managerAudit access to sensitive tables and rotate credentials regularly. The tradeoff: fine-grained RBAC and encryption add administrative overhead and a small performance cost (encryption, masking), but they shrink the blast radius of a compromised credential and are usually mandatory for compliance — so make least privilege the default, not an afterthought.
67A critical query for Reality Labs device services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on database security, encryption, RBAC, and secrets, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
68How would you handle correctness, transactions, isolation, idempotency, and recovery for database security, encryption, RBAC, and secrets in a distributed environment supporting serving a targeted ad?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on database security, encryption, RBAC, and secrets, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Database security is layered: encrypt data in transit (TLS) and at rest, enforce least-privilege access with role-based access control (RBAC) so each service/user has only the grants it needs, and never embed credentials in code — fetch them from a secrets manager with rotation. Column-level encryption or masking protects the most sensitive fields even from users who can query the table.
Least-privilege RBAC grants for an application role:
CREATE ROLE app_readwrite;
GRANT SELECT, INSERT, UPDATE ON orders TO app_readwrite; -- no DELETE, no DDL
GRANT SELECT ON customers TO app_readwrite; -- read-only where enough
REVOKE ALL ON schema_migrations FROM app_readwrite; -- deny sensitive tables
-- app connects as app_readwrite with credentials from the secrets managerAudit access to sensitive tables and rotate credentials regularly. The tradeoff: fine-grained RBAC and encryption add administrative overhead and a small performance cost (encryption, masking), but they shrink the blast radius of a compromised credential and are usually mandatory for compliance — so make least privilege the default, not an afterthought.
69Explain your strategy for scaling messaging metadata: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on database security, encryption, RBAC, and secrets, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
70What operational risks such as ad-delivery regression, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on database security, encryption, RBAC, and secrets, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
71Design the database layer for ad campaign and delivery records at Meta Platforms with focus on audit trails, lineage, and compliance retention. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on audit trails, lineage, and compliance retention, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Compliance requires knowing who did what to which data, when — and being able to prove it. Audit trails log data access and changes to an append-only, tamper-evident store; data lineage tracks how data flows and transforms from source to report so you can answer 'where did this number come from?'; retention policies keep records for the mandated period and delete them on schedule. These are built in, not bolted on.
An audit table populated by a trigger on every change:
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY, table_name TEXT, row_id BIGINT,
action TEXT, actor TEXT, changed JSONB, ts TIMESTAMPTZ DEFAULT now()
);
CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_change(); -- writes who/what/when to audit_logProtect the audit store from tampering (append-only, restricted access) and enforce retention with automated archival/deletion. The tradeoff: comprehensive auditing adds write overhead and storage, so scope detailed auditing to regulated/sensitive tables rather than every table, and tier retention to legal requirements to control cost.
72A critical query for social feed ranking services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on audit trails, lineage, and compliance retention, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
73How would you handle correctness, transactions, isolation, idempotency, and recovery for audit trails, lineage, and compliance retention in a distributed environment supporting moderating harmful content?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on audit trails, lineage, and compliance retention, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Compliance requires knowing who did what to which data, when — and being able to prove it. Audit trails log data access and changes to an append-only, tamper-evident store; data lineage tracks how data flows and transforms from source to report so you can answer 'where did this number come from?'; retention policies keep records for the mandated period and delete them on schedule. These are built in, not bolted on.
An audit table populated by a trigger on every change:
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY, table_name TEXT, row_id BIGINT,
action TEXT, actor TEXT, changed JSONB, ts TIMESTAMPTZ DEFAULT now()
);
CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_change(); -- writes who/what/when to audit_logProtect the audit store from tampering (append-only, restricted access) and enforce retention with automated archival/deletion. The tradeoff: comprehensive auditing adds write overhead and storage, so scope detailed auditing to regulated/sensitive tables rather than every table, and tier retention to legal requirements to control cost.
74Explain your strategy for scaling ad campaign and delivery records: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on audit trails, lineage, and compliance retention, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
75What operational risks such as privacy-sensitive social graph exposure, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on audit trails, lineage, and compliance retention, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
76Design the database layer for social graph storage at Meta Platforms with focus on capacity planning, storage growth, and performance budgets. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on capacity planning, storage growth, and performance budgets, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Database capacity planning forecasts storage, IOPS, connections, and memory against projected data and traffic growth, then provisions with headroom before limits bite. You track growth trends, model when you'll hit a ceiling (disk full, connection saturation, buffer-cache misses), and act ahead of time — adding storage, tuning the cache, or introducing read replicas. Performance budgets set target latencies that guardrail changes.
Monitor growth and headroom to forecast the next limit:
-- Table + index size growth and largest objects
SELECT relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_stat_get_live_tuples(relid) AS rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
-- trend these over time -> project when storage/IOPS ceiling is hitWatch connection counts (add a pooler like PgBouncer before you exhaust them) and cache hit ratio. The tradeoff: generous headroom avoids emergencies but wastes money, while tight provisioning risks hitting a hard limit at peak — plan from real growth trends and keep enough margin to survive a spike plus a failover.
77A critical query for ads delivery platforms becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on capacity planning, storage growth, and performance budgets, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
78How would you handle correctness, transactions, isolation, idempotency, and recovery for capacity planning, storage growth, and performance budgets in a distributed environment supporting syncing a VR device?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on capacity planning, storage growth, and performance budgets, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Database capacity planning forecasts storage, IOPS, connections, and memory against projected data and traffic growth, then provisions with headroom before limits bite. You track growth trends, model when you'll hit a ceiling (disk full, connection saturation, buffer-cache misses), and act ahead of time — adding storage, tuning the cache, or introducing read replicas. Performance budgets set target latencies that guardrail changes.
Monitor growth and headroom to forecast the next limit:
-- Table + index size growth and largest objects
SELECT relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_stat_get_live_tuples(relid) AS rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
-- trend these over time -> project when storage/IOPS ceiling is hitWatch connection counts (add a pooler like PgBouncer before you exhaust them) and cache hit ratio. The tradeoff: generous headroom avoids emergencies but wastes money, while tight provisioning risks hitting a hard limit at peak — plan from real growth trends and keep enough margin to survive a spike plus a failover.
79Explain your strategy for scaling social graph storage: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on capacity planning, storage growth, and performance budgets, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
80What operational risks such as abuse traffic spike, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on capacity planning, storage growth, and performance budgets, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
81Design the database layer for messaging metadata at Meta Platforms with focus on stored procedures, scheduled jobs, and operational automation. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on stored procedures, scheduled jobs, and operational automation, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Stored procedures push logic into the database for atomicity and to cut round-trips on data-heavy operations; scheduled jobs automate recurring maintenance (aggregations, cleanup, VACUUM). The operational goal is that routine database work runs reliably without manual intervention, with monitoring and alerting if a job fails. Keep procedures focused and version-controlled like any other code.
A scheduled maintenance job that purges and re-aggregates:
CREATE PROCEDURE nightly_rollup() LANGUAGE plpgsql AS $$
BEGIN
-- delete data past retention, in batches to avoid a long lock
DELETE FROM events WHERE ts < now() - INTERVAL '90 days';
-- refresh a summary table used by dashboards
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_metrics;
END $$;
-- scheduled via pg_cron / external scheduler; alert on failureAlert on job failures and long runtimes so silent breakage doesn't rot data. The tradeoff: stored procedures reduce round-trips and enforce atomicity but scatter business logic into the database (harder to test and version than app code), so keep heavy business logic in the app and reserve procedures for data-local, set-based operations.
82A critical query for messaging reliability systems becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on stored procedures, scheduled jobs, and operational automation, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
83How would you handle correctness, transactions, isolation, idempotency, and recovery for stored procedures, scheduled jobs, and operational automation in a distributed environment supporting loading an Instagram feed?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on stored procedures, scheduled jobs, and operational automation, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Stored procedures push logic into the database for atomicity and to cut round-trips on data-heavy operations; scheduled jobs automate recurring maintenance (aggregations, cleanup, VACUUM). The operational goal is that routine database work runs reliably without manual intervention, with monitoring and alerting if a job fails. Keep procedures focused and version-controlled like any other code.
A scheduled maintenance job that purges and re-aggregates:
CREATE PROCEDURE nightly_rollup() LANGUAGE plpgsql AS $$
BEGIN
-- delete data past retention, in batches to avoid a long lock
DELETE FROM events WHERE ts < now() - INTERVAL '90 days';
-- refresh a summary table used by dashboards
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_metrics;
END $$;
-- scheduled via pg_cron / external scheduler; alert on failureAlert on job failures and long runtimes so silent breakage doesn't rot data. The tradeoff: stored procedures reduce round-trips and enforce atomicity but scatter business logic into the database (harder to test and version than app code), so keep heavy business logic in the app and reserve procedures for data-local, set-based operations.
84Explain your strategy for scaling messaging metadata: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on stored procedures, scheduled jobs, and operational automation, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
85What operational risks such as misinformation spread, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on stored procedures, scheduled jobs, and operational automation, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
86Design the database layer for ad campaign and delivery records at Meta Platforms with focus on time-series, telemetry, and observability data stores. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on time-series, telemetry, and observability data stores, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Time-series data (metrics, telemetry, IoT) is append-heavy, queried by time range, and grows relentlessly, so it needs a store optimized for it — time-partitioned tables, columnar compression, and automatic downsampling/retention. Purpose-built stores (TimescaleDB, Prometheus, InfluxDB) provide these natively, keeping recent data at full resolution and older data downsampled to control cost.
Time-partitioned hypertable with a retention/downsampling policy:
-- TimescaleDB: automatic time partitioning + retention
SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day');
-- downsample raw points into 1-minute rollups for cheap long-range queries
SELECT time_bucket('1 minute', ts) AS minute, avg(value)
FROM metrics WHERE ts > now() - INTERVAL '7 days' GROUP BY minute;
-- drop chunks older than retention automatically
SELECT add_retention_policy('metrics', INTERVAL '30 days');Bound cardinality — high-cardinality labels (unbounded IDs) explode the index and cost. The tradeoff: keeping full-resolution history is ideal for debugging but prohibitively expensive at scale, so downsample and expire old data on a schedule matched to how far back queries realistically reach.
87A critical query for content moderation pipelines becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on time-series, telemetry, and observability data stores, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
88How would you handle correctness, transactions, isolation, idempotency, and recovery for time-series, telemetry, and observability data stores in a distributed environment supporting sending a WhatsApp message?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on time-series, telemetry, and observability data stores, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Time-series data (metrics, telemetry, IoT) is append-heavy, queried by time range, and grows relentlessly, so it needs a store optimized for it — time-partitioned tables, columnar compression, and automatic downsampling/retention. Purpose-built stores (TimescaleDB, Prometheus, InfluxDB) provide these natively, keeping recent data at full resolution and older data downsampled to control cost.
Time-partitioned hypertable with a retention/downsampling policy:
-- TimescaleDB: automatic time partitioning + retention
SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day');
-- downsample raw points into 1-minute rollups for cheap long-range queries
SELECT time_bucket('1 minute', ts) AS minute, avg(value)
FROM metrics WHERE ts > now() - INTERVAL '7 days' GROUP BY minute;
-- drop chunks older than retention automatically
SELECT add_retention_policy('metrics', INTERVAL '30 days');Bound cardinality — high-cardinality labels (unbounded IDs) explode the index and cost. The tradeoff: keeping full-resolution history is ideal for debugging but prohibitively expensive at scale, so downsample and expire old data on a schedule matched to how far back queries realistically reach.
89Explain your strategy for scaling ad campaign and delivery records: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on time-series, telemetry, and observability data stores, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
90What operational risks such as messaging delay, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on time-series, telemetry, and observability data stores, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Query tuning is driven by the execution plan, which shows how the engine will run the query — which indexes it uses, join methods, and estimated vs. actual rows. The usual culprits are full scans where an index should be used, bad join order from stale statistics, and row-estimate errors. You read the plan, find the most expensive node, and fix the cause (add an index, rewrite, or refresh stats).
Reading a plan to spot a missing index:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
-- Seq Scan on orders (cost=... rows=... ) <- red flag: full scan
-- Filter: (customer_id = 42 AND status = 'open')
-- Rows Removed by Filter: 1,200,000
-- Fix: CREATE INDEX ON orders (customer_id, status); then re-EXPLAINKeep statistics fresh (ANALYZE) so the planner estimates rows correctly, and beware SARGability — a function on an indexed column (WHERE lower(email)=...) can defeat the index. The tradeoff: hand-tuning specific queries helps but can overfit, so fix systemic issues (indexes, stats) before micro-optimizing individual statements.
91Design the database layer for social graph storage at Meta Platforms with focus on data lifecycle, archival, TTL, and deletion guarantees. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on data lifecycle, archival, TTL, and deletion guarantees, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Data has a lifecycle: hot (frequently accessed, fast storage), warm, cold (archived to cheap storage), and finally deletion. Managing it controls cost and satisfies compliance/privacy mandates like a user's right to erasure. TTLs auto-expire transient data; tiering moves aged data to cheaper storage; and true deletion guarantees (including from backups and replicas) are required to honor deletion requests.
TTL-based expiry plus archival tiering:
-- Native TTL (e.g. DynamoDB/Cassandra): rows self-expire
-- ALTER TABLE sessions ... default_time_to_live = 2592000; (30 days)
-- Relational tiering: move cold partitions to cheap storage, then archive
ALTER TABLE events DETACH PARTITION events_2025_q1; -- age out old partition
-- export detached partition to object storage (Parquet), then DROP
COPY (SELECT * FROM events_2025_q1) TO 's3://archive/events_2025_q1.parquet';For deletion guarantees, propagate erasure to replicas, caches, and backups (or use crypto-shredding — delete the encryption key so backups become unreadable). The tradeoff: aggressive archival cuts cost but makes old data slower to access, so tier by real access patterns and honor deletion SLAs with an auditable, verifiable process.
92A critical query for Reality Labs device services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on data lifecycle, archival, TTL, and deletion guarantees, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
93How would you handle correctness, transactions, isolation, idempotency, and recovery for data lifecycle, archival, TTL, and deletion guarantees in a distributed environment supporting serving a targeted ad?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on data lifecycle, archival, TTL, and deletion guarantees, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Data has a lifecycle: hot (frequently accessed, fast storage), warm, cold (archived to cheap storage), and finally deletion. Managing it controls cost and satisfies compliance/privacy mandates like a user's right to erasure. TTLs auto-expire transient data; tiering moves aged data to cheaper storage; and true deletion guarantees (including from backups and replicas) are required to honor deletion requests.
TTL-based expiry plus archival tiering:
-- Native TTL (e.g. DynamoDB/Cassandra): rows self-expire
-- ALTER TABLE sessions ... default_time_to_live = 2592000; (30 days)
-- Relational tiering: move cold partitions to cheap storage, then archive
ALTER TABLE events DETACH PARTITION events_2025_q1; -- age out old partition
-- export detached partition to object storage (Parquet), then DROP
COPY (SELECT * FROM events_2025_q1) TO 's3://archive/events_2025_q1.parquet';For deletion guarantees, propagate erasure to replicas, caches, and backups (or use crypto-shredding — delete the encryption key so backups become unreadable). The tradeoff: aggressive archival cuts cost but makes old data slower to access, so tier by real access patterns and honor deletion SLAs with an auditable, verifiable process.
94Explain your strategy for scaling social graph storage: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on data lifecycle, archival, TTL, and deletion guarantees, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Schema design starts with normalization — organizing data so each fact lives in exactly one place, eliminating update anomalies and redundancy. Third normal form (3NF) is the usual target for OLTP: every non-key column depends on the key, the whole key, and nothing but the key. You denormalize deliberately, later, only where read performance demands it — duplicating data to avoid expensive joins on hot paths.
A normalized OLTP schema with a foreign-key relationship:
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer email stored once; orders reference it, not copy itDenormalize with intent (e.g. a cached order_count on customers) and keep it consistent via triggers or application logic. The tradeoff: normalization keeps writes clean and storage lean but needs joins; denormalization speeds reads at the cost of redundant data and harder updates — normalize by default, denormalize the proven hot query.
95What operational risks such as ad-delivery regression, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on data lifecycle, archival, TTL, and deletion guarantees, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Transactions give ACID guarantees so a group of operations commits all-or-nothing. Isolation levels control what concurrent transactions can see: Read Committed (the common default) prevents dirty reads; Repeatable Read prevents non-repeatable reads; Serializable prevents all anomalies as if transactions ran one at a time. Higher isolation is safer but reduces concurrency and can cause more conflicts/retries.
A transaction with explicit locking to prevent a lost update:
BEGIN;
-- lock the row so a concurrent txn can't overwrite our update
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT; -- both succeed or both roll backAt Serializable, be ready to catch serialization failures and retry the transaction. The tradeoff is correctness vs. throughput: stronger isolation eliminates anomalies but increases locking/aborts, so choose the lowest level that's still correct for the operation, and use explicit row locks (FOR UPDATE) for critical read-modify-write sequences.
96Design the database layer for messaging metadata at Meta Platforms with focus on multi-region databases and global consistency trade-offs. Include schema, indexes, access patterns, and expected query shapes.Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on multi-region databases and global consistency trade-offs, measurable outcomes, failure modes, and trade-offs. I would start with access patterns, not tables. I would identify entities, relationships, read/write paths, cardinality, consistency requirements, retention, and expected growth. The schema would normalize where correctness and reuse matter, and denormalize where read performance or isolation justifies it. Indexes should come from real predicates, join keys, sort order, and uniqueness constraints. I would plan partitioning, archiving, backups, migrations, and security from the beginning: encryption, RBAC, audit logs, and secrets rotation. A strong database design makes the common path fast while preserving correctness under failure.
Running a database across regions trades latency, availability, and consistency. Writing to a single primary region gives strong consistency but adds cross-region latency for distant users and a failover gap if that region dies. Multi-master / globally-distributed databases (Spanner, CockroachDB, DynamoDB global tables) let each region take writes, but you must then resolve conflicts and accept either synchronized-clock complexity or eventual consistency. Per CAP and its practical cousin PACELC, even without a partition you trade latency for consistency.
Choose the consistency model per operation — strong where correctness demands it, regional/eventual where latency matters:
-- CockroachDB: pin critical data to a region for low-latency strong reads
ALTER TABLE ledger SET LOCALITY REGIONAL BY ROW;
-- reads/writes for a row are served from its home region (fast + consistent)
-- DynamoDB global tables: multi-region, last-writer-wins EVENTUAL consistency
-- => fine for a shopping cart, NOT for a bank balance (use strong/single-writer there)Keep strongly-consistent, conflict-prone data (money, inventory) single-writer or use a consensus-backed store; let low-conflict data (profiles, carts) replicate eventually. The tradeoff is fundamental: you cannot have low latency, strong global consistency, and full partition-tolerance at once — decide per dataset which two matter and design around the third.
97A critical query for social feed ranking services becomes slow during peak demand. How would you inspect the execution plan, tune indexes or schema, and validate the fix?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on multi-region databases and global consistency trade-offs, measurable outcomes, failure modes, and trade-offs. I would troubleshoot the slow query methodically. First, capture the exact query, parameters, execution plan, row estimates versus actuals, wait events, locks, and recent schema or data changes. Then I would check whether predicates are sargable, indexes match filter and sort order, joins explode cardinality, or statistics are stale. Fixes may include composite or covering indexes, query rewrite, partition pruning, materialized views, batching, or schema changes. I would validate before/after p95 latency, CPU, I/O, cache behavior, and production-like load. I would not add indexes without measuring write amplification.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
98How would you handle correctness, transactions, isolation, idempotency, and recovery for multi-region databases and global consistency trade-offs in a distributed environment supporting moderating harmful content?Advanced
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on multi-region databases and global consistency trade-offs, measurable outcomes, failure modes, and trade-offs. For correctness, I would define invariants first: what must always be true despite retries, crashes, duplicate requests, and concurrent writers. Then I would choose transaction boundaries and isolation level. I would use idempotency keys, locking where appropriate, unique constraints, and outbox/inbox patterns for reliable messaging. In distributed systems, I would be explicit about consistency trade-offs, conflict resolution, and recovery. I would test concurrent workloads, failpoints, deadlocks, network partitions, and replayed events. The database should enforce critical invariants, not just application code.
Running a database across regions trades latency, availability, and consistency. Writing to a single primary region gives strong consistency but adds cross-region latency for distant users and a failover gap if that region dies. Multi-master / globally-distributed databases (Spanner, CockroachDB, DynamoDB global tables) let each region take writes, but you must then resolve conflicts and accept either synchronized-clock complexity or eventual consistency. Per CAP and its practical cousin PACELC, even without a partition you trade latency for consistency.
Choose the consistency model per operation — strong where correctness demands it, regional/eventual where latency matters:
-- CockroachDB: pin critical data to a region for low-latency strong reads
ALTER TABLE ledger SET LOCALITY REGIONAL BY ROW;
-- reads/writes for a row are served from its home region (fast + consistent)
-- DynamoDB global tables: multi-region, last-writer-wins EVENTUAL consistency
-- => fine for a shopping cart, NOT for a bank balance (use strong/single-writer there)Keep strongly-consistent, conflict-prone data (money, inventory) single-writer or use a consensus-backed store; let low-conflict data (profiles, carts) replicate eventually. The tradeoff is fundamental: you cannot have low latency, strong global consistency, and full partition-tolerance at once — decide per dataset which two matter and design around the third.
99Explain your strategy for scaling messaging metadata: partitioning, replication, backups, retention, failover, and capacity planning.Senior
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on multi-region databases and global consistency trade-offs, measurable outcomes, failure modes, and trade-offs. My scaling strategy would separate read scale, write scale, storage growth, and recovery. For reads, I would consider replicas, caching, materialized views, and query routing. For writes, I would evaluate partitioning, sharding, batching, hot-key mitigation, and data-model changes. I would set RPO/RTO targets, automate backups, regularly test restores, and document failover. Capacity planning should track data growth, index bloat, IOPS, latency, replication lag, connection count, and compaction or vacuum health. Database scaling succeeds only if recovery and correctness scale too.
Indexes make reads fast by letting the engine seek instead of scanning, but every index slows writes and consumes storage, so you index for actual query patterns, not speculatively. A composite index must match the query's filter and sort order (leftmost-prefix rule), and covering indexes that include all selected columns avoid a table lookup entirely. Analyze the real workload before adding indexes.
A composite covering index matched to the hot query:
-- Query: recent orders for a customer, only these columns
-- SELECT id, total_cents FROM orders
-- WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_orders_cust_time
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_cents); -- covering: no heap fetch neededDrop unused indexes — they're pure write overhead — and watch for low-selectivity columns where an index won't help. The tradeoff is read speed vs. write cost and storage: each index accelerates matching reads but taxes every insert/update, so keep only the indexes your queries actually use.
100What operational risks such as privacy-sensitive social graph exposure, schema drift, hot partitions, stale replicas, or data loss would you monitor, and how would you mitigate them?Intermediate
I would frame this for Meta Platforms's context: Facebook, Instagram, WhatsApp, Threads, advertising, social graph, Reality Labs, AI ranking. For Database Engineer, I would keep the answer focused on multi-region databases and global consistency trade-offs, measurable outcomes, failure modes, and trade-offs. I would monitor query, storage, replication, and data-quality risks. Key signals include p95/p99 query latency, lock waits, deadlocks, slow queries, cache hit ratio, storage growth, replication lag, backup success, restore time, hot partitions, failed migrations, and data freshness. Mitigations include query budgets, index review, partitioning, throttled migrations, read/write splitting, circuit breakers, and runbooks. For data loss, I would test point-in-time recovery and restore drills rather than assuming backups work. For compliance, I would track retention, deletion, lineage, and access auditability.
Replication copies data to standby nodes for high availability and read scaling. A primary handles writes and streams changes to replicas; on primary failure, a replica is promoted (failover). Read/write splitting routes reads to replicas to offload the primary, but you must handle replication lag — a read right after a write may hit a stale replica and not see the change.
Route reads to replicas but pin read-after-write to the primary:
def query(sql, just_wrote=False):
if is_write(sql) or just_wrote:
return primary.execute(sql) # writes + read-after-write -> primary
return replica.execute(sql) # normal reads -> replica (offload)
# after a user updates their profile, read from primary for a short window
update_profile(uid, data)
profile = query('SELECT * FROM users WHERE id=%s', just_wrote=True)Automate failover with a coordinator that fences the old primary to avoid split-brain. The tradeoff: asynchronous replication is fast but can lose the last few writes on failover (non-zero RPO), while synchronous replication guarantees no loss but adds write latency — choose per data criticality.
More Meta interview prep
Practice other Meta tracks: DevOps / SRE · AI / ML · Data Science · Software Developer / Engineer. Or browse 1,000+ general interview questions and role quizzes.