Is this the 500-row cap?

No. The gateway truncates tool output at 500 rows so the model does not drown. LIMIT 10 without ORDER BY is the model asking the database for ten arbitrary rows. One is a product cap. The other is a missing sort.

Is this OFFSET pagination skipping rows?

Related family, different bug. OFFSET without a stable ORDER BY also drifts. OFFSET with a sort still skips rows when the underlying data moves. This post is 'there was no sort at all.'

Does Postgres guarantee insertion order?

No. Without ORDER BY, the planner may return whatever is cheapest — heap scan, index-only scan, parallel workers. VACUUM, an analyze, or a new index can change 'the first 10' overnight.

What if they asked for a sample, not a ranking?

Then say it is a sample and use ORDER BY random() or TABLESAMPLE so nobody treats it as 'top.' 'Top,' 'first,' 'best,' and 'latest' all need a named column.

Can I add ORDER BY id and call it done?

That makes it stable. It does not make it 'top by revenue.' Match the sort to the English: revenue DESC, created_at DESC, or whatever the dashboard uses, plus a unique tiebreaker.

LIMIT Without ORDER BY | Non-Deterministic SQL

You ask for "our top 10 customers." The list looks plausible. You ask again tomorrow and three names swapped. Nothing deployed. The SQL was LIMIT 10 with no ORDER BY. That is not a ranking. It is "ten rows the planner felt like reading."

LIMIT is a cutoff, not a sort

-- not "top"
SELECT id, name, revenue
FROM customers
LIMIT 10

-- a ranking
SELECT id, name, revenue
FROM customers
ORDER BY revenue DESC NULLS LAST, id
LIMIT 10

English "top," "first," "best," "latest," and "biggest" all smuggle in a sort. LIMIT does not. Postgres, MySQL, and SQL Server will all accept the first query. The order is whatever access path won — sequential heap, an index on email, a parallel worker. After VACUUM, a new index, or a different work_mem, you get a different ten.

"Latest 10 signups" without ORDER BY created_at DESC is the same bug wearing a time word. "Random sample" without TABLESAMPLE / ORDER BY random() is the same bug wearing a research word.

This is not the 500-row tool cap (the gateway stops sending rows). It is not OFFSET skipping rows (a moving window on a sorted list). It is no list order at all.

NULLS LAST matters if revenue can be empty — otherwise the "top" ten can be ten NULLs. That's the SUM/NULL family on a sort key.

How to catch it

Read the SQL for LIMIT / FETCH FIRST / TOP. If there is no ORDER BY, it is not a ranking. Ask for the same question twice. If the ids move and the data didn't, there was no stable sort.

Show the ORDER BY. If you used LIMIT, name the sort column and a
unique tiebreaker (id). If I said top / latest / first, do not
omit the sort.

The prompt that heads this off

Every LIMIT needs an ORDER BY that matches the English
(revenue DESC, created_at DESC, …) plus a unique tiebreaker.
If I wanted a sample, say so and use TABLESAMPLE or ORDER BY random().
Never send LIMIT n alone.

The short version

LIMIT 10 means "stop after ten." It does not mean "the best ten." If the top list shuffles, read the ORDER BY before you read the join.

Read-only MCP gateway: mcpserver.design.

Related reading: