Doing complex sql query on Chembl database and it takes 40 minutes on the machine from lab. I was thinking using dask, extract each table and export to parquet, when do the data processing . Is this a good approach or there is a better alternative?

  • $\begingroup$ Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. $\endgroup$
    – Community Bot
    Jul 15 at 15:25

1 Answer 1


I suspect the issue is you want to parallelise (multi-threading really) the job to reduce run time.

parquet is recommended. It is simply an archive format that allows compression and is often used with a pandas dataframe. Use parquet to store data by all means, but thats easy via pyarrow and the pandas command

    df.to_parquet('/User/OP/location/file,parquet' ,engine='pyarrow', compression='gzip')

You will need to install pyarrow via pip or conda, there is a faster version (forget its name, something like fastparquet or something used by default). Yeah it's a very good archiving format, in fact its industrial standard and I should use it more.

Python has loads of ways to parallelise of which dask is one way. dask is an entire framework. To leverage dask to parallelise postgres I would refer to this post.

I don't use dask, I am aware it is a solution in high performance Python. Personally I would use this approach directly via multiprocessor

from psycopg2 import connect
import multiprocessor

... etc ...

I would suggest its horses for courses, I simply know how to multiprocess in Python so I don't bother with dask.

To answer the question in summary

  • parquet is the best, if SPEED is not important. If speed is important then feather
  • dask is mainstream within high performance Python and its doable. There's no reason not to
  • There are other multithread Postgress approaches and thats what I would do, but if you just getting into parallelisation you may as well use dask. It will take some learning though.

If you're not into parallelisation and you're not writing a data pipeline for publication I would simply consider getting a machine with more RAM. It shouldn't be taking that length of time, parallelisation will solve the run time - no question - but it's the time it will take to get to grips with a parallel solution. What is likely occurring is the job and database size is exceeding the RAM capacity of the machine. At a wild random guess a 16 or 32 Meg RAM chip would collapse the runtime to a few minutes and thats what you are looking for.

From the comments.

parquet is really for data compression and archiving. The issue is I/o is reasonably fast but not lightening fast. It is probably slower than pickle (it will be slower if you're compressing via gzip as well).

For a 'temporary' save (couple months) and really fast i/o then its feather. There is no dispute about that.

If you are keeping your data a long time and need to save space its parquet. Performance-wise feather is way faster than parquet. I've never used feather BTW I use pickle or parquet

To give some idea a 500Meg flat file compressed an archived via parquet resulting in a 50Meg file, and will take several minutes to write/import. Thats 'cause it's doing loads of stuff. My suspicion is that this too slow for you.

  • 1
    $\begingroup$ Thank you, the idea is using dask distributed with multiple machines, then save parquet. But parquet files for a fastapi application is it good idea? $\endgroup$
    – Jack2501
    Jul 15 at 16:01
  • 1
    $\begingroup$ Please note, "accepts" (green tick-button) are much more welcome than thanks. Answered above $\endgroup$
    – M__
    Jul 15 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.