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?

Improve this question
3
  • 1
    I think the most drastic operation is dropping the index and recreating it. Updating/inserting data is faster without indexes
    – pietrop
    Oct 8, 2016 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.
    – Luciano Andress Martini
    Oct 10, 2016 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, 2016 at 13:01

1 Answer 1

Reset to default
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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