Best practices for incremental updates to a local OpenAlex PostgreSQL database?

[Arash Hajikhani]

Hi,

I'm running a local copy of OpenAlex in PostgreSQL (~444M works loaded through March 2026). I'm trying to update it with the latest snapshot data (through June 2026) using the new S3 JSONL path structure (s3://openalex/data/jsonl/<entity>/updated_date=YYYY-MM-DD/).

The initial full load took a reasonable amount of time, but my incremental update is taking extremely long — roughly 50+ hours just for the works entity (88 date partitions, tens of millions of records across all sub-tables). The main bottleneck seems to be inserting into already-indexed tables with hundreds of millions of existing rows.

My current approach:

• Download only partitions with updated_date > 2026-03-30
• Flatten JSON to CSV, then INSERT into existing tables (no deletes during load)
• Deduplicate afterward (keep newest version per ID)

My questions:

1. Is there a recommended approach for incremental updates to a local PostgreSQL instance?
2. Would dropping indexes before loading and rebuilding afterward be the standard practice, or is there a better strategy?
3. Does anyone have a working pipeline for this they'd be willing to share?
4. Is there a way to get only truly new/changed records rather than the full updated_date partitions (which seem to include mass re-processing batches with millions of records)?

Any tips from others maintaining a local copy would be greatly appreciated.

Thanks!

[Rainer M Krug]

I do not maintain a PostgreSQL mirror - so take with a grain of salt.

1. Maybe the parquet files help? I can import them directly into e.g. duckdb (no idea about PostgreSQL, though). 

2. I would try disabling the indexing - it might be that the indexing is taming place after each imported batch?

Cheers

Rainer

--
You received this message because you are subscribed to the Google Groups "OpenAlex Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openalex-commun...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/openalex-community/CAPkhOtJUx2RtjvUrH4cRm_V9TU-q3%2BAW4JKRyS3DDgGefTQZWA%40mail.gmail.com.

[Alison Clarke]

We're using the parquet files, and using them to update Iceberg tables, which is faster than your pipeline but still takes a lot of processing. We also decided to just run the pipeline weekly rather than daily, so we're not reprocessing the same data daily due to changes in the number of citations etc.

Suspect this would be a breaking change so not easy to do, but I wonder if OpenAlex could rejig their schemas so that the things that change most often (I'm guessing that's citation/referenced works stats) were part of a separate object (e.g. 'citation_data'). That way the main works object (which is big because of the abstract) would not change so often, and the smaller citation_data object could be updated more frequently with less overhead. But as I say I think that would be a really tricky change for OpenAlex to introduce, even if it would work!