Database Troubleshooting and Diagnostics Questions

Systematic approaches and technical techniques for diagnosing database issues and restoring healthy operation. Topics include identifying symptoms, gathering diagnostic data from error logs and system views, analyzing slow queries with explain plans and profiling, diagnosing connection and authentication failures, detecting and resolving deadlocks and blocking, capacity and storage issues, replication and consistency problems, backup and restore verification, and corruption investigation. Candidates should be familiar with database specific diagnostic tools, monitoring and alerting metrics, indexing and query optimization strategies, and effective communication of findings to application and infrastructure teams.

HardTechnical

29 practiced

You are responsible for storage capacity planning across 500 TB of active database data. Describe how to forecast growth (per-tenant/shard), set soft and hard thresholds, automate provisioning and alerting, and run emergency procedures for storage pressure events. Include retention policy, compaction, and cost optimization considerations.

Sample Answer

Approach framework: forecast per-tenant/shard growth, define thresholds with actionable runbooks, automate provisioning/alerting, and prepare emergency procedures while optimizing cost via retention, compaction and tiering.

Forecasting (per-tenant/shard)- Collect metrics: historical daily used_bytes, write_bytes, object counts, retention deletes over 12+ months.- Model growth: use rolling 30/90-day exponential weighted moving average (EWMA) + 95th-percentile peak delta for bursts. For long-term, linear + seasonal component or ARIMA for tenants with clear seasonality.- Produce per-shard forecasts: capacity_t+N = current + expected_growth_rate * N + safety_buffer. Flag outliers (top 5% growth tenants) for quota review or shard-splitting.

Thresholds (soft/hard)- Soft (warning): 65–75% used — generate tickets, start proactive provisioning/test compaction.- Medium (action): 80–90% — throttle noncritical writes, notify tenant owners, schedule immediate provisioning.- Hard (critical): 92–95% — enforce quotas, move shards read-only if necessary, trigger emergency runbook.- Rationale: soft threshold gives time for ops; hard threshold protects availability and prevents uncontrolled failure.

Automation: provisioning & alerting- Infra-as-code (Terraform/Ansible) templates for volumes, LVM/XFS resize, PV/PVC in k8s, and object storage buckets.- Autoscale policy: precreate candidate volumes and attach via orchestrator when forecast crosses soft threshold. Use Prometheus metrics and Alertmanager with routing to runbooks and oncall.- Reconciliation job: nightly job compares forecast vs actual; auto-create PRs/tickets for human approval for large ops.- Integration: CI pipelines to validate resize scripts; use feature flags for staged rollout.

Retention, compaction, and maintenance- Retention tiers: hot (0–30d), warm (31–180d), cold (181–365d), archive (>365d). Enforce TTL policies at ingestion or in background jobs.- Compaction: schedule compaction/GC during low-traffic windows; use incremental compaction to avoid I/O spikes. Track fragmentation metrics and compaction effectiveness.- Backup & snapshot strategy: incremental backups plus periodic full snapshots; store snapshots in cheaper storage class with lifecycle to delete after retention window.

Emergency procedures for storage pressure- Automated first response: Alert → page oncall → run automated pre-checks (IOPS, inode usage, tail latencies).- Fast mitigations: enable write-throttling, evict noncritical jobs, promote replicas to spread read load, snapshot+offload largest candidates to archive, temp divert new writes to overflow bucket.- If unsafe: perform shard-split or tenant-migration to spare capacity (prebuilt automation); if not possible, set critical tenants to read-only.- Post-incident: blameless postmortem, update forecasts and thresholds, run drills quarterly.

Cost optimization- Use compression, dedupe, and block-level dedupe for DBs; prefer object storage for cold data with lifecycle policies.- Right-size tiers: move >X days cold data to Glacier/Archive. Use spot/preemptible VMs for batch compaction jobs.- Use capacity reservations or sustained-use discounts; model TCO (storage + egress + admin).- Continuous optimization: monthly cost vs usage report, mark cold tenants for archival or negotiated SLAs.

Key metrics to monitor- used_bytes per-shard, growth_rate, free_bytes, inode_usage, IO_wait, tail latency (p99/p999), backup/snapshot lag, compaction effectiveness.- Run SLA/SLOs for restore and capacity provisioning times.

This approach balances predictive forecasting, automated safe provisioning, clear thresholds/runbooks, and cost controls so storage pressure is anticipated, manageable, and recoverable.

HardSystem Design

43 practiced

A distributed database uses semi-synchronous replication and during a network partition some replicas couldn't confirm writes to the primary. Propose an operational failover strategy that balances availability versus potential data loss, including automated decision criteria, quorum handling, promotion steps, and how to recover and reconcile replicas after the partition heals.

Sample Answer

Requirements & trade-off clarification:- Goal: keep service available while minimizing irreversible data loss. Allow operators to tune preference on a per-service SLO: prefer-availability (reduce downtime, accept bounded data loss) or prefer-consistency (avoid loss, accept longer outage).

High-level strategy:1. Cluster coordination: use an external coordinator (Consul/Zookeeper/etcd) to hold a leader lock + epoch/term and act as quorum oracle.2. Quorum rule: require a majority of healthy replicas (N/2+1) reachable to perform a safe automated promotion. For semi-sync, also track last-ack LSN/GTID per replica.

Automated decision criteria:- Promote automatically only if: - A majority of replicas are reachable by the candidate node AND - Candidate's last-ack LSN is within an acceptable staleness window relative to the highest known LSN (configurable: e.g., <= 1 commit or <= T seconds), OR - Policy = prefer-availability AND candidate has at least one semi-sync ack + operator-configured max-loss threshold.- If criteria not met, failover goes to manual approval (page SREs).

Promotion steps (automated safe path):1. Candidate acquires leader lock in coordinator, increments term/epoch.2. Candidate fencing: write a fencing token and reject any old-primary writes (use TCP/IP firewall rule or cluster-wide role flag).3. Candidate enters recovery: promote to primary, enable write acceptance, broadcast new term and replication slot.4. Update monitoring and routing (DNS/LB) to redirect traffic.

Quorum handling details:- Always require majority for durable decisions. If only minority reachable, do not promote automatically unless policy explicitly allows risk and operator confirms.- Maintain per-write durability metadata (LSN, term, ack-count) so any promotion decision can compute potential data loss.

Recovery & reconciliation after partition heals:1. Old primary and lagging replicas refuse to accept writes until they verify term/epoch and receive fencing token.2. Use incremental WAL shipping / logical replication to catch up replicas: apply missing LSNs; if the replica has divergent history (writes accepted while partitioned and promoted), detect conflicts by comparing epochs/GTIDs.3. Reconciliation strategy: - If divergent writes exist and are small and idempotent -> auto-apply last-writer-wins by LSN/term. - If conflicting/non-idempotent -> surface to SREs/devs for manual merge; provide conflict report (rows/keys/operations) and tooling to replay or reverse transactions.4. Once replica caught up and term matches, revoke fencing and rejoin as follower.

Operational controls & observability:- Expose metrics: reachable-replicas, highest LSN per node, promotions, fencing events, potential data-loss estimate.- Alerting: when partition begins, on any automatic promotion, and when reconciliation requires manual intervention.- Chaos-tested runbooks and automated dry-run promotions to validate behavior.- Periodic drills and post-incident analysis to tune staleness thresholds and policy defaults.

Rationale:- Using majority + LSN bounds preserves safety for most failovers.- External coordinator and fencing prevent split-brain.- Configurable policy and clear alerts let SREs trade availability vs data-loss per SLO.

MediumTechnical

35 practiced

An application started throwing intermittent 'duplicate key value violates unique constraint' errors after a migration. Outline how you'd determine whether this is due to application-level race conditions, a migration script bug, or inconsistent data. Include SQL queries to locate duplicates, checks on migration scripts, and steps to mitigate impact in production.

Sample Answer

Situation: Production began throwing intermittent "duplicate key value violates unique constraint" errors after a migration. I would investigate three hypotheses: application race conditions, migration script bug, or pre-existing inconsistent data.

Investigation steps (high level)1. Reproduce & scope: collect error traces, timestamps, app/node IDs, SQL statements, and transaction IDs from logs. Correlate with migration run time.2. Query the DB to find duplicates and the offending constraint.3. Review migration scripts and run history (idempotency, transaction boundaries, concurrent runs).4. Mitigate impact in prod quickly, then fix root cause and harden.

SQL to locate duplicates and offending constraint- Find which constraint name is failing in application logs; or list unique constraints:

sql

SELECT conname, conrelid::regclass, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'u';

- Find duplicate key values for a specific unique index/columns (replace table, cols):

sql

SELECT col1, col2, COUNT(*) AS cnt
FROM my_table
GROUP BY col1, col2
HAVING COUNT(*) > 1;

- Inspect full duplicate rows (choose the alive row and duplicates):

sql

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY created_at DESC, id) rn
  FROM my_table
) t
WHERE rn > 1;

- If the error gives index name, find columns:

sql

SELECT indexdef FROM pg_indexes WHERE indexname = 'my_unique_idx';

Checks on migration scripts- Idempotency: ensure INSERTs use INSERT ... ON CONFLICT ... DO NOTHING/UPDATE when appropriate.- Transaction scope: verify scripts wrap related steps in a transaction if needed (and avoid long-running transactions that block).- Concurrency: confirm migration runners cannot run concurrently (check job scheduler, CI/CD hooks); look for missing advisory locks.- Data checks: did script assume uniqueness that wasn't enforced yet? Did it create a non-deferrable constraint before cleaning duplicates?- Run history: check migration logs and timestamps to see partial failures or retries.

Mitigation steps in production (fast, safe)1. Short-term: implement application retry/backoff for uniqueness errors if operations are idempotent.2. Prevent concurrent writers during cleanup: acquire a global advisory lock from app or quiesce writers (maintenance mode) for cleanup.3. Cleanup duplicates during low traffic: - Back up affected rows first. - Use the ROW_NUMBER query to delete duplicates safely:

sql

WITH duplicates AS (
  SELECT ctid
  FROM (
    SELECT ctid, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY created_at DESC, id) rn
    FROM my_table
  ) t WHERE rn > 1
)
DELETE FROM my_table WHERE ctid IN (SELECT ctid FROM duplicates);

4. Add the unique constraint/index after cleanup. To avoid locks, create the index concurrently:

sql

CREATE UNIQUE INDEX CONCURRENTLY my_table_col1_col2_key ON my_table(col1, col2);

Note: CONCURRENTLY cannot run inside a transaction and still needs exclusivity for brief moments; ensure maintenance window if heavy write load.

Longer term fixes- Make migrations idempotent and use advisory locks in migrations:

sql

SELECT pg_try_advisory_lock(hashtext('migration_name'));
-- run migration
SELECT pg_advisory_unlock(hashtext('migration_name'));

- Add tests that simulate parallel runs and validate idempotency.- Add monitoring/alerts on constraint-violation error rates and surge of 400/500 errors.- Consider making constraint DEFERRABLE if transactional ordering requires it (requires careful schema planning).

Why these steps- The duplicate-row queries identify whether data is already inconsistent (pre-existing).- Reviewing migration concurrency and idempotency distinguishes script bugs from race conditions.- Short-term mitigations minimize customer impact; adding indexes/constrains after cleanup prevents recurrence.

Edge cases & cautions- Deleting duplicates must preserve business rules (merge vs drop).- CREATE INDEX CONCURRENTLY still can fail if new duplicates are inserted during the run — ensure writers are paused or use a two-step quiesce.- If constraints already exist but errors still happen, investigate application transaction isolation and retries causing lost updates.

This approach quickly isolates root cause, resolves immediate production impact safely, and prevents recurrence via migration hardening and operational safeguards.

EasyTechnical

40 practiced

Write a PostgreSQL SQL query that lists the top 5 queries by average duration in the last 24 hours using pg_stat_statements. Include columns: query, calls, total_time, mean_time, and max_time. Provide the SQL and a short explanation on how you'd interpret the results to prioritize optimizations.

Sample Answer

Use pg_stat_statements to rank statements by average duration (pg_stat_statements stores times in milliseconds). Note: pg_stat_statements is cumulative since the extension was loaded or last reset; to get a true "last 24 hours" view you must take periodic snapshots or reset the stats 24 hours ago. This query shows top 5 by mean time, excluding very-low-call statements.

sql

SELECT
  substring(query for 200) AS query_snippet,  -- shorten for readability
  calls,
  round(total_time::numeric, 3) AS total_time_ms,
  round((total_time / NULLIF(calls,0))::numeric, 3) AS mean_time_ms,
  round(max_time::numeric, 3) AS max_time_ms
FROM pg_catalog.pg_stat_statements
WHERE calls > 1                           -- ignore one-off statements
  AND query NOT ILIKE '%pg_stat_statements%'  -- optional: exclude monitoring queries
ORDER BY mean_time_ms DESC
LIMIT 5;

How to interpret & prioritize:- mean_time_ms: average latency per call — high mean with many calls indicates a high aggregate latency/cost and is a top optimization candidate.- calls + total_time_ms: total_time = calls * mean_time; a statement with moderate mean but huge calls can consume more overall resources than a very slow rare query.- max_time_ms: shows outliers; if max is much larger than mean, investigate variability (e.g., locks, plan changes).Prioritization heuristic:1. High mean_time AND high calls → highest priority (big immediate wins).2. High total_time but moderate mean (many calls) → optimize by caching, batching, or reducing frequency.3. High max_time (sporadic spikes) → investigate locking, long-running transactions, or plan instability.Operational notes:- Capture pg_stat_statements snapshots regularly (e.g., hourly) or reset before your 24h window to get accurate windowed metrics.- Correlate with pg_locks, pg_stat_activity, and EXPLAIN (ANALYZE) to find root causes and test fixes.

HardTechnical

43 practiced

Design an index maintenance and access strategy for a high-write table that must support both low-latency OLTP and occasional complex analytics. Address auto-vacuum tuning, index type selection (btree vs BRIN), partial/covering indexes, concurrent index builds, partitioning, and how to perform rolling maintenance without blocking application writes.

Sample Answer

Requirements summary: low-latency OLTP on a very high-write table, plus occasional complex analytics. Goal: keep writes fast, keep reads (point queries) low-latency, and let analytics run without impacting OLTP or blocking writes.

Strategy (Postgres-focused):

1) Partitioning- Range or time-based partitioning by write-heavy column (e.g., created_at or tenant_id). Small partitions for hot recent data, older partitions larger.- Benefits: smaller indexes, faster vacuum per-partition, easy drop/archive of old partitions without blocking.

2) Index type selection- B-tree for OLTP point/lookup and equality/range predicates (primary key, foreign keys).- BRIN for very large, append-mostly columns that are naturally clustered (timestamp, monotonically increasing id) used by analytics WHERE on ranges — extremely low write overhead.

3) Partial & covering indexes- Create partial btree indexes for the common OLTP predicates (WHERE status='active' AND deleted=false) to keep index small.- Use covering (include) columns to avoid heap fetch for frequent projections:

sql

CREATE INDEX CONCURRENTLY idx_active_userid ON events (user_id) INCLUDE (payload_summary) WHERE deleted = false;

4) Autovacuum tuning- Reduce autovacuum cost delay on hot partitions: tune autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold per-table:

sql

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 50);

- Increase autovacuum_max_workers and lower autovacuum_naptime so vacuums run more frequently but shorter.- Use higher autovacuum_vacuum_cost_limit on dedicated DB nodes for higher throughput.

5) Concurrent index builds & online maintenance- Always build/drop indexes CONCURRENTLY to avoid write blocking:

sql

CREATE INDEX CONCURRENTLY ...
DROP INDEX CONCURRENTLY ...

- For large index rebuilds, create new index with different name then atomically swap by dropping old index after validating, or use pg_repack to reorganize without long locks.

6) Rolling maintenance with zero-write-blocking- Perform maintenance per-partition in rolling windows across replicas: - On follower replica: run REINDEX CONCURRENTLY or pg_repack, then promote or switch reads via connection routing. - For primary: prefer CREATE INDEX CONCURRENTLY on primary; if heavy, create on replica and promote/point read-only analytics to replica.- Use connection pooler routing (pgbouncer) and blue/green traffic shift for maintenance windows to avoid impacting writes.

7) Observability & safeguards- Monitor bloat, index usage (pg_stat_user_indexes), autovacuum stats, and long-running queries.- Alerts for high heap/free space, autovacuum backlog, or high index sizes.- Set SLOs: e.g., 99.9% point-query latency < X ms; track index_rw contention.

Trade-offs- More partitions and partial indexes reduce read latency but increase planning overhead and complexity.- BRIN reduces write overhead but only useful when data is physically correlated.

This approach balances write throughput (small, focused indexes, BRIN where appropriate, tuned autovacuum) with analytics capability (larger/BRIN indexes and partition-level operations), and uses CONCURRENTLY + replica-based rolling maintenance to avoid blocking writes.

Unlock Full Question Bank

Get access to hundreds of Database Troubleshooting and Diagnostics interview questions and detailed answers.

Join thousands of developers preparing for their dream job.