N̶o̶ ̶s̶h̶i̶t̶,̶ ̶S̶h̶e̶r̶l̶o̶c̶k̶!̶ You don't say?
It was a bit abrupt but seriously it is kinda disheartening to read that a _lead_ developer _discovered_ such a basic thing.
Boosting the performance of PostgreSQL’s COPY command by dropping indexes
11–20 of 49 posts
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#12yep
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#13Is it not common knowledge that dropping indexes improves database insert performance?
Of course, that's often not an option when you you're loading records into a live database that's also getting queries, you usually don't want every query to result in a full table scan.
This was well known 20+ years ago when I was an entry-level DBA, and I assumed it was still well known today.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#14I must be getting old.
Kids, many years ago, even before jQuery, software would come with documentation that you could read and it would tell you how to use it effectively.
I know, crazy right? But to this day some of that old software, of which PostgreSQL is an example, still has this documentation that you can read, even before you use the software in a production system.
Yeah, yeah, I know Agile and Docker solved the problem of ever having to document anything, but this is the way things used to be and a few of us are stuck in our ways and still like it.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#15They should have stuck with bog-standard mySQL to get this time saving for free - if you restore a SQL dump created with phpMyAdmin or mysqldump then all the disable index commands are already in there, good to go, and in SQL. Whoever wrote the Django bit didn't really do a good job on the defaults.
I don't know what makes mysql more "bog standard" than postgres, but pg_dumpall takes `-F format`, one of which is standard SQL statements.
But that's not what they're dealing with. They're dealing with CSV, presumably from some external source. It'd also be faster if they were dealing with pre-formed database files that they could just rsync. But they're not.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#16Wow. You've gotta love the audacity in making a big announcement like this based on the developer finally getting around to reading the docs . "Drop constraints and indexes for faster imports" is mass import 101. The entirety of the "Why We Did It" section: ----- > This improvement was pioneered by James Gordon, the Coalition’s lead developer. > He drew instruction from PostgreSQL’s official documentation, which read…
Yeah, it's OK to not know about this, and get excited about discovering it, but to categorize it as "pioneering" is, as you say, audacious. This announcement would have been better as a minor bullet-point in the release notes. If and when someone publicly points out how much faster it is, the appropriate response would be, "heh, this is embarrassing, but we didn't know you should drop indexes before importing"
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#17I must be getting old. Kids, many years ago, even before jQuery, software would come with documentation that you could read and it would tell you how to use it effectively. I know, crazy right? But to this day some of that old software, of which PostgreSQL is an example, still has this documentation that you can read, even before you use the software in a production system. Yeah, yeah, I know Agile and Docker solved…
>> even before jQuery
If you're getting old then I must be ancient! I remember when all the software documentation had to be printed on this white stuff made out of dead trees.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#18Wow. You've gotta love the audacity in making a big announcement like this based on the developer finally getting around to reading the docs . "Drop constraints and indexes for faster imports" is mass import 101. The entirety of the "Why We Did It" section: ----- > This improvement was pioneered by James Gordon, the Coalition’s lead developer. > He drew instruction from PostgreSQL’s official documentation, which read…
Heads are going to explode when they discover the arcane rarely-utilized tool known, nay, merely whispered of, only as "transactions".
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#19It looks like the article is directed at those doing bulk uploads to their system and not at developers necessarily. This may not be universally intuitive to people outside of Database admins and developers.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#20N̶o̶ ̶s̶h̶i̶t̶,̶ ̶S̶h̶e̶r̶l̶o̶c̶k̶!̶ You don't say?
It was a bit abrupt but seriously it is kinda disheartening to read that a _lead_ developer _discovered_ such a basic thing.
Reminds 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.