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.

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.

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.

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

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

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

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?"