Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

Performance

What each query type sends to each database, and the optimizations behind it

This page lists, query type by query type, what VibORM does to keep round trips, statement size and query plans small on each database. It describes the current engine (src/query-engine/raptor3/). Every optimization keeps the answer the unoptimized form would give; where the two could differ, the engine takes the longer route.

Reading the tables

Each section has one table. Its columns are the five ways VibORM reaches a database:

Column Drivers Route
PostgreSQL pg, postgres, pglite, bun-sql Interactive transaction
Neon HTTP neon-http Native batch, no interactive transaction
MySQL mysql2, planetscale Interactive transaction
SQLite sqlite3 (better-sqlite3), bun-sqlite, libsql Interactive transaction
D1 d1 Native batch, no interactive transaction

A round trip is one exchange with the database: a lone statement, a whole native batch, or a BEGIN/COMMIT. Interactive counts include the BEGIN/COMMIT of the operation’s own transaction.

Where a number appears, it comes from the benchmark reports in benchmarks/ (perf-1.3.3.md, perf-1.4.0.md) or from the nested-write census. The census ran on PGlite and better-sqlite3, and the Neon HTTP and D1 columns come from the same two engines driven through the batch route. The MySQL column was derived from MySQL’s capability flags on SQLite, not measured on a MySQL server; it is marked (sim.).

What decides the differences

Most rows of the tables follow from a capability the adapter or driver states (src/adapters/adapter-capabilities.ts):

Capability PostgreSQL Neon HTTP MySQL SQLite D1
RETURNING on writes ✅ ✅ ❌ ✅ ✅
Data-modifying WITH ✅ ✅ ❌ ❌ ❌
Upsert on a named conflict target ✅ ✅ ❌ (any unique key) ✅ ✅
Row locks (FOR UPDATE) ✅ ✅ ✅ ❌ ❌
A locking miss takes a lock ❌ ❌ ✅ (gap lock) — —
Shared relation bodies (WITH members) ✅ (default) ✅ (default) ❌ ❌ ✅
Many-to-many filters joined to the junction Unbounded reads Unbounded reads ❌ ✅ ✅
Bind values per statement 65,535 (PGlite 32,767) 65,535 65,535 32,766 100
Limits worked around Neki: 256 relations Neki: 256 relations Recursion iterations — 5 compound terms, expression depth 100

Every operation

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
An operation that needs one statement opens no transaction ✅ ✅ (no batch) ✅ ✅ ✅ (no batch)
Inside a callback $transaction, a savepoint only for an operation of several statements ✅ — ✅ ✅ —
Argument schemas built on first use of each operation (cold start) ✅ ✅ ✅ ✅ ✅
Clients on the same schema share the engine’s per-model views ✅ ✅ ✅ ✅ ✅
Result decoder compiled once per result, not per row ✅ ✅ ✅ ✅ ✅
Collection reads returned as positional rows — — — better-sqlite3 ✅
Prepared statements reused postgres with prepare: true — — 100-entry cache per database (better-sqlite3), query() cache (bun) —

The SQL of an operation is not cached across calls: it is built from the call’s arguments each time. JSON and timestamp decoding was reworked in 1.3.3: a 100-row read decodes about 21% faster and allocates 28% less.

postgres (postgres.js) runs with prepare: false by default for pooler compatibility; options.prepare: true lets it reuse prepared statements on a session that keeps them.

findUnique

One statement on every database.

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
findUnique calls in the same tick share one statement (findUniqueBatching: true) ✅ ✅ ✅ ✅ ✅ (split at 100 binds)
No ORDER BY: a unique key names at most one row ✅ ✅ ✅ ✅ ✅

Opt-in since 1.4.1 (the shared read runs in the first call’s async context). Calls are batched outside transactions when they target the same model, unique key and projection, and their where is the key alone (string, int or bigint members). The batch is split at the driver’s bind budget. 50 lookups in one tick: 50 statements become 1 (SQLite 1.29 → 0.63 ms, PGlite 7.75 → 0.60 ms). Implementation: src/query-engine/find-unique-batch.ts.

A findUnique states no order since 1.15.0: PostgreSQL planned the sort of its one row all the same, 15 % of the statement’s server time (35 against 41 µs) and 9 % of the call (40 against 44 µs), local PostgreSQL 18.

findFirst and findMany

One statement on every database, relations included.

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
Ordering by a to-one relation: one LEFT JOIN per path ✅ ✅ ✅ ✅ ✅
Cursor as one row-value comparison an index can use ✅ ✅ ✅ ✅ ✅
Mixed-direction cursor bounded on its leading key ✅ ✅ ✅ ✅ ✅
distinct as one window With orderBy (else DISTINCT ON) With orderBy (else DISTINCT ON) ✅ ✅ ✅
_count ordering as a correlated count ✅ ✅ ✅ ✅ ✅
  • To-one ordering reads each relation path once through a LEFT JOIN instead of a correlated subquery per order term. 100k rows: SQLite 38 → 11 ms, PGlite 413 → 79 ms.
  • Cursors. When every order term runs the same direction over non-nullable columns, the cursor is one row-value comparison. With mixed directions, a range bound on the leading key is added so the index still narrows the scan. At row 75k: SQLite 90 → 0.24 ms, PGlite 143 → 1.1 ms.
  • distinct keeps the first row of each group with one window function, about 2× faster at 200k rows. PostgreSQL uses native DISTINCT ON when no orderBy is given.

Relation projections (select and include)

A read with relations is one statement: each relation is a correlated JSON subquery, nested to any depth, _count and polymorphic relations included.

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
One statement for the whole relation tree ✅ ✅ ✅ ✅ ✅
Many-to-many read from the junction joined to the target ✅ ✅ ✅ ✅ ✅
Lean JSON spelling Redundant column aliases dropped Redundant column aliases dropped Redundant column aliases dropped json_group_array(json_array(…)), no extra json()/COALESCE Same as SQLite
Related rows carried as arrays of values, no key per column ✅ json_build_array ✅ json_build_array ✅ JSON_ARRAY ✅ json_array ✅ json_array
Repeated relation bodies stated once in a WITH ✅ NOT MATERIALIZED leaves ✅ NOT MATERIALIZED leaves ❌ ❌ ✅ MATERIALIZED non-leaf members
Recursive projections LATERAL derived table LATERAL derived table LATERAL derived table Correlated CTE per reader Correlated CTE per reader
To-one relation joined LEFT JOIN LATERAL ✅ (not under relationsPerStatement) ✅ ❌ ❌ ❌
Recursive edges ordered by key read from the walk, no table rejoined ✅ ✅ ✅ ✅ ✅
Recursive nodes read by key (opt-in: fewRows) ✅ ✅ ❌ (ignored) ❌ (ignored) ❌ (ignored)
  • Recursive edges. A recursive projection’s siblings ordered by key (the default) are sorted from the walk’s own columns. Joining the walked rows back to their table only to order them could make PostgreSQL scan the whole table for every listed row: 1,040 rows of a 5,000-row tree with their ancestors went from 255 to 133 ms (1.10.0). A sibling order on a non-key field, a relation or a count still joins the table.
  • Lateral to-one relations. On PostgreSQL a to-one relation in a read is a LEFT JOIN LATERAL rather than a subquery in the select list, so the planner can cache the related row per key (Memoize) where many rows share it: ten posts with their author and the author’s posts, 178 → 145 µs on the server, 14 % of the call. A read bounded by take or skip is paged first and joined after, then ordered again: a join between a sort and its bound would make a top-N sort a whole one (300,000 posts ordered by an unindexed title, 20 with their author: 54 ms as subqueries, 604 ms joined after the sort, 28 ms paged first). Not in a locking read, a distinct read, a read ordered through a relation and bounded, a bounded related page, a polymorphic relation or a recursive projection, nor under a relation budget (relationsPerStatement, Neki), where each join would be one relation more.
  • Related rows are arrays, not objects. A related row, and a _count, an aggregate or a $through row, is the JSON array of its values, read back by position: no key is written per column, bound as a parameter or parsed again for every row. On a local PostgreSQL 18, three reads of two relation levels with a filter at each take 4 % to 7 % less end to end than with json_build_object (513 → 475 µs, 425 → 406 µs, 731 → 683 µs), and 500 users with their posts and each post’s author take 17 % less for an answer 48 % smaller. MySQL 8.4 takes 5 % to 6 % less and SQLite 10 % to 15 % less on the same three reads. SurrealDB answers native records and Snowflake keeps keyed objects.
  • No whole-row reads. row_to_json(alias.*) would also drop the keys from the statement, but PlanetScale Postgres (Neki) refuses a whole-row read, and the engine does not know it talks to Neki before its first statement. An array of values needs no whole-row read and is smaller still.
  • Alias trimming makes plain PostgreSQL and MySQL reads about 9–11% shorter. The SQLite JSON spelling took a 9.4 MB include from 181 to 109 ms.
  • Shared relation bodies. A statement that repeats the same relation body (typical of permission rules) states it once as a WITH member and tests each use against its key set. On PostgreSQL the leaf members are NOT MATERIALIZED, so the planner can still push filters into them (a nested include at 100k rows: 19.65 → 1.48 ms). On D1, members that other members read are MATERIALIZED, which brought four permission reads that failed out of memory under the limit (63,867 → 14,345 VDBE opcodes). In-process SQLite keeps sharing off: there it measured about 230 µs slower per statement. sharedRelationBodies: false turns it off on the PostgreSQL drivers.

Filters (where)

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
some A OR some B merged into some (A OR B) ✅ ✅ ✅ ✅ ✅
{ $row, up } chains as one EXISTS over a join ✅ ✅ ✅ ✅ ✅
Many-to-many filter joins the junction to the target Unbounded reads Unbounded reads ❌ ✅ ✅
Recursive walk carries the columns its next hop reads ✅ ✅ ❌ (walk kept in place) — ✅
Past 256 relations: walks as reaching sets, rows joined to shared key sets Neki Neki — — ✅
Expression depth kept under 100, unions nested 5 terms at a time — — — — ✅
Equal values bound once past the bind budget — — — — ✅
  • Merged quantifiers shorten permission-policy SQL by 8–12%. $row chains as one EXISTS took a PGlite policy count from 864 to 87 ms.
  • Junction-joined many-to-many filters let the planner turn the filter into a hashed semijoin. On PostgreSQL that is only done for reads that scan (count, aggregate, groupBy, findMany without take; a count went from 479 to 7 ms), because it plans about 0.2 ms slower and a bounded read never wins that back. On SQLite and D1 it is always used (a count: 1,240 → 61 ms).
  • Neki’s 256-relation limit. PlanetScale Postgres (Neki) refuses a statement that references more than 256 relations. Past that bound only, a walk the statement repeats becomes one recursive set, then rows test the shared sets (arrays on PostgreSQL, joined flag members on D1). The bound is read from the server version or Neki’s first refusal; relationsPerStatement sets it. All nine $access reads of the permissions fixture now run on Neki (96–683 → 95–253 relations); on PGlite at 10× the fixture, document with every relation went from 4.8 s to 0.14 s.
  • MySQL keeps recursive walks in place because a recursive CTE fails after cte_max_recursion_depth iterations (1,000 by default).

count, aggregate, groupBy and exist

One statement on every database.

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
Unpaged count, aggregate and exist read the filtered table directly ✅ ✅ ✅ ✅ ✅
Many-to-many filters joined to the junction (see Filters) ✅ ✅ ❌ ✅ ✅

A count over 200k SQLite rows went from about 1.8 ms to about 30 µs when the derived-table wrapper was removed.

$transaction

Shape PostgreSQL Neon HTTP MySQL SQLite D1
Array of operations One transaction One native batch One transaction One transaction One native batch
Callback One transaction; a savepoint per operation of several statements Not supported Same as PostgreSQL Same as PostgreSQL Not supported

The savepoint inside a callback transaction keeps each nested write all-or-nothing when your code catches its error and continues, and lets an upsert or connectOrCreate that lost a create race re-run and converge.

create

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
Create answered by its own INSERT … RETURNING ✅ ✅ ❌ insert, then read by key ✅ ✅
include answered in the same statement ✅ WITH … RETURNING * ✅ WITH … RETURNING * ❌ ✅ relation subqueries in RETURNING ✅ relation subqueries in RETURNING
A nested to-many create list as one multi-row INSERT ✅ One INSERT per row, same batch ✅ ✅ One INSERT per row, same batch
Nested create answered by the root’s INSERT, no final read ✅ — (rides the batch) ❌ ✅ — (rides the batch)
  • include in the write’s statement (1.4.0): create with an include went from 4 round trips to 1 on PostgreSQL and SQLite, and from 2 statements to 1 in the Neon HTTP and D1 batch. The engine keeps the separate read when the projection reads the written table, when decoding could refuse the row, or on D1 when a shared body would need a WITH.
  • No final read after nested writes. A create with nested writes used to end by reading its row back. When nothing written after the root’s INSERT can change that row, the INSERT’s own RETURNING answers instead: a create with a nested create went from 5 to 4 round trips. The read is kept when a later nested write names the same model, or deletes or re-keys a row the root references through its foreign keys.
  • Generated keys on the batch route cross statements through a scratch table (__viborm_batch_refs): one statement on Neon HTTP (a data-modifying WITH), two on D1. It is only used when a generated key is needed by a later statement.

createMany and createManyAndReturn

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
One INSERT per run of rows with the same columns ✅ ✅ ✅ ✅ ✅
Chunks sized by the bind budget, measured on the compiled SQL ✅ ✅ ✅ ✅ ✅ (100 binds)
skipDuplicates inside the grouped statement ✅ ON CONFLICT DO NOTHING ✅ ❌ one row per savepoint ✅ ✅
createManyAndReturn RETURNING per chunk RETURNING per chunk One grouped INSERT, re-read by key in chunks; row by row where a key is generated by the database or with skipDuplicates RETURNING per chunk RETURNING per chunk

Several chunks run in the operation’s own transaction, or in one native batch on Neon HTTP and D1. Under the permissions extension, the check on each created row sits inside the INSERT … SELECT and adds no statement.

update

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
Update answered by its own UPDATE … RETURNING ✅ ✅ ❌ locate, update, re-read ✅ ✅
include answered in the same statement ✅ ✅ ❌ ✅ ✅
Nested update: the root UPDATE finds and locks its row, no SELECT … FOR UPDATE first ✅ — ❌ ✅ —
Nested update answered by the root’s UPDATE, no final read ✅ — ❌ ✅ —
  • Update with an include went from 5 round trips to 1 on PostgreSQL and SQLite, and from 2 statements to 1 in the batch.
  • The locating UPDATE. A nested update used to lock its root row with a SELECT … FOR UPDATE, then update it by key. The UPDATE now carries the selector itself, takes the same lock and raises the same NotFoundError when no row matches. With the final read gone too, an update with a nested create went from 6 to 4 round trips. The SELECT is kept when something must run between it and the UPDATE: a connect of a relation the root holds the key of, a nested lookup, a change of a relation key, or a field a later step reads off the located row.

updateMany and deleteMany

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
limit in one statement key IN (SELECT … LIMIT n) key IN (SELECT … LIMIT n) Native ORDER BY key LIMIT n key IN (SELECT … LIMIT n) key IN (SELECT … LIMIT n)

MySQL cannot read the table it is modifying in a subquery, so a selector that reads a relation is first materialized in a derived table there.

upsert

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
Root upsert as one INSERT … ON CONFLICT (key) DO UPDATE … RETURNING ✅ ✅ ❌ probe, then one arm ✅ ✅
The probe locks the row it finds, no confirming re-read ✅ — ❌ unlocked probe, then a locked re-read ❌ re-read kept (no row locks) —
  • The ON CONFLICT fold took an upsert with an include from 5 round trips to 1 on PostgreSQL and SQLite, and from 2 statements to 1 in the batch. It is not used for a conditional upsert, relation writes in create or update, an update that writes the key, a create that does not spell the key the where names, or a selector narrowed by a row scope (permissions, soft delete). MySQL’s ON DUPLICATE KEY UPDATE fires on any unique key, which could silently update a row the call never named, so MySQL always probes.
  • An upsert a write hook concerns must say which arm it took, and keeps the fold. On PostgreSQL its RETURNING carries xmax = 0, true for an inserted row. SQLite’s statement cannot say, so a probe of the key goes just before it, in the same transaction (in the same batch on D1): 2 statements.
  • The probe. Where the upsert is not folded (and for connectOrCreate), a probe decides between update and create. On PostgreSQL it reads FOR UPDATE: a miss locks nothing there, and a hit holds the row, so the second, locking read is gone (one round trip fewer). MySQL’s locking miss takes a gap lock that deadlocks two concurrent creates, so it reads unlocked and confirms the row it found under lock. SQLite has no row lock, so its confirming read is what sees a change landed in between. A to-many nested upsert always confirms that the row it found belongs to the parent.

delete

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
Root delete in one statement ✅ ✅ ❌ (sim.: 2 statements + transaction: the rows locked and read, then deleted) ✅ ✅
include answered in the same statement ✅ snapshot WITH ✅ snapshot WITH ❌ ❌ (RETURNING reads after the cascade) ❌

Nested relation writes

Optimization PostgreSQL Neon HTTP MySQL SQLite D1
A to-many connect or set reads its targets in one statement ✅ ✅ ✅ ✅ ✅
…and links them in one junction INSERT ✅ ✅ (in the batch) ❌ one per link ✅ ✅ (in the batch)
…or, where each target holds the key, rewrites them in one UPDATE ✅ — (one batch either way) ✅ ✅ — (one batch either way)
Planning reads in one native batch — ✅ — — ✅
Writes, checks and the final read in one native batch — ✅ — — ✅
  • Targets read together. A run of sibling lookups of one model (the targets of a to-many connect or set) is read in one statement: the disjunction of the lookups’ own selectors, under the lock each would take. The answer is kept only if every lookup matches exactly one row and no row twice; otherwise each target is read alone, and a missing target fails exactly as before. Lookups that go through the parent’s membership, whose filter reads a relation the same write can change, or that probe for a row connectOrCreate would create are read alone.
  • Targets rewritten together. Where each target holds the foreign key (a user’s posts), every target of the run takes the same UPDATE, so the run sends one, WHERE the targets’ keys. A policy judging the rows it produces judges each of them in that statement, and a refused row refuses the write as its own UPDATE did.
  • On Neon HTTP and D1, the reads that decide the plan run first, as one native batch of their own, and the writes, the checks that abort the batch on a stale premise, and the result read follow as another. Each read answers as it would alone: nothing has been written between them. A read that waits for a write of the same call (a lookup through a key the call changes), a series member’s read and a branch’s read stay alone, at their own place.

Round trips per nested write

Without include. The count before 1.4.0’s nested-write work is in brackets where it differs. MySQL is simulated.

Call PostgreSQL Neon HTTP MySQL (sim.) SQLite D1
create + nested create 4 [5] 1 5 4 [5] 1
create + nested create ×3 4 [5] 1 5 4 [5] 1
create + to-one connect 4 [5] 2 5 4 [5] 2
create + connectOrCreate, found 4 [6] 2 6 5 [6] 2
create + connectOrCreate, missing 5 [6] 2 6 5 [6] 2
create + many-to-many connect ×3 5 [10] 2 [4] 8 5 [10] 2 [4]
update + nested create 4 [6] 2 7 4 [6] 2
update + many-to-many connect ×3 5 [11] 2 [5] 10 5 [11] 2 [5]
update + many-to-many set 7 [10] 2 [4] 10 7 [10] 2 [4]
update + one-to-many set 7 [10] 2 [4] 9 [10] 7 [10] 2 [4]
update + nested delete 6 [7] 3 8 6 [7] 3
update + to-many upsert, found 6 [8] 2 [3] 9 6 [8] 2 [3]
root upsert with a nested write, found 7 [8] 3 9 8 3
delete (foreign keys cascade) 1 1 4 1 1

Over the 72 calls of the census, round trips went from 480 to 397 on PostgreSQL, 484 to 409 on SQLite, 176 to 146 on Neon HTTP and 178 to 148 on D1, every result identical to before.

Opt-ins: what only your data knows

Some plans are faster only when the data has a shape no schema states: an index says which column a read can probe, not how many rows hold one value. VibORM never guesses those. Each is an option you state where you know it is true; left out, the statement is exactly the one sent without it.

Option Where You state What changes Where it applies
recurse: { fewRows: true } select / include each listed row’s walk reaches few rows (a folder chain, a category’s ancestors) each reached row is read by its key instead of joining the table PostgreSQL drivers; ignored elsewhere
recurse: { selective: true } where, permission rules the rows the walk’s some names, and the rows that reach them, are few (the teams the actor belongs to) where the walk is read only inside bodies computed over their whole tables, it is computed once from those rows PostgreSQL drivers and D1 (shared relation bodies)

Measured on PostgreSQL 18 (1.10.0):

  • fewRows: 1,040 rows of a 5,000-row tree with their ancestors 129 → 13.5 ms; a governed 100-row page 6.3 → 1.7 ms. Stated on a walk that reaches most of its table, it is slower (the 9,999 descendants of one root: 1.21×).
  • selective: opening a page under a 40-model permissions policy at 500 organizations 1.4 s → 89 ms (2.0 → 0.8 ms with tenant). Stated on a walk whose matches are many, it reads most of the table (on SQLite, 30,000 teams: 45 → 61 ms).

State an option only when it holds for the data the call reads, and measure your own reads. Performance opt-ins explains each one with the SQL it changes, the plans before and after, and how to check your own reads.

Not optimized yet

  • MySQL answers creates, updates and upserts with a separate read, having no RETURNING.

Was this page helpful?