Can my DB?

-- Build an index without locking writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Concurrent unique index
CREATE UNIQUE INDEX CONCURRENTLY idx_users_unique_email ON users (email);

-- Concurrent partial index
CREATE INDEX CONCURRENTLY idx_active_users ON users (email) WHERE is_active = true;

-- Rebuild an existing index concurrently (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- If a concurrent build is aborted, an INVALID index remains - drop it:
SELECT indexname, indexdef
FROM   pg_indexes
JOIN   pg_index ON pg_index.indexrelid = (SELECT oid FROM pg_class WHERE relname = indexname)
WHERE  tablename = 'users'
  AND  NOT pg_index.indisvalid;

-- Drop the invalid index and retry
DROP INDEX CONCURRENTLY idx_users_email;

• CREATE INDEX CONCURRENTLY performs two table scans and waits for existing transactions to finish between passes, making it slower than a regular CREATE INDEX.
• Cannot be run inside a transaction block - it must be a standalone statement.
• If the build is aborted (e.g., due to a unique constraint violation or cancelled), an INVALID index is left in the catalog and must be dropped manually before retrying.
• REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds an existing index non-disruptively.
• On a physical replica, concurrent index builds require wal_level = logical on the primary.

-- Online DDL: create index with no DML lock (default for most operations)
CREATE INDEX idx_email ON users (email)
  ALGORITHM=INPLACE, LOCK=NONE;

-- Explicit online index creation
ALTER TABLE users ADD INDEX idx_email (email),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Check what lock level an operation will require:
ALTER TABLE users ADD INDEX idx_email (email),
  ALGORITHM=INPLACE, LOCK=NONE;  -- fails if LOCK=NONE is not supported

-- Monitor online DDL progress
SELECT event_name, work_completed, work_estimated
FROM   performance_schema.events_stages_current
WHERE  event_name LIKE 'stage/innodb/alter%';

• InnoDB online DDL (introduced in MySQL 5.6) allows most index creation operations with ALGORITHM=INPLACE, LOCK=NONE.
• ALGORITHM=INPLACE rebuilds the index in-place without copying the table; LOCK=NONE allows concurrent DML.
• Some operations still require LOCK=SHARED or LOCK=EXCLUSIVE (e.g., adding a primary key to a table without one).
• MySQL does not have an explicit CONCURRENTLY keyword; online behavior is controlled by ALGORITHM and LOCK clauses.
• Performance Schema events_stages_current tracks in-progress online DDL operations.

-- Online index creation (InnoDB)
ALTER TABLE users ADD INDEX idx_email (email),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Create index statement form
CREATE INDEX idx_email ON users (email)
  ALGORITHM=INPLACE, LOCK=NONE;

-- Check if the operation supports LOCK=NONE before running
-- (omit it to let MariaDB choose, or use LOCK=DEFAULT)

• InnoDB online DDL works the same as MySQL - most index creation operations support ALGORITHM=INPLACE, LOCK=NONE.
• Aria storage engine tables require metadata locks during index operations.
• Some operations (e.g., adding a primary key) may still require a table rebuild.
• MariaDB 10.0+ supports most online DDL operations that InnoDB in MySQL 5.6+ supports.

-- Build an index online (Enterprise Edition)
CREATE INDEX idx_email ON users (email) ONLINE;

-- Rebuild an existing index online
ALTER INDEX idx_email REBUILD ONLINE;

-- Monitor index build progress
SELECT opname, target, sofar, totalwork, units,
       ROUND(sofar/NULLIF(totalwork,0)*100, 1) AS pct_done
FROM   v$session_longops
WHERE  opname LIKE 'Create Index%'
  AND  sofar < totalwork;

• The ONLINE keyword allows DML operations to continue during index creation on Oracle Enterprise Edition.
• Available since Oracle 8i for regular B-Tree indexes; not available for bitmap indexes, domain indexes (Text, Spatial), or some clustered tables.
• Online index build is slower than offline because it must track DML changes during the build.
• Not available in Oracle Standard Edition.
• ALTER INDEX ... REBUILD ONLINE rebuilds an existing index without downtime.

-- Online index creation (Enterprise Edition)
CREATE INDEX idx_email ON users (email)
  WITH (ONLINE = ON);

-- Online index rebuild
ALTER INDEX idx_email ON users REBUILD WITH (ONLINE = ON);

-- Resumable online index build (SQL Server 2017+)
CREATE INDEX idx_email ON users (email)
  WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

-- Pause a resumable index operation
ALTER INDEX idx_email ON users PAUSE;

-- Resume a paused index operation
ALTER INDEX idx_email ON users RESUME WITH (MAX_DURATION = 120 MINUTES);

-- Check progress of online index operations
SELECT * FROM sys.index_resumable_operations;

• ONLINE = ON requires Enterprise Edition or Developer Edition (for testing).
• ONLINE = ON allows reads and writes to continue during index creation and rebuild, using a versioning mechanism.
• RESUMABLE = ON (SQL Server 2017+) allows pausing and resuming long-running index operations, useful for maintenance windows.
• Online index builds are slower than offline builds and use more resources.
• sys.index_resumable_operations shows paused resumable index builds.
• Azure SQL Database supports ONLINE = ON for all service tiers.

-- SQLite requires a write lock during index creation.
-- In WAL mode, readers are not blocked but writers are:
PRAGMA journal_mode=WAL;

-- CREATE INDEX acquires a write transaction:
CREATE INDEX idx_email ON users (email);

-- There is no CONCURRENTLY or ONLINE keyword available.

• SQLite acquires a database-level write lock during CREATE INDEX, blocking concurrent writers.
• In WAL (Write-Ahead Logging) mode, readers are not blocked by a write transaction, but concurrent writers are still serialized.
• SQLite is designed for embedded use cases; long-running concurrent workloads are not its primary target.
• For production systems with heavy write concurrency, a client-server database is recommended.

-- Redshift has no user-defined indexes to build.
-- Sort key changes require a table rebuild (VACUUM FULL or ALTER TABLE):
ALTER TABLE orders ALTER SORTKEY (customer_id, order_date);

-- VACUUM SORT ONLY re-sorts data to match the sort key:
VACUUM SORT ONLY orders;

• Redshift has no user-managed B-Tree indexes, so concurrent index building is not applicable.
• Changing sort keys on a large table requires a full table re-sort via VACUUM or ALTER TABLE, which can be disruptive.
• Automatic VACUUM in Redshift handles sort and delete reclaim in the background.

-- Allow write access during index creation
CREATE INDEX idx_email ON users (email)
  ALLOW WRITE ACCESS;

-- Allow read-only access during index creation (more restrictive)
CREATE INDEX idx_email ON users (email)
  ALLOW READ ACCESS;

-- Rebuild index online
REORG INDEXES ALL FOR TABLE users ALLOW WRITE ACCESS;

• ALLOW WRITE ACCESS permits DML operations to continue during index creation.
• ALLOW READ ACCESS permits read-only queries during index creation but blocks writes.
• Online index builds are supported in DB2 LUW but availability of specific options may vary by edition.
• REORG INDEXES rebuilds all indexes on a table and supports ALLOW WRITE ACCESS for online operation.

-- Snowflake has no user-managed index builds.
-- Cluster key changes are applied asynchronously in the background:
ALTER TABLE orders CLUSTER BY (customer_id, order_date);

-- Clustering is applied asynchronously without blocking DML:
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(customer_id, order_date)');

-- Search Optimization is also a background operation:
ALTER TABLE orders ADD SEARCH OPTIMIZATION;

• Snowflake has no traditional index build operations that could block reads or writes.
• Cluster key application and Search Optimization indexing are background processes that do not block DML.
• All Snowflake DDL operations are metadata-only and instantaneous from the user's perspective.
• Multi-cluster warehouses handle concurrent queries without locking.

-- BigQuery DDL is metadata-only and non-blocking.
-- Search Index creation is a background managed operation:
CREATE SEARCH INDEX idx ON mydataset.orders (ALL COLUMNS);

-- Check Search Index status
SELECT * FROM mydataset.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE  table_name = 'orders';

• BigQuery is serverless - there are no table-level locks from the user's perspective.
• Search Index creation is managed by BigQuery in the background and does not block queries or DML.
• Table creation (CREATE TABLE AS SELECT) runs as a background job; queries against the table can proceed once it completes.
• Clustering and partitioning are applied at write time and do not require a separate index build step.