The first version of our cross-engine join was correct, and it took 617
seconds to show five rows. The statement joined an OpenSearch index of 226,000
documents to a DynamoDB table of 712,699 items on that table's own
partition key. To return five rows it read 939,482 items:
the whole index and the whole table.

Teams that feed OpenSearch from DynamoDB, through a stream or the zero-ETL
pipeline, end up with the same records in two engines and questions that span
both. Which customers in this search result have unpaid orders? How many
documents per country match this table's status? DynamoDB has no JOIN, and
OpenSearch has one you should not trust with a GROUP BY.

DynoTable's SQL Workbench now answers those questions
in one statement:

SELECT c.country, COUNT(*) AS orders, SUM(o.amount) AS revenue FROM dynamodb.orders o JOIN opensearch.customers c ON o.customerId = c.customerId GROUP BY c.country

OpenSearch's SQL plugin returned wrong numbers without an error

OpenSearch can run SQL, joins included, through its SQL plugin. Pushing the
join there was the obvious plan, so we probed it first on OpenSearch 2.19. We
ran this against an orders index of 150 documents and a customers index:

SELECT c.country, COUNT(*) FROM orders o JOIN customers c ON o.customerId = c.customerId GROUP BY c.country

The answer came back HTTP 200 with no error. It had 150 rows where there
should have been six, one per order, and every count was null. The schema
listed three columns for a two-column query: o.COUNT(*), c.country and
c.COUNT(*). The same join without GROUP BY returned correct rows.

That alone ruled it out, and two more findings closed it. With a join, the
plugin ignores fetch_size and returns the whole result in one response, so
there is no cursor to page through a large join. A join on an analyzed text
field returns zero rows, again without an error. OpenSearch's own
documentation calls its joins "best-effort" and says they do "not support
aggregations on the joined result".

So every join that names an index runs in the app. What we still send to
OpenSearch is each side's own filter: the conditions OpenSearch can answer
travel with that side's search, and the full WHERE runs again on the joined
rows, so pushing a filter down can only shrink a read, never change the
answer.

Reading both sides in full was correct and far too slow

The first version did the simple thing. It read every member of the statement
in full into a local engine, then ran the SQL there. That is correct for any
statement, and its cost is the size of every table and index involved,
whatever the WHERE or the LIMIT says.

On that 226,000-document domain, a five-row peek with no WHERE took 2.0
seconds over the index alone. Joined to a 599-item DynamoDB table it took 58.2
seconds. Joined to the 712,699-item table it took 617.4 seconds. A peek has no
WHERE to push down, so filter pushdown could not help.

A join side is read by key, one page at a time

The fix was to stop reading the joined side at all, except for the rows the
base actually needs. The base streams page by page. For each page, the app
collects that page's join values and fetches only the matching records:

Diagram: view on dynotable.com

This is why the Workbench insists on a keyed join. On a DynamoDB table the
join must land on the partition key of a table without a sort key, or on a
GSI partition key, because that is what BatchGetItem and Query
can fetch. On an index it lands on
_id or on any keyword, numeric, date, boolean or ip field, or a
text field with a keyword sub-field, because a terms query matches whole
values there.

The read can also stop early. For a statement where a prefix of the result is
safe to return, the app counts the rows after each base page and stops at the
LIMIT. The OpenSearch base reads
5,000 documents per request, so the stop saves whole requests, never part of
one. An index smaller than 5,000 documents saves nothing on the base, and the
saving lands on the join side instead.

Four ways a key read would have answered wrong

A key read is only safe if it returns what the full read would have. We found
four cases where it does not, and in each one the app refuses the key read and
runs the whole statement the slow way. There is no half mode: a statement
either reads by key from start to finish or reads in full.

A value longer than ignore_above. OpenSearch stops indexing a keyword
value past the field's ignore_above length, so a terms query never matches
it, while the full read still sees it in the document. We test each join value
against the limit as it arrives. A per-field rule was the first idea and the
wrong one: nearly every keyword field in the indices we tested carries an
ignore_above, so it would have refused almost every string join.

One name backed by several indices. An alias can point at indices that
hold documents with the same _id. Keyed by _id alone, the second document
would overwrite the first in the local engine. The app refuses that member
before reading.

A relationship that fails on some page. If one page's lookup fails, an
INNER JOIN silently loses rows and a LEFT JOIN silently blanks columns.
Both refuse.

A join value the index cannot parse. This one we fixed by dropping instead
of refusing. OpenSearch rejects a terms query whole when a single value does
not fit the field's type, such as a word sent to a long field. One bad value
used to fail the page's entire lookup and send the statement to the slow path.
A value the mapping cannot hold can never match on either path, so the app now
drops it from the lookup and keeps the rest.

The early stop was quadratic until we spaced it out

Checking "do we have enough rows yet?" means running the statement over
everything read so far. The first version checked after every 100-row batch, on
the app's main process. Over 100,000 base rows that was 1,000 checks costing
1,202 milliseconds, and over 226,000 rows it was 2,260 checks costing 6,115
milliseconds, growing with the square of the base.

Now each check that comes up short delays the next one by a quarter of the rows
already read. The same two runs make 27 checks in 11 milliseconds and 31 checks
in 27 milliseconds. The price is lateness: a join that matches one base row in
a thousand stops at row 4,800 instead of 4,100. Inside one 5,000-document
request that costs nothing, because the server already sent those documents.

What the agent's scan budget was not seeing

The same routes serve the AI agent and the MCP server, where every read counts
against a scan budget. The first version charged the rows it handed on, not the
documents it received, and on an OpenSearch base those differ by a request's
worth. One run reported 101 items against 5,000 documents transferred, an
under-charge of up to 50 times. The budget now counts what the server sent on
every base page plus every record the key reads returned.

What transfers if you're building one

  • Probe a database's SQL layer with an aggregate over a join before you build on it. Ours returned HTTP 200 and wrong numbers.
  • Push each side's filter down as a superset and re-check the full WHERE locally. Then pushdown is an optimization and never the correctness boundary.
  • Read the joined side by key, page by page. A join that reads both sides in full costs the size of both, and no filter fixes a peek.
  • List every way the key read can miss a record, and refuse the whole statement when one applies. A partial fallback is a second engine to test.
  • Space out any "are we done?" check that rescans what you have read.

OpenSearch SQL has the join rules and query examples,
and the JOIN guide covers keyed joins on the DynamoDB
side. The free DynamoDB Expression Builder
shows the key condition a join side reads with.

To try it, download DynoTable, turn on OpenSearch for your
profile, and join an index to a table in a Workbench tab.