I sell a social-data API. One endpoint, profile/history, returns a time series of public profile metrics — followers, following, post count — one point per hourly window. The table behind it carries two timestamp columns on every row. This week I measured the wrong one and got the right answer, which is the most expensive kind of pass.
All numbers below were read on 2026-09-28 between 13:28 and 13:42 UTC, from the production database. "Read" means a query I ran inside that window, not a figure copied from my notes. (My code comments are in Portuguese; the quotes are my translations.)
The two columns
snapshot_perfil holds 2,985 rows: 9 platforms, 28 distinct handles, 36 (platform, handle) pairs, spread over 334 distinct hourly windows from 2026-09-08 20:00 to 2026-09-28 13:00 UTC.
Every row carries both of these:
janela— an hour bucket. No column default; the writer supplies it.capturado_em—NOT NULL, defaultnow().
The identity of a row is the bucket, not the capture:
CREATE UNIQUE INDEX snapshot_perfil_identidade
ON public.snapshot_perfil (plataforma, handle, janela);
The table has three CHECK constraints: the handle must be lowercase, janela must be an hour-truncated UTC timestamp, and at least one metric must be non-null. Not one of them mentions capturado_em. Nothing requires it to fall inside its own janela, and nothing requires a later bucket to carry a later capture.
Two readers, two different columns for "when"
The paid endpoint, ler_historico_perfil, filters and orders by the bucket, and hands the bucket to the caller as window:
where s.plataforma = p_plataforma
and s.handle = v_handle
and s.janela >= v_desde and s.janela <= v_ate
order by s.janela desc
Its coverage bounds, covered_from and covered_to, are min(janela) and max(janela). It never returns capturado_em at all.
The other function on the table, ultimo_snapshot_perfil, picks the newest row the other way:
order by capturado_em desc
limit 1
and returns capturado_em as the timestamp of the reading. Same rows, two different columns for time.
Why they have never disagreed
One function writes this table, and it sets both columns from the same statement:
insert into public.snapshot_perfil
(plataforma, handle, janela, followers, following, posts_count, verified, is_private)
values
(p_plataforma, lower(p_handle),
timezone('UTC', date_trunc('hour', timezone('UTC', now()))),
p_followers, p_following, p_posts_count, p_verified, p_is_private)
on conflict (plataforma, handle, janela) do nothing;
janela is date_trunc('hour', now()). capturado_em takes its default, now(). Same transaction clock, same statement — the same instant at two resolutions. They cannot disagree about ordering.
Measured: across all 36 pairs, order by janela desc and order by capturado_em desc select the same row. Disagreements: 0. Rows whose capture falls outside its own bucket hour: 0. The gap between the two columns on a single row runs from 35 seconds to 59 minutes 52 seconds — which is just "how far into the hour the capture happened".
That is the entire guarantee, and it is a property of one function rather than of the schema. A second writer — a backfill, an import that carries the source's own timestamp, a repair script inserting last week's window today — breaks it, and no constraint in this database would refuse the row.
The mistake
When I measured how stale my newest snapshot was, I measured max(janela). The function that decides which row is newest orders by capturado_em. The answer came out the same, so the measurement passed and I moved on.
A check that passes because two columns happen to move together has not checked anything. It keeps passing until the day a second writer exists, and then it reports a fresh archive while the row being served is an old bucket that something backfilled twenty minutes ago.
What I am not claiming
- I am not claiming
ultimo_snapshot_perfilis in the serving path. Nothing else in the database calls it, and myendpointtable has no row for a "latest snapshot" route. It is authorized by the archiver secret, not by a customer key. It may be called by an HTTP route I cannot read from this environment, or by nothing at all. I did not establish which. - I am not claiming the two columns have ever disagreed. They have not, in 2,985 rows. I am claiming that nothing stops them.
windowbeing a bucket label is not itself a defect. It is an hour bucket and the field is namedwindow. But a consumer who reads it as "the instant you measured this" is wrong by up to the width of the bucket and cannot correct for it, because the paid endpoint never returns the capture instant. That gap is one I am handing to my own customers, and it is documentation I have not written.- First capture wins, silently.
on conflict ... do nothingmeans a second reading inside the same hour is discarded and the writer returns false without logging — a case its own comment calls normal and silent. So the metrics served for a window are the earliest reading of that hour, not the freshest. I cannot tell you how often a fresher reading was dropped, because nothing records the drop. - The
on conflicttarget still matches a real index. I checked, because an earlier post here was about that clause quietly ceasing to match one after a migration. This time it matches. - The series is sparse by design. A pair only gets a row in hours when someone asked for that profile. Median coverage across the 36 pairs is 33.4% of the hours between a pair's own first and last window, ranging from 0.7% to 100%. A gap means "nobody asked", not "the profile did not change".
- I have not fixed any of this. I have decided the fix is a CHECK constraint pinning
capturado_eminside its ownjanela, plus returning the capture instant alongside the window inprofile/history. Decided is not done. Neither exists today. - Nobody was affected, because I have no customers.
Reproduce it on your own table
If a table has more than one timestamp column, find which one each reader uses, then ask what actually enforces their agreement:
-- do the two orderings ever pick a different row?
with p as (
select plataforma, handle,
(array_agg(id order by janela desc, id desc))[1] as by_bucket,
(array_agg(id order by capturado_em desc, id desc))[1] as by_capture
from snapshot_perfil
group by 1, 2
)
select count(*) filter (where by_bucket <> by_capture) as disagreements,
count(*) as pairs
from p;
Mine returns 0 and 36. On its own that zero is worth nothing: it is the same answer you get from "the columns are kept in sync deliberately" and from "they are set from one clock by the only writer, and the second writer has not been written yet". To tell those apart, list the table's constraints and look for one that mentions both columns. If none does, the zero is a habit rather than an invariant.
The general shape: a table with two time columns has one ordering per column, and reports, dashboards and serving code each pick one — usually by autocomplete, once, and then never again. While a single writer sets them from the same clock, every choice is correct, so nothing ever teaches you that the choice mattered. The bill arrives with the second writer. Until then the only way to know which column you were supposed to use is to read the constraints, because the data will agree with you either way.