<p>I suspect the issue is you want to parallelise (multi-threading really) the job to reduce run time.</p>
<p><code>parquet</code> is recommended. It is simply an archive format that allows compression and is often used with a <code>pandas</code> dataframe. Use <code>parquet</code> to store data by all means, but thats easy via <code>pyarrow</code> and the <code>pandas</code> command</p>
<pre><code>    df.to_parquet('/User/OP/location/file,parquet' ,engine='pyarrow', compression='gzip')
</code></pre>
<p>You will need to install <code>pyarrow</code> via pip or conda, there is a faster version (forget its name, something like <code>fastparquet</code> or something used by default). Yeah it's a very good archiving format, in fact its industrial standard and I should use it more.</p>
<p>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 <a href="https://stackoverflow.com/questions/75825264/parallel-and-distribute-across-npartitions-with-dask-and-postgresql-db">post</a>.</p>
<p>I don't use dask, I am aware it is a solution in high performance Python. Personally I would use this approach directly via <a href="https://medium.com/nerd-for-tech/postgres-major-version-upgrade-parallel-analyze-with-python-af9931ffd087" rel="nofollow noreferrer"><code>multiprocessor</code></a></p>
<pre><code>from psycopg2 import connect
import multiprocessor
</code></pre>
<p>... etc ...</p>
<p>I would suggest its horses for courses, I simply know how to multiprocess in Python so I don't bother with dask.</p>
<p>To answer the question in summary</p>
<ul>
<li>parquet is the best, if SPEED is not important. If speed is important then <code>feather</code></li>
<li>dask is mainstream within high performance Python and its doable. There's no reason not to</li>
<li>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.</li>
</ul>
<hr />
<p>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.</p>
<hr />
<p>From the comments.</p>
<p><code>parquet</code> is really for data compression and archiving. The issue is I/o is reasonably fast but not lightening fast. It is probably slower than <code>pickle</code> (it will be slower if you're compressing via gzip as well).</p>
<p>For a 'temporary' save (couple months) and really fast i/o then its <code>feather</code>. There is no dispute about that.</p>
<p>If you are keeping your data a long time and need to save space its <code>parquet</code>. Performance-wise <code>feather</code> is way faster than <code>parquet</code>. I've never used <code>feather</code> BTW I use <code>pickle</code> or <code>parquet</code></p>
<p>To give some idea a 500Meg flat file compressed an archived via <code>parquet</code> resulting in a 50Meg file, and <em>will take several minutes to write/import</em>. Thats 'cause it's doing loads of stuff. My suspicion is that this too slow for you.</p>

CC BY-SA 4.0
2023-07-15T16:01:28.050Z
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?
Jack2501
CC BY-SA 4.0
2023-07-15T16:06:21.297Z
Please note, "accepts" (green tick-button) are much more welcome than thanks. Answered above
M__
CC BY-SA 4.0
2023-07-15T15:41:05.110Z
2023-07-15T16:17:52.060Z
2023-07-15T16:17:52.060Z
M__
<p>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?</p>

CC BY-SA 4.0
2023-07-15T15:25:12.700Z
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.
Community
<python><database><data-retrieval><cheminformatics>
Sql query slow on Chembl postgres database
# Sql query slow on Chembl postgres database

(Asked by: username_0 on 2023-07-15)

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?

**Tags:** python, database, data-retrieval, cheminformatics
> **Comments:**
> **username_1**: 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.  

---

## 1 answers

### Answer by username_2 on 2023-07-15. Score: 0 | Accepted answer

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.

> **Comments:**
> **username_0**: 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?  
> **username_2**: Please note, "accepts" (green tick-button) are much more welcome than thanks. Answered above  

---

