ARGUS A27

ARGUS-A27: NON_CONCURRENT_INDEX_CREATION

Meta Field	Specification
Rule Code	ARGUS-A27
Identifier	NON_CONCURRENT_INDEX_CREATION
Severity	CRITICAL
Category	Database Schema Migration, Zero-Downtime DDL & Concurrency Availability
Analysis Layer	Layer 1 - Pure SQL-AST Migration Analysis
CWE Mapping	CWE-662: Improper Synchronization, CWE-400: Uncontrolled Resource Consumption
OWASP ASVS	OWASP ASVS v4.0.3/v5.0 §V1.4.3 (Architecture & Zero-Downtime Upgrades)
PostgreSQL Target	PostgreSQL Concurrent Indexing Architecture §13.3, SHARE Table Lockout & Production Write Outage Prevention
Default Status	enabled

---

1. Executive Summary & Architectural Invariant

Creating secondary indexes on existing database tables in migration files (db/migrations/) must use the CREATE INDEX CONCURRENTLY clause and must not execute within an active transaction block.

┌─────────────────────────────────────────────────────────────────────────────┐
│                            ARCHITECTURAL INVARIANT                          │
│                                                                             │
│  Adding indexes to existing production tables MUST use `CONCURRENTLY`.      │
│                                                                             │
│  Standard `CREATE INDEX` acquires an exclusive `SHARE` table lock that      │
│  blocks all concurrent `INSERT`, `UPDATE`, and `DELETE` queries, causing    │
│  severe production outages during large table re-indexing.                  │
│                                                                             │
│  Exception: Tables newly created within the SAME migration file are exempt  │
│  because the new table is empty and has zero production traffic.            │
└─────────────────────────────────────────────────────────────────────────────┘

---

2. Threat Mechanics & Engine Reality (PostgreSQL 18)

┌─────────────────────────────────────────────────────────────────────────────┐
│                 THE PRODUCTION WRITE OUTAGE LOCK DISASTER                   │
│                                                                             │
│  Table `audit_logs` has 10,000,000 rows. Live production traffic.          │
│                                                                             │
│  Case A: Standard CREATE INDEX without CONCURRENTLY (VIOLATION):            │
│  CREATE INDEX idx_logs_created ON audit_logs (created_at);                  │
│  ├─► Acquires `SHARE` lock on table `audit_logs`                            │
│  ├─► `INSERT INTO audit_logs` blocked waiting for index build (15 min hang!)│
│  ├─► Connection pool exhausted within 3 seconds                             │
│  └─► SEV-1 OUTAGE: All write operations fail across entire platform!        │
│                                                                             │
│  Case B: Using CREATE INDEX CONCURRENTLY (COMPLIANT):                       │
│  CREATE INDEX CONCURRENTLY idx_logs_created ON audit_logs (created_at);     │
│  ├─► Acquires `SHARE UPDATE EXCLUSIVE` lock only                            │
│  ├─► Does NOT conflict with `ROW EXCLUSIVE` (`INSERT`, `UPDATE`, `DELETE`)  │
│  └─► Zero-Downtime: App continues writing normally while index builds!      │
└─────────────────────────────────────────────────────────────────────────────┘

2.1. The SHARE Lock Conflict Matrix

According to PostgreSQL table locking rules (§13.3):

• CREATE INDEX (without CONCURRENTLY) acquires a SHARE lock.
• SHARE locks conflict directly with ROW EXCLUSIVE locks (required for all INSERT, UPDATE, DELETE).
• While PostgreSQL builds the index B-tree (which may take minutes on multi-gigabyte tables), all application writes queue up and time out.

2.2. Zero-Downtime Indexing with CONCURRENTLY

• CREATE INDEX CONCURRENTLY only acquires a SHARE UPDATE EXCLUSIVE lock.
• SHARE UPDATE EXCLUSIVE does not conflict with ROW EXCLUSIVE.
• PostgreSQL runs a two-pass table scan in the background, allowing uninterrupted application read and write operations.

---

3. Architecture & Execution Flow

flowchart TD
    A["Migration File (*.up.sql)"] --> B["Extract All IndexStmt Nodes via pg_query_go"]
    B --> C{"Was Table Created in the SAME Migration File?"}
    C -- "Yes (Brand New Empty Table)" --> D["PASS (Exempt)"]
    C -- "No (Existing Table)" --> E{"Does IndexStmt Have 'CONCURRENTLY' Flag?"}
    E -- "Yes" --> F["PASS (Zero-Downtime Compliant)"]
    E -- "No" --> G["FAIL: ARGUS-A27 Non-Concurrent Index Creation (CWE-662)"]

Loading

---

4. Detection Logic & Rule Anatomy

1. Table Creation Inventory: Scans all CreateStmt nodes in the migration file to register newly created tables.
2. IndexStmt Verification: Inspects each IndexStmt node:
   • If the target table is listed in the current file's new table inventory $\rightarrow$ Exempt.
   • If the target table is an existing table and indexStmt.Concurrent == false $\rightarrow$ Report Critical Violation.
3. Exemptions: Suppressed via -- argus:ignore ARGUS-A27 <reason>.

---

5. Code Examples Matrix

Non-Compliant (Locking Existing Production Table)

-- VIOLATION: Non-concurrent index on existing populated table
CREATE INDEX idx_users_email ON users (email);

-- VIOLATION: Non-concurrent composite index
CREATE INDEX idx_orders_created_status ON orders (created_at, status);

---

Compliant (Zero-Downtime Indexing)

-- COMPLIANT: Concurrent index creation on existing table
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- COMPLIANT: Standard index on newly created table in same migration
CREATE TABLE IF NOT EXISTS notification_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(150) NOT NULL
);

-- Permitted without CONCURRENTLY because table is empty and not live
CREATE INDEX idx_notification_templates_code ON notification_templates (code);

---

6. Mitigation & Remediation Guide

1. Add CONCURRENTLY:

   CREATE INDEX CONCURRENTLY idx_name ON table_name (column_name);

2. Handle Transactional Migrations: PostgreSQL forbids CREATE INDEX CONCURRENTLY inside transaction blocks. Ensure migration runners (e.g. golang-migrate) run index creation scripts outside transaction blocks (-- argus:notransaction).

---

7. Configuration & Suppression Directives

Configuration in .argus.yaml

rules:
  ARGUS-A27:
    enabled: true

Inline Ignore Directives

-- argus:ignore ARGUS-A27 isolated offline maintenance migration
CREATE INDEX idx_legacy_archive ON archive_data (archive_date);