Re:“Column Tetris” – Calculating and Saving Space in PostgreSQL
by beefhash on Tuesday February 27, 2018 @08:25AM
(#16473109)
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.
Re:“Column Tetris” – Calculating and Saving Space in PostgreSQL
by vertex-four on Tuesday February 27, 2018 @08:56AM
(#16473337)
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.
Re:“Column Tetris” – Calculating and Saving Space in PostgreSQL
by dx034 on Tuesday February 27, 2018 @09:22AM
(#16473543)
> 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..
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?