// advent-of-postgres/day-15
The Postmortem Archive
Search a year of postmortems for certificate renewals. Full-text search finds every form of a word; LIKE does not.
Alabaster is sure that expired certificates cause half the incidents at the North Pole. Before the next planning meeting, she wants a number from the postmortem archive.
Every postmortem from 2026 is in postmortems:
| Column | Type | Notes |
|---|---|---|
id |
int |
Primary key |
title |
text |
For example Incident 12: gift-router |
body |
text |
A few sentences |
published_on |
date |
When it was published |
The elves do not write the same way twice. A postmortem can talk about a renewal, say that nobody renewed a certificate, or mention a job that was renewing something. All of these count.
Your task
Use Postgres full-text search with the english configuration on the title and body together. How many postmortems match both certificate and renewal, in any form of those words?
Submit the number.
Stuck? Show a hint
to_tsvector('english', ...) @@ to_tsquery('english', 'certificate & renewal') matches renewed and renewing too, because both words are reduced to their stems.
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