1️⃣ “No CREATE INDEX without CONCURRENTLY (Postgres) if table is large”

What actually happens in Postgres

CREATE INDEX (default)

• Takes a SHARE LOCK on the table
• Blocks writes (INSERT / UPDATE / DELETE)
• On large tables → lock can be held seconds to minutes
• Any blocked query piles up → latency spikes → request timeouts

Result in prod:

• API threads block
• Connection pool saturates
• Kubernetes sees liveness/readiness failures
• Pods restart → cascade failure

📌 Interview phrase:

“CREATE INDEX without CONCURRENTLY blocks writes and can cause an outage on large tables.”

---

What CREATE INDEX CONCURRENTLY changes

CREATE INDEX CONCURRENTLY idx_user_email ON users(email);

Behavior:

• Does not block writes
• Builds index in multiple phases
• Uses weaker locks
• Safe during live traffic

Trade-offs:

• Slower
• Uses more IO
• Not transactional (important!)

But:

Slower is fine; downtime is not.

Why this rule exists

On a table with:

• millions of rows
• frequent writes

A normal index create = production outage risk

That’s why teams enforce:

“CONCURRENTLY or explain why not.”

Important nuance (senior detail)

You cannot do this inside a transaction:

BEGIN;
CREATE INDEX CONCURRENTLY ...; -- ❌ ERROR
COMMIT;

Which leads directly to rule #2.

2️⃣ “No non-transactional DDL in the same deployment step (where it matters)”

This rule is about partial failure safety.

---

What is non-transactional DDL (Postgres examples)

• CREATE INDEX CONCURRENTLY
• DROP INDEX CONCURRENTLY
• VACUUM
• ALTER TYPE ... ADD VALUE (older PG)
• Some ALTER TABLE variants

These:

• Cannot be rolled back
• May partially apply
• Leave DB in an intermediate state on failure

---

Why mixing them is dangerous

Bad migration example

BEGIN;

ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;
CREATE INDEX CONCURRENTLY idx_users_email_verified ON users(email_verified);

COMMIT;

Outcome:

• First statement runs
• Second fails immediately (syntax rule)
• Migration aborts
• Schema is partially applied
• Flyway/Liquibase may mark it failed

Now you have:

• Half-applied schema
• App + DB potentially inconsistent
• Manual cleanup required

Even worse: mixing with app deployment

If you deploy app + migration together:

• App starts expecting index / column
• Index build is still running or failed
• Performance degrades or errors appear
• Rollback is impossible

📌 Interview phrase:

“Non-transactional DDL breaks the all-or-nothing guarantee.”

Correct pattern (what seniors do)

Separate steps

1. Transactional schema change

ALTER TABLE users ADD COLUMN email_verified BOOLEAN;

Deploy app (still backward-compatible)

Non-transactional operation

CREATE INDEX CONCURRENTLY idx_users_email_verified ON users(email_verified);

Often:

• Separate migration
• Separate deploy
• Or even separate maintenance window

How Flyway/Liquibase enforce this

Flyway

• Detects non-transactional statements
• Executes migration outside a transaction
• You must design ordering carefully

Liquibase

• runInTransaction="false"
• Signals: “this change is dangerous”

Interview tip:

“If a migration isn’t transactional, I isolate it.”

Why CI enforces these rules

These rules are not theoretical:

• They come from real outages
• They’re guardrails to protect production

So CI blocks:

• CREATE INDEX without CONCURRENTLY
• Mixed transactional + non-transactional changes

Because:

Humans forget; automation doesn’t.

Interview-ready explanation (30 seconds)

“In Postgres, creating an index without CONCURRENTLY takes a write-blocking lock, which can cause outages on large, busy tables. Using CONCURRENTLY avoids blocking but is non-transactional, so it can’t be safely mixed with transactional DDL. That’s why we separate these changes into different steps or migrations to preserve atomicity and avoid partial failures during deployment.”