Hacker News Reader

Comment by bjarneh | Hacker News Reader

> assuming that your application doesn’t actually need all the columns.

Title is a bit click-bait-ish; I was expecting some deep insight of why this:

     SELECT A,B,C FROM TBL;

was superior to this:

     SELECT * FROM TBL;

when the table had three columns. Instead we get a wildcards are bad argument; especially when that wildcard fetches data that you don't need etc; which I guess everyone agrees with.

View on HN

#1 comment:

> Instead we get a wildcards are bad argument; especially when that wildcard fetches data that you don't need etc; which I guess everyone agrees with.

#2 comment:

> Nevertheless in reality most of the stated reasons have almost no real practicability... So in most cases fetching to many columns would be no problem

This happens surprisingly frequently in a lot of tech discussions. The most popular comment accuses the author of saying something so obviously correct, that it's a waste of time to even bring it up. And the second most popular comment is someone telling the author that he's wrong.

Both comments can work together. Yes, doing more work than necessary is bad (#1). The amount of "bad" is very low to barely measurable in most real-life scenarios (#2).

"The amount of "bad" is very low to barely measurable in most real-life scenarios (#2)."

When you constantly apply this "only things bigger than X matter" reasoning you still end up with slow, shitty software but you just died by a thousand cuts instead of 1.

And the rub is if you fail to apply it liberally enough you end up with no completed software or difficult to maintain software because someone went around applying every micro-optimisation in the book regardless of how nessasary or appropriate it might be.

"someone went around applying every micro-optimisation in the book regardless of how nessasary or appropriate it might be"

This feels like a strawman. I have yet to see an instance of this in my 10 years. The much, much, much more common case is people justifying half-assed shoddy work with YAGNI.

See: solo indie gamedevs that build their own engines for years and subsequently fail to release anything.

Survivorship bias suggests we're more likely to see projects that resulted in released software...

Fortunately, stars in sql are also really bad for maintenance ;-) So I guess there is indeed no reason to use them!

I work that way, and I don't end up slow, shitty software.

I do end up shipping working software much sooner than if I optimized every statement everywhere.

Building software is making a series of trade-offs, but most tech articles present themselves as though they have the answer. It's not too surprising, then, that the top comments take opposing stances on an issue that is definitely more complicated than the article made it sound.

We have trade-offs but they are not all equal. It is both legitimate and encouraged to argue about which are the correct trade-offs IMO.

Agreed! The fact that the top two comments usually disagree is one of the primary reasons I read comments on HN. The dialectic allows me to make an informed decision later.

> accuses the author of saying something so obviously correct

Perhaps I do; but my main "accusation" here is that the title is pure click bait, if it was replaced by this:

     SQL - Don't fetch data you don't need!

Would anyone here bothered reading it?

The correct answer to almost every db question is: “it depends”.

For instance. “Select *” can be good for ad-hoc exploration (as long as you include a limit to the rows returned). It’s bad practice for shipped code because it can lead to binding issues, in code, when you modify or add columns.

“Select *” will almost certainly be optimized away by the execution plan but if you have blob data, could introduce a lot of unnecessary disk i/o.

Is “select *” bad? It depends.

> “Select ” will almost certainly be optimized away by the execution plan
What do you mean? Ask for all the columns, you're gonna get all the columns in a resultset. Only in a subquery could the planner know what is unused.

'select ' is _perhaps_ tolerable for creating a view--under the conditions that the database transforms the query into the unambiguous named columns at view creation time--and that's about it.

You’re right. When I wrote that, I was thinking higher up the chain in an orm layer. Sending “select *” to the db can’t be “optimized away” at that layer, it will have to return all the columns. Unless, like you said, the scope of the “select *” contains it in a sub query where the columns used can be known... “it depends :)”

And, not to be a curmudgeon, but, most apps don't really need that level of optimization. In 90% of apps it's fine. The 10% that really need the performance are hopefully doing it right with well tuned indices, careful denormalization, and other things. It is unimaginable to me, that someone with a higher performance SQL/RDBMS backed app can't run a query planner and tame ORMs for performance and identify things like wildcard column select. But what do I know :)

Very true. Also, along those lines, if you have fewer than 10k rows, it’s likely the dB will keep the whole table in memory after the first access, anyway.

In an ideal situation, agreed. But surely that depends entirely on how many tables are being queried (how many tables already exist in the cache), the size of the rows, how much RAM the host has / how big the cache is, plus a few other variables most probably.

What I mean is: like all other things, it depends. And with assumptions like this, it is generally better to measure.

he's not really talking about the performance of the DB though, focusing more on size of the resultant payload thatr gets sent over the wire. In the actual database which is traditionally very challenging to scale, projections aren't very expensive. The other things you and GP mention are way more likkely to move the performance needle.

>> binding issues, in code, when you modify or add columns.

This is very common IME, and it is a very bad code smell. It tends to break things very low in the architecture, so even capturing the error makes the application essentially not work in a fundamental way.

Well, the good news is that this comment cracked me up, thus demonstrating that this thread does provide some value to HN.

Cheers

it does not invalidate his/her observation

Using select * can cause some correctness issues, but from a performance perspective, it’s basically an archetypal topic for bike shedding. In the sense that using the smallest projection you actually need is an optimization, but for most use cases it will end up being an irrelevant optimization. So select * is bad for performance, but you don’t need to care about it unless you’re experiencing one of the problems it case cause, which most people probably don’t. But you can have a back and forth discussions about that forever.

This reminds me of a chilling short story I read a while back, but I cannot remember its name. It was about a machine learning algorithm trained on social network archives to take as input a body of dialogue between people and communities and to output statements which would strongly divide them. As a joke, the developers ran the algorithm on their internal corporate email and read the output sentences, laughing that anybody at their company could possibly come down on the other side of any of the resulting statements before realizing that they were talking about different sides. No longer being able to stand each other, the company dissolves. It was a good story if anyone recalls its name.

I just read it again recently. Shiri’s Scissor.

https://slatestarcodex.com/2018/10/30/sort-by-controversial/

That's it! Thanks so much.

That was a hell of a read. Thanks for the link!

This is because "strong opinions, lightly held" creates a culture where people with little to no insight or experience feel empowered to create an unceasing high-volume firehose of worthless opinions that drown out anything useful.

In general, in my limited experience, the former is better than the latter because you have NO IDEA what's going to be in that database a decade from now, but your query is probably still getting called by some obscure application that nobody even knows exists.

When someone does something stupid and puts a whole bunch of unexpected data into that database, the latter query will cripple the entire show, whereas in theory the former will still just ask for the original intended data.

Really? In my experience adding columns to a production database table is pretty infrequent, and even when it happens it's usually only one or two. (It's often a pretty big headache to add columns in practice.)

I've worked on a lot of databases and I've never seen the number of columns grow to the extent that it would impact performance in a large way. It might go from 8 to 11 columns, but I've never experienced anything like 5 to 55.

For a variety of reasons, if there's a whole new set of data (like customer survey responses to be associated with the customers table), or large data (like product thumbnails to be associated with the products table), to be associated with primary keys, it's added to an entirely new table that you can perform a JOIN on only when required.

Not to mention that while any application client can often add new rows to a database, they can't generally add new columns -- whoever administers the database often keeps those privileges for themselves for general security reasons.

Could also be a matter of the column content.

It may just be one json column added to your Postgres table, but who knows how much space one row could take in production.

I worked on the Facebook MySQL team for a few years. SELECT * was banned for exactly the reason GP stated. One scenario that can really bite you here is having a column of sub-structured data, like a JSON struct, added to an existing table of “skinny” data: imagine your schema is (id, first, last, phone, email) and you add a bio_as_html column. Now your SELECT * is returning a potentially large, opaque string blob for every row, to no purpose. This wastes network bandwidth, CPU, heap pressure, cache pressure, and so on.

Another reason—if you have multiple databases (or just non-synchronous replicas) and do online schema changes, it’s possible to end up with different schemas visible on the same table. If you are confident that every engineer in your employ is an experienced SQL author and/or your ORM is perfect and would never access a column by index number rather than text name, you should be OK. Everybody else should query explicit column names.

Some columns have large size variance. You are hedging against them growing. It's almost never a problem on fixed size columns.

> In my experience adding columns to a production database table is pretty infrequent,

It's infrequent because crappy development practises mean that when you try and change the schema a pile of people scream abuse at you because their crappy wildcard queries break, because the crappy code written around those wildcard queries shits the bed if the columns change in any way, shape, or form.

I was thinking more that the process can take hours with large tables (and even longer with replication), you have to be careful it doesn't lock your tables, and basically just a ton of edge cases.

"Crappy wildcard queries" would seem to be the least of it, ha. Most of the time, a wildcard query that returns additional columns at the end won't cause any problems at all -- client software will just ignore them. Unless you're making each row 100x larger in size and you get a performance hit, but that's the point I was making -- that's not something that usually happens in reality anyways.

That's fair, but some database systems have added functionality to avoid this specific problem. In Oracle, MySQL, and MariaDB, you can mark a column as INVISIBLE to exclude it from SELECT *.

It's still not a great practice to use SELECT *, but at least this gives you a way to add columns without breaking existing applications. It's also useful when you plan to remove a column, to test in advance whether anything using SELECT * would break without that column there.

That's an interesting feature and could be helpful for varbinary(max) type columns. But also very annoying because I'd be thinking "Does that column exist or not? Why is it not showing up for me?"

Yes, and this is the biggest non-performance reason that wildcards are bad: because over time, the schema should be able to change, often in ways that render select * (and the equally works-only-by-accident code calling it) broken.

This is even more so when joins or views are involved, because the columns returned may change over time; joins particularly. Or, for that matter, migrations; while a CREATE plus ALTER may have resulting in a particular column ordering in 2002-2017, the new DB using the CREATE that rolls up all those changes in 2018 may result in a different column ordering, with subsequent broken code (which will be blamed on the DB by the thoughtless programmer, of course).

Your first question is fairly common too- select * is to be avoided if your table can change or you care about order of columns.

Select * is better from cte/temp table because you then only need to make changes in one place.

I'm not sure how much deeper it's possible to go.

I agree wildcards are better avoided but so are column orders in result extraction. They are a source of bugs and much review work when changes are made.

If I had to choose (which usually isn't necessary) I'd say wildcards are the lesser evil.

it's becoming increasingly more common to sort columns at the application layer

I agree. Applications that rely on the ordering of columns are as bad as people who complain when I add a new column into, or change the ordering of a CSV which has column headers. There is no technical reason to do this; computers are ample fast enough to look up columns by name!

>computers are ample fast enough to look up columns by name!

Sure, but depending on number of records and/or implementation of the name lookup, it can add up over a large resultset, or have a slightly cost over many smaller queries.

Mind you, I think -most- implementations are smart enough to only need to do the lookup once for a returned datareader, but the cost is still there.

> so are column orders in result extraction.

While I agree, there are some languages + db drivers where this is your only choice unless you roll your own. Go's sql package relies on order in the Scan method, for example.

"care about the order of your columns" is a far bigger red flag than "select *".

I think the title is apt. It is quite common for people to mistakingly do this pattern, I have seen this mostly happen through ORMs.

It might have changed but in ActiveRecord it was fairly common to do.

  models = Model.where(column_name: column_val)

and then further down the fetch, just use two three columns from the whole fetch. This used to issue a `SELECT *` to the table if no select clause went along, and most people don't realise the harm with fetching all columns. Even if it is a single row, storage engines like InnoDB store blobs in an indirected way, which can lead to more read IO ops than required.

That's not a mistake at all. It's the simplest code that loads the data you want. 99.9% of cases the "harm" is insignificant. Anything more complicated is premature optimization.

I think using SELECT in all cases, enforces the idea of just requiring what you need which just gives the performance benefit in other cases even if they occur less.

Even without the optimisation bit, personally I prefer when code is explicit about what it is doing, like the data it is operating on/with. While omitting select makes the code succinct, having it there gives an idea at a glance about what it is operating with.

The real landmine underneath all this is: what is going to be updated when.

In all probability (we did say Oracle, which means enterprise), this application code is going to live forever, and never receive dev attention once it reaches functional status (unless something breaks).

Consequently, encoding something like "SELECT *" on the app side means that will never be updated, or even noticed, which is the same as saying it will be impossible to subsequently optimize.

Because you can't optimize specifics (these columns) from the DB side, when the only information an application gives you is generic (requests all columns).

Multiple this by 100 apps hitting the same core databases, and you start to see why it's a big deal, from an organization-lifecycle perspective (over 20 years+).

There is no optimization to which you cannot apply that logic. You're claiming "always optimize everything, because we can never change code". Even in enterprise, this is nonsense.

This is classic premature optimization. You don't know that overselecting will have any significant performance impact, and barring a few obvious cases (LOBs) it's quite unlikely to matter. There's a material cost in complexity to add situation-specific SQL vs the aforementioned:

    models = Model.where(column_name: column_val)

Most databases make it pretty easy to find out what queries are consuming resources. Spend your time optimizing those.

There are tons of optimizations that logic can't be applied to!

For starters, any software developed in any company with the ability to deliver updates in a timely manner.

It's a tradeoff. You might want to encapsulate database access and deal with objects instead of relations in most of your stack, which leads to these inefficient access patterns. Usually it doesn't matter. And if it does matter, you are probably doing something harmful like querying in a loop, n+1 etc.

>which I guess everyone agrees with.

You would be surprised how many don't even think a second about it, not to say realize any of the points made by the author.

> [...] > was superior to this: > [...]

For API Stability:

- querying only the columns you really need makes it less likely that a column you don't really need happens to somehow brake your code if changed (e.g. because some ORM mapping not working). (This btw. is one of the major drawbacks of many REST APIs, i.e. they default to the rest equivalent of ).

- deciding the order in which you want columns to be returned explicitly is less bug prone to a variety of bugs, relying on column order of `` is brittle but some ORM do so and some other code might do so by accident in some way or another. Explicitly defining the order makes it easier to avoid such bugs.

- SQL queries doesn't need to be updated if you add columns (to explicitly not query the for the query unnecessary new column)

Depends upon how you use your database. If tables are a 1-to-1 representation of domain objects then you should use select *. If it isn't then you should prefer to be choosy about your columns.

It's obvious once you think about it, but it's not something I'd ever thought about before. I think the article still had value

Hi, IT auditor here.

Sometimes 'we' ask * instead of "gimme everything that starts with a number" because we may need to check that the column "ID" which is supposed to be a 10-digit number, may have an entry "A1234567890". And this has not raised any alarms. And this means that this human is not paying taxes, because his/her tax-ID is not 'run' when the gov runs the tax calculations, because the script pulls 10-digit numbers, and my ID has a letter. So some DBA got paid $50k, changed my field, run the thing, change back my field. If you think this is not happening, it is.

Consider other scenarios on obligations like that.

I once did a KYC audit for a bank. When I asked * , I laughed and cried with the results.

Soooooooooo.. yes, very click bait-y title. Each SELECT should fit the purpose it serves for the report reader. When doing KYC and you have to review 5mil clients, you want *, otherwise you will miss the: Surname: 123Smith!!!, DoB: 30/2/1980, and other duplicates, nasty surprises, people over 150yo, etc.

Unless I'm misunderstanding, what you're talking about there isn't wildcard selects but rather SELECT's without any filtering.

ie having `SELECT id, firstname, surname FROM people` would still catch 'A1234567890' and '123Smith!!!'

Where as `SELECT * from people WHERE id REGEXP '^[0-9]+$'` would exclude them despite being a wildcard SELECT.

Ummm... you seem very confused... The "select" part of the query solely filters on columns, not rows. You are talking about filtering on rows.

Beyond that, when you are looking for anomalous conditions you don't say "get me all records," because how you going to pick out something that doesn't look right out of five million items? When you are looking for anomalous conditions you say "get me all records that are NOT in the form I am expecting." Then you add constraints, foreign keys, etc so it doesn't happen again.

Reply on news.ycombinator.com