Here's my use case: I wrote this because I was (and am) working on a music streaming service, and I needed a youtube-like shortkey for public links to artists, albums etc. For my scale (~10,000 artists, ~120,000 tracks), I haven't run into any problems.
Its extremely easy to use uuids as the primary key; make the column a `uuid` type and the `uuid-ossp` extension will generate them. Your sql would look like:
CREATE TABLE posts (
post_id uuid DEFAULT uuid_generate_v4(),
As to whether or not primary keys should even ever be exposed in URLs, different story.