Repository navigation
Mason v0.16.0
A preview of a join (a LIMIT with no ORDER BY) now stops as soon as it has its rows, where it sorted every joined
row: milliseconds where it took seconds to tens of seconds over a table of a hundred million rows. A page of an outer
join sorted by its preserved table's columns sorts that table's first rows alone. A statement that takes a second or
more has its plan recorded with its row of the request log (mason logs --requests --id <request id>). Statement times
are whole milliseconds everywhere, and the synced data's size counts its write-ahead log. Every project has something
to do first: its pin moves. A tool that pages through a join with no ORDER BY may now see a row twice and miss another.
Before you upgrade
- Move the pin:
version: 0.16.0inmason/mason.yml. A project pinned to 0.15.0 is refused by the 0.16.0 command
and image, with the way out named: before 1.0 they run a project pinned to their own minor version only, at their own
patch or an older one. Set the pin and start 0.16.0 in the same step. - On a hosted replica, upgrade the replica before you deploy a project pinned to 0.16.0.
mason deployasks the
replica's Mason version first and refuses a project whose pin the replica would not run, naming both versions and the
ways out;mason replica upgrade <org>/<name>is one of them, where the service offers it. - If a tool pages through a join with
LIMIT n OFFSET mand no ORDER BY, and the pages matter, give it an ORDER BY.
Every such statement may now give overlapping pages ("What changes").
New
- A statement that takes a second or more has its plan recorded with its row of the request log, so why it was
slow can be read afterwards without rebuilding the plan by hand. Once the statement has ended and its answer was
sent, Mason runs a plainEXPLAINof it in the background: planning only, nothing run a second time. The plan says
the tables it read and how fresh each was, the SQL sent to the synced data, and its operators with their estimated
rows. Read it inmason logs --requests --json(plan,planTruncated), in a row's details on Observability ›
Queries, or whole with the newmason logs --requests --id <request id>. A plan is at most 8 KiB and says when it
was cut. One plan is recorded at a time: a slow statement that ends while another's is being recorded gets none, and
the log says so. A statement bound with parameters has its operators recorded without their details, which could
show the values it was bound with. A cancelled statement has no plan. Neither does one refused before it reached
the synced data, or refused as SQL Mason does not support. It is kept as the request log is (MASON_HISTORY_DAYS).
What changes
- A preview of a join with a LIMIT and no ORDER BY stops as soon as it has its rows
(SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id LIMIT 100, what a BI tool sends to show a table). Before,
where each join matched on a primary key, Mason sorted every row of the join to keep its pages stable. That took
seconds to tens of seconds over a table of a hundred million rows; now it takes milliseconds. The pages of every
LIMIT with no ORDER BY over a join may now overlap:LIMIT n OFFSET mcan show a row twice and skip another, as
SQL and Postgres allow. Add an ORDER BY where the pages matter. - A page of an outer join sorted by its preserved table's columns limits that table first: a LEFT JOIN sorted by
the left table (SELECT … FROM a LEFT JOIN b ON a.id = b.a_id ORDER BY a.created_at DESC LIMIT 100, a filter ona
allowed), and likewise a RIGHT JOIN sorted by its right table or a chain of LEFT JOINs sorted by its first table.
Mason now sorts only that table's first rows, then joins them, instead of joining and sorting every row of the other
side. Over 25 thousand × 30 million rows it took about a fifth of the time (0.9 s to 0.2 s, warm). It still reads
the other table's join column whole, so on a cold disk a table of a hundred million rows still takes seconds (about
20 s at 150 million rows, an estimate). Unchanged: a sort or a filter that reads another table, an INNER, CROSS or
FULL join on the way, a DISTINCT ON, a subquery in the WHERE, and a LIMIT or OFFSET bound as a parameter. - How long a statement took, and the average, p50 and p95 of those times, are always whole milliseconds with
thousands separators (19,800 ms,810 ms,27 ms), so a column of them can be compared at a glance. Before, the
unit changed at a second and at a minute (19.8 s,2 min 5 s). This applies on the web page (Observability ›
Queries' Duration, the Overview's p95 card, statements table and query errors, Usage's cards, hours, p95 chart
read-out and slowest statements) and in the text ofmason logs --requests,mason statusandmason usage.
--jsonstill gives numbers. A chart's axis, a statement still running, a sync's time, an interval and an age keep
their form. On Observability › Queries, the Statement cell now has its own+beside the statement. It opens and
closes the same row as the Query id's+.
What was wrong and is now right
- The synced data's size left out its write-ahead log: the Overview's "The synced data's file" card,
mason status,mason usageandmason_copy_bytesread 12 KiB for a file whose log held 1.3 MB. They now count the log. - A small synced table's size could never be shown, and the page said it was coming at the next measurement. A
table's size is measured once its rows are written from the write-ahead log into the file, which happens on its
own, for instance when the log passes 16 MiB or Mason stops, so a small table's can wait for the life of a run.
Until then the Tables view shows—, whose tooltip says why;mason usagelists no size for the table, and says
why under the sizes when another table has one (and inmason usage --help).
Known
- A generated table can't yet hold a numeric that divides (an average is one); is a variance or a standard deviation, which Postgres computes exactly at decimals it picks per value, rounded, cast to numeric(p,s) or not; is a sqrt, exp, ln, log or power, whose decimals Postgres picks per value, rounded, cast to numeric(p,s) or not, whatever the decimals of what it is computed from; is a JSON number's text cast to numeric with no scale (cast to numeric(p,s), it is stored); has more than 18 decimals, at the end or on the way; sums values whose decimals the rows decide; is a CASE, GREATEST, LEAST or COALESCE of values with differing decimals; or is cast from a double; nor a domain or a composite, and
kind: liveis refused. Each is refused by file and key before anything is built, said as Postgres types the value (a sqrt or an average of a double is a double, so cast to numeric it is a cast from a double), wherever it sits in the column (under an operator, a coalesce, a round, a cast or an aggregate, or in a scalar subquery, aWITHquery, a subquery inFROMor aUNION's arm), with the way out your own SQL with the cast moved and the aggregate kept (sum(sqrt(x)::double precision)), or theWITHquery, subquery orUNIONthe cast belongs in, given whole. Each of these keeps its reason read back from its text (::text,::varchar,|| '', a jsonb's->>, aWITHcolumn), cast to numeric(p,s) or not, so a typmod over a variance's text never stores an approximation; only a stored column's or a JSON number's text read as a numeric is said as a text: a JSON number's, a stored double's or any other. A run that fails (a value past 38 digits,bigint out of range,division by zero) records Postgres's error, SQLSTATE and DETAIL (numeric field overflowsays the precision and scale it must fit, on every path), and is tried again after its back-off. mason statusshows generated tables; the web page doesn't yet.- Pages of every LIMIT with no ORDER BY over a join may overlap:
LIMIT n OFFSET mwith no ORDER BY over a join has
no stable order, so a tool paging one can see a row twice and miss another. Add an ORDER BY where the pages matter. - A replicated table whose re-read never completes can rejoin the stream with no catch-up, and miss what the stream
passed over, as in 0.8.0 to 0.15.0. It was found by reading the code and has not been reproduced.