16

I've a table which will contain around ~15 Million records. Now I need to add an index to the table.

Adding an index will take some time to update every entry in the table.

I'm quite confused whether adding the index will cause downtime.

If yes, then how can I overcome the downtime?

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
Surender Thillainathan
  • 263
  • 2
  • 8

1 Answers1

14

With plain CREATE INDEX, the table will be locked for writes but not reads.

Use CREATE INDEX CONCURRENTLY to avoid write locks as well.

From the PostgreSQL docs on CREATE INDEX:

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently.

And more specifically (Like @ypercube commented):

PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete. However, since it allows normal operations to continue while the index is built, this method is useful for adding new indexes in a production environment.

Bold emphasis mine.

Community
  • 1
Fabrizio Mazzoni
  • 1,976
  • 5
  • 22
  • 32