The Postgres window function that made rank history 50× faster

The query was eight seconds. I needed it to be under one. Replacing a self-join with a single LAG() window function — and changing the underlying table to suit it — got me to 0.15s. Here's the schema decision behind the Amazon Analytics dashboard.

The problem

The Amazon Analytics dashboard tracks rank history for 3,500+ keywords across 18 ASINs. The "Keyword Rank" page shows, for each keyword: this week's rank, last week's rank, the delta, and a tiny sparkline of the last 12 weeks. Filter, sort, paginate.

The first version stored each weekly scrape as a row in keyword_rank_history and reconstructed the week-over-week delta with a self-join:

SELECT
  curr.keyword_id,
  curr.rank        AS this_week_rank,
  prev.rank        AS last_week_rank,
  curr.rank - prev.rank AS delta
FROM keyword_rank_history curr
JOIN keyword_rank_history prev
  ON prev.keyword_id = curr.keyword_id
 AND prev.captured_at = (
       SELECT MAX(captured_at)
       FROM keyword_rank_history
       WHERE keyword_id = curr.keyword_id
         AND captured_at < curr.captured_at
     )
WHERE curr.captured_at = (SELECT MAX(captured_at) FROM keyword_rank_history);

That works. It also took eight seconds against ~180,000 rows (3,500 keywords × ~50 weeks of history). The correlated subquery in the join condition was a nested-loop nightmare in the query plan.

The fix in one query

PostgreSQL's LAG() window function is built for exactly this. Given a window partitioned by keyword and ordered by time, LAG() returns the value of a column from the previous row in that window — without joining the table to itself.

SELECT
  keyword_id,
  rank                                        AS this_week_rank,
  LAG(rank) OVER (
    PARTITION BY keyword_id
    ORDER BY captured_at
  )                                           AS last_week_rank,
  rank - LAG(rank) OVER (
    PARTITION BY keyword_id
    ORDER BY captured_at
  )                                           AS delta,
  captured_at
FROM keyword_rank_history
WHERE captured_at >= NOW() - INTERVAL '14 days';

One table scan. No self-join. PostgreSQL computes the window once and reuses it. Same 180,000-row table, same result, 0.15 seconds.

The schema change that made it 50×, not 5×

The query above was a 10× win. The 50× win came from changing the table itself. The original keyword_rank_history was normalised: (keyword_id, captured_at, rank), with keyword_id joining to a separate keywords table for name, ASIN, category, and so on.

Every dashboard query joined back to that keywords table. Five tables involved in the simple "rank with metadata" query. The fix: store everything the dashboard needs in one denormalised reporting table.

CREATE TABLE keyword_rank_history (
  id                bigserial PRIMARY KEY,
  keyword_id        bigint    NOT NULL,
  keyword_text      text      NOT NULL,   -- denormalised
  asin              text      NOT NULL,   -- denormalised
  category          text,                  -- denormalised
  marketplace       text      NOT NULL,   -- denormalised
  captured_at       timestamptz NOT NULL,
  organic_rank      integer,
  sponsored_rank    integer,
  impressions       integer,
  clicks            integer,
  spend_micros      bigint
);

CREATE INDEX ON keyword_rank_history (keyword_id, captured_at DESC);
CREATE INDEX ON keyword_rank_history (asin, captured_at DESC);
CREATE INDEX ON keyword_rank_history (marketplace, captured_at DESC);

The orthodoxy says don't denormalise. The orthodoxy assumes you're optimising for write throughput and storage. A weekly-write reporting table is the inverse: read-heavy, written once, queried thousands of times. Denormalisation is the correct call.

Why (keyword_id, captured_at DESC) as the primary index

Every dashboard query partitions by keyword_id and orders by captured_at. The window function uses the same partition + order. A composite index on (keyword_id, captured_at DESC) means PostgreSQL can walk the index in window order — no sort step. The DESC matters because the most common filter is "last 14 days" and we want the most recent rows first.

You can see this in EXPLAIN ANALYZE. Before the index, the plan included a Sort node consuming ~40% of execution time. After, no sort — just an Index Scan straight into the WindowAgg.

What window functions are not good for

Two honest weaknesses:

1. Cross-keyword comparisons. A window function operates within a partition. If you need "rank of keyword A compared to category average this week," that's a separate aggregate, not a window. Window functions are for intra-series math (this row vs prior row of same keyword), not inter-series.
2. Bounded historical windows. LAG(rank, 4) gets the rank from 4 rows back — but "4 rows back" only equals "4 weeks back" if there are no missing weeks. If your scraper failed twice, LAG(rank, 4) is no longer "4 weeks ago." For accurate week-over-week, range on the timestamp explicitly: WHERE captured_at >= NOW() - INTERVAL '14 days' then take the two distinct weeks the window produces.

The wider lesson

The window-function pattern (LAG, FIRST_VALUE, NTH_VALUE, AVG OVER) replaces a whole category of self-join queries that look ergonomic but plan terribly. Anywhere you're writing "the previous N rows of this entity" or "the running total per entity," there's almost certainly a window function that's both shorter and faster.

For dashboards specifically, the combination that keeps paying off:

1. One denormalised reporting table per dashboard surface
2. Composite index matching the dashboard's primary partition + order
3. Window functions instead of self-joins for "compare to previous" math

The full architecture this query is part of — Puppeteer scraper, Postgres store, Next.js dashboard — is in the Amazon Ads analytics writeup, with the broader case study on the work page.