9,104 rows in, 5,000 out: the silent cap that made my dashboard lie

I sat down to add a small thing to an internal dashboard: a NEW badge on a search-rank table, so you could tell "this keyword just broke into the rankings" from "this keyword didn't move." An afternoon of work.

Then I went looking for the data to base it on, and found that the table had been reading 55% of its own rows for the last four days, without saying so.

What was actually happening

The page pulled raw daily rows through a generic query endpoint and folded them in the browser. The request asked for 8,000 rows. The server had this:

_MAX_LIMIT = 5000
...
limit = max(1, min(limit, _MAX_LIMIT))

min(). Not a 400, not a warning header, not a truncated: true in the response body. It hands you 5,000 rows and a 200 and lets you assume that's everything.

The table had 9,104 rows — 9 days of collection at roughly 1,000 keywords a day. So 4,104 rows never arrived. And because the query carried no ORDER BY, which 5,000 arrived was entirely up to the planner.

Both halves are needed for it to really hurt. A cap with a deterministic sort truncates the tail, which is at least a bug you can reason about: you know you're missing the oldest, or the lowest-ranked, or whatever your sort key implies. A cap with no sort hands you an arbitrary sample and presents it as the whole population.

Why nobody noticed

The dashboard didn't render an error. It rendered a dash.

Rank change is computed by comparing the current window against the previous one. If the previous window's rows happen to be the ones that fell off the truncated edge, there's nothing to compare against, so the cell shows —. Which is exactly what a keyword whose rank didn't change looks like.

That's the part worth stealing from this: the failure didn't surface as missing data, it surfaced as "no change." Absence and zero rendered identically, and one of them is boring enough that your eye slides right over it.

There's a second reason it stayed hidden, and it's the one I keep thinking about. At 1,000 rows a day, this code was correct on launch day. Day 4 fits under the cap. Day 5 fits. Day 6 crosses 5,000 and the dashboard starts lying, a little more each morning. There is no deploy to correlate it with, no error rate to alert on, no bad commit to bisect to. The bug arrives on a schedule you set months earlier and forgot about.

The fix is boring, and the payload got smaller

Stop shipping raw daily rows to the browser at all. Fold the window in SQL. Postgres DISTINCT ON gives you the latest measurement per key in a single pass:

WITH cur AS (
  SELECT DISTINCT ON (kind, keyword)
         kind, keyword, day::date AS day, rank
    FROM naver_site_rank
   WHERE day::date > %s AND day::date <= %s      -- current window
   ORDER BY kind, keyword, day::date DESC
), prv AS (
  SELECT DISTINCT ON (kind, keyword)
         kind, keyword, day::date AS prev_day, rank AS prev_rank
    FROM naver_site_rank
   WHERE day::date > %s AND day::date <= %s      -- previous window
   ORDER BY kind, keyword, day::date DESC
)
SELECT c.*, p.prev_rank, p.prev_day,
       (p.prev_day IS NOT NULL) AS measured_prev
  FROM cur c
  LEFT JOIN prv p ON p.kind = c.kind AND p.keyword = c.keyword

(Windows are half-open — (from, to] — so the current and previous window can't both claim the boundary day.)

One row per key means the response size is bounded by how many keywords exist, not by how long you've been collecting. About 1,300 rows and 275 KB, whether you ask for a week or a month or next year. That's smaller than the broken 5,000-row response, and it's complete.

The rule I wrote into the repo's guide afterward: if the row count grows with the date range, fold it on the server. Anything else is a cap waiting to be crossed, and you won't be watching on the day it happens.

The second bug, which was worse than the first

With real data finally reaching the page, I could compute the NEW badge. It came out wrong anyway.

The keyword sample rotates. There are 1,308 keywords total and the collector measures about 1,000 of them per day, so any given keyword is simply absent from some days. If you read absent as "wasn't ranking," every gap in the rotation becomes a triumphant new entry. On a weekly window that inflated NEW from 10 to 47.

So the state isn't a boolean, it's three-valued:

def _is_new(row, has_baseline):
    """Ranked now, and last window we LOOKED and it wasn't there."""
    if not has_baseline or not row["measured_prev"]:
        return False   # not measured is unknown, not "wasn't there"
    return row["rank"] is not None and row["rank_prev"] is None

• it was ranked
• it was measured, and it wasn't ranked
• we didn't look

Only the middle one can produce a NEW. The third has to stay unknown, which the UI draws as ? rather than as an arrow.

has_baseline covers the degenerate case: if the previous window contains no measurements at all — pick "month" on day 9 of collection and it won't — then the honest answer isn't "everything is new," it's "can't compare." The page says that in words and draws no arrows at all.

I pulled that predicate into its own function specifically so I could pin it in a test. It's the kind of rule that gets quietly re-broken by the next person who thinks a NULL rank means rank zero.

Takeaways

• A cap that returns 200 is a lie generator. If you clamp, say so in the response. {"truncated": true, "cap": 5000} costs you one key and saves someone a day.
• LIMIT without ORDER BY is sampling, not truncation. The Postgres docs say it plainly: without an ORDER BY that constrains the rows into a unique order, you get "an unpredictable subset". Which is indistinguishable from complete data at the call site.
• Watch for bugs whose trigger is a row count. They ship green and go red on their own schedule, long after the deploy that caused them.
• Missing and zero are different values, and they must render differently. Every place you collapse them, you're choosing to make an unknown look like a measurement.

---

This is from an internal analytics dashboard I built to track a handful of my own sites — mostly Toolio, a set of small browser-based tools — which is how a rank table ended up growing by a thousand rows a day in the first place.