1

If I correctly understand according to this advice: https://stackoverflow.com/questions/6146024/is-it-possible-to-temporarily-disable-an-index-in-postgres

it is possible to temporary disable indexes in all catalogue in order to increase performance of huge number of insert operations:

update pg_index
set indisready = false
<where ... >

But there is a problem: I run my instance on Amazon RDS and I have user granted access only to my particular schema. It is not granted to perform updates on pg_index table. Other user is rdsadmin which is internal Amazon's system user (according to this explanation: https://serverfault.com/questions/587695/what-is-the-rdsadmin-user-and-should-i-care ).

Are any other ways increase speed of huge number of inserts operation or disable indexes in this case?

  • 1
    I think the most drastic operation is dropping the index and recreating it. Updating/inserting data is faster without indexes – pietrop Oct 8 '16 at 9:33
  • "updating or insert data is fastest", that is not true, cause in most of the cases in real world, update or insert will depend in another operations that depends on indexes so much, even the external data loading, like file loading based, specially if that files depends on some existent data on the database. For example, updating a table without indexes on their children foreign keys will be "impossible" or too slow in some environments, if the data to be updated is referred by the children tables. – 777Anon Oct 10 '16 at 16:55
  • @777Anon obviously :) But pure INSERT and UPDATE will benefit when loading a lot of data. The docs say are quite exhaustive about this topic: postgresql.org/docs/current/static/populate.html – pietrop Oct 11 '16 at 13:01
1

As @pietrop already said: it's a good pratice drop de index and recreate it after the data load.

If you are loading a lots of data, use COPY[1] instead of INSERTs. Consider using PGLoader[2] on huge data load environments.

Hope it helps.

References:

  1. https://www.postgresql.org/docs/current/static/sql-copy.html
  2. http://pgloader.io

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.