Hacker News Reader

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.

I don't disagree with writing solid SQL. I would go so far as to say some things (most) need to be in stored procedures that are reviewed by competent people. But, some folks don't think about usage sometimes.

This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have some basic distinct scenarios and to plan for them.

The most common is adding or querying data about a single entity. Most application developers really only deal with this scenario since that is what most applications care about and how you get your transactional data. Basic database knowledge gets most people to do this ok with proper primary and secondary keys.

Next up is a simple rule, "if you put a state on an entity, expect someone to need to know all the entities with this state." This is a killer for application developers for some reason. It actually requires some database knowledge to setup correctly to be performant. If the data analysts have problems with those queries, then its to to get the DBA to fix the damn schema and write some stored procedures for the app team.

At some point, you will need to do actual reporting, excuse me, business intelligence. You really should have some process that takes the transactional data and puts it into a form where the queries of data analysts can take place. In the old days that would be something to load up Red Brick or some equivalent. Transactional systems make horrid reporting systems. Running those types of queries on the same database as the transactional system is currently trying to work is just a bad idea.

Of course, if you are buying something like IBM DB2 EEE spreading queries against a room of 40 POWER servers, then ignore the above. IBM will fix it for you.

At its simplest its OLTP vs OLAP. Separate the data entry/transactional side of things from the reporting part. Make it efficient for data analysts do do their jobs.

But sometimes, your line-of-business application does analytics queries. Which means you need your app developers to understand how to do OLAP, and you also need a schema design that can run arbtrary OLAP queries within a few orders of magnitude of OLTP speeds (e.g. <10s.)

I can't help thinking that with better processes and tools those 1400 analysts could instead be 50 analysts? (or even 5?)

And that building an aggregation ETL pipeline, maybe inspired by this post, could be the solution?

VA is a massive org, this is not a particularly large number of analysts for a healthcare system of this size (or honestly any complex org).

Yes, but the answer that it is a massive organization does not satisfy my curiosity regarding what they are actually doing.

I come from a small country, in total comparable to the 6 million veterans mentioned here.

1400 analysts is just a lot and I wouldn't imagine for example our national healthcare service could ever employ that amount of analysts?

Hm, or would they? It would make a pretty big dent in the total amount of persons in that field in our country.

6 million Veterans are just the subgroup from one of the studies I did. In reality, the VA system serves 15-20 million patients, given there are 17.4 million Vets, some who use private health care, and some whose families also use VA.

The reason there are 1400 analysts: research studies each require one or two analysts. At this very moment, there are thousands of research studies taking place in the US medical system. Without these number of analysts, you'd have to completely revamp the system, killing all current projects, all current code, and creating a HUGE HUGE headache for everyone, not to mention laying off 1000+ through a system which it is NOT easy to layoff individuals through.

As a matter of fact, they want to transition to a new data infrastructure at the VA, but it's been delayed many times and the logistics have been very vague.

Is it even feasible to revamp the system efficiently?

We really should commission 2 more analysts to figure this out...

VA -> government -> bloat

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?

Disclaimer: It's been a while, but my very first job out of uni was dealing with large queries like that for reporting and import/export out of our OLTP database directly.

15 tables would've been not very common for us, but something between 5 and 10 was normal. Many of those tables would've had millions upon millions of rows (while some were simple 'key tables' with only hundreds or a few thousand records)

The one thing that I learned really fast is that yes, "prefiltering/subgrouping" as the OP calls it, is very important. If you cut down on the number of rows that your query "starts out with" was very very important, as it cuts down on the amount of data that needs to be dealt with in the rest of the query. This was for an old Sybase ASE based system. IIRC, ASE would only be able to automatically optimize this across 4 clauses (might misremember the number and I left before they upgraded to the new newer version with a better optimizer), so ordering of your join clauses was important. If the filtering that cut down on the amount of data needed from other tables came first, your query would run way faster, than if the filters were way down with the rest of the joins.

Just think about it, if you start out with getting 6 million patient records and then start collecting 6 million records from the next table for a join and so on and so forth, that's way more data that needs to be read and churned through than if you can 'start on the other end' so to speak, whittle it down to say 50000 records that now need to be looked up in the patient table.

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.

> why isn't someone rolling those tables into something a bit easier with some backend process.

You'd identified why we're all going to have jobs in 100 years.

Automation sounds great. It's exponentially augmenting to some users in a defined user space.

Until you get to someone like me, who looks at the production structure and goes: "this is wholly insufficient for what I need to build, but it has good bones, so I'm going to strip it out and rebuild it for my use case, and only my use case, because to wait for a team to prioritize it according to an arcane schedule will push my team deadlines exceedingly far."

This is why you don't roll everything into backend processes. Companies set up for production (high automation value ROI) and analytics (high labor value ROI) and has a hard time serving the mid tail. EVERYTHING on either direction works against the mid-tail -- security policies, data access policies, software approvals, you name it.

People, policy, and technology. These are the three pillars. If your org isn't performing the way it should, then by golly work at one of these and remember that technology is only one of three.

Tree pillars where you only can choose two to be perfect. Just like databases and CAP theorem

Technology is the easiest to adjust, ironically.

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.

> why isn't someone rolling those tables into something a bit easier with some backend process.

To put it in more concrete terms(plain SQL): the tables could be aggregated on a set returning function or a view.

Yes, in these situations materialized views with indexes are generally the correct answer.

May I introduce: Protomyth, meet DW and ETL.

How many TBs of data are we really talking here, if it's just 6M rows? Surely this processing could easily be done on a single machine.

It doesn't say it is just 6m rows. It is 6m patient, which only hints that one of the dimension tables is 6m. Facts gathered in patients might be significantly larger. Also, my experience is saying, if you have hundreds of queries running simultaneously, it is not the volume of data that can be a bottleneck. Depending on the system it can be anything, starting from acquiring lock on a record or initiating a transaction.

True, the number of events / records is probably significantly larger than 6M. I still have a hard time believing any reasonably modern datawarehouse would struggle with queries on this dataset.

If you've got 1400 analysts, I hope you've exported your data from an OLTP database to an OLAP database. Those generally are much easier to scale to many users.

I work for a large healthcare org and from personal experience these things can get large. 6m patients is prob 20m encounters, each encounter may have 10 different kinds of meta data (each one it’s own table) and each kind has 10-100 rows. So really you are doing joins between 10 tables each with ~1-10 billion rows. It actually does slow down even running on expensive hardware.

Ah yes that does sound painful. I'd generally recommend that data be denormalized in the warehouse so the joins don't need to be done at query time. That can make the queries more complex, however.

Hi, I saw your comment on the wechat page, I was wondering if you have the account and can verify me?

> 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.

I know a team that would read in multi-GB CSVs from an FTP site into an orchestrator server using Pandas, write to locsl CSV in the orchestator, validate data after write, reload into pandas, painfully and manually check every data type, then use a pandas connector to a database.

Every step along the way here is wrong. The database came with SFTP connectors. The orchestrator should have simply told the database server to pull from the SFTP site into a landing table. Even if not, Pandas has datatypes you can specify on ingestion, which is heaven if your CSV or other files don't record filetypes yet are consistent in structure. Further, if you have custom validation you're applying (not a bad thing!) you likely rarely even need pandas; the native CSV library or (XLRD/openpyxl) for Excel are fine.

Ultimately, it is a training issue. The toolspace is too complex, with too many buzzwords to describe simple things, that people get lost in the whirlwind.

Uses pandas to infer datatypes leads to nasty coercions. Also, pandas --> objects --> chunk based reading yields the same performance as any other library you mentioned.

Absolutely. On top of that, the Pandas object type (most people's default for strings) can house any scalar type, including ints and floats. This has hit a LOT of immature adoption of pyspark, for example, where the datatypes get screwy.

Pandas is great when you're working on the data manually because it lets you interleave python code and "queries", but it's strictly worse than a plain SQL statement if you're writing, say, a REST service that needs to output some data.

SQL executed by the database is orders of magnitude more efficient, way more expressive, and doesn't require you to memorize pandas' absurd API.

And I say this as someone who is by no means a SQL wizard, and fully acknowledging all of SQL's blemishes.

> a REST service

This is a post about data analysis, and everyone wants to point out that pandas isn't good at real-time service requests.

> SQL executed by the database is orders of magnitude more efficient

Compared to pandas? No, it's not. Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.

> Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.

Both of you are right.

Sure, if you need to grab a huge chunk of the entire database and then do tons of processing on every row that SQL simply cannot do, then you're right.

But when most people think SQL and database, they're thinking of grabbing a tiny fraction of rows, sped up by many orders of magnitude because it utilizes indexes, and doing all calculations/aggregations/joins server-side. Where it absolutely is going to be orders of magnitude more efficient.

Traditional database client API's often aren't going to be particularly performant in your case, because they're usually designed to read and store the entire result of a query in-memory before you can access it. If you're lucky you can enable streaming of results that bypasses this. Other times you'll be far better off accessing the data via some kind of command-line table export tool and streaming its output directly into your program.

I agree, they're both right.

I've been doing data science since around 2008 and it's a balance between local needs and repeated analysis and an often more efficient one off query. Sure the SQL optimizer is going to read off the index for a count(*), but it doesn't really help if I need all the rows locally for data mining anyway. The counts need to line up! So I'll take the snapshot of the data locally for the one off analysis and call it a day. If I need this type of report to be run nightly, it will be off of the data warehouse infrastructure not the production DB server.

Shrug. These things take type and experience to fully internalize and appreciate.

> But when most people think SQL and database

We're not talking about most people, but data analysts. If we're just doing simple sum/count/average aggregated by a column or two with some basic predicates, SQL and an RDBMS are your best friend. Always better to keep compute + storage as close together as possible.

> Traditional database client API's

I'm not sure what point you're trying to make with this. Most data analysts are not working against streaming data, but performing one-off analyses for business counterparts that can be as simple as "X,Y,Z by A,B,C" reports to "which of three marketing treatments for our snail mail catalogue was most effective at converting customers".

I'm not talking about streaming live data, I'm talking about streaming query results if you re-read my comment.

If you're reading many MB's or GB's of data from a database, it's a lot more performant to stream it from the database directly into your local data structure, rather than rely on default processing of query results as a single chunk which will be a lot worse for memory and speed.

Streaming aggregation relies on knowing the problem beforehand, so it's pointless in the context of the day-to-day work of analysts where most of the project is spent figuring out the right questions to ask.

If I need to stream data because the size of data is a constraint, I'm either working on the 0.1% of projects that require it or on an analytical product rather than an analytical project.

> Compared to pandas? No, it's not.

I'm not saying you shouldn't use pandas, it depends on the size of the data. I'm working right now on a project where a SELECT * of the entire fact table would be a couple hundred gigabytes.

The flow is SQL -> pandas -> manipulation, and as always in pipelines like those, the most work you can do at the earliest stage, the better.

Yeah, speaking as a data person, the SQL argument is correct. Python/R are much, much, much slower for this kind of work.

OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging.

Personally, I'm a big fan of using SQL/Airflow/whatever to generate whatever data I'll need all the time at a high level of granularity (user/action etc), and then just run a (very quick) SQL query to get whatever you need into your analytics environment.

Gives you the best of both worlds, IME.

> OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging.

OTGH, to some (I suspect often rather large) extent, that's because most people (I suspect including many "data scientists") are pretty bad at doing their data munging in SQL.

Perhaps (although this tends to be a core skill for any experienced DS - I 100% would not hire anyone without basic SQL, as it's just setting them up for failure). SQL is the only tool I've used everywhere I worked.

But, SQL is bad at lots of stuff. For example, if you need to compare id1 and id2 (with some kind of locality sensitive hash or something). This requires a full join, which is prohibitive in any large data environment.

And honestly, writing 50 case when statements when I could write a function in R or Python is not my idea of a good time.

I love SQL, and do a lot with it, but there are definitely cases where it's the wrong tool. As an example, retention analyses are really annoying to do in SQL, but pretty easy in R/Python (to be fair, the article actually provides a solution here, but it's not standard).

This is a great point and way of looking at it: pandas and SQL live at opposite ends of the maturation level of data wrangling pipelines.

The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL. Also in production or when your database doesn't fit in memory the idea is to first use SQL to generate an optimized view of your data from the database before further analysis and transformation.

> The point is that if you only need to join and aggregate data it's easier and more efficient to do it directly in SQL

citation needed? I've seen plenty of cases where it would have taken the db ages to do something that pandas does fast, and I don't consider pandas to be particularly fast.

TFA

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

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.

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.

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).

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.

Covering index is right.

Select * has a few valid use cases.

If you're selecting from a cte or subquery then writing the same column list twice is redundant and increases complexity/risk of mistakes.

* is also harmless for count and exists.

Data analysis workloads more often run into problems solved by partitioning rather than indexes.

yes and no - I trained on "larger than RAM datasets" intentionally, and subsequently took on projects that require "larger than RAM datasets". Two things happened on my way, companies led by Google invented and deployed datasets previously impossible e.g. BigTable, and secondly the equipment I worked on went from modest to mid-sized RAM (hard to define that). Granted that lots of very useful (and common?) tasks are not "larger than RAM datasets", and then have very different characteristics.. which is starting to sound like "Excel problems look like this, they always fit in RAM, they need partitions not indexes" and the like..

There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..

> companies led by Google invented and deployed datasets previously impossible e.g. BigTable

BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.

I got the impression the problem is not so much the "SELECT *" as the missing "WHERE" clause.

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.

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.

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.

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.

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>

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.

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.

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.

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.

> 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.

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.

> 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.

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.

> 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.

Any psql script would be building and running SQL queries so I fail to see how that has any impact of the point being made.

Edit: The argument is that there are operations that should be done in the SQL layer and it is worth time learning enough about that layer to understand when and how to use it for computation. Once you learn that, it isn't really relevant if you are using psql or some other client library to build those queries.