Repository navigation
This release makes TinyJoin faster across the comparative benchmarks, with no change to the API or to the storage format. With OPFS storage, TinyJoin is the fastest of the three engines in all 20 timed workloads, up from 11, where it took up to 1.5 times as long as the faster engine. Range aggregates take half the time they did, upserts, LIKE scans, a transaction's range UPDATEs, and deletes by key about two-fifths less, updates by key over a third less, the LIKE delete three-tenths less, 10,000 inserts in a transaction, with or without an index on the table, and 200-row inserts over a quarter less, selects by key a little under a quarter less, and the 8,000-row range delete a fifth less.
Scanning rows
A WHERE clause's simple terms, those that compare an INTEGER or TEXT column with constants, match a LIKE pattern, or test for NULL, are compiled into tests of the stored record, and a table scan applies them to each leaf's rows in one pass over the leaf's bytes, decoding and presenting only the rows they accept. A LIKE pattern of literal segments is matched as bytes. A DELETE or UPDATE run outside a transaction scans the same way, where before it decoded every row and judged each with the whole predicate. In the benchmark, 100 range aggregates without an index fell from 48 ms to 25, 100 LIKE aggregates from 113 ms to 70, and a transaction's 100 range UPDATEs from 62 ms to 41; engine-only, the LIKE delete takes a fifth less time before V8 has optimized the code and a tenth less after.
Writing rows by key
A prepared INSERT, including one of many listed rows, a lone upsert on the primary key, and an UPDATE or DELETE of the row a primary-key equality names, run inside a transaction, is planned straight from its template and parameters: its key is encoded from them, the row looked up once, and the change built as the record a commit writes, rather than binding the statement's syntax tree and planning it as any statement is. A test stages the same statements both ways and checks that the transactions, the results, and the committed databases agree, and that a statement one way refuses is refused the other way with the same error. Staging then looks a statement's table up once rather than for every row, a listed row's values are resolved to literals or parameter positions once, when the statement is prepared, rather than for every row of every execution, and an insert into a table none of whose staged rows is a delete skips the search of the staged rows for an entry it would replace, which planning would have read. Engine-only, 10,000 inserts in a transaction take a quarter less time, updates by key a fifth less, upserts a third less, and 200-row inserts a third less.
Such an UPDATE, DELETE or upsert then encodes its key once, and takes the row it changes as the entry the transaction keeps of it, copied out of its page once. The key had been encoded twice, or three times for an upsert, and the row copied twice. Before V8 has optimized the code, an upsert runs 3% fewer instructions, and an update or delete by key 1% fewer.
Such a DELETE is then staged by its encoded key. It had been planned as a map of its key's columns, which was built for every delete only to be measured twice and dropped. Where no key column holds text, the table already knows what that map would measure, and none is made. Before V8 has optimized the code, a delete by key runs 5% fewer instructions.
Changing many rows
The rows a statement changes share one name for their table, kept once per statement rather than copied for every row, and the entries an index gains and loses for them are encoded into one buffer rather than a vector each. A statement's work is charged once rather than for every row, a row's estimated bytes are added and checked in place rather than through calls, a table's record layout keeps the JSON overhead of its rows, which planning a statement by key measured for every statement, and a result's changed keys share their table's schema rather than copying its name and key columns. The Worker writes each response into the text kept from the last. In Node's V8, deleting 8,000 rows by an indexed range takes 12% less time optimized and 18% less before optimization, a LIKE delete 6% less, and 10,000 inserts in a transaction 6% less before optimization.
A DELETE outside a transaction, over a table with no index and no foreign key, measures each row it removes rather than copying it, since nothing would read the copy, and charges per statement what it charged per row. Engine-only, the LIKE delete takes a further 6% less time before optimization and 15% less after.
Staging rows
A transaction keeps each table's staged rows in one vector, in the order they arrive, and finds a row by a hash of its encoded key rather than by searching a B-tree map. The rows are put in key order only when a scan or the commit needs them, by sorting the keys that arrived out of order and merging them in. Engine-only, 10,000 inserts in a transaction take 11% less time before V8 has optimized the code and 10% less after, 200-row inserts 16% and 15% less, and updates, upserts and deletes by key 3% to 5% less; the engine is 2.2 KiB smaller compressed.
A statement that changes one row, as nearly every statement in a transaction does, builds its entry in two vectors that the transaction keeps from one statement to the next, where it built four vectors, a copy of its table's name, and two sets of unique-index claims that almost always stayed empty. The search for its table's staged rows and the probe for its key, each made to read the row it replaces, are kept for installing its entry, where each was made again. Before V8 has optimized the code, an update, upsert or delete by key and an insert in a transaction each run 4% to 5% fewer instructions, with a third fewer calls in staging.
Reading pages
A page the cache already holds is fetched with one call, the cache's lookup. A read went through seventeen: its checks that the pager is usable, that the page is a data page the active root allocates, and that it is not reserved, each a function of its own, and then the cache's readers and the decoding of the page's envelope. A read now tests all of that in one place, and one that does not pass there takes the old path, which reports what failed. A statement that finds a row fetches a page at each level of its tree, so before V8 has optimized the code an update, delete or select by key runs 2% fewer instructions, and a scan 3% fewer.
A lookup then searches each of those pages in one loop, which reads the keys it probes in place and decides a key of eight bytes, as an INTEGER's is, by comparing two words. Each probe had gone through a reader and a comparison of byte slices, four calls in all. Before V8 has optimized the code, an update, upsert, delete or select by key runs a further 5% to 6% fewer instructions.
Writing pages
A commit stores each cell's header fields and its slot with no call, tests a value's size where the cell is made, leaving the storing of a large value in overflow pages out of line, and hashes a replaced entry's key once for the fingerprints of the value it held and of the one it takes. Before V8 has optimized the code, the commit of 10,000 inserted rows makes a sixth fewer calls and takes a fifth less time, and a tenth less when the table has an index.
A write's batch of entries for an index, and its records for the catalog, are put in order by the one sort the engine already compiles for keys beside their positions, where the two shared a second, and a row's old and new index entries are compared only when it has both. The engine is 1.4 KiB smaller compressed, and deleting 8,000 rows by an indexed range runs 2% fewer instructions before optimization.
A commit that changes some of a leaf's rows then copies the rest in runs. It had sized, copied and read again each cell it kept, one at a time. It now checks each cell once, as it merges the changes in, comparing keys of eight bytes as words, and copies each run of cells it keeps as one piece, moving their slots as far as the bytes moved. Before V8 has optimized the code, the commit of 1,000 updates or deletes by key takes over a third less time, a statement that commits by itself, as each single insert does, runs 14% fewer instructions, and the LIKE delete 23% fewer. The check is also wider than it was: every cell of a leaf a commit reaches is tested for its packing and its order, so a damaged leaf that still passes its checksum fails that commit, whether or not the commit would have changed it, rather than being written back.
Comparing memory
The WebAssembly build compares memory eight bytes at a time, in place of the byte loops the compiler provides, which nearly every comparison of keys in a B-tree search, a sort, or a transaction's staged rows ran. It matters most before V8 has optimized the code, when a statement over many rows runs the loop for every one of them: under baseline compilation, 10,000 inserts in a transaction take 14% less time, and creating two indexes 18% less.
The client and the Worker
A result's command is one of a few fixed words, kept as a static string and compared rather than tested against regular expressions; the client builds a prepared execution's request in its exact shape; the Worker sizes a statement's scalar parameters by their shape and writes them into its request in one pass; and a response's keys are counted rather than walked where each is checked by name. Warm in Chromium, an update by key in a transaction spends about 2 µs less in the Worker's JavaScript and 1 µs less in the page's.
A statement and its result now cross between the page and the Worker as flat arrays. A statement's request is one array: its text or its prepared statement, its transaction, and then its parameters themselves. The response to a statement that published nothing, which is every statement in a transaction and every read, is one array too: the command, the revision and the row count, followed by a read's rows as text, or by the table a write changed and its changed keys. Each was an object holding objects, whose every key and value a message's structured clone writes on one thread and reads on the other, and a write's result was JSON text that the engine wrote, the Worker decoded and the page parsed. The engine now writes such a result as numbers into a small buffer that the Worker reads in place. A result of any other shape, such as one with RETURNING rows, one that changed several tables, or one that committed, crosses as it did.
The page does less for each statement as well. It counts a transaction's statements in flight, where it kept each one's promise in a set, and a prepared statement's in a second, with a reaction on each to remove it, and it checks a response with plain comparisons. That matters most in a page's first few thousand statements, which is what every benchmark sample times: V8 has not yet optimized the client there, and each small helper function is a call. In Chromium, sample for sample against the build before, 1,000 updates by key in a transaction fell from 30.5 ms to 24.5, upserts from 29.5 to 22.9, deletes by key from 30.0 to 23.8, and selects by key from 27.7 to 22.1; 10,000 inserts in a transaction fell from 247 ms to 200, and into an indexed table from 259 to 207.
The Worker also answers a statement or a commit before it merges the keys that were changed into the change event that follows, where it merged them first. A commit that changed 1,000 rows is acknowledged about a quarter of a millisecond sooner.
A statement's parameters are read from their array one by one, so an array that could not be copied whole, such as a reactive proxy of an array of plain values, is now sent, where it failed with WORKER_POST_FAILED.
Download size
The compressed download is now 354 KiB, up from 339 KiB in v0.5.0, for the record tests, the point planner, the word-wise comparison, the flat statement messages, and the rewriting of leaves by runs.
Upgrading from v0.5.0
Nothing is required of an application that installs the package whole: the API and the storage format are unchanged.
The Worker protocol version moves from 11 to 12. Client and Worker ship together and are upgraded together by a normal install, so this affects only a deployment that pins or caches a Worker file independently of the client bundle. A mismatched pair fails cleanly with PROTOCOL_MISMATCH, and tabs running different releases fail with DATABASE_VERSION_MISMATCH, rather than misreading each other's results.