How we join DynamoDB and OpenSearch in one SQL query

DynoTable's SQL Workbench runs one SQL JOIN across a DynamoDB table and an OpenSearch index. When the AI agent, the MCP server or the editor integration ran that join, it was correct and took 617 seconds to show five rows. The statement joined an OpenSearch index of 226,783 documents to a DynamoDB table of 712,699 items on that table's own partition key. To return five rows it read 939,482 records, every document in the index and every item in the 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 the JOIN in OpenSearch's SQL plugin gave us wrong numbers once a GROUP BY was added.

The SQL Workbench 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. The plugin cannot reach a DynamoDB table, so the probe asked only whether a join of two indices could go to OpenSearch. We ran it on OpenSearch 2.19.1, 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, one per order, where there should have been six, one per country, and every count was null. The schema listed three columns for a two-column query: o.COUNT(*), c.country and c.COUNT(*).

That alone ruled it out, and the same version had two more problems. 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 limitations page calls 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.

Reading both sides in full was correct and far too slow

The route the agent, the MCP server and the editor integration share started with the simple thing. It copied every table and index the statement names, in full, into a SQL engine inside the app, then ran the statement there. That works for any statement, and it costs the size of every table and index in it, whatever the WHERE or the LIMIT says.

The numbers come from an OpenSearch 3.7 domain in eu-west-1 whose index takes writes all day, so its document count drifts between runs. 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, because the join forced a read of the whole index. Joined to the 712,699-item, 981 MB table it took 617.4 seconds.

The joined table or index is read by key, one batch at a time

Call the first table or index in FROM the base, and every other table or index a join side. The Workbench grid already read each join side by key. The fix moved the route the agent, the MCP server and the editor integration share onto the same key reads. The base is read from its engine, 5,000 documents per OpenSearch request, and handed on in batches of 100 rows. For each batch, the app collects the join values and fetches only the matching records:

On local test data the key reads return the same answers as the full read, and an index-to-index join read 302 records instead of 6,002.

A key read needs a key to read by. On a DynamoDB table the join must use the partition key of a table with no sort key, or the partition key of a GSI that has no sort key and projects all attributes. A sort-keyed GSI leaves out every item that lacks its sort attribute, so a key read on it would lose rows. Every read here is eventually consistent, and a GSI can lag further, so an item written seconds ago may not join yet. A table whose own key has a sort key can be the base instead, with the other table or index joined onto it; for a LEFT JOIN that changes which side's rows are kept. On an OpenSearch index the join uses _id or 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 Workbench grid accepts only those joins. On the shared route, any other join still runs on the full read, as before.

Only the base's filter goes to its engine

The base's own filter now goes to its engine, widened to a superset of the rows the WHERE keeps. The join side is read by key with no filter. The full WHERE runs again on the joined rows, so a filter that matches too much costs extra reads and the answer stays the same. On local test data, WHERE o.sku > 18.5 read 604 documents instead of more than 6,000. The widened filter also brought back sku 18, which the full WHERE then dropped.

Three ways a key read would have answered wrong, and one value we drop

A key read is safe only if it finds every record the full read would. We found three cases where it does not. In each one the shared route falls back to the full read for the whole statement and returns what the full read returns, at that cost. The key reads already made are not reused. On the MCP server and the editor integration the full read also counts against the scan budget, a cap on how much one client query may read.

A value longer than ignore_above. OpenSearch indexes the document but skips a keyword value longer than the field's ignore_above, so a terms query never matches it, while the full read still sees it in _source. We test each join value against the limit as it arrives. We first sent any field that carries an ignore_above to the full read. In the indices we measured nearly every keyword field has one, 200 of 225 on one of them, so almost every string join would have fallen back. To stay on the key read, join on a field whose values fit the limit. Raising ignore_above works only after every existing document is reindexed, for example with _update_by_query. The app reads the new limit from the mapping at once, but documents indexed under the old limit still lack the term.

One name backed by several indices. An alias can point at indices that hold documents with the same _id. The local copy keys documents by _id alone, so the second document would overwrite the first. The shared route falls back before any read, and the Workbench grid and the editor flag such a join side as an error. When one index holds all the documents you mean, name that index instead of the alias.

A key read that fails on one batch. If one batch's key read fails, an INNER JOIN would silently lose rows and a LEFT JOIN would silently blank columns, so the route falls back on either kind. A repeated fallback here usually means the join side's permissions or throttling. Check those first, because the full read scans every table and index in the statement, which needs Scan permission and spends far more read capacity than the key reads it replaces.

A join value the index cannot parse. This one we fixed by dropping instead of falling back. 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 batch's entire key read and send the statement to the full read. The join compares typed values, so a value of the wrong type for the field matches no document whose source agrees with its mapping. The route now drops it from the key read and keeps the rest.

The early stop was quadratic until we spaced it out

The read can stop early, but only for a plain row read, one that selects plain columns or * without DISTINCT and has at most a WHERE and a LIMIT with no OFFSET. It then stops at the LIMIT, or, when there is none, one row past the output cap, the row that shows the result was cut. The GROUP BY example above aggregates, so it reads the whole base and saves only on the join side. The stop saves whole 5,000-document requests, so an index smaller than that saves nothing on the base.

Checking "do we have enough rows yet?" means counting over everything read so far. The first version checked after every 100-row batch, and the check blocked the app's main process while it ran. In a worst-case join that never reaches its LIMIT, 100,000 base rows took 1,000 checks and 1,202 milliseconds, and 226,000 rows took 2,260 checks and 6,115 milliseconds. Two and a quarter times the rows cost five times the time.

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 spacing makes the stop late. A join that matches one base row in a thousand stops at row 4,800 instead of 4,100. On an OpenSearch base that is still inside the first 5,000-document request, so it costs no extra base reads, only the join-side key reads for those 700 rows.

The MCP scan budget charged 101 items for 5,000 OpenSearch documents

The first version charged the budget for the rows the route handed on, not the documents OpenSearch sent. On an OpenSearch base those differ by up to one 5,000-document request. One run reported 101 items against 5,000 documents transferred. The budget now counts what OpenSearch returned for each base request 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 the base's filter down as a superset and re-check the full WHERE locally. Then a pushdown bug can cost reads but cannot change the answer.
• Read the joined side by key, batch by batch. A join that reads both sides in full costs the size of both, and no filter speeds up a peek.
• List every way the key read can miss a record, and fall back for the whole statement when one applies. Falling back for part of a statement would mean testing a third read mode.
• 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 builds the key condition a GSI Query on the join side uses.

To try it, download DynoTable, turn on OpenSearch for your profile, and join an index to a table in a Workbench tab. With an AI provider set up, you can also ask the agent to answer the question with a SQL join.