Storing Story Timestamps as Text Cost Us Two Hours

We run a small aggregation service — around 40k story records in Postgres 14, single primary, one read replica. Last month a reader complained that a story from Tuesday showed up above one from Thursday in the “latest” tab. Not a sorting bug in the UI. The timestamps themselves were wrong.

The culprit: our ingestion writer had been storing published_at as text, copied straight from feed payloads. Most feeds gave us RFC 822 (Tue, 03 Jun 2025 14:22:00 GMT), some gave ISO 8601, a few gave local offsets with no zone. We sorted those strings lexicographically and it mostly worked, which is exactly why nobody looked at it for seven months.

What I did, in staging first:

-- 1. add a real column, don't touch the old one yet
ALTER TABLE stories ADD COLUMN published_at_ts timestamptz;

-- 2. backfill with explicit per-format parsing
UPDATE stories SET published_at_ts =
  CASE
    WHEN published_at ~ '^[A-Z][a-z]{2},' THEN to_timestamp(published_at, 'Dy, DD Mon YYYY HH24:MI:SS')
    WHEN published_at ~ 'Z$' THEN published_at::timestamptz
    ELSE (published_at || ' +00')::timestamptz
  END;

-- 3. find the rows that still failed
SELECT id, published_at FROM stories WHERE published_at_ts IS NULL;

Step 3 found 312 rows. Two feeds were sending GMT+0 instead of GMT, and one was sending a date with no time at all. I left those NULL rather than guessing and added a constraint so new writes must include a parseable value. Then I renamed the columns and updated the writer to parse on ingest, not on read.

The mechanism matters more than the SQL: text timestamps sort by byte order, so 2025-06-03 sorts before 2025-06-03T14:00 only if the shorter string is a prefix — and once offsets enter the string, the ordering is simply wrong. timestamptz normalizes to UTC on write, so every comparison is honest. The cost: we now reject malformed feeds at the writer instead of accepting them and sorting garbage, which means a bad upstream can drop a story that used to appear (late and wrong, but appear). I accepted that trade — a missing story is easier to notice than a misordered one.

Where this breaks: to_timestamp with Dy is locale-sensitive. Our staging cluster had en_US and the new one had en_GB; Tue parsed in both but I don’t trust it past that. If you have non-English feeds, parse in the application with an explicit library, not in SQL. Also, timestamptz on a column with no zone assumes the session timezone — my first backfill ran with SET TIME ZONE 'America/New_York' inherited from a psql profile and shifted 40 rows by five hours before I caught it. Run backfills with SET TIME ZONE 'UTC' explicitly.

The part I’m still unsure about: we kept the original text column for one release as a fallback and it drifted — someone fixed the writer but not the backfill path. If you’ve done a schema migration like this, how long did you keep the old column around before dropping it?