Orm Database Load

Loading and backfilling the ORM database

The ORM database is populated in two stages. ingest writes the ord.* search index and the public.* payload; derived writes derived.* SMILES, the rdkit.* structures and their links. Every pass is guarded by NOT EXISTS, so all of it is idempotent: safe to re-run, safe to kill and resume.

Where it runs

Not on a laptop. Loads run on a dev VM inside the database's VPC, because the Aurora cluster has no public endpoint and the job is chatty. The VM is normally stopped — start it, use it, stop it.

aws ec2 describe-instances --filters "Name=tag:Name,Values=dev-vm" \
  --query 'Reservations[].Instances[].{Id:InstanceId,State:State.Name}'

Reach it with SSM (aws ssm send-command, document AWS-RunShellScript); there is no SSH. Two traps: SSM runs commands as root under dash, so wrap real work in sudo -u ubuntu -H bash -l; and fs.protected_regular stops even root from reopening a ubuntu-owned file in /tmp, so give each uploaded script a unique name.

The RDKit version must match the database

derived.*.smiles and rdkit.mols.smiles are written by Python RDKit, not by the Postgres cartridge, and the two do not agree on every molecule. The database stores [Br][Ag]; the cartridge canonicalizes that same structure to Br[Ag]. Load from a machine with a different RDKit and a few thousand organometallics will canonicalize differently, inserting duplicate structures under different strings.

Any SMILES already in rdkit.mols must be a fixed point of the writer you are about to use. Check before writing anything:

from rdkit import Chem
for smiles in ("[Br][Ag]", "[B]#[Ni]", "[Cl][Ti]([Cl])([Cl])[Cl]"):
    assert Chem.MolToSmiles(Chem.MolFromSmiles(smiles)) == smiles, smiles

Full load

Put PGHOST / PGPORT / PGUSER / PGPASSWORD / PGSSLMODE / PGDATABASE in the environment (the master password lives in Secrets Manager) and let the DSN inherit them:

python -u -m ord_schema.orm.scripts.add_datasets \
  --pattern "$ORD_DATA/data/*/*.parquet" \
  --dsn "postgresql+psycopg://" \
  --n_jobs 16

Backfill

--stages derived fills in derived rows over already-ingested datasets. It runs the same per-dataset, per-shard RDKit passes as an end-to-end load, so nothing special is required:

python -u -m ord_schema.orm.scripts.add_datasets \
  --pattern "$ORD_DATA/data/*/*.parquet" --stages derived \
  --dsn "postgresql+psycopg://" --n_jobs 16

Backfilling is not rebuilding

Every derived pass is guarded by NOT EXISTS. That is what makes the load idempotent, and it also means an existing row is never revisited: after a change to how SMILES are derived, the command above writes nothing and exits clean, leaving every stale row in place. Silence here is not success.

--rederive recomputes every row and updates the ones whose value changed:

python -u -m ord_schema.orm.scripts.add_datasets \
  --pattern "$ORD_DATA/data/*/*.parquet" --stages derived --rederive \
  --dsn "postgresql+psycopg://" --n_jobs 4

Rows are updated in place, never deleted and reinserted. That matters because search inner-joins through derived.compound_smiles / derived.product_compound_smiles / derived.reaction_smiles into rdkit.* — a row that is absent even briefly is a reaction that briefly cannot be found, and emptying those tables would return zero results for every structure query until the rebuild finished.

The DO UPDATE is conditional on the value actually differing, so an unchanged row is not rewritten. That is not the same as writing nothing: the upsert still does per-row work, and a rederive whose values have changed is a full rewrite of the affected tables. Measured over the published corpus in August 2026, a rederive after #935 rewrote essentially every reaction SMILES — 10 compound updates against 2.4M reaction updates — because the database predated that change.

A rederive removes reactions from structure search for the length of the run. Every row whose SMILES changed has its rdkit_mol_id / rdkit_reaction_id cleared, and the linking pass that refills them runs serially after every dataset's SMILES stage — not per dataset. On the August 2026 run that put 1.98M of 2.43M reactions (81%) out of reaction-SMARTS search for hours. Component search is unaffected, since compound_smiles and product_compound_smiles relink almost immediately; only ReactionSmartsQuery joins rdkit.reactions. Plan the window accordingly, and do not start one expecting the "only changed rows are affected" reading — on a corpus that has drifted, changed rows are most of them.

Do not confuse it with --overwrite, which is an ingest flag meaning "re-ingest a dataset whose MD5 changed."

A row whose source stops yielding a SMILES is dropped, not left and not set to NULL. A row present always means a SMILES was derived, so a stale value can never stay joinable, and an entity that derives nothing is retried by any later pass rather than sitting behind the NOT EXISTS guard.

Size the parallelism for the largest dataset, not the corpus

--n_jobs 16 exhausted Aurora's local temp space on the RDKit stage of ord_dataset-1158e351… (the 2M-reaction USPTO set), failing with psycopg.errors.DiskFull: could not write to file "base/pgsql_tmp/…". FreeLocalStorage bottomed at 1.27 GB on a db.t4g.large. The other 52 datasets completed; only the largest one has shards big enough to spill concurrently.

Watch FreeLocalStorage alongside CPU, and prefer 4 workers for a corpus-wide rederive. The run is not much slower — the RDKit stage is serial anyway — and it is the stage that fails.

Resuming after a failed rederive

A failed dataset rolls back its shard, so the database stays consistent and the SMILES that did land are correct. What remains is the linking, which the ordinary derived pass already does: it fills rdkit_*_id wherever it is NULL, which is exactly the set a failure left behind, and the partial indexes on those tables exist to make that query cheap.

python -u -m ord_schema.orm.scripts.add_datasets \
  --pattern "$ORD_DATA/data/*/*.parquet" --stages derived --prune_rdkit \
  --dsn "postgresql+psycopg://" --n_jobs 4

Without --rederive — the SMILES are already recomputed, so re-running the full rederive would redo 2.4M rows to fix a few hundred thousand links, and hit the same temp-space ceiling. Confirm first that the SMILES stage really finished, by checking that whatever the rebuild was meant to change has changed corpus-wide.

Collecting the structures a rebuild strands

rdkit.mols and rdkit.reactions are shared and deduplicated by structure, so a rederive rewrites the rows that point at them rather than touching them. A rebuild that changes a SMILES therefore links to a new structure and leaves the old one referenced by nothing. --prune_rdkit deletes those, once the RDKit pass has finished:

python -u -m ord_schema.orm.scripts.add_datasets \
  --pattern "$ORD_DATA/data/*/*.parquet" --stages derived --rederive --prune_rdkit \
  --dsn "postgresql+psycopg://" --n_jobs 4

Whole-database by necessity: a structure is orphaned only if no dataset references it, so it cannot be scoped to one dataset or shard. Two consequences. Do not run it beside another load — _update_rdkit_mols inserts structures that _link_mol_ids links in a later statement, so a concurrent derive has a window where live rows look orphaned. And it is skipped automatically when any dataset failed, since an unfinished pass leaves exactly that window open; the log says so rather than staying quiet.

Should you scale up the writer?

Decide from metrics, not instinct.

• The writer is a single instance with no reader. Changing its class restarts it: measured ~3.5 minutes of downtime, and you pay that twice (up, then back down). The outage hits the live search database, which shares the instance.
• Check CPUCreditBalance first on burstable classes. Observed 2026-07-09: a 4.7M-row backfill ran at ~93% CPU and burned ~65–80 credits/hour against a full balance of 864 — hours of runway. The instance class was never the constraint.
• If a job is projecting hours, suspect the query plan before the instance size. Doubling the CPU does not fix a quadratic scan. EXPLAIN the statement that pg_stat_activity shows.
• Expect buffer-cache pollution regardless: the live database's index working set gets evicted, so search runs cold for a while afterwards. Prefer a low-traffic window.