Indexes

Why indexes?

Traditional

index creation

Traditional

How the code looks like

Traditional

Demo

Concurrent

index creation

Concurrent

How the code looks like

Concurrent

Demo

Concurrent

Challenges

• Performance Overhead: 

  • Concurrency means more resource consumption (if needed)

  • ​Extra scans: it takes longer

• Operational Caveats:

  • CREATE INDEX CONCURRENTLY doesn't like transaction.

  • One concurrent index creation per table is allowed at a time.

  • Deadlocks... deadlocks can happen...

Concurrent

Challanges

• Performance Overhead:

  • Make sure you have enough CPU and disk space

• Operational Caveats:

  • Do not run many complex migration at once when creating indexes

Enjoy!