The Standard Advice and Its Hidden Costs
The prevailing wisdom in PostgreSQL administration is to always use CREATE INDEX CONCURRENTLY in production environments. This advice, while generally sound for maintaining application availability during schema changes, is often applied without a full understanding of its implications. The concurrent option allows other database operations, including writes, to proceed with minimal blocking while the index is built. However, this concurrency comes at a cost, both in terms of performance and the resources it consumes. For many scenarios, the overhead of CONCURRENTLY is unnecessary, leading to slower index creation and potentially impacting database performance when it doesn't need to.
To understand these costs, a practical setup was tested: PostgreSQL 16 with a table containing twenty million rows, occupying 1.6 GB on disk. During index builds, a secondary session continuously inserted one row every fifty milliseconds. The experiment measured the duration of the slowest insert operation, providing a ratio to illustrate the impact. While the absolute times are specific to the test hardware, the relative performance differences highlight the core issue: CREATE INDEX CONCURRENTLY is not free, and its benefits are often overlooked in favor of a blanket recommendation.
Understanding Index Creation Methods
PostgreSQL offers two primary ways to create indexes. The standard CREATE INDEX command locks the table exclusively for the duration of the index build. This means no reads or writes can occur on the table until the index is fully created. While this is the fastest method for building an index, it is often unacceptable in production systems where downtime is not an option. The exclusive lock can bring an application to a halt.
On the other hand, CREATE INDEX CONCURRENTLY builds the index in multiple passes. It first builds a shared snapshot of the table, then creates the index on that snapshot. After that, it must perform a second scan to catch any changes that occurred during the first scan, and then it applies those changes to the index. This multi-stage process significantly reduces blocking but introduces its own set of performance characteristics. The primary benefit is that it allows regular DML operations (INSERT, UPDATE, DELETE) to continue with only brief, lightweight advisory locks. However, this process is considerably slower than a standard index build, often taking several times longer. More importantly, during the concurrent build, the database must manage a more complex state, which can lead to increased CPU usage and I/O, and crucially, can slow down write operations on the table. The cost is not just time; it's also contention on writes.
The Performance Impact of CONCURRENTLY
The benchmark revealed a significant performance hit on write operations when using CREATE INDEX CONCURRENTLY. While the standard CREATE INDEX command blocks all writes, its impact on ongoing transactions is limited to the duration of the lock. CREATE INDEX CONCURRENTLY, however, can cause write operations to stall for much longer periods, even if they are not strictly blocked by an exclusive lock. During the concurrent index build, PostgreSQL must perform additional work to ensure the index is consistent with the latest state of the table. This includes tracking changes and revalidating the index. This extra work can saturate I/O and CPU resources, making it harder for write operations to complete quickly.
The test data showed that while inserts were theoretically possible, the slowest insert operation experienced a substantial delay. This indicates that the system was under strain, and write throughput was significantly degraded. In a real-world scenario, this could translate to user-facing latency, failed transactions, or timeouts, depending on the application's error handling and retry mechanisms. The ratio of the slowest insert time to a baseline insert time (when no index is being built) is a critical metric. If this ratio is high, it suggests that CREATE INDEX CONCURRENTLY is causing more harm than good for write-heavy applications.
When is CONCURRENTLY Necessary?
The decision to use CREATE INDEX CONCURRENTLY should not be automatic. It is a tool designed to mitigate downtime for applications that cannot tolerate exclusive table locks. If your application can afford a brief period of unavailability for a standard CREATE INDEX operation—perhaps during a scheduled maintenance window or a low-traffic period—then the standard command is likely the better choice. It will be faster and less resource-intensive, and the impact on write performance during the build will be concentrated into a shorter, predictable window.
Consider the characteristics of your application and its tolerance for downtime. For batch processing jobs that run overnight, a standard CREATE INDEX might be perfectly acceptable. For critical, always-on services, CREATE INDEX CONCURRENTLY is essential. The key is to perform this analysis rather than defaulting to the concurrent option. Half the time, the argument for CONCURRENTLY is based on a fear of downtime that doesn't align with the actual operational requirements or the acceptable maintenance windows. The cost of CONCURRENTLY, measured in slower writes and increased resource utilization, is a real penalty that should be avoided when not strictly necessary.
Alternatives and Best Practices
Before resorting to CREATE INDEX CONCURRENTLY, evaluate the actual downtime tolerance of your application. Can you schedule schema changes during off-peak hours? If so, a standard CREATE INDEX is faster and more efficient. If downtime is truly not an option, CREATE INDEX CONCURRENTLY is your best bet, but be prepared for its performance implications. Monitor your write performance closely during the build. If you observe significant degradation, consider if the trade-off is worth it.
Another consideration is the size and type of index being created. For very small tables or simple indexes, the overhead of CONCURRENTLY might be disproportionately large compared to the time saved on blocking. For complex, multi-column, or expression indexes, the build time can be substantial, making the choice between blocking and concurrency more critical. Always test schema changes in a staging environment that closely mirrors your production setup to understand the real-world impact before deploying them.