Skip to content
Squonk Visualisation Designer

Why PostgreSQL and RDKit

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 data centre is PostgreSQL with the RDKit cartridge installed. This page gives the reasons for each half, and for the pair.

Why PostgreSQL

The datasets are tables - rows of numbers, text and true/false values, plus structures - and the questions asked of them are the ordinary ones of a database: count the rows in each range, take the columns for a sample, find the rows matching a condition, combine two tables on a key. PostgreSQL is a mature, widely operated answer to those, and it has the properties the data centre leans on:

  • Transactions, including for changes to the table itself. Adding a column, renaming one, building an index and swapping in a new type all happen inside a transaction, so a change that fails or is cancelled leaves nothing behind and a chart never sees a half-made dataset.
  • Indexes of several kinds. B-tree, hash and BRIN indexes can be put on any column (see Edit fields), and the chemistry cartridge adds GiST indexes of its own.
  • A query planner and parallel scans. The database decides how to answer a question from the statistics it keeps about the table, and spreads a large scan over several processes. That is why a histogram of ten million rows takes under two seconds on an ordinary machine.
  • Per-project isolation that the database enforces. Each project has its own schema and its own database role, with limits on how long a statement may run, how much temporary space it may use and how many connections it may hold. One project cannot read another’s tables, and one runaway question cannot take the rest down; the database, and not the application’s own checks, is what stops it.
  • Plain, open tables. Nothing about a dataset is in a private format. Anything that speaks SQL can read it, which keeps the data from being locked into one tool.
  • It is what the Data Manager already runs. The chemistry side of the Data Manager already uses PostgreSQL with the RDKit cartridge, so operating one is familiar, and a separate instance is used for the data centre so that its settings can be tuned for this work.

Why the RDKit cartridge

RDKit is the open-source cheminformatics toolkit the rest of the Squonk tooling already relies on. Its PostgreSQL cartridge teaches the database about molecules:

  • a molecule type, so a column can hold structures that the database has actually read, not just text;
  • substructure search (which molecules contain this pattern?), answered with a chemistry index instead of by testing every molecule;
  • fingerprints and similarity search (which molecules look like this one?), again indexed;
  • calculated properties - molecular weight, logP, polar surface area, hydrogen-bond donors and acceptors, ring counts, canonical SMILES, InChI and more - which Calculate molecular properties uses to add columns to a dataset without any row leaving the database.

Without it, every structure question would have to be answered in the browser, one molecule at a time. RDKit does run in the browser too (as WebAssembly), and the Designer uses it to draw structures, but for searching the difference is large: in our early tests a chemistry search in the database was 10 to 65 times faster per molecule than the same search in the browser, before counting the fact that the database does not have to be sent the molecules at all, and the indexed searches on the performance page do much better than that, because an index lets the database skip most of the molecules.

Why the pair

A table of numbers and a table of molecules are asked about together all the time: show me the compounds that contain this ring, are under 400 Da, and have a score better than -30, as a histogram of logP. With one engine, that is one query over one set of tables. With a general-purpose database for the numbers and a separate chemistry service for the structures, the answers have to be matched up between two systems, and the rows crossing between them are exactly the data volume the data centre exists to avoid.

An embedded analytical engine was tried first and is excellent at the columnar half of the job, but it has no chemistry, so every structure question would still have had to leave it and go to the browser. That, and the isolation between projects, are why the data centre is PostgreSQL.

What to expect of it

  • Statements are limited in time, and differently for charts and for jobs. A question a chart asks is cancelled after 60 seconds, which nothing a chart asks should come near. A step inside a job (an import, an index build, an edit, a join) may run for 30 minutes, because those steps are long: the first step of indexing 315,810 molecules takes 94 seconds. An administrator can change either limit. The performance page shows the times.
  • Structure queries are checked first. A malformed pattern is rejected with a message before it reaches the cartridge, so a typo cannot cause trouble in the database.
  • Chemistry is as good as the cartridge. Substructure matching, fingerprints and properties are RDKit’s, so they agree with RDKit anywhere else. One difference worth knowing: the cartridge’s Morgan fingerprint is not bit-for-bit identical to the one RDKit builds in the browser, so a similarity count right at a threshold can differ by a few molecules between the two.