Aster Web Data / Start here

What runs on your database

The SQL Aster Web Data sends for common requests, the indexes it relies on, the privileges it needs, and how to find its statements and their cost.

Before a new service reads a production database, its DBA wants to know what it will send. Aster Web Data sends the kind of SQL you would write by hand: a SELECT in the database's own dialect that filters, sorts and pages in the database, with the values bound as parameters. The database's planner, indexes and statistics apply to it as to any other query, and each statement is tagged so you can find it in your logs.

A filtered, sorted page

This request asks for the first page of EU customers, sorted by name:

PostgreSQL receives one statement:

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

MariaDB and SQLite receive the same statement in their own dialects:

/* aster read customers */ SELECT c.`id`, c.`name`, c.`email`, c.`region` FROM `test_aster_docs_capture`.`customers` AS c WHERE c.`region` = ? ORDER BY c.`name`, c.`id` LIMIT ?
-- binds: "EU", 20

/* aster read customers */ SELECT c."id", c."name", c."email", c."region" FROM "main"."customers" AS c WHERE c."region" = ?1 ORDER BY c."name", c."id" LIMIT ?2
-- binds: "EU", 20

The comment at the start, /* aster read customers */, names the operation and the table. Columns are compared bare, with no cast and no COLLATE, so an ordinary index on the column serves the filter and the comparison uses the column's own collation. The sort ends with the key, c.id, so pages never overlap. On PostgreSQL the statement also reads the row version, xmin, which becomes the record's ETag.

A page with its total

A list that shows "20 of 1,240" sends $count=true. That adds one COUNT(*) with the same WHERE, and the two statements run in one read-only snapshot, so the total agrees with the rows:

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

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

Related records

On PostgreSQL, MariaDB and SQLite, an $expand is read in the same statement as its parents: a lateral join on PostgreSQL, and a subquery per parent that builds the children as JSON on MariaDB and SQLite. PostgreSQL uses that form only where the navigation's foreign key has an index that leads with its column. Where it has none, each level is read by a second statement that takes the parents' keys as one array (= ANY($1)), and the server logs that navigation at start-up so you can add the index. DuckDB always reads each level by the parents' keys.

When the parents and the children are on different databases, the parents' keys go to the second database in one batch. Here the customers are on PostgreSQL and their invoices on MariaDB:

/* 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

Reads across databases explains how those reads are combined, and From request to SQL how a request is divided between the database and the engine.

Indexes

Aster Web Data does not create indexes on your existing tables. Index the columns your screens filter and sort by, as you would for a hand-written query. These rules apply on every database:

• Filters use ordinary indexes. Columns are compared bare, so a range such as $filter=created ge 2026-01-01T00:00:00Z and created lt 2026-02-01T00:00:00Z is an index range scan, and eq and in on a text column use an index on it.
• A sorted page needs the sort column and the key. Paging adds the key to the end of every sort, so $orderby=name sorts by name, id. An index on name alone is used, and the database then sorts the rows that share a name. An index on (name, id), or (created DESC, id) for a descending sort, serves the whole order.
• A related read needs an index on the foreign key. An $expand of a customer's invoices looks the invoices up by customerId. Without an index there, each parent's lookup scans the table, and on PostgreSQL the read falls back to one statement per level, as Related records describes.

Each database adds rules of its own: a nullable sort column on PostgreSQL needs NULLS FIRST, and a descending sort on MariaDB needs its own index. PostgreSQL, MariaDB and each database's page give them.

Read-only databases and privileges

The database user needs only the privileges its sets use:

• Reads. SELECT on each table a set publishes. On PostgreSQL, also CONNECT on the database and USAGE on each schema.
• Writes. INSERT, UPDATE and DELETE on the tables of the sets that accept writes.
• The start-up check. It reads the catalog, so the user must be able to see the tables. It runs no DDL and writes nothing.

Set read_only: true on a database to guarantee that nothing writes to it. The server then refuses to start unless every set on that database refuses writes and none has a write binding or an action, and it names what writes. Where the database supports it, each session is opened read-only as well.

setup: recreate drops and creates tables, and is for test databases only. A read-only database cannot use it.

Finding the expensive requests

• Statement tags. Every statement starts with /* aster read <table> */, /* aster count <table> */ or the like, so pg_stat_statements and slow-query logs show which table each came from.
• Plan shapes. The metrics group requests by the shape of their plan, with the values removed, and /shapes on the ops port lists the shapes that cost the most time.
• The explain header. For a request you want to examine, a caller with the configured role can ask for its plan: which parts ran in the database and which in the engine, and why.

Observability covers the metrics, the shapes and the explain header.

Limits on a request

Every service caps the rows the engine may hold, the time of each statement and of each request, and the connections it opens to each database. A request that goes past one is stopped with an error that names the limit. Limits lists each limit and its default.