Aster Web Data / Operate

What makes Aster Web Data fast

Why a request through Aster Web Data costs about what the same query written by hand costs, on every database and with what each database adds.

Before you put a service in front of a production database, you want to know whether it will keep up. With Aster Web Data, a request costs about what the same query costs when a developer writes it by hand. Aster sends the database one statement for most reads, with the filter, the sort, the page and the related rows inside it, and the database's own indexes and planner do the work. Related rows come in that statement or in one more statement per level, never in one query per parent row. Aster's own work on a request is parsing it and planning it in memory against a model it built at start-up.

On every database

One statement per read

A read is one statement, and two when the client asks for a count. $filter, $orderby, $top and $skip are clauses of that statement, and a filter on related records, such as invoices/any(i: i/amount gt 500), is an EXISTS inside it. The count is a COUNT(*) with the same WHERE, run in the same snapshot as the page. From request to SQL shows both statements for a real request.

Related rows in one batch per level

An $expand never sends one query per parent row. On PostgreSQL, MariaDB and SQLite the related rows of most tables are read in the parents' statement. Elsewhere, and wherever a navigation has no one-statement form, Aster reads the parents first and then each level of related rows in one statement, keyed by all the parents' keys on the page. A level with more than 8,192 parents is read in chunks of that size (limits.key_batch). The same batching serves a read whose related rows are on another database, and a virtual data source that accepts a batch of keys.

Several rows in one insert

A bulk insert sends its rows as one multi-row statement, in batches that stay within the database's limit on bound values, rather than one INSERT per row. In a deep insert, the children of one parent that have no children of their own go in one statement the same way. Oracle is the exception, and inserts each row with a statement of its own. A deep insert and a $batch atomic group each run in one transaction on one connection, and on PostgreSQL a whole deep insert or atomic group can be a single statement, as PostgreSQL below describes.

Filtering, sorting and paging in the database

Aster sends each condition to the database written so that an ordinary index on the column can serve it: columns are compared as they are stored, with no cast and no COLLATE. Every sort ends with the key, so pages are stable and an index on the sort column and the key serves the whole order. Values are bound as parameters, so the statement text depends only on the shape of the request.

Aster's own engine runs only what the database cannot, over rows the database has already filtered and paged where it can. It holds at most limits.residual_rows rows, 50,000 by default, and refuses a request that would need more. A large table is never filtered or sorted in Aster's memory.

Expensive requests refused early

Requests past a cost limit, such as an $expand nested too deep or an in list that is too long, are refused while the request is parsed and planned, before any SQL is sent. Each statement also runs under a timeout, and each request under a deadline. Limits lists every limit with its default.

Little work in Aster Web Data itself

• Built once. The model, the rules and $metadata are compiled when the server starts and when it reloads. A request is parsed and planned in memory against them. The few facts the planner takes from the database's catalog, such as whether a foreign key has an index, are read once and kept for minutes rather than read on every request.
• Prepared statements. On PostgreSQL and MariaDB each statement is prepared once per connection and reused. Because the text depends only on the request's shape, the same few prepared statements serve all the traffic a screen sends.
• Plans for the values. A prepared PostgreSQL statement can fall back to one generic plan, made without the values. Where the best plan depends on the value (a pattern, a range, an in list), Aster asks PostgreSQL to plan the statement for its values each time. Key and equality reads keep the generic plan.
• Pooled connections. Connections are opened once and reused. Session settings that never change are set when the connection opens, and the per-request ones, such as the statement timeout, travel with the statement instead of adding a round trip.
• Streamed responses. Rows are written to the client as the database returns them, so a long page does not wait for its last row.

When the database supports it

Each database has features that save a statement or a round trip, and Aster uses them where they exist. Each database's page gives the statements of every read and write shape.

PostgreSQL

• $expand inside the read. Each level is a LEFT JOIN LATERAL that builds the related rows with json_agg, with its own filter, sort and page. Aster uses this form where the navigation's foreign key has an index, and logs the navigations that lack one at start-up.
• Key batches as arrays. A batch of keys is one array parameter, = ANY($1), so the statement text is the same for any number of keys. Several rows of an insert are INSERT ... SELECT ... FROM unnest($1, $2, ...), one array per column.
• Writes as one statement. A write by key, with its rule checks, its row lock, its If-Match comparison and the read of its answer, is one statement of chained common table expressions ending in RETURNING. A deep insert, and the inserts and checks that lead an atomic group, can be one statement too.
• One network wait. The BEGIN, the session settings, the statements and the COMMIT leave together, so a read, a read with its count and most writes each wait on the network once.

PostgreSQL gives the conditions, such as the 48-column limit and tables with triggers.

MariaDB

• $expand inside the read. MariaDB has no LATERAL, so each level is a correlated subquery per parent that builds the related rows with JSON_ARRAYAGG, filtered, sorted and paged inside it. A single-valued navigation is a plain LEFT JOIN.
• INSERT ... RETURNING gives a new row's answer without a second read.
• The statement timeout in the statement. SET STATEMENT max_statement_time=... FOR is a prefix of the statement it governs, so it costs no statement of its own.

MariaDB gives the statements of each write.

SQLite

• No network. SQLite runs inside the server process, so a statement has no round trip.
• $expand inside the read. Each level is a correlated subquery per parent that builds the related rows with json_group_array, paged per parent.
• RETURNING on inserts and updates gives the answer without a second read.

SQLite gives the tables that read by key batches instead.

DuckDB

• No network. DuckDB also runs inside the server process.
• Per-parent pages with QUALIFY. DuckDB reads each $expand level by key batch, and a nested $top is a window filter in a QUALIFY clause rather than a derived table.
• RETURNING on inserts and updates.

SQL Server

• A keyed write in one round trip. A PATCH, PUT or DELETE by key sends its lock, which compares the ETag, the write and the read of the stored row as one batch.
• OUTPUT ... INTO captures the keys of inserted rows, so the answer is read back by key.
• Multi-row inserts take up to 1,000 rows per statement.

SQL Server gives the statements of each write.

Db2

• SELECT ... FROM FINAL TABLE (INSERT ...), and the same for UPDATE, returns the written rows from the write itself, without a second read.

Db2 gives the statements of each write.

Oracle

• Update and read in one block. A PATCH or PUT by key runs its update and the read of the row in one PL/SQL block after the lock.

Oracle gives the statements of each write.

Indexes

Aster does not create indexes on your tables, and the statements above are only as fast as the indexes behind them. Index the columns your screens filter and sort by, the sort column with the key for a sorted page, and the foreign key of every navigation you expand. Indexes gives the rules with a table of requests and the index that serves each. Two databases add rules of their own: on PostgreSQL, a filter that wraps a column in an expression needs an index on the expression (PostgreSQL), and on MariaDB a descending sort needs its own index (MariaDB).

Checking your own requests

Every statement Aster sends starts with a fixed tag, such as /* aster read customers */, so your database's statement statistics and slow-query log show it under the table it reads, and you can run its own EXPLAIN on it. The server's plan-shape metrics show which kinds of request take the most time, and the explain header shows, for any one request, which parts ran in the database and which in Aster's engine. Finding the expensive requests shows where to look, and Observability how to turn the header on.