Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-07-15T16:01:28.050Z
Answers.Text: 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?
Answers.UserDisplayName: Jack2501
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-07-15T16:06:21.297Z
Answers.Text: Please note, "accepts" (green tick-button) are much more welcome than thanks. Answered above
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-07-15T15:41:05.110Z
Answers.LastActivityDate: 2023-07-15T16:17:52.060Z
Answers.LastEditDate: 2023-07-15T16:17:52.060Z
Answers.UserDisplayName: M__
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-15T15:25:12.700Z
Comments.Text: 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.
Comments.UserDisplayName: Community
Tags: <python><database><data-retrieval><cheminformatics>
Title: Sql query slow on Chembl postgres database
ThreadText: # 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](https://stackoverflow.com/questions/75825264/parallel-and-distribute-across-npartitions-with-dask-and-postgresql-db).
  
  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`](https://medium.com/nerd-for-tech/postgres-major-version-upgrade-parallel-analyze-with-python-af9931ffd087)
  
  ```
  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  
  
  ---
  
  