5 ms·
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
by crazygringo 6y ago
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.
- rm_-rf_slash 6y agoCould 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.
- ericbarrett 6y agoI 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.
- deleted 6y ago[deleted]
- jlouis 6y agoSome columns have large size variance. You are hedging against them growing. It's almost never a problem on fixed size columns.
- rodgerd 6y ago> 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.
- crazygringo 6y agoI 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.