Question: Expected size for psql database

[aaron-lohmann]

Assume, I want to dump the current snapshot (~437GB) into a local PostgreSQL database as discussed here: https://docs.openalex.org/download-all-data/upload-to-your-database/load-to-a-relational-database

Can someone provide some guidance on what disk size I should I expect? This is pretty hard to gauge. I am completely fine with things taking long, there is no particular rush to what I intend to do. My ideal is to have an external ssd which contains the most recent snapshot as a database to which I can connect whenever I need it.

[liang5012]

To those who check this issue:
According to the official documentation, after I deployed 413Gb of data to the local database, it took up about 1.38Tb of SSD disk.

[ivarref]

Hi @liang5012

I've seen that you've made speed improvements to the import scripts on your fork https://github.com/liang5012/openalex-faster-flaten Are you planning to contribute this back to ourresearch? Perhaps I could try help out with this effort. Thanks!

[liang5012]

Hi @liang5012

I've seen that you've made speed improvements to the import scripts on your fork https://github.com/liang5012/openalex-faster-flaten Are you planning to contribute this back to ourresearch? Perhaps I could try help out with this effort. Thanks!

Hi [@ivarref ],

Thank you for noticing the improvements on my forked project! I really appreciate your interest and the offer to help. However, I’m currently not planning to contribute these changes back to the main project for a few reasons:

1. Partial Optimization: I’ve only optimized the flattening operation for the authors files and extracted some additional information from the works files. For the most time-consuming part of the original project, the flatten_works() function, I haven’t made any optimizations. This is because this function involves writing too many files at once, and launching all the writing processes simultaneously would exceed the CPU core count. While splitting this function into two passes might help, I don’t currently plan to invest more time in optimizing this issue.

2. Limited Testing Environment: So far, I’ve only tested the modifications on a Windows 11 environment. I’m unsure if they would work seamlessly on macOS or Linux, and I haven’t had the chance to verify compatibility across different operating systems.

3. Hardware Dependency: The producer-consumer model I implemented has a minimum requirement for CPU core count, which might not be suitable for everyone’s hardware setup. This could limit the usability of my changes for users with less powerful machines.

For now, I’d like to keep these changes in my fork as an experimental optimization. Once I’ve addressed these limitations and ensured broader compatibility, I’ll definitely consider contributing back to the main project.

Thank you again for your understanding and support!

[ivarref]

Thanks for a thorough response @liang5012 !

Re CPU core count: you can get that using os.cpu_count(). It's fine to have more processes than the CPU core count, but if you are CPU bound, it will not help you though.

I've did a bunch of parallelization optimizations here: https://gist.github.com/ivarref/0c7375a9fe244f83be3937f3517335d2

The improved script does the following things:

• It executes aws s3 sync ... so that the snapshot dir is always up to date. Then it checks if the database needs updating, and if not, exits. This makes the script suitable to be run as a cron job.

• It produces one set of CSV files for each input file of authors and works. It caches this in the sense that it looks at the sha of the input file to avoid double work on subsequent runs. The production of gzipped CSV files is done in parallel.

• Inserts to the database done by executing the psql command from python. The program does not try to be clever about how it does insertions: if there is a change, the old database will be deleted and then everything will be re-inserted. This is done because there is logic in the original python script to filter out existing already seen items (for topics for example). I did not want to move this logic to the DB itself, so thus I simply went with wiping the database on any change. The inserts to the database is also done in parallel.

For our case here, on our server with 32 cores and fast disks, for a fresh population of the database, we got the following numbers:

...
Creating CSV files done! Spent 01:45:05
...
Main exiting. Spent 02:48:43.

So that is a fully populated database in less than three hours. On subsequent runs with updated files (and cached CSV files), it will take approximately 1 hours to create a fully populated database.

Any interest in having this on the main branch? CC @nolanm1122 @Mearman @caseydm

[liang5012]

Hi @ivarref,

I just went through your improvements to the project, and I’m truly impressed! Great work on the improvements! The changes have significantly boosted the project‘s efficiency, and it’s clear you put a lot of time and thought into it. Your implementation is more rigorous and polished than mine—really well done!

Thanks for the contribution!

[ivarref]

Thanks for your kind words @liang5012 !

I've made some small changes to the script: move all configuration parameters to the top and documenting them. Also the ruff python linter now passes. The functionally it is the same, but hopefully slightly easier to use and understand.

Update: Some more minor improvements are updated in the gist. Regards.

Update2: Even more changes and hopefully better behavior.

All the best.