// advent-of-postgres/day-4
The Longest Outage
Health checks run every minute. Find the longest unbroken run of failures, not the service that failed the most in total.
Sugarplum is writing the outage report for 4 December. The status page has to show the single worst outage of the day, and the health checker is the only record.
The checker writes one row per service per minute to health_checks:
| Column | Type | Notes |
|---|---|---|
service |
text |
Part of the primary key |
checked_at |
timestamptz |
Every minute on 4 December |
ok |
boolean |
false when the check failed |
An outage is a run of failed checks in a row for one service. One passing check ends it. Each failed check counts as one minute, so 12 failed checks in a row is a 12-minute outage. A service can have many short outages and still not have the longest one.
Your task
How many minutes long was the longest outage of the day, across all services?
Submit the number of minutes.
Stuck? Show a hint
Number the checks per service, and number them again per service and ok value. The difference stays the same inside a run.
Download today's data as SQL to solve it on your own Postgres (14 or later).
Solved this puzzle? Share it.
Tag @thedevopsdaily and share it with the community.
We earn commissions when you shop through the links below.
Bitrise
Managed runners for GitHub Actions
Bitrise Build Hub runs your GitHub Actions jobs on M4 Macs and AMD Linux machines. Change runs-on and keep your workflows, secrets and logs on GitHub.
Atomsized
AWS platform engineering and GitOps
Design and automation for reliable AWS and Kubernetes platforms, safer delivery workflows, and preview and UAT environments your engineers can understand and own.
DigitalOcean
Cloud infrastructure for developers
Simple, reliable cloud computing designed for developers
DevDojo
Developer community & tools
Join a community of developers sharing knowledge and tools
SMTPfast
Developer-first email API
Send transactional and marketing email through a clean REST API. Detailed logs, webhooks, and embeddable signup forms in one dashboard.
QuizAPI
Developer-first quiz platform
Build, generate, and embed quizzes with a powerful REST API. AI-powered question generation and live multiplayer.
Want to support DevOps Daily and reach thousands of developers?
Become a Sponsor