Skip to content
Squonk Visualisation Designer

Why a database

Optional reading: this explains what happens out of sight. See The data centre for what this section is, and Data for how to do things.

The problem with holding a dataset in the browser

The first versions of the Visualisation Designer read a file into the browser and kept every row in memory, as JavaScript objects, for every chart to read. That works well for a few thousand rows, and stops working somewhere before a few hundred thousand, for reasons that have nothing to do with how clever the charts are:

  • Memory. A browser tab can use a few gigabytes at most, and a row held as an object is much larger than the row in the file. Measured in this guide’s performance tests, a table of five columns costs about 440 bytes per row in the browser, so a million rows is about 440 MB, and ten million would be over 4 GB, more than a tab will hold. A table of structures is worse: a docking run of 35,090 poses with 46 fields took 376 MB, about 10 KB for each molecule, because every structure is a block of text of several kilobytes.
  • Time. Every row has to be read, parsed and typed before anything can be drawn: 2.4 seconds for that docking run, about a second per million rows of a plain table, and a long wait for anything bigger. And it has to be done again every time the page is opened.
  • Sharing. The data exists only in one browser tab. A second window, a colleague, or the same person tomorrow starts again from the file.
  • Searching structures. To find the molecules containing a substructure, the browser has to test them one by one. With RDKit running in the browser that takes about 0.4 ms for each molecule, so 35,090 molecules take about 15 seconds, and a million would take about seven minutes.

What changes when the data is in a database

A database is built for exactly these jobs, and it is not in the browser.

  • Charts ask for what they draw. The question is “the x and y of 10,000 evenly spread rows” and not “all the rows, please”. See How charts get their data. The browser’s memory use then stays flat as the dataset grows: 10,000 points are about 250 KB whether the dataset has ten thousand rows or ten million.
  • The data is read once. Importing a file puts it into a table; opening the application again, or a second window, or a colleague’s, finds it already there. Types are worked out once, from every value in the file.
  • Heavy work is a job. Importing, calculating a column, building an index or joining two datasets run on the server as jobs with a progress bar. You can cancel one, and closing the browser does not stop it.
  • Changes are all or nothing. The database makes a change inside a transaction, so an edit that fails half way, or that you cancel, leaves the dataset exactly as it was. That is what lets Edit fields rename, retype, index and delete in one go without a half-edited dataset ever being visible to a chart.
  • Searching is indexed. A chemistry index can answer a structure search without looking at most of the molecules. See How molecules are stored.
  • The tables are ordinary tables. A dataset is a plain table named for it, with one column per field. Anything that speaks SQL can read it, which keeps the data from being locked inside the application.
  • Each project is separate. Every Data Manager project has its own schema in the database and its own credential, and the database itself enforces that one project’s credential reaches only that project’s tables.

What the browser still does

The browser is not idle: it draws. Observable Plot turns the numbers it receives into the picture, RDKit running in the browser draws each molecule’s 2D structure from its text, and NGL draws the 3D scene. Hovering, brushing and selecting all happen there. What moved is the holding and the counting: the database holds every row and does the arithmetic over all of them, and the browser draws the result.

What it costs

A database is another thing that has to be there. The data centre is provisioned for a Data Manager project and the Visualisation Designer connects to it; without one, the application has no datasets to offer (the demo gallery, which has no server, keeps its data in the browser and is subject to the limits above). There are limits of its own too, which the performance page measures: a database statement is allowed 60 seconds when a chart asks it, and 30 minutes when it is part of a job such as an import or an index build, and an administrator can change both.