Back end

SQL benchmark database

Schema, views, window functions and indexes, with an in-browser SQL playground.

Done Sprint S10 · 5 points

In short, no jargon for non-developersRead the summaryClose

What it is: A database that stores speed measurements of the other projects, and that you query right in the page.
What it is for: To organise results so as to answer precise questions quickly: which program is fastest, on which files.
What it shows: Command of databases, and the habit of measuring before concluding: these measurements revealed a slowdown, fixed later.

Taught in L2 (databases)

How the pieces fit together

Benchmark (Node, lib-c, topology) → Flat CSV (one row per repetition): writes
Flat CSV (one row per repetition) → PostgreSQL (schema, constraints): loaded in SQL
PostgreSQL (schema, constraints) → Views (windows, regressions)
pgTAP (tests in the image) → PostgreSQL (schema, constraints): checks
PostgreSQL (schema, constraints) → SQLite copy (sql.js, on the page): same schema, SQLite dialect

A SQLite copy of the database (same tables, same views) runs in your browser with sql.js: read anything, even break everything, the database reloads.

Examples:

SQL query (SQLite dialect)

Ctrl+Enter (⌘+Enter on Mac) to run. At most 200 rows are shown; a query is stopped after 5 s.

Tables and views

PostgreSQL: Stores the performance measurements (campaigns, meshes, files, timings), with constraints and a trigger.
SQL: The analyses: window-function views, log-log regression of the complexity, regression detection, an index justified by the query plan.
sql.js: SQLite compiled to WebAssembly: the same database can be queried in the browser, on the project page.
pgTAP: The database tests: schema, constraints, views and expected results, in CI.