The slug is the primary key of the posts table. Not an integer with a slug column beside it: the slug itself, text PRIMARY KEY, with a check constraint that pins its shape to lowercase letters, digits and hyphens.
Two things fall out of that, and both are worth the constraint.
The first is that a duplicate slug is impossible rather than unlikely. There is no window in which two posts race for the same URL and the second one wins, because the second one fails.
The second is that the URL is validated at the edge of the database
The build turns each row into a file at /posts/<slug>/index.html. A slug containing a slash, a pair of dots, or a percent sign would be a path, not a name. The check constraint means the database refuses that row before anything downstream has to think about it.
This is the general shape of a constraint worth writing: not one that restates what the application already does, but one that makes an entire category of downstream bug unrepresentable. Anybody can insert into this table. Nobody can insert a slug that breaks a path.