pubmed2db

Download PubMed abstracts, load them into a DuckDB database (keeping full version history), and export the latest version of every abstract to JSON (for ingest into Node Annotator / ElasticSearch) and to Parquet (for downloadable queries).

The JSON export follows the field names of the NCATS Translator DocumentMetadataAPI, plus four fields of our own: id, identifiers, pub_date and publication_types. The database itself uses PubMed's own field names.

All three outputs — the database, the Parquet export and the NDJSON export — follow one versioned data model, currently 1.0.0, documented for consumers in docs/data-model/: what every table and field means, what PubMed data is left out, and how the design was decided. Each output carries its version, and the database documents its own columns (SELECT table_name, column_name, comment FROM duckdb_columns()).

How it works

File listing and XML parsing reuse pubmed-downloader (the PubMed baseline + daily update file listings and cache layout, plus the rich Article data model); the transfers themselves use pubmed2db's own download, which times out a stalled connection. On top of it, pubmed2db adds:

• MD5 tracking — each <file>.xml.gz.md5 sidecar is fetched and the checksum stored; a new or changed checksum triggers a (re)load. PubMed files are normally immutable, so this mostly just lets new files be discovered incrementally.
• Full version history — every loaded file's rows are tagged with their source_file provenance, so an abstract revised across several update files is kept as multiple rows. The latest_article view selects the newest non-deleted version of each PMID (honoring <DeleteCitation> removals).
• Faithful publication dates — raw PubDate components (Year/Month/Season/ Day/MedlineDate) are preserved rather than collapsed to a single date. <Season> shares the pub_month column with <Month> — the DTD makes the two mutually exclusive — so a Parquet consumer reading article.pub_month sees Winter or Sep-Dec as readily as Mar.
• Journal names — the journal title and abbreviations come from NLM's J_Entrez.txt (uv run pubmed2db journals) and are joined on nlm_catalog_id; publication years come from NLM's serial catalog. Two sources, deliberately: see docs/JournalCatalog.md.

Setup

Clone the repository and let uv build the environment; there is no need to install pubmed2db itself — every command below is run from the repo root with uv run, which syncs dependencies on demand.

git clone https://github.com/TranslatorSRI/pubmed2db.git
cd pubmed2db
uv sync

If uv's default cache directory (~/.cache/uv) is not writable or is on a small quota — common on HPC login nodes — point it somewhere else once per shell (or in your ~/.bashrc / job script):

export UV_CACHE_DIR=/path/to/writable/uv-cache

Usage

All commands share --data-dir (default data/), which sets the root for downloaded PubMed files and the database. pubmed-downloader keeps its cache in a pubmed/ subdirectory inside it (data/pubmed/baseline/, data/pubmed/updates/), so the layout under data/ is managed automatically.

Budget disk accordingly: the 2026 files alone are 51 GB of baseline and 13 GB of updates, before the DuckDB database and any export. A new baseline year adds that much again — see Re-running after a gap.

# Download the baseline + update files to data/ (MD5-checked, incremental).
uv run pubmed2db --data-dir data download

# Refresh the journal dimension from J_Entrez.txt and NLM's serial catalog
# (~890 MB on a first run).
uv run pubmed2db --data-dir data journals

# Parse downloaded files into the database (full history).
uv run pubmed2db --data-dir data load

# Export the latest abstract of every PMID as sharded NDJSON (DocumentMetadataAPI fields).
uv run pubmed2db --data-dir data export --format json --out data/json --shards 16

# Export the database to Parquet (latest version per table, or --all for full history).
# Note: unlike the JSON export, this has not yet been run against the full corpus.
uv run pubmed2db --data-dir data export --format parquet --out data/parquet

# Download + journals + load in one step (for scheduled runs).
uv run pubmed2db --data-dir data update

# Report what's been downloaded, loaded, and is ready to export (read-only).
uv run pubmed2db --data-dir data status

# Sanity-check a finished export and write validation_report.json alongside it.
# (Uses the DB when present; --offline skips the Entrez API cross-checks.)
uv run pubmed2db --data-dir data validate data/json --email you@example.com

--data-dir data is the default, so omitting it gives the same result. The DuckDB database is stored as <data-dir>/pubmed.duckdb; override with --db.

Both exports finish by writing metadata.json into the output directory: the data model version, the record count, the corpus cutoff (the last file loaded, and when NCBI posted it), and a SHA-256 per file. The same hashes go into SHA256SUMS, so sha256sum -c SHA256SUMS checks a download with no JSON parser. Both files are deleted when an export starts, so a directory holding a metadata.json is one whose export finished. validate checks the shards against metadata.json, and status prints the same cutoff.

How the JSON export is written

DuckDB writes the NDJSON itself — a single COPY ... (FORMAT JSON) whose projection is built from export._JSON_FIELDS, rather than Python serializing row by row. The serialization therefore runs in C++ across every thread (3x faster on a 2M-document benchmark), and two things follow:

• --shards N is a maximum, not a count. Output is one file per writer thread, so --shards caps that statement's threads; a small dataset can be written by fewer. Omit it to use DuckDB's own thread count.
• Records are not in PMID order. The export no longer sorts — that sort cost ~3 minutes and most of the peak memory of a full run. Nothing downstream needs the order; validate builds its own sorted PMID manifest.
• Shards are gzipped (pubmed_metadata_0.ndjson.gz) unless you pass --no-gzip. NDJSON compresses ~4-5x — a full corpus goes from ~52 GiB to ~12 — and each shard is compressed as it is written, so there is no second pass. validate <dir> reads either form with no flag, and zcat still works line by line.

Re-running an export first deletes the pubmed_metadata_* files already in the output directory, so a shorter run cannot leave a previous run's shards behind.

pub_date in the JSON export

The three parsed date fields are conveniences, and no arrangement of them represents every PubDate PubMed publishes: a cross-year range like <MedlineDate>1998 Dec-1999 Jan</MedlineDate> (PMID:10188493) has no single month, so splitting it leaves a year inside pub_month. So every record also carries pub_date, PubMed's own date string verbatim — the same thing NCBI esummary ships as pubdate:

{
  "id": "PMID:10188493",
  "pub_date": "1998 Dec-1999 Jan",
  "pub_year": "1998",
  "pub_month": "Dec-1999 Jan",
  "pub_day": "",
  "...": "..."
}

Render citations from pub_date; sort and filter on pub_year. Neither has to parse the other. Records with an ordinary date get the same treatment — "2019 Mar 15" — so a consumer never needs to know which shape it is looking at.

pub_year takes the leading year of a cross-year range, matching what NCBI's own sortpubdate does. A consumer that needs the trailing year reads it out of pub_date.

pub_date is new, and a re-export is required to ship it. An export made before this field existed has no pub_date field.

Publication types in the JSON export

Each record carries publication_types, PubMed's publication types. Each entry has the type's MeSH descriptor as a CURIE and PubMed's name for it. They are in PubMed's own order, the order NCBI's esummary serves as pubtype:

{
  "id": "PMID:22456213",
  "publication_types": [
    {"id": "MESH:D002363", "name": "Case Reports"},
    {"id": "MESH:D016428", "name": "Journal Article"},
    {"id": "MESH:D016454", "name": "Review"}
  ],
  "...": "..."
}

• Verbatim. Every type PubMed lists is included, funding labels such as Research Support, Non-U.S. Gov't too (about a fifth of records carry one). Filter on name or id to pick out reviews, trials, errata and so on.
• Usually more than one. In a sample of 3,600 random PMIDs, about half carried two or more types and 89% carried Journal Article. A record with no types gets [], never null.
• id uses the MESH: prefix, Biolink's casing.

The same data is in the database's publication_type table (type_ui, type_name, and position for PubMed's order), so a direct query needs no MeSH lookup:

SELECT count(*) FROM latest_article la
JOIN publication_type pt USING (pmid, source_file)
WHERE pt.type_name = 'Review';

Identifiers in the JSON export

Alongside the DocumentMetadataAPI fields, each JSON record carries an identifiers array of CURIEs — the PMID, plus the DOI and PMCID when PubMed published them:

{
  "id": "PMID:30690000",
  "identifiers": ["PMID:30690000", "PMC:PMC6423490", "doi:10.1016/j.ejphar.2019.01.030"],
  "journal_name": "European journal of pharmacology",
  "...": "..."
}

All three prefixes (PMID, PMC, and the lowercase doi) deliberately match Babel's src/prefixes.py, so these CURIEs join directly against the Babel publication compendium. PubMed's PMCID values already begin with PMC, hence the doubled PMC:PMC6423490. A record with neither a DOI nor a PMCID still gets its own ["PMID:<id>"]; the array is never empty and never null.

The PMCID prefix may still change. PMC follows Babel for now. Biolink registers PMC on biolink:Publication and PMCID on biolink:JournalArticle, and Translator has not chosen between them: see biolink-model#1366 and Babel#1044. This export follows whatever they decide (#33). The production DocumentMetadataAPI endpoint does not resolve PMCIDs under either prefix.

Consumers must match identifiers case-insensitively. Values are stored and exported exactly as PubMed published them, with no case normalization (Babel does the same). DOIs are case-insensitive by specification and PubMed is not internally consistent about them, so doi:10.1234/ABC and doi:10.1234/abc can both occur and refer to the same article.

Only DOIs and PMCIDs are promoted into this field. Any other ArticleId type PubMed supplies (pii, mid, …) is still loaded and is available in the article_id table and the Parquet export.

Three more group-level options tune DuckDB itself, which matters on a cluster:

• --threads N (PUBMED2DB_THREADS) caps the thread pool. Rarely needed on Slurm: DuckDB reads the cgroup's CPU quota and already sizes the pool from --cpus-per-task (it uses the machine's core count only off a cluster).
• --memory-limit SIZE (PUBMED2DB_DUCKDB_MEMORY_LIMIT, e.g. 48GB) caps the buffer pool. DuckDB does read the Slurm cgroup, defaulting to ~76% of --mem (measured on duckdb 1.5.4), so this is not about rescuing it from a node-sized default — it is about leaving room for the memory DuckDB's limit does not cover: the lxml tree, the parsed records and the Arrow batch all live in the same cgroup.
• --temp-dir PATH (PUBMED2DB_DUCKDB_TEMP_DIR) sets where DuckDB spills when a query exceeds its memory budget.

All three go before the subcommand. See slurm/README.md.

The steps are independent and incremental (re-running download revalidates existing files rather than refetching them), and each checks its prerequisites against the database's own state: load errors if nothing has been downloaded, and export errors if nothing has been loaded, warns if some downloaded files have not been loaded yet, and warns if the journal dimension is empty (journal names would be blank). When in doubt, uv run pubmed2db update runs the whole pipeline (download → journals → load) in order.

A PubMed file that fails to download (an HTTP error, or no data for 120 seconds) is skipped while the rest are still fetched; download then exits non-zero naming the failures, and a re-run fetches only those.

journals reads two NLM sources: J_Entrez.txt (10 MB, refetched every run) for the titles and abbreviations the export ships, and the serial catalog (serfilebase.YYYY.xml, ~449 MB, plus that year's monthly updates — usually ~1 MB but occasionally a bulk re-release of 150-250 MB, so budget ~890 MB of cache per year) for start_year / end_year / active. NLM publishes no .md5 sidecars for the catalog, so each cached file gets an .etag sidecar holding the server's ETag instead, and a HEAD per file per run decides whether to re-fetch — a warm run downloads nothing. The catalog is an enrichment: if it is unreachable, or its baseline will not parse, the step logs a warning and refreshes the titles while keeping the years a previous run loaded (NULL on a first run) rather than failing. A monthly update that fails to download or parse is skipped with a warning and fetched again next run, and a republished file whose re-fetch fails keeps its cached copy. Unlike the PubMed files, the catalog cache cleans up after itself: once a new baseline year has downloaded, the previous year's files are deleted. About 1% of journals (proceedings volumes, mostly) have no catalog record and keep NULL years permanently.

uv run pubmed2db validate <dir> inspects a finished JSON export and writes a validation_report.json (pretty-printed, archivable) whose leading errors/warnings arrays are empty when the export looks good. It confirms every record parses and has the expected fields, compares the exported count to both the live PubMed total and the local database, re-fetches a random sample from NCBI Entrez to confirm field values match, and confirms a sample of dropped PMIDs are genuinely gone. It exits non-zero on errors (--fail-on-warn also fails on warnings) so it can gate an HPC run; pass --offline to skip the network checks. Set --email (or NCBI_EMAIL) and optionally --api-key (or NCBI_API_KEY, which raises the rate limit) for the API checks.

The STRUCTURE heading also carries the share of records that came out with each identifier type (identifier_coverage in the report). Nothing gates on it — one export in isolation cannot say whether 96% is right and 6% is not — but a rate that collapsed between two runs is invisible without the number.

A long run narrates itself: one start line naming the shards and confirming whether the database and an API key were picked up (never the key itself), a progress line with an ETA once a minute while the shards are read, and a line per phase after that — so a run waiting on NCBI is distinguishable from one still reading. Peak RSS is logged at the end and stored in the report.

Stdout is a test report: every check is listed with what it expected and what it saw, so a reviewer can tell what was verified, what was skipped, and what is not covered at all. Abridged, from a full-corpus run:

Validation WARN: 40,901,984 record(s) in 16 shard(s) of data/json
  database available · Entrez online, no API key (3 req/s; set NCBI_API_KEY for 10/s)
  ran in 10m 51s, peak RSS 5.177 GiB

COVERAGE
  [pass] vs-entrez       within [95%, 105%] of the live PubMed total  40,901,984 of 40,944,369 (99.896%)
  [pass] vs-database     matches the database's latest_article count  exact match (40,901,984)
  [skip] vs-previous     coverage within 10% of the previous report's no --previous-report

FIELD ACCURACY  (240 records sampled: 15/shard x 16 shards, seed 0)
  [WARN] core-fields     <20% of compared fields differ from Entrez   20 of 1,600 differ (1.25%)
         by field: 18x pub_year, 1x article_title, 1x issue
            0 exported a different value (incorrect data)
           20 exported blank where Entrez has a value (missing data)
         e.g. PMID:152567 pub_year exported "" vs. Entrez "1978"

NOT CHECKED
  - compared strictly against Entrez: article_title, volume, issue, pub_year, ...
  - article_title compared after folding efetch's rendering (terminal period, [ ] around a translated title, [Not Available] for an empty title)
  - compared but never fails the run (assigned upstream after our last update file): identifiers
  - MeSH terms, authors, affiliations and grants are stored in the DB, never exported

The values_differ count is printed even when it is zero, because that zero is the decision a reviewer has to make: fields left blank are a completeness gap that is safe to pass downstream, whereas a field exported with the wrong value is a correctness bug. [skip] means a check could have run with another flag or a network; [n/a ] means there was nothing to verify (no deletions to sample).

Every line of that output is rendered from validation_report.json, which also gains a checks_run array — the same list, machine-readable, with structured mismatch tallies.

Coverage counts alone cannot tell you that two exports of the same size hold the same records, so validate can write a sorted PMID manifest and diff against an earlier one:

# Archive this export's PMID set (gzipped, one PMID per line). Date-stamp it,
# and keep it outside the export directory: one shared name means the next run
# overwrites the manifest it is diffing against.
uv run pubmed2db validate data/json --manifest data/manifests/pmids-20260820.txt.gz

# Next month: report which PMIDs disappeared since that export.
uv run pubmed2db validate data/json-new \
    --previous-manifest data/manifests/pmids-20260820.txt.gz \
    --manifest data/manifests/pmids-20260921.txt.gz

A dropped PMID is fine if the database recorded a DeleteCitation for it; one that vanished without a recorded deletion is an error, since it means records were lost rather than retired. With no database available the drops can't be attributed, so they downgrade to a warning to review.

The coverage check expects the export to be within ±5% of the live PubMed total (the 2026-07-30 full run came in at 99.90% of it). A partial export — from a --limit test download — is legitimately far below that, so pass something like --entrez-low 0.001 when validating one, or --offline to skip the comparison.

Use --limit N on download/update to fetch only the newest N files when testing.

Note: The file layout under data/ differs from Babel's PubMed download, so the two cannot share a download cache at this time.

Re-running after a gap

Running download and then load again is safe: load picks up only the files that are new or changed, and it cannot introduce duplicate data.

• Only new/changed files are loaded. download bumps a file's downloaded_at only when its published MD5 is new or differs from the stored one, and load skips every file whose processed_at is at or after its downloaded_at. Unchanged files are no-ops.
• Reloading a file replaces it. Before inserting, load deletes all rows previously loaded from that source_file, in the same transaction, so even a forced reload (load --force) is idempotent.
• Several versions of a PMID are the design, not duplication. A row's identity is (pmid, source_file); the latest_article view returns the version with the highest file_order_key per PMID, and drops the PMID if a later file recorded a <DeleteCitation> for it. Exports therefore see one row per PMID no matter how many versions are stored.

load --force or a fresh database?

A normal incremental run only re-parses files whose published checksum moved, so neither a parser fix nor a schema change reaches data that is already loaded. Two ways to apply one, and they are not interchangeable:

A database from before data model 1.0 must be rebuilt. Every command refuses one, naming the problem and leaving the file untouched: 1.0 renamed and dropped columns (see the data model changelog), which no migration can do in place. The same holds for a database from another major version. An older minor version is upgraded on open, since it only lacks columns.

• load --force re-parses every local file and replaces its rows, so it applies a parsing change to the whole corpus: roughly a full load's worth of time (about a day at corpus scale — see slurm/README.md), no re-download, and the database keeps its history.
• A fresh database (delete <data-dir>/pubmed.duckdb, then journals and load) is the answer whenever the schema removed or redefined something, and the safer default if you are unsure.

The asymmetry is that within a major version schema.sql only ever adds: CREATE TABLE IF NOT EXISTS plus explicit ALTER TABLE ... ADD COLUMN IF NOT EXISTS migrations. So a column added in a minor version is applied on the next connect and filled by load --force. A forced reload refreshes the rows a parser change affects, but it leaves any pre-existing wrong rows in place for files it re-parses identically, and only a rebuild is guaranteed to leave exactly the shape and rows the current code would write.

Rebuilding costs a full load and a journals refresh, and nothing else — the downloaded files are not touched — so unless the corpus is already loaded on a machine where a day of load is cheaper than the disk churn, prefer the rebuild. At a new baseline year the question is moot: a fresh database is the recommended path anyway, since loading the new year into the old database stores a second version of every PMID (see above).

A fresh database needs journals run again. The journal dimension lives in the database, so deleting it deletes the journals too, and load does not rebuild them: skip journals and export warns, then ships blank journal names. update and ./slurm/submit.sh all both include it. Nothing is assumed current from an earlier run: J_Entrez.txt is re-fetched every time, and the serial catalog cache (<data-dir>/pubmed2db/serfile/, which survives deleting the database) is re-checked against NLM's listing and ETags, so a warm cache downloads only what NLM has added or republished. What a fresh database cannot do is fall back on earlier years. If the catalog is unreachable or its baseline will not parse, journals still succeeds, but every journal's start_year, end_year and active is NULL rather than last run's values — so check the journals output for serial catalog unavailable before exporting, and re-run journals if it is there.

Watch for a new baseline year. Around November–December PubMed publishes a fresh baseline for the coming year (pubmed26n*.xml.gz lands in late 2025, after pubmed25n*.xml.gz), which is a complete re-issue of the corpus, not an increment. Downloading it makes ~1,300 files new at once, so the following load re-parses everything and the article table ends up holding a second full copy of every PMID. The result is still correct — file_order_key puts the newer year first, so latest_article resolves to it — but the database roughly doubles in size and the load takes as long as the original one. status says so directly once a second baseline year is on disk, and pending_files in the thousands rather than the dozens is the same signal.

Prefer a fresh database at the year boundary, for a reason beyond size: a grown database is additive. It can gain PMIDs but never lose them. A PMID that is in last year's baseline and absent from this year's, without a <DeleteCitation> ever reaching us — a missed updatefile, or a record PubMed drops quietly — keeps last year's row as its newest version and stays in every export from then on. A database built from the new baseline simply doesn't contain it.

Either way, delete the previous year's files yourself:

rm data/pubmed/baseline/pubmed25n*.xml.gz data/pubmed/updates/pubmed25n*.xml.gz

download never removes anything. Once PubMed drops a file from its listing we stop hearing about it, so a full extra copy of the corpus sits in data/ until you clear it — for 2026 that is 51 GB of baseline plus 13 GB of updates.

Verification is on by default, but only hashes files that are new or whose published checksum changed — re-running download over an unchanged baseline costs no local I/O. Corruption happens at download time, which stays covered. --no-verify skips the hashing entirely; either way the .md5 sidecars are still fetched, so a changed published checksum is always detected.

Notes

• We reuse pubmed-downloader as-is for file listings and XML parsing, but work around three bugs in it (≤ 0.0.14), all tracked in FUTURE.md: catalog.process_journal_overview() requires start_year/end_year fields the real J_Entrez.txt does not contain, so it raises on live data (fixed by pubmed-downloader#16 in 0.1.0, which our <0.1 pin still excludes); its reference extraction looks under MedlineCitation for a <ReferenceList> that PubMed puts under <PubmedData>, so it never finds one; and its article-ID extraction descends into that <ReferenceList>, attributing every cited reference's DOI to the citing article. We parse the journal file and the article IDs ourselves; the reference bug is moot here because we deliberately do not store the citation graph (one article carries ~444 references, and nothing consumes them). Separately, catalog.ensure_serfile_catalog() skips the serfilebase* baseline and takes all 83 monthly deltas instead; that costs 2.52 GiB rather than 872 MiB and returns 80% duplicate records for the same coverage, which is why journals enumerates NLM's listing itself.
• MeSH names are PubMed's, not MeSH's. The descriptor, qualifier and publication-type names come from each record's own XML. They are never looked up in the MeSH vocabulary or reconciled across records, so if PubMed names a descriptor differently in two records, both names are kept. Each version of a PMID keeps the names its file gave it, and exports read the latest version. This keeps the data identical to PubMed's files. Any drift between older and newer records is cleared by the year-boundary rebuild, which reloads every record from the new baseline.
• This tool is intended to eventually replace the PubMed download in Babel (createcompendia/publications.py).

See AGENTS.md for a map of the source and the design decisions behind it, FUTURE.md for known limitations and planned work, and docs/JournalCatalog.md for why the journal dimension reads from two NLM sources rather than one.

Information on running this pipeline

• uv run pubmed2db load takes a long time to run, and it might be beneficial to parallelize this: transform each PubMed file into a separate DuckDB file, then use a query that spans multiple files to either load everything into one file or to simply export it from the multiple files (using PMIDs to group related queries might not take very long?). Running it with 64G of memory seems sufficient.
• uv run pubmed2db export --format json is fast but memory-hungry: 41,153,516 documents in 13m 14s (≈52k documents/s) at a peak RSS of 191.9 GiB, run with --mem 256G and a 200GB DuckDB memory limit (153.3 GiB and 12m 03s under a 160GB limit — the peak follows the limit, not the query). Unlike load, its memory scales with the whole database rather than the largest input file — see slurm/README.md for why, and for what to request on a cluster.
• export publishes in place, not atomically. Both formats write straight into --out: the JSON export deletes the shards of the previous run before DuckDB writes the new ones, and the Parquet export replaces one table file at a time (sweeping any left by a table that has since left the schema). A run that dies partway — OOM, full disk — therefore leaves a half-written dataset that looks complete to anything globbing the directory. Export into a fresh directory and swap it into place yourself if consumers read the output while exports run.
• export --format parquet is untested at full scale. Only the JSON export has been run against the whole corpus. Parquet should be the lighter of the two (each table is written by a DuckDB COPY ... TO rather than pulled through Python), but that is reasoning, not a measurement: request the same 256 GB the first time and check the logged peak RSS.

Development

uv sync --extra dev
uv run pytest

Tests gzip the readable XML fixtures under tests/fixtures/ into temporary pubmedNNnNNNN.xml.gz files; scratch downloads and databases go under ./data (gitignored).

Releases, and the dated data builds they produce, follow docs/Releases.md.

Helper scripts live in scripts/ and document themselves — run one with --help for its usage and options. benchmark_load.py times parsing against insertion for a given file, which is where slurm/README.md's memory and wall-time figures come from.