
Online index creation is a critical capability for modern databases, allowing you to add indexes without blocking concurrent read/write operations. This comparison examines the two leading approaches: PostgreSQL 17's CREATE INDEX CONCURRENTLY
and MySQL 8.0's Online DDL with ALGORITHM=INPLACE
.
Basic Syntax
PostgreSQL 17
CREATE INDEX CONCURRENTLY idx_email ON users (email);
MySQL 8.0
-- Basic online index (equivalent to PostgreSQL CONCURRENTLY)
CREATE INDEX idx_email ON users (email)
ALGORITHM=INPLACE, LOCK=NONE;
-- Different concurrency levels
CREATE INDEX idx_status ON users (status)
ALGORITHM=INPLACE, LOCK=SHARED; -- Blocks writes, allows reads
CREATE INDEX idx_created ON users (created_at)
ALGORITHM=INPLACE, LOCK=EXCLUSIVE; -- Fastest, blocks all access
Internal Mechanisms: How They Actually Work
PostgreSQL: CREATE INDEX CONCURRENTLY
Traditional CREATE INDEX (Non-Online):
// Simplified internal flow
DefineIndex() {
lock_table(EXCLUSIVE); // Blocks ALL operations
scan_table(); // Single scan
build_index_structure();
write_to_disk();
update_catalogs();
unlock_table();
}
CREATE INDEX CONCURRENTLY (Online):
-- Phase 1: Create "invalid" index entry (brief exclusive lock)
INSERT INTO pg_class (relname, relkind) VALUES ('idx_name', 'i');
UPDATE pg_index SET indisvalid = false WHERE indexrelid = new_index_oid;
-- Phase 2: First table scan - build initial index
...
-- Phase 3: Second table scan - catch concurrent changes
...
-- Phase 4: Wait for conflicting transactions to complete
...
-- Phase 5: Mark index "valid" (brief exclusive lock)
UPDATE pg_index SET indisvalid = true WHERE indexrelid = index_oid;
MySQL: ALGORITHM=INPLACE
Traditional ALGORITHM=COPY:
mysql_alter_table() {
create_temp_table_with_new_structure();
lock_table_metadata(SHARED_UPGRADEABLE);
copy_all_data_to_temp_table(); // 2x storage needed
apply_concurrent_changes();
lock_table_metadata(EXCLUSIVE); // Brief exclusive lock
swap_table_names();
drop_old_table();
}
ALGORITHM=INPLACE (Online):
// Phase 1: Preparation
innobase_inplace_alter_table_prepare() {
dict_mem_index_create(); // Allocate index space
row_log_allocate(); // Set up change logging
acquire_metadata_lock(SHARED_UPGRADEABLE);
}
// Phase 2: Building with change tracking
innobase_inplace_alter_table_build() {
while (has_more_records()) {
record = fetch_next_record();
index_entry = build_index_entry(record);
insert_into_index(index_entry);
if (concurrent_change_detected()) {
row_log_insert(change_info); // Log concurrent changes
}
}
}
// Phase 3: Apply changes
innobase_inplace_alter_table_commit() {
acquire_metadata_lock(EXCLUSIVE); // Brief exclusive lock
row_log_apply(); // Apply logged changes
dict_index_set_online_status(ONLINE);
release_metadata_lock();
}
Monitoring Progress
PostgreSQL 17
-- Monitor concurrent index creation
SELECT
pid,
datname,
phase,
blocks_total,
blocks_done,
ROUND(100.0 * blocks_done / blocks_total, 2) as pct_complete
FROM pg_stat_progress_create_index;
MySQL 8.0
-- Monitor online DDL progress
SELECT
event_name,
work_completed,
work_estimated,
ROUND(100 * work_completed / work_estimated, 2) as pct_complete
FROM performance_schema.events_stages_current
WHERE event_name LIKE '%alter%';
Error Handling & Recovery
PostgreSQL
-- Failed concurrent index leaves invalid index
SELECT schemaname, indexname
FROM pg_indexes
WHERE indexname LIKE '%_ccnew';
-- Manual cleanup required
DROP INDEX CONCURRENTLY idx_email_ccnew;
-- Retry
CREATE INDEX CONCURRENTLY idx_email ON users (email);
MySQL
-- Automatic rollback on failure
-- No cleanup needed, just retry with different options
-- If LOCK=NONE fails, try LOCK=SHARED
CREATE INDEX idx_email ON users (email)
ALGORITHM=INPLACE, LOCK=SHARED;
References
-
PostgreSQL 17 Documentation - Building Indexes Concurrently
https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY -
PostgreSQL 17 Documentation - Progress Reporting
https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING -
MySQL 8.0 Reference Manual - Online DDL Operations
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html -
MySQL 8.0 Performance Schema - Events Stages
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-events-stages-current-table.html