count(*) OVER () or Repeatable Read for a paged report

The combined query states the rule best. One read, nothing to keep in step. count(*) OVER () next to the page columns does it, since the window count is computed before LIMIT cuts the rows. The weak spot is the empty page. Ask for a page past the end and no rows come back, so the total disappears with them.

Repeatable Read works too, provided both SELECTs really run in that one transaction. Both statements only read, so serialization failures don't come into it.

The read is only half of it. Replace the displayed pair together. Publish the count and the rows as two updates and the screen can show a mixed report again, with the database having done everything right.

Batch reports have done this for a long time. The control total is accumulated in the same pass that prints the detail lines, so the footer cannot disagree with the page above it. Nobody calls that a snapshot. It is simply one read. The combined query is the same idea, and I prefer it for the reason lichen gives. The pair is produced together and should be shown together.

The combined query, as suggested. One statement, one snapshot, and the screen replaces count and rows in one assignment.

SELECT id, placed_at, count(*) OVER () AS total
FROM orders
WHERE status = 'open'
ORDER BY placed_at, id
LIMIT 20 OFFSET 0;

For correctness it does what it should. The total and the rows always describe the same set, and a page past the end comes back empty, with no total, as predicted. A plain SELECT count(*) covers that case and runs only when the page is empty, so its second snapshot has no rows on screen to disagree with.

Cost is the catch. Without the window count the planner can walk the index on placed_at and stop after twenty rows. With it, it cannot stop. count(*) OVER () needs every matching row before the first one can be returned, so the LIMIT applies after the work is done. For a narrow filter nobody notices. For the broad status most people open the report with, the first page has to count the whole set before it shows anything.

So the rule is right and this statement is an expensive way to hold it. I am now weighing Repeatable Read around a count and a keyset page, which keeps the early stop and still shares one snapshot, against keeping the combined query for narrow filters only. Either way the screen keeps replacing count and rows together, which is the part I am not willing to give up.

That cost is inherent. A window over the whole result has to see the whole result, and no index changes that. Two repairs, both inside one Repeatable Read transaction so the pair still shares a snapshot. First, a keyset page, which keeps the early stop. Second, a capped count. Nobody reads "4,812 orders" differently from "more than a thousand".

SELECT count(*) FROM (
  SELECT 1 FROM orders
  WHERE status = 'open'
  LIMIT 1001
) AS capped;

Show 1001 as "1000+". If the screen truly needs the exact figure for a broad filter, someone pays for a full count, and the only choice left is which query pays.

Keyset plus capped count in one Repeatable Read transaction, yes. Open it READ ONLY as well, so a write added to that code later fails at once instead of turning into occasional serialization errors.

Settled on that combination. One Repeatable Read transaction opened READ ONLY, a capped count, then a keyset page. From Go it looks like this.

func reportPage(ctx context.Context, db *sql.DB,
	after time.Time, afterID int64) (int, []Order, error) {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{
		Isolation: sql.LevelRepeatableRead,
		ReadOnly:  true,
	})
	if err != nil {
		return 0, nil, err
	}
	defer tx.Rollback()

	var total int
	err = tx.QueryRowContext(ctx, `SELECT count(*) FROM (
		SELECT 1 FROM orders WHERE status = 'open'
		LIMIT 1001) AS capped`).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	rows, err := keysetPage(ctx, tx, after, afterID)
	if err != nil {
		return 0, nil, err
	}
	return total, rows, tx.Commit()
}

The page past the end changed character. With OFFSET gone, an empty page only happens when the cursor is already beyond the last matching row, and the capped count still arrives from the same snapshot, so the separate fallback count from my earlier version could go. Total and rows now come out of one transaction and leave in one return. I checked the level once from inside the transaction with SHOW transaction_isolation, because that option is easy to lose in a refactor.

That fixes one page. Each click on the next page opens a new transaction, so every page is its own sample with its own total. An order inserted behind the cursor between two clicks raises the total on page two and never appears on any later page, because the keyset has already passed its position. That is the same snapshot rule applied once per request, not a fault in it. Make it explicit on the screen. Recompute the total with every page, replace it together with the rows, and do not carry the total from page one forward as if it described the later pages.

Not convinced it needs explaining on the screen. Nobody adds up the rows of twenty pages and compares the sum with the footer. A total that moves by one between clicks reads as exactly what it is, a list that changed. Recomputing per page is right, and tally6's code already does it.

Part of this I had not thought through. The total moving between pages I accept, for the reason greyledger gives. The skipped order is different. Someone paging through to find one particular order can miss it entirely, and a fresh total that went up by one does not tell them where it went. For now the report keeps the keyset, and when a page's total differs from the one the previous request showed, a small link back to the first page appears beside it. Whether that link earns its space I don't know yet.

Check which orders can actually land behind the cursor before building the link. With ORDER BY placed_at, id a new order sorts last, so it turns up on a later page and nothing is skipped. What lands behind is an older order that goes back to 'open', or a row whose placed_at came from now() in a transaction that committed late, since now() is the transaction's start time in PostgreSQL. Count how often either happens. If it's rare, the link is answering a case nobody hits.