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

41–49 of 49 posts

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

#41

Post by imtringued »

davidgould wrote: post #36
karlmdavis wrote: post #30

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

COPY of course is single threaded. If you can split your input data and run multiple copy operations you can get similar increases although I would never suggest hundreds of threads. Depending on your IO system something like twice as many threads as CPU threads is probably going to work better. On a one for one basis COPY IN will be faster than inserts: - COPY uses a special optimization in the access method: instea…

Why not just use regular batched inserts with unnest to turn several array parameters into a table instead of using some arcane hard to use SQL command?

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

#42

Post by adrianmonk »

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.

It's weird but doing less work requires less time. Who'd have thought that?

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

#43

Post by adrianmonk »

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.

"Batch-mode processing is always more efficient."

Not sure where I first heard that, but it applies here. Essentially it is almost the same thing as saying that computers are often set up to exploit economies of scale.

Thus building an index all at once after a large set of changes are made is more efficient than incrementally updating an index as each change is made.

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

#45

Post by gaius »

adrianmonk wrote: post #43
cuchoi wrote: post #22

Earlier quoted context omitted.

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.

"Batch-mode processing is always more efficient." Not sure where I first heard that, but it applies here. Essentially it is almost the same thing as saying that computers are often set up to exploit economies of scale. Thus building an index all at once after a large set of changes are made is more efficient than incrementally updating an index as each change is made.

It is more accurate to say that sequential I/O is always more efficient than random - even if there is no physical seek time anymore

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

#46

Post by C14L »

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.

https://xkcd.com/1053/

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

#47

Post by mgkimsal »

mbrumlow wrote: post #31
mgkimsal wrote: post #25

Earlier quoted context omitted.

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

> apparently not. Just becasue one blog post making it seem that way makes it not known ?

not just because of that - cuchoi also seemed to imply that.

relatedly/anecdotally, I still run in to people who aren't aware of this, as they don't understand what happens when you're inserting data in to a database or what indexes are in the first place.

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

#48

Post by whistlerbrk »

gaius wrote: post #37
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…

This is where the rampant ageism in the industry has lead us; millennials who expect a prize for doing something totally obvious to anyone with experience, who was never considered for the job because they were “too old”

I understand your sentiment, but I think your wording is a bit mean spirited. I don't think this person "expects a prize", nor do I think this is a trait that can be applied to millenials as a whole. My sister who has hired literally a hundred people bucketed as "millenials" has had the exact opposite experience in dealing with them than you describe. She thinks they are some of the hardest working people she has ever worked with. But we digress.

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

#49

Post by adrianmonk »

gaius wrote: post #45
adrianmonk wrote: post #43

Earlier quoted context omitted.

"Batch-mode processing is always more efficient." Not sure where I first heard that, but it applies here. Essentially it is almost the same thing as saying that computers are often set up to exploit economies of scale. Thus building an index all at once after a large set of changes are made is more efficient than incrementally updating an index as each change is made.

It is more accurate to say that sequential I/O is always more efficient than random - even if there is no physical seek time anymore

The reasons why batch mode processing can be more efficient go beyond just sequential I/O.

If I do a little of something now and a little later and yet more even later, I will probably have to deal with caches that don't have my data in them because other things happen in the intervening time. If I do it all at once, then a lot of the work benefits from already-warm caches. (This can apply to disk caches, CPU data caches, and even instruction caches.)

Not to mention that sometimes batch mode processing opens up opportunities to use a more efficient algorithm (even if sometimes just by a constant factor). For example, if you maintain an index in a balanced tree and keep adding to it piecemeal, you do extra work continually rebalancing that tree. Whereas in theory if you built an index all at once, you could collect all the data, sort it using some kind of fast sort like mergesort, and then write out a final tree which is already balanced as desired and doesn't need to be rearranged as data comes in in random order.

Post reply on HN