11 ms·

This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs

This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs will do 'SELECT *' and use pandas as a sort of pseudo ORM. You run a query to get your data as a bunch of objects, but you'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. I regularly see Django apps require several gigs of RAM per worker and when you look at the queries, it's just a bunch of poorly rendered SELECTs. It's grotesque. Contrast PostgREST: 100 megabytes of RAM per worker. I'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 immediately upon generating the first result row which is then streamed to the browser. No double buffering. Unlike Django, it's not that I don't like Pandas, it's that people way overuse it for SQL tasks as this article points out. I've seen pandas scripts that could be a simpler psql script. If psql can't do what you want, resist the temptation to 'SELECT *' 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.

password4321 5y ago
I would appreciate pointers to any other low/no-copy db -> browser technologies. I was thinking it would be cool to use parts of JavaScript db libraries to parse query results passed straight through in (compressed?) db wire protocol format via WebRTC.
tomnipotent 5y ago
> it's that people way overuse it for SQL tasks as this article points out I'm very confused by this. I've used pandas for ever a decade, and in most cases it's a massive time saver. I can do a single query to bring data into local memory in a Jupyter notebook, and from there re-use that memory across hundreds or more executions of pandas functions to further refine an analysis or whatever task I'm up to. Your "copy-object-copy" is not relevant in data analysis use cases, and exists in pretty much any system that pulls data from an external service be it SQL or not.
- mistrial9 5y ago
  parent post says "devs do SELECT *" and ... relational databases can have a lot more data in one table, or related tables, than one query needs. It is often very wasteful of RAM to get ALL and filter again
  - tomnipotent 5y ago
    Not for data analysis, it's a pointless constraint unless you're having issues. Most data analysis isn't against datasets larger than memory, and I'd rather have more data than I need to spend time waiting to bring it all local again because I forgot a few columns that turn out to be useful later on.
    - disgruntledphd2 5y ago
      True, but in that kind of exploratory environment, you'd normally reduce data load and speed up iteration by using sampling, which is super easy in SQL. > Not for data analysis, it's a pointless constraint unless you're having issues. This will always happen, as time spent on the project scales up (unless you use some kind of autoscaling magic I suppose).
    - EForEndeavour 5y ago
      I totally get what you're saying because most of my datasets also used to be smaller than my laptop's memory. Used to. I'm in the process of moving more and more processing "upstream" from local pandas to the DBMS and it's already proving to be a bit of a superpower. I regret not learning it earlier, but the second-best time is now.
  - protomyth 5y ago
    If your query does " * " it gets all the columns in all the tables. Often, the optimizer when all the columns you need are on the index, never visits the actual table (I remember the term covered index). " * " basically screws this up. "Select *" should never be used in anything in an actual production environment.
mixmastamyk 5y ago
Interesting. I'm guessing the extra layer may be needed for some manipulations of data at the application layer, but perhaps that is almost always avoidable? Have you ever tried Hasura? I'm thinking of giving it a go in a future project.
- tylerhannan 5y ago
  If/as you give it a go in the future...do let us know what is working and what could be better. We do, in fact, care. <disclaimer: I work at Hasura>
- musingsole 5y ago
  It's only avoidable to the degree that your software architecture and company architecture allow you to make the more efficient decision to move the logic upstream. The cases where this pattern emerges are invariably because someone without insufficient access to the DB and its configuration bites the bullet and instead builds whatever they need to do it in the environment they have complete access to. I've similar problems lead to dead cycles in processor design where the impact is critical. These problems aren't software technology problems. They're people coordination problems.
- arcticfox 5y ago
  As a Hasura user, I highly recommend it for OLTP workloads. And for the specific subject at hand (memory usage) it is fantastic. Its (in-database) serialization speed just completely runs circles around anything we could do before in Ruby.
  - mixmastamyk 5y ago
    Sounds correct to me. Although I've heard Ruby is slow, I suspect json serialization is done in C? At least it is in Python which I'm more familiar with. Simple cutting out the extra layers is where the big savings likely are.
dragonwriter 5y ago
> If psql can't do what you want, resist the temptation to 'SELECT *' 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. Why are we introducing an additional tool (psql) here unnecessarily? Sure, if it can be a simpler postgres query, that can be useful, but introducing psql and psql scripting into a workflow that is still going to use python is...utterly wasteful. And even simplifying by optimizing the use of SQL, while a good tool to have in the toolbox, is probably pretty low value for the effort for lots of data analysis tasks.
- EForEndeavour 5y ago
  I'm pretty sure the premise here is that the many gigabytes/terabytes of data reside in an RDBMS to begin with, so we aren't introducing another tool -- we're saying it makes sense to leverage multiple decades' worth of database optimizations by doing a bunch of data filtering, processing, and analysis in the database software and then exporting intermediate results to a pandas environment for final tweaks, visualization, etc.
  - dragonwriter 5y ago
    > I'm pretty sure the premise here is that the many gigabytes/terabytes of data reside in an RDBMS to begin with, so we aren't introducing another tool psql is a separate scriptable client tool from the postgres database server.
    - shkkmo 5y ago
      The point was about doing more work using the SQL DB engine, the client library you do that with seems irrelevant to making that point.
      - dragonwriter 5y ago
        > The point was about doing more work using the SQL DB engine The specific reference was to “psql script”, which runs in the psql client program, not the DB engine. Since I suspected that might be an error intending to reference SQL running on the server, I separately addressed, in my initial response, both what was literally said (psql script) and sql running in the DB.
WhompingWindows 5y ago
Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know you'll throw out NA's on certain variables, if you need a certain date or age range, or even the way you format these filters: these all lead to important savings. And this saves R from being full on memory, which would often happen if you fed it way too much data. Within a system of 1400 analysts, it makes a big difference if everyone's taking 2X or 4X the time pulling that they could be. Then, even the efficiently written pulls get slowed down, so you have to run things overnight...and what if you had an error on that overnight pull? Suffice to say, it'd have been much simpler if people wrote solid SQL from the start.
- solaxun 5y ago
  I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?
  - protomyth 5y ago
    Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.
    - deleted 5y ago
      [deleted]
    - alextheparrot 5y ago
      This is where I think the original data admins were deluding themselves. Expecting 1,400 analysts to write better code is a really non-trivial problem, but easy to proclaim. An actual solution is creating pre-joined tables and having processes ("Hey your query took forever, have you considered using X?") or connectors (".getPrejoinedPatientTable()") that make sure those tables are being used in practice.
geraneum 5y ago
Well it's true that bringing row(s) of data from database to python and then serializing it for any reason as has extra overhead. But in Django's case, the same arguments can be made as for Pandas. Django is a big complex framework and an application using it might consume more memory due to countless number of other reasons. There are also best practices to use Django ORM. But to say if a given Django instance consumes more memory it is only because of "Copy-Object-Copy effect"... I don't think so.
allknowingfrog 5y ago
Is it possible to do any kind of version control with PostgREST? I have no doubt that raw SQL offers huge performance advantages over my ORM, but I'm pretty fond of the ability to roll back to a previous state when I push a bad change to production. Performance is just one of a number of tradeoffs that I consider when I'm choosing tools.
- sitkack 5y ago
  It is customary to check in all DB assets, table creation scripts, queries, stored procedures, etc. No different than the rest of the development process. If something isn't being versioned, there is a huge problem.

password4321 5y ago

I would appreciate pointers to any other low/no-copy db -> browser technologies. I was thinking it would be cool to use parts of JavaScript db libraries to parse query results passed straight through in (compressed?) db wire protocol format via WebRTC.

tomnipotent 5y ago

> it's that people way overuse it for SQL tasks as this article points out I'm very confused by this. I've used pandas for ever a decade, and in most cases it's a massive time saver. I can do a single query to bring data into local memory in a Jupyter notebook, and from there re-use that memory across hundreds or more executions of pandas functions to further refine an analysis or whatever task I'm up to. Your "copy-object-copy" is not relevant in data analysis use cases, and exists in pretty much any system that pulls data from an external service be it SQL or not.

mistrial9 5y ago
parent post says "devs do SELECT *" and ... relational databases can have a lot more data in one table, or related tables, than one query needs. It is often very wasteful of RAM to get ALL and filter again
- tomnipotent 5y ago
  Not for data analysis, it's a pointless constraint unless you're having issues. Most data analysis isn't against datasets larger than memory, and I'd rather have more data than I need to spend time waiting to bring it all local again because I forgot a few columns that turn out to be useful later on.
  - disgruntledphd2 5y ago
    True, but in that kind of exploratory environment, you'd normally reduce data load and speed up iteration by using sampling, which is super easy in SQL. > Not for data analysis, it's a pointless constraint unless you're having issues. This will always happen, as time spent on the project scales up (unless you use some kind of autoscaling magic I suppose).
  - EForEndeavour 5y ago
    I totally get what you're saying because most of my datasets also used to be smaller than my laptop's memory. Used to. I'm in the process of moving more and more processing "upstream" from local pandas to the DBMS and it's already proving to be a bit of a superpower. I regret not learning it earlier, but the second-best time is now.
- protomyth 5y ago
  If your query does " * " it gets all the columns in all the tables. Often, the optimizer when all the columns you need are on the index, never visits the actual table (I remember the term covered index). " * " basically screws this up. "Select *" should never be used in anything in an actual production environment.

mixmastamyk 5y ago

Interesting. I'm guessing the extra layer may be needed for some manipulations of data at the application layer, but perhaps that is almost always avoidable? Have you ever tried Hasura? I'm thinking of giving it a go in a future project.

tylerhannan 5y ago
If/as you give it a go in the future...do let us know what is working and what could be better. We do, in fact, care. <disclaimer: I work at Hasura>
musingsole 5y ago
It's only avoidable to the degree that your software architecture and company architecture allow you to make the more efficient decision to move the logic upstream. The cases where this pattern emerges are invariably because someone without insufficient access to the DB and its configuration bites the bullet and instead builds whatever they need to do it in the environment they have complete access to. I've similar problems lead to dead cycles in processor design where the impact is critical. These problems aren't software technology problems. They're people coordination problems.
arcticfox 5y ago
As a Hasura user, I highly recommend it for OLTP workloads. And for the specific subject at hand (memory usage) it is fantastic. Its (in-database) serialization speed just completely runs circles around anything we could do before in Ruby.
- mixmastamyk 5y ago
  Sounds correct to me. Although I've heard Ruby is slow, I suspect json serialization is done in C? At least it is in Python which I'm more familiar with. Simple cutting out the extra layers is where the big savings likely are.

dragonwriter 5y ago

> If psql can't do what you want, resist the temptation to 'SELECT *' 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. Why are we introducing an additional tool (psql) here unnecessarily? Sure, if it can be a simpler postgres query, that can be useful, but introducing psql and psql scripting into a workflow that is still going to use python is...utterly wasteful. And even simplifying by optimizing the use of SQL, while a good tool to have in the toolbox, is probably pretty low value for the effort for lots of data analysis tasks.

EForEndeavour 5y ago
I'm pretty sure the premise here is that the many gigabytes/terabytes of data reside in an RDBMS to begin with, so we aren't introducing another tool -- we're saying it makes sense to leverage multiple decades' worth of database optimizations by doing a bunch of data filtering, processing, and analysis in the database software and then exporting intermediate results to a pandas environment for final tweaks, visualization, etc.
- dragonwriter 5y ago
  > I'm pretty sure the premise here is that the many gigabytes/terabytes of data reside in an RDBMS to begin with, so we aren't introducing another tool psql is a separate scriptable client tool from the postgres database server.
  - shkkmo 5y ago
    The point was about doing more work using the SQL DB engine, the client library you do that with seems irrelevant to making that point.
    - dragonwriter 5y ago
      > The point was about doing more work using the SQL DB engine The specific reference was to “psql script”, which runs in the psql client program, not the DB engine. Since I suspected that might be an error intending to reference SQL running on the server, I separately addressed, in my initial response, both what was literally said (psql script) and sql running in the DB.

WhompingWindows 5y ago

Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know you'll throw out NA's on certain variables, if you need a certain date or age range, or even the way you format these filters: these all lead to important savings. And this saves R from being full on memory, which would often happen if you fed it way too much data. Within a system of 1400 analysts, it makes a big difference if everyone's taking 2X or 4X the time pulling that they could be. Then, even the efficiently written pulls get slowed down, so you have to run things overnight...and what if you had an error on that overnight pull? Suffice to say, it'd have been much simpler if people wrote solid SQL from the start.

solaxun 5y ago
I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?
- protomyth 5y ago
  Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.
  - deleted 5y ago
    [deleted]
  - alextheparrot 5y ago
    This is where I think the original data admins were deluding themselves. Expecting 1,400 analysts to write better code is a really non-trivial problem, but easy to proclaim. An actual solution is creating pre-joined tables and having processes ("Hey your query took forever, have you considered using X?") or connectors (".getPrejoinedPatientTable()") that make sure those tables are being used in practice.

geraneum 5y ago

Well it's true that bringing row(s) of data from database to python and then serializing it for any reason as has extra overhead. But in Django's case, the same arguments can be made as for Pandas. Django is a big complex framework and an application using it might consume more memory due to countless number of other reasons. There are also best practices to use Django ORM. But to say if a given Django instance consumes more memory it is only because of "Copy-Object-Copy effect"... I don't think so.

allknowingfrog 5y ago

Is it possible to do any kind of version control with PostgREST? I have no doubt that raw SQL offers huge performance advantages over my ORM, but I'm pretty fond of the ability to roll back to a previous state when I push a bad change to production. Performance is just one of a number of tradeoffs that I consider when I'm choosing tools.

sitkack 5y ago
It is customary to check in all DB assets, table creation scripts, queries, stored procedures, etc. No different than the rest of the development process. If something isn't being versioned, there is a huge problem.