Postgresql

The search box on the media library returned results in 800 milliseconds on staging. Production had forty times more rows. The query plan showed a sequential scan: no index involved, no way to fix it with a standard B-tree. The product team also wanted multi-word search: type “interview president”, get results containing both words. A LIKE query with wildcards has no clean way to express that without multiple independent conditions, each requiring its own scan. ...

Every content update on the platform creates a revision. That’s by design: editors need a history they can roll back to, and the platform needs an audit trail. What nobody anticipated was the rate. Some articles go through forty saves in a single afternoon. A high-traffic piece accumulates hundreds of revisions over its lifetime. After a few months, the revision table had several million rows. Deleting them naively wasn’t an option. “Keep the last 50” loses all historical context for articles that haven’t been touched in a year. “Keep one per day” loses all the detail for content that’s actively being edited. What we needed was a distribution that matched how revisions are actually used: dense coverage for recent history, sparse coverage for old history. ...