Hacker News Reader

Comment by mistrial9 | Hacker News Reader

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

View on HN

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.

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

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.

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.

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.

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.

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

Reply on news.ycombinator.com