3 ms·

It's really unfortunate how much of database technology requires manual tweaking, what is sensible and optimized should be the default behavior. Obviously for A

It's really unfortunate how much of database technology requires manual tweaking, what is sensible and optimized should be the default behavior. Obviously for ALTER statements reordering of columns could mean a huge table rewrite, but there'd still be ways to handle it. On table create, though, you really should have to specify a manual override in order to prevent the DB from automatically performing these kind of obvious optimizations, IMO. On a side note, I've started to hear more buzz around this, but when we can have obvious indexing, sharding, caching, and query planning handled by ML...we will see a real revolution in the way we handle data. The models could be generalized to some degree across data sets and query patterns, but pretty easily progressively trained in production for a particular data set. The outputs we optimize for are basically read time, write time, and storage space...which all strike me as relatively easy targets for training. How great would it be to just move some sliders for those 3 around based off of preference and call it a day?

beefhash 9y ago
To me, this just seems to be struct packing[1] on the database layer. I would find the opposite behavior of automatic reordering of columns strange. [1] http://www.catb.org/esr/structure-packing/ http://www.catb.org/esr/structure-packing/
- vertex-four 9y ago
  As a counter-argument: databases do ~interesting~ things with your queries and the layout of your data anyway. SQL is meant to be declarative - it's not meant to define exactly how an operation is done, only that it's done in a method that results in something that logically looks like what you've declared it to look like. So as long as the row is ordered correctly when you do a `SELECT *`, it'd be perfectly reasonable in my opinion to store it physically in any order the database engine prefers.
dx034 9y ago
> It's really unfortunate how much of database technology requires manual tweaking, what is sensible and optimized should be the default behavior. Obviously for ALTER statements reordering of columns could mean a huge table rewrite, but there'd still be ways to handle it. I find that problematic with Postgres anyway as it doesn't allow moving columns. In SQL server, rearranging columns is fairly easy (still needs table rewrite) and it often makes sense, both from performance point of view (if you often read the same set of columns) as well as for program logic (reflecting the data logic in column order). But then again you pay for those SQL Server features..

beefhash 9y ago

To me, this just seems to be struct packing[1] on the database layer. I would find the opposite behavior of automatic reordering of columns strange. [1] http://www.catb.org/esr/structure-packing/ http://www.catb.org/esr/structure-packing/

vertex-four 9y ago
As a counter-argument: databases do ~interesting~ things with your queries and the layout of your data anyway. SQL is meant to be declarative - it's not meant to define exactly how an operation is done, only that it's done in a method that results in something that logically looks like what you've declared it to look like. So as long as the row is ordered correctly when you do a `SELECT *`, it'd be perfectly reasonable in my opinion to store it physically in any order the database engine prefers.

dx034 9y ago

> It's really unfortunate how much of database technology requires manual tweaking, what is sensible and optimized should be the default behavior. Obviously for ALTER statements reordering of columns could mean a huge table rewrite, but there'd still be ways to handle it. I find that problematic with Postgres anyway as it doesn't allow moving columns. In SQL server, rearranging columns is fairly easy (still needs table rewrite) and it often makes sense, both from performance point of view (if you often read the same set of columns) as well as for program logic (reflecting the data logic in column order). But then again you pay for those SQL Server features..