Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Boosting the performance of PostgreSQL’s COPY command by dropping indexes

californiacivicdata.org

Post reply on HN
Print view View original

21–30 of 49 posts

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#21

Post by jeremiep »

drblast wrote: post #14

I 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…

And then people wonder why Agile never produces quality results.

Intellisense has replaced the need to read the docs and Agile has replaced the need to understand what you're doing.

Its no surprise that basic knowledge found in the documentation is later "discovered" when the project is already running in production.

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#22

Post by cuchoi »

Johnny555 wrote: post #13

Is 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.

I think what the 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.

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#23

Post by derefr »

drblast wrote: post #14

I 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…

I second reading the Postgres docs. They're fabulous resources. Any time I'm attempting to apply a new SQL syntax feature I'm not 100% fluent with, I'll give the PG docs a read-through—not just for usage, but also for idiomatic examples, performance analysis, edge-cases to consider, and more.

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#25

Post by mgkimsal »

cuchoi wrote: post #22
Johnny555 wrote: post #13

Is 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.

I think what the 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.

and I think that's what what johnny555 is questioning - isn't this common knowledge already? apparently not.

here's another tip, at least on mysql, but possibly other databases that have memory tables. Import stuff in to memory tables, then insert from the memory table to a disk-based table. I took a process that was naively importing data via SQL commands which took close to 24 hours down to around 20 minutes by breaking it up, chunking imports to memory tables, then copying those to permanent disk. This was years ago (12?) and mysql is probably better about insert handling than it was, but that approach (plus the drop/recreate indexes) meant this was a smallish process vs a 24 hour import cycle.

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#26

Post by maxxxxx »

cuchoi wrote: post #22
Johnny555 wrote: post #13

Is 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.

I think what the 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.

I thought this is pretty common knowledge and I don't even do much with databases.

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#27

Post by segmondy »

cuchoi wrote: post #24
p2t2p wrote: post #9

N̶o̶ ̶s̶h̶i̶t̶,̶ ̶S̶h̶e̶r̶l̶o̶c̶k̶!̶ You don't say?

The 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.

It'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.

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#28

Post by protomyth »

cuchoi wrote: post #24
p2t2p wrote: post #9

N̶o̶ ̶s̶h̶i̶t̶,̶ ̶S̶h̶e̶r̶l̶o̶c̶k̶!̶ You don't say?

The 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.

Yep, 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.

Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes

#30

Post by karlmdavis »

From personal experience: PostgreSQL's COPY commands aren't really all that performant, indexes or no.

Our project saw SIGNIFICANTLY better performance with batched multi-threaded INSERTs. If you can run a few hundred load threads and manage the concurrency correctly (not trivial), it will chew through big loads like a monster.

If I ever have the time/excuse, I want to go back and try a multi-threaded COPY. But if you need speed and have a choice between multi-threaded INSERTs or a single-threaded COPY, go with the INSERTs every time.

Post reply on HN