Postgresql – Disable indexes on Amazon RDS PostgreSQL instance

amazon-rdsindexperformancepostgresql

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?

Best Answer

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
Related Question