CREATE INDEX CONCURRENTLY is not free, and half the time you do not need it

The conventional wisdom in database management is to always create indexes concurrently in production environments. However, this advice can be misleading as it fails to account for the costs and unnecessary overhead that concurrent index builds can introduce. This report provides concrete evidence and practical guidance on when to use the concurrent form of index creation and when the simpler, non-concurrent approach is more appropriate.

The experiment was conducted on a PostgreSQL 16 environment with a table containing twenty million rows, totaling 1.6 GB of disk space. While the actual execution times are small due to the use of a laptop, the relative performance differences are the focus. The benchmark table was populated with 20 million rows, each containing an ID, customer ID, status, total amount, and creation timestamp.

When comparing the build time and the worst-case insert wait time for both standard and concurrent index builds, the results reveal a stark contrast. The plain index build took 4.4 seconds, during which a second session could insert records at a rate of one every 50 milliseconds. In contrast, the concurrent build took 7.3 seconds, but the slowest insert only had to wait for an eighth of a second.

The critical observation is that the concurrent build never blocked writes for more than an eighth of a second, whereas the standard build blocked every insert, update, and delete during its entire duration.

The concurrent build incurs a higher price tag in terms of build time, taking twice as long to complete as the standard method. This additional time is due to the need for the concurrent process to scan the table twice: once to build the index and once to account for any changes during the first scan. Furthermore, the concurrent build must wait at the end for any transactions that were open when it started, potentially causing significant delays in a busy environment.

When considering the trade-offs, it is essential to assess the specific circumstances of each situation. For tables with frequent writes, such as a checkout, signup, or webhook handler system, the concurrent build is the better choice. It minimizes the impact on write operations, ensuring that user experience remains smooth. However, for new tables, small tables with minimal traffic, or during maintenance windows where writes are already stopped, the standard build is more efficient and simpler to implement.

A critical aspect of index creation is the potential for failure. If a concurrent build encounters issues like deadlocks, unique violations, or cancelled statements, it leaves behind an invalid index. This invalid index can cause subsequent migrations to fail silently, resulting in unexpected errors and requiring manual intervention to clean up the debris.

Regularly checking for invalid indexes using a query such as "SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;" can help maintain database health and prevent these hidden issues from causing further problems.

In conclusion, the decision to use CREATE INDEX CONCURRENTLY should be based on the specific context and requirements of the database environment. While the concurrent form offers advantages in certain scenarios, it also carries costs and risks that must be carefully considered. By understanding the trade-offs and implementing appropriate checks and maintenance routines, database administrators can make informed decisions that optimize performance and minimize potential pitfalls.