How Do You Design a Robust Database Health Check?
Learn how to design database health checks using liveness queries, replication lag checks, and failure thresholds.
Expected Interview Answer
A robust database health check goes beyond a simple TCP ping by verifying that the database can actually execute a real query within an acceptable time, checking replication status on replicas, and using consecutive-failure thresholds so a single slow response does not trigger a false failover.
Health checks typically operate at multiple layers: a connectivity check (can a connection be opened at all), a liveness query (does a lightweight SELECT return within a timeout, proving the engine itself is responsive and not just listening on a port), and a replication check (is a replica within an acceptable lag window of the primary, since a stale replica should not be promoted or serve reads that require freshness). Good designs also separate 'is this node healthy' from 'should this node accept traffic,' since a node might be technically alive but degraded (very high replication lag, disk almost full) and should be pulled from rotation without being declared fully dead.
- Catches real failures, not just closed ports
- Avoids false failovers from transient slowness
- Distinguishes degraded nodes from fully dead ones
- Feeds accurate signals into failover and load-balancing decisions
AI Mentor Explanation
A shallow health check is like a coach only confirming a player showed up to the ground, without checking if they can actually bowl a legal delivery. A robust health check is like the coach also watching them bowl a few practice overs and checking their run-up timing before match day, confirming they can genuinely perform, not just physically stand on the field. Only after several consistently poor overs, not one bad ball, does the coach decide to bench the player, mirroring a consecutive-failure threshold before declaring a node unhealthy.
Step-by-Step Explanation
Step 1
Check basic connectivity
Verify a TCP connection to the database port can be established within a short timeout.
Step 2
Run a lightweight liveness query
Execute a trivial SELECT to confirm the engine itself is responsive, not just listening on the port.
Step 3
Check replication lag on replicas
Query replication status to ensure a replica is within an acceptable staleness window before serving reads or being eligible for promotion.
Step 4
Apply a consecutive-failure threshold
Require several consecutive failed checks before declaring the node unhealthy, avoiding false failovers from transient slowness.
What Interviewer Expects
- Awareness that a TCP-only check is insufficient
- Mention of a real liveness query with a timeout
- Knowledge of replication-lag checks for replicas
- Understanding of consecutive-failure thresholds to avoid false positives
Common Mistakes
- Relying only on a port/connectivity check
- Triggering failover on a single failed check
- Not distinguishing a degraded node from a fully dead one
- Ignoring replication lag when deciding whether a replica can be promoted
Best Answer (HR Friendly)
“A good database health check does more than confirm the server is reachable, it actually runs a small real query to prove the database can respond, and for replicas it checks how far behind they are. It also waits for a few consecutive failures before declaring the database down, so a single slow moment does not trigger an unnecessary failover.”
Code Example
-- Liveness check: a trivial query with a short timeout
-- (run by the monitoring agent, timeout enforced client-side)
SELECT 1;
-- Replication lag check on a PostgreSQL replica
SELECT
now() - pg_last_xact_replay_timestamp() AS replication_lag;
-- if replication_lag exceeds the acceptable threshold (e.g. 10s),
-- mark the replica as degraded rather than eligible for promotionFollow-up Questions
- How would you set an appropriate consecutive-failure threshold?
- What is the difference between a liveness check and a readiness check?
- How should a health check treat a replica with high replication lag?
- How do you avoid a health check itself overloading the database?
MCQ Practice
1. Why is a TCP connectivity check alone insufficient for a database health check?
A database can accept TCP connections while being unable to actually execute queries, so a real liveness query is needed.
2. What is the purpose of a consecutive-failure threshold in health checks?
Requiring multiple consecutive failed checks prevents a brief network blip from causing an unnecessary failover.
3. Why should a replica’s replication lag be checked before promotion eligibility?
Promoting a replica that is far behind the primary can permanently lose recently committed writes.
Flash Cards
Why is a TCP ping insufficient as a health check? — The port can be open while the database engine itself cannot execute queries.
What does a liveness query check? — That the engine responds correctly to a real, lightweight query within a timeout.
Why check replication lag? — To avoid promoting or reading from a replica that is too far behind the primary.
Why use a consecutive-failure threshold? — To avoid a false failover from one transient slow or failed check.