Boosting the performance of PostgreSQL’s COPY command by dropping indexes
californiacivicdata.org
Boosting the performance of PostgreSQL’s COPY command by dropping indexes
1–10 of 49 posts
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#2[deleted]
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#3Do you have any numbers on how much extra time (that is, time spent servicing queries above the normal query times when your tables are indexed) application queries take after the loads, but before the index rebuilds are complete?
If so, how does that compare, in aggregate, to the time saved in the loads?
Or are you simply not putting the application back into service until the index rebuilds have finished? How long does that take, compared to the time saved?
EDIT: I'm mostly asking these questions to nudge people to think about them in the course of trying this in their own environments. It's my day job to think about these kinds of things; I've worn the PostgreSQL DBA hat for over a decade now.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#4Do you also disable autovacuum while this is running? That is another good trick for speeding up large database imports.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#5Do you have any numbers on how much extra time (that is, time spent servicing queries above the normal query times when your tables are indexed) application queries take after the loads, but before the index rebuilds are complete? If so, how does that compare, in aggregate, to the time saved in the loads? Or are you simply not putting the application back into service until the index rebuilds have finished? How long…
Yes, you would never do this unless writes were completely disallowed to your app. You will save time building the indices in one-shot.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#6coming from california finance budget data ... what are they doing ... keep your unique indexes, keeps your data safe.
stop cooking the numbers with your fast queries but duplicate data
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#7Do you have any numbers on how much extra time (that is, time spent servicing queries above the normal query times when your tables are indexed) application queries take after the loads, but before the index rebuilds are complete? If so, how does that compare, in aggregate, to the time saved in the loads? Or are you simply not putting the application back into service until the index rebuilds have finished? How long…
Yes, you would never do this unless writes were completely disallowed to your app. You will save time building the indices in one-shot.
My point is: this is not necessarily true. The Fine Article even mentions that the benefits are situational.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#8Wow. 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 reads:
>> [snipping quoted sections from PostgreSQL manual at https://www.postgresql.org/docs/10/static/populate.html#POPU... ]
>Gordon’s code handles this task using rarely utilized, low-level tools in Django’s database manager.
-----
Sadly, in the current day and age, a developer actually taking the time to RTFM may indeed qualify as "pioneering" work!
Perhaps the rest of us need to start trumpeting our accomplishments when we find some clearly-stated performance gain in the manual, rather than hiding our heads in embarrassment for not finding out until we released version 2.2 of our mass DB import tool.
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#9N̶o̶ ̶s̶h̶i̶t̶,̶ ̶S̶h̶e̶r̶l̶o̶c̶k̶!̶ You don't say?
Re: Boosting the performance of PostgreSQL’s COPY command by dropping indexes
#10They 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.