PartiQL: targeted reads

Part of the dialect series: the difference between a read that touches three items and one that reads — and bills — the whole table is often a single predicate. Start with the ordinary Query/Scan path, then use the same editor for native vector similarity and explicitly parallelized scans — each with its own cost and guardrails disclosed before the wire.

The Query/Scan divide

SELECT * FROM "stage.Outbox" WHERE pk = 'Outbox#123';       -- QuerySELECT * FROM "stage.Outbox" WHERE pk IN ('O#1', 'O#2');     -- key lookupsSELECT * FROM "stage.Outbox"WHERE pk = 'Outbox#123' AND begins_with(sk, 'EVT#');         -- narrowed QuerySELECT * FROM "stage.Outbox" WHERE Status = 'pending';       -- Scan (warned)

A partition-key equality or IN list in WHERE avoids the full read — DynamoDB's equality-or-IN rule. Equality executes as a Query; an IN list executes as one key lookup per value (an IN-driven read claims no sort-key narrowing — every other predicate filters after those lookups). Anything else degrades to a Scan that reads — and bills — every item. When the attribute you need to filter by isn't a key, the fix is a GSI whose partition key is that attribute: read it with the FROM "table"."index" form and the filter becomes a key condition again (multi-attribute GSIs are below).

The editor points at the exact predicate (or OR) responsible for a Scan, and with a live connection the warning is sized with the table's item count ("~432,926 items at last describe"). Tables outside your parsed schema still classify: their live key schema stands in, so pk IN (…) on an unmapped table reads as key lookups, not "can't classify".

SELECT pk, sk, Status
FROM "stage.Outbox"
WHERE Status = 'pending'

Three rows back, the whole table read to find them — that 0.0007% is the Scan tax. Now flip to the Keyed — Query variant: the same question with a partition-key equality in front reads 12 items instead of 432,926, and the strip turns green before anything runs.

And the tax compounds as the table grows. Drag the slider — the Scan's bill tracks the table, the keyed Query's doesn't:

WHERE: the predicate reference

For ordinary table and GSI Query/Scan reads, all of these predicates are native. What varies is where they execute — in the key condition (bounding what DynamoDB reads) or in the filter (applied after the read; matched items are still read and billed):

• Comparisons — = <> != < <= > >=. Partition-key equality forms the key condition; sort-key comparisons on a queried target narrow it; anything else filters.
• BETWEEN low AND high — on the queried sort key it narrows the key condition itself; elsewhere it filters.
• IN ('a', 'b') — literal list. On the partition key it stays a key condition (one key lookup per value); never on a sort key; elsewhere it filters.
• AND — composes key conditions and filters freely. OR / NOT — can never form a key condition; their presence around key predicates degrades the read to a Scan, and the editor says so.
• IS MISSING / IS NOT MISSING — attribute absence. Absent is not the same as NULL: DynamoDB stores explicit nulls, and the two test differently.
• Functions — begins_with(path, prefix), contains(path, value), attribute_type(path, type), size(path). On a queried sort key, begins_with narrows the key condition; the rest always filter.

Multi-key GSIs

-- a multi-key GSI: two partition + two sort attributesSELECT * FROM "stage.Orders"."byRegionStatus"WHERE region = 'eu' AND tenant = 'acme'         -- equality on every partition attr  AND status = 'open' AND createdAt >= '2026'   -- sort attrs, left-to-right-- a gap, a missing partition equality, or a 2nd range → Scan

A GSI isn't limited to one partition key and one sort key. It can carry up to four partition and four sort attributes, kept separate and typed — not concatenated into one region#tenant#status string you have to parse back out. The studio classifies a read against one as a Query only when it honours the multi-key rule:

• Equality on every partition attribute — all of them, no exceptions; a missing one degrades to a Scan.
• Sort attributes bound left-to-right, no gaps — you may stop early, but you can't skip one and bind a later one.
• At most one trailing inequality — < <= > >= BETWEEN, or begins_with (which must come last). A second range degrades to a Scan.

A gap, a missing partition equality, or a second range turns the read into a Scan, and the strip names the attribute that broke the rule — so you see why it fell off the Query path, not just that it did.

SELECT orderId, region, status, createdAt
FROM "stage.Orders"."byRegionStatus"
WHERE region = 'eu' AND tenant = 'acme'
  AND status = 'open' AND createdAt >= '2026'

This is the green counterpart to the amber Scan above: same table, but every key attribute is pinned the way the rule wants, so the whole read is one targeted Query on the index. Drop the tenant equality or reorder the sort attributes in DynoStudio and the strip flips amber, naming what broke. (Following along on the sample tables? byRegionStatus is the one-statement optional step from the setup page — run it and this exact query returns these exact two orders.)

Vector similarity search

SELECT ProductId, Title,       VECTOR_DISTANCE(Embedding, {queryVector}) AS scoreFROM "Products"."ProductEmbeddingIndex"WHERE Category = {category} AND Brand = 'Acme'ORDER BY VECTOR_DISTANCE(Embedding, {queryVector}) ASCLIMIT 10

VECTOR_DISTANCE targets a live DynamoDB vector index and lowers to one native, eventually consistent SearchVectors request. The returned score comes from the service; DynoStudio never recomputes it client-side. When selected, the score needs an explicit, non-colliding AS alias. COSINE and EUCLIDEAN use ASC for nearest-first, while DOT_PRODUCT uses DESC; omitting the direction uses the index's nearest-first order.

• Vector binding — {queryVector} is a numeric PartiQL list such as [0.12, -0.34, 0.56]. Its length must exactly match the index dimensions, and every value must be a finite 32-bit number. History stores only its digest and dimension count, then asks you to bind the vector again on replay.
• Top-k bound — LIMIT is required and must be 1–100. One request returns one page.
• Search schema — a HASH attribute declared by PARTITION BY must be pinned with =. Optional conditions may use only top-level INLINE_FILTER attributes declared by FILTER BY, also joined with = and AND.
• Projection — select *, projected top-level attributes, and optionally one aliased score. Fetching a non-projected base-table attribute needs a separate join-back shape.
• Readiness and cost — the table must be on-demand and the index must be ACTIVE with backfill finished. Vector search is byte-metered rather than RCU-metered; the receipt reports VectorSearchRequestBytes.

The editor uses the current DescribeTable descriptor to complete vector-index names, the vector and search-schema attributes, dimensions, distance metric, projection, and readiness. To create the target itself, use CREATE VECTOR INDEX.

Sorting and paging

-- native: provably the queried sort key, server-side orderSELECT * FROM "stage.Users" WHERE pk = 'User#9' ORDER BY sk DESC;-- lowered: per-page client sort, disclosedSELECT * FROM "stage.Users" ORDER BY Country ASC, Age DESC NULLS LASTLIMIT 50 OFFSET 10;

• ORDER BY (native case) — a single key that provably is the queried target's sort key stays in the native statement, so DynamoDB returns rows in index order (ASC/DESC).
• ORDER BY (lowered case) — multi-key lists, non-key attributes, document paths (ORDER BY address.city), or unprovable targets sort each fetched page client-side, disclosed in the strip. Type order: booleans before numbers before text. Absent values sort as largest unless NULLS FIRST / NULLS LAST says otherwise.
• LIMIT n — core PartiQL that DynamoDB's dialect rejects; the studio sends it as the request's page limit.
• OFFSET n — skipped rows are fetched on top of the first page and dropped client-side. Skipped rows are still read and billed. First page only.
• DISTINCT — de-duplicates each fetched page client-side by deep value equality (numbers by value, sets order-insensitive). Like a client-side ORDER BY, it acts per page, so duplicates across page boundaries can reappear.

PARALLEL n — faster scans, same bill

SELECT * FROM "stage.Orders"WHERE status = 'FAILED' PARALLEL 4;​SELECT region, SUM(order.total)FROM "stage.Orders" GROUP BY region PARALLEL 4;​PROFILE TABLE "stage.Orders" PARALLEL 4;

A trailing PARALLEL n runs an intentional Scan as n concurrent DynamoDB segments and merges them client-side. It can shorten wall-clock time for a profile, full-table aggregate, or sweep, but it reads the same items and bills the same total RCUs as a serial Scan — while consuming that capacity at up to n× concurrently. The Executes-as strip discloses the fan-out and throttle risk before the run.

• Scans only — on a key-condition Query it is a no-op. Base tables and GSIs are supported; a parallel GSI scan is eventually consistent.
• Pushable filters only — comparison, BETWEEN, begins_with, IN, IS [NOT] MISSING, and boolean composition can stay parallel. Unsupported predicates, a client-side ORDER BY, subqueries, or set operations run serially with the reason shown.
• Bounded output — LIMIT stops the fan-out when enough rows arrive, so which rows win is nondeterministic just as it is for a serial Scan. Without LIMIT, a plain parallel SELECT returns one fast page; aggregates and PROFILE TABLE drain and fold the full scan.
• Guarded concurrency — Block Scans still blocks it. The degree is capped in Settings → Querying (default 4); above the configured cap it refuses. Parallel scan acceleration is a Professional capability.

SELECT * FROM "stage.Outbox"WHERE pk = 'Outbox#123' AND Status = 'pending'

SELECT * FROM "stage.Outbox"WHERE pk = 'Outbox#123' AND sk BETWEEN 'EVT#0100' AND 'EVT#0200'

SELECT * FROM "stage.Orders"."byRegionStatus"WHERE region = 'eu' AND tenant = 'acme'  AND status = 'open' AND createdAt >= '2026'