Aster Web Data / How it works

Reads across databases

How one request executes across several databases and virtual data sources.

Customers are often in one database and invoices in another: a CRM on PostgreSQL, say, and a ledger on MariaDB. With both sets on one Aster Web Data service, a client asks for customers with their invoices in one request and gets one response, as if the two were one database. Aster reads each database with a bounded number of statements, set by the depth of the request rather than by its rows, and joins the rows in its engine.

Across two databases, a client can use $expand at any depth, with its own $filter, $orderby, $top, $skip and $count; a navigation from one record to its related set; $select; $count and paging on the first set; and a sort or grouping by the other database's field. Limits gives the few requests that are refused and the bounds on the rest.

One request over two databases

Each DataSource is stored on one database. Several databases in one service shows the configuration; nothing more is needed to read across them.

With Customer on PostgreSQL and Invoice, InvoiceLine and Product on MariaDB, a client can read across the two in one request. This request lists EU customers, their latest unpaid invoices, the lines of each invoice and the product of each line:

The engine reads the page of customers from PostgreSQL. It then reads the matching invoices, their lines and their products from MariaDB, one statement for each level, and attaches each row to its parent. These are the statements each database received:

/* aster read customers */ SELECT c."id", c."name", CAST(c.xmin AS text) AS c3 FROM "public"."customers" AS c WHERE c."region" = $1 ORDER BY c."name", c."id" LIMIT $2
-- binds: "EU", 5

/* aster count customers */ SELECT COUNT(*) FROM "public"."customers" AS c WHERE c."region" = $1
-- binds: "EU"

/* aster read invoices */ SELECT q2.`id`, q2.`customerId`, q2.`issued`, q2.`amount` FROM (SELECT q.`id`, q.`customerId`, q.`issued`, q.`amount`, q.`paid`, ROW_NUMBER() OVER (PARTITION BY q.`w` ORDER BY q.c7 DESC, q.c8) AS c6 FROM (SELECT i.`id`, i.`customerId`, i.`issued`, i.`amount`, i.`paid`, i.`customerId` AS `w`, i.`issued` AS c7, i.`id` AS c8 FROM `test_aster_docs_capture`.`invoices` AS i WHERE i.`paid` = FALSE AND i.`customerId` IN (?, ?, ?, ?, ?, ?, ?, ?)) AS q) AS q2 WHERE q2.c6 <= ?
-- binds: 4, 5, 12, 15, 17, 17, 17, 17, 3

/* aster read invoice_lines */ SELECT i.`id`, i.`invoiceId`, i.`productId`, i.`quantity`, i.`unitPrice` FROM `test_aster_docs_capture`.`invoice_lines` AS i WHERE i.`invoiceId` IN (?, ?, ?, ?, ?, ?, ?, ?)
-- binds: 13, 41, 54, 53, 59, 60, 61, 61

/* aster read products */ SELECT p.`id`, p.`name` FROM `test_aster_docs_capture`.`products` AS p WHERE p.`id` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
-- binds: 10, 6, 9, 5, 2, 8, 3, 1, 4, 4, 4, 4, 4, 4, 4, 4

The response is the same document that a single database gives for the same request:

HTTP 200
content-type: application/json;odata.metadata=minimal
odata-version: 4.01

{
  "@odata.context": "http://localhost:8080/shop/$metadata#Customer(id,name,invoices(id,issued,amount,lines(quantity,unitPrice,id,product(name,id))))",
  "value": [
    {
      "@odata.etag": "W/\"54706161\"",
      "id": 4,
      "name": "Birch Works",
      "invoices": [
        {
          "id": 13,
          "issued": "2026-08-20",
          "amount": 18.75,
          "lines": [
            {
              "quantity": 5,
              "unitPrice": 3.75,
              "id": 33,
              "product": {
                "name": "Sanding block",
                "id": 10
              }
            }
          ]
        }
      ]
    },
    {
      "@odata.etag": "W/\"54706161\"",
      "id": 5,
      "name": "Cedar Supply",
      "invoices": []
    },
    {
      "@odata.etag": "W/\"54706161\"",
      "id": 12,
      "name": "Fern Works",
      "invoices": [
        {
          "id": 41,
          "issued": "2026-08-16",
          "amount": 19.50,
          "lines": [
            {
              "quantity": 3,
              "unitPrice": 6.50,
              "id": 107,
              "product": {
                "name": "Utility knife",
                "id": 6
              }
            }
          ]
        }
      ]
    }
  ],
  "@odata.count": 11
}

A smaller expansion reads the customers first, then their invoices. This one adds a count of each customer's matching invoices:

/* aster read customers */ SELECT c."id", c."name", c."email", c."region", CAST(c.xmin AS text) AS c5 FROM "public"."customers" AS c ORDER BY c."id" LIMIT $1
-- binds: 20

/* aster read invoices */ SELECT q3.`id`, q3.`customerId`, q3.`issued`, q3.`amount`, q3.`paid`, q3.c6 FROM (SELECT q2.`id`, q2.`customerId`, q2.`issued`, q2.`amount`, q2.`paid`, q2.c6, ROW_NUMBER() OVER (PARTITION BY q2.`w` ORDER BY q2.c8 DESC, q2.c9) AS c7 FROM (SELECT q.`id`, q.`customerId`, q.`issued`, q.`amount`, q.`paid`, COUNT(*) OVER (PARTITION BY q.`w`) AS c6, q.`customerId` AS `w`, q.`issued` AS c8, q.`id` AS c9 FROM (SELECT i.`id`, i.`customerId`, i.`issued`, i.`amount`, i.`paid`, i.`customerId` AS `w` FROM `test_aster_docs_capture`.`invoices` AS i WHERE i.`amount` > ? AND i.`customerId` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS q) AS q2) AS q3 WHERE q3.c7 <= ?
-- binds: 10, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 3

An expansion that goes from the invoices to their customers works the same way, and so does a navigation from one customer:

1 statement on MariaDB, 1 statement on PostgreSQL

The metadata describes each navigation with what the planner accepts across databases, so a client can see before it asks. The metadata is the same whether a database is up or down.

A virtual data source, such as a handler that calls a REST API, takes part in a read across databases like any other set when its DataSource declares key_batches: true under pushdown:. It belongs to the service that declares it. Virtual data sources explains how to write one.

How related rows are fetched

A read that crosses databases runs level by level, where a level of a $expand is one set of related rows. The engine reads the parent page on its own database, collects the distinct keys of those rows, and reads the related set in one statement that carries all of the keys: a key batch. It then attaches each child row to its parent by key.

A key batch has few statement shapes however many keys it carries, so the database sees few distinct statements. PostgreSQL receives the keys as one array and matches them with = ANY. The other databases receive an IN list padded to a power of two by repeating a key.

The options of a nested $expand run on the related database, wherever that database can apply them:

• The $filter and $orderby go into the statement for the related set.
• A $top or $skip per parent becomes a ROW_NUMBER window partitioned by the parent key, so each parent gets its own page. The exact page cut is made in the engine.
• A $count beside a page is one grouped count statement for the level.

A level with no parent rows sends no statement. A level with more distinct keys than limits.key_batch is read in chunks of that size; for a composite key the bound counts the values sent, so fewer keys fit in a chunk. Limits gives the default.

Limits

Refused requests

A $filter that tests the related rows of another database, such as any or all over a navigation into it, is refused with 501, and the response names the sets. The same request works when both sets are on one database:

HTTP 501
content-language: en
content-type: application/json;odata.metadata=minimal
odata-version: 4.01

{
  "error": {
    "code": "NotImplemented",
    "message": "this request reads `Customer` and `Invoice`, which are stored apart; a filter by the rows of one (`any`, `all`) or a union across them is not supported yet"
  }
}

A union across two databases is refused in the same way. A changeset that writes to two databases is refused before anything runs. The member that crosses between them gets 501, and the other members of its group get 424.

Sorting by another database's field

A sort by a field of the other database, such as $orderby=customer/name, is a join in the engine. A page over such a sort is cut in the engine too. It runs while both sides fit in limits.residual_rows. Past that limit the request fails with QueryTooExpensive. This capture ran with limits.residual_rows set to 50, below the size of the invoice table:

HTTP 400
content-language: en
content-type: application/json;odata.metadata=minimal
odata-version: 4.01

{
  "error": {
    "code": "QueryTooExpensive",
    "message": "the request would hold more than 50 rows in memory to answer it. Filter, sort or page it so the database does the work, narrow the request, or raise `limits.residual_rows`"
  }
}

Grouping by another database's field

An $apply that groups by a field of the other database, such as groupby((customer/region),aggregate(amount with sum as total)), is computed in the engine from the rows both databases return. It has the same ceiling as a sort across databases: past residual_rows the request fails with 400 QueryTooExpensive. This capture ran with limits.residual_rows set to 50:

HTTP 400
content-language: en
content-type: application/json;odata.metadata=minimal
odata-version: 4.01

{
  "error": {
    "code": "QueryTooExpensive",
    "message": "the request would hold more than 50 rows in memory to answer it. Filter, sort or page it so the database does the work, narrow the request, or raise `limits.residual_rows`"
  }
}

Isolation

There is no snapshot across two databases. Each statement reads at its own database's default isolation level: read committed on Postgres and repeatable read on MariaDB. The read as a whole runs at the weaker of the two. A row written between the two statements can therefore appear on one side of the join and not the other.

Security

Each database's statement carries the row restrictions and hidden fields of its own set, as From request to SQL describes. Aster writes them into the statements itself and does not rely on a database's own row-level policies.

Unavailable databases

A request that needs a database that is down is answered with 503, the code ConnectionLost, and a Retry-After header. The error does not name the database. Requests that touch only the other databases keep working.

Virtual data sources

A virtual data source without paging reads its level without a page, restricted to the keys of the parent rows. Its rows still count against residual_rows.

Expansion depth

Nested expansions are limited by limits.max_expand_depth. Each level is one more statement per database, so the bound also limits the statement count. Limits lists the defaults.