3 ms·
N̶o̶ ̶s̶h̶i̶t̶,̶ ̶S̶h̶e̶r̶l̶o̶c̶k̶!̶ You don't say?
by p2t2p 9y ago
N̶o̶ ̶s̶h̶i̶t̶,̶ ̶S̶h̶e̶r̶l̶o̶c̶k̶!̶ You don't say?
- p2t2p 9y agoIt was a bit abrupt but seriously it is kinda disheartening to read that a _lead_ developer _discovered_ such a basic thing.
- trhway 9y agoReminds about that funny article from Uber on why they switched from one DB to another. Seems like they had failed to discover a number of things.
- cuchoi 9y agoThe article is proposing is that it can be quicker to drop the indexes and recreate them than to load a lot of data in an indexed table.
- segmondy 9y agoIt's not a proposal, it's common knowledge. INSERTS causes the index to be rebuilt. You have to search and find the right location then insert the new pointer. If you do 1,000,000 inserts that's 1,000,000 searches and writes to the index.
- ninkendo 9y agoSure INSERTs do, but I personally would have guessed COPY does its work in a single transaction, which under the scenes I would hope would avoid rebuilding the index until the transaction is committed? I admit there’s no reason to expect this other than “so you don’t have to do the dumb drop/recreate indexes” trick... maybe I just expected COPY to be smarter than just expanding into a set of insert statements.
- protomyth 9y agoYep, and this is common knowledge for most folks who do this kind of work. Heck, if you can stop the database, a whole host of things become quicker by going drops and recreating things. Most alter commands are quicker if you do drops and creates often even if a copy of a table needs to be made. Maybe we are missing something by getting rid of the DBAs.