Skip to main content

// advent-of-postgres/day-15

Day 15 of 24IntermediateFull-text searchtsvector

The Postmortem Archive

Search a year of postmortems for certificate renewals. Full-text search finds every form of a word; LIKE does not.

Opens 15 December 2026, 00:00 UTC

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.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

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.