Live data from Hacker News

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

californiacivicdata.org

31–40 of 49 posts

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

#31
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.

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 ?

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

#32
post #24

Earlier quoted context omitted.

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.

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

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

#33
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.

To be fair, the honest among us will admit that it's not unusual to miss something glaringly obvious for an embarrassingly long amount of time.

The difference is really in whether you recognize the issue and quietly hope no one finds out how dumb you really are, or whether you make a big celebratory blog post about the secret behind your "pioneering" work, making sure that your title and first and last name are clearly attached. And of course, we can't fail to highlight the further brilliance of accomplishing this marvelous feat by employing "rarely used, low-level" commands from within the framework's ORM.

Hold on to your butts, because next week he's going to learn that you can execute commands directly on the server, without even having to use the "low-level" elements of an ORM! I can't wait for the field to be revolutionized by Lead Developer James Gordon's next discovery.

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

#34
post #18

Wow. 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".

Just today someone asked me "what's MVCC[1]?". One of today's lucky 10,000 [2].

[1]: https://en.wikipedia.org/wiki/Multiversion_concurrency_contr... [2]: https://xkcd.com/1053/

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

#35

Earlier quoted context omitted.

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.

To be fair, the honest among us will admit that it's not unusual to miss something glaringly obvious for an embarrassingly long amount of time. The difference is really in whether you recognize the issue and quietly hope no one finds out how dumb you really are, or whether you make a big celebratory blog post about the secret behind your "pioneering" work, making sure that your title and first and last name are clear…

Maybe cut Lead Developer James Gordon some slack. He may not have wanted this attention, which came from Ben Welsh. Welsh describes himself as a "data journalist" and a "hack computer programmer". Perhaps he just thought this improvement made by the developer was really cool, and wanted to blog about it, not understanding the snark's nest he was stepping into.

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

#36

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: instead of running the full insert logic (find a target page, lock it, insert, unlock page) per row, it batches all the rows that will fit on the target page.

- COPY overall has shorter code paths than regular inserts.

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

#37
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”

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

#38
Why is everyone acting so pissy about the fact that someone happened to find a performance improvement trick by reading the docs? Isn't this what's supposed to happen?

None of you read all the performance "tricks" to Postgres before writing your first SQL statement.

Every day, somebody's born who doesn't know how to boos the performance of COPY by dropping indexes.

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

#39
post #35

Earlier quoted context omitted.

To be fair, the honest among us will admit that it's not unusual to miss something glaringly obvious for an embarrassingly long amount of time. The difference is really in whether you recognize the issue and quietly hope no one finds out how dumb you really are, or whether you make a big celebratory blog post about the secret behind your "pioneering" work, making sure that your title and first and last name are clear…

Maybe cut Lead Developer James Gordon some slack. He may not have wanted this attention, which came from Ben Welsh. Welsh describes himself as a "data journalist" and a "hack computer programmer". Perhaps he just thought this improvement made by the developer was really cool, and wanted to blog about it, not understanding the snark's nest he was stepping into.

You're right, of course. I considered mentioning the possibility that this was meant more as a test PR style or tongue-in-cheek "Haha this is obviously super important"-style post, since these are definitely viable explanations, but I felt the rant was already long enough and I couldn't find an easy way to work it in.

One of the most dangerous things about sharing stuff with others, especially isolated items from unknown authors with a worldwide audience, is that you never really know how much of their own context the recipient will read in, or how much of the assumed / pre-requisite context they'll fail to infer (or infer differently than intended).

You only get better at this through repeated practice, but you can't ever be perfect at it. Especially in a world of complex social interactions where people don't always mean what they say or say what they mean, and the lack of body language and facial expression in written language silently corrupts the signal.

All readers should always remember, it is easy to criticize. It is much harder to do. Critics especially need to remember this, because it's very easy, automatic in fact, to fall into a pattern of judgment and criticism when we're regularly exposed to so much stuff from so many sources. But it's good to get out there and try, because it's often much harder than it looks, and especially if you've been on the sidelines judging for a long time, it can be jarring how much harder it is to do than to say (or, particularly, deride).

A great way to test this: if there's some radio program you listen to regularly where callers can share a brief anecdote or story, call in. As a regular listener, you've been silently evaluating callers on a daily basis for years. You likely have some opinion about the practices of good callers and bad callers. Call in and you'll be surprised how nerve-wracking it can be, even for someone as "experienced" as yourself, and I think you'll be disappointed in your overall performance (unless you've thoroughly rehearsed ahead of time).

This is just a tiny, irrelevant thing that most people wouldn't give any thought to, a quick ~60 second phone call. You certainly don't give much thought to it every day when you dismiss anecdotes or stories told by amateurs. But try it yourself and you'll get a great deal on some perspective for the difficulty gap between doing v. criticizing.

The peanut gallery can, and will, always find something to nitpick. Don't take it personally. Use that data to hone your interactions and get a better-tuned result next time.

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

#40
post #38

Why is everyone acting so pissy about the fact that someone happened to find a performance improvement trick by reading the docs? Isn't this what's supposed to happen? None of you read all the performance "tricks" to Postgres before writing your first SQL statement. Every day, somebody's born who doesn't know how to boos the performance of COPY by dropping indexes.

Most people don’t brag about being “pioneers” for having read the docs for a basic function. That’s what’s causing all the mirth.
Post reply on HN