Practical SQL for Data Analysis
This is an excellent example of what I call the Copy-Object-Copy effect.  It&#x27;s particularly apparent in frameworks with ORMs like Django.  In Pandas case, devs will do &#x27;SELECT *&#x27; and use pandas as a sort of pseudo ORM.<p>You run a query to get your data as a bunch of objects, but you&#x27;re copying the data over the db connection from the postgres wire protocol into Python objects in memory, which are typically then garbage collected at the end of the transaction, then copy the result to a JSON buffer that is also garbage collected, and then send the final result to send to the browser which has been waiting this whole time.<p>I regularly see Django apps require several gigs of RAM per worker and when you look at the queries, it&#x27;s just a bunch of poorly rendered SELECTs.  It&#x27;s grotesque.<p>Contrast PostgREST:  100 megabytes of RAM per worker.  I&#x27;ve seen Django DRF workers require 50 to 1 memory vs PostgREST for the same REST interface and result with PostgREST being much faster. Since the database is generating the json, it can do so <i>immediately</i> upon generating the first result row which is then <i>streamed</i> to the browser.  No double buffering.<p>Unlike Django, it&#x27;s not that I don&#x27;t like Pandas, it&#x27;s that people way overuse it for SQL tasks as this article points out.  I&#x27;ve seen pandas scripts that could be a simpler psql script.  If psql can&#x27;t do what you want, resist the temptation to &#x27;SELECT *&#x27; into a data frame and break the problem up into stages where you get the database to do the maximum work before it gets to the data frame.