Database Patterns — Series 3

Preview — 3 of 10 questions

The migration fails with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Why, and what is the fix?

javascript

export class AddOrdersCreatedAtIndex implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query('CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at)');
  }
}

AThe statement is unsupported and a plain CREATE INDEX must be used

BThe migration must be split across two files, one per statement

CConcurrent index creation requires superuser privileges

DMigration runners wrap each migration in a transaction by default, and the concurrent build must not be transactional — it makes several passes over the table while writes continue; the migration has to opt out of the wrapping transaction (transaction = false on the migration, or running it outside the runner)

What does this change for a 90-day retention policy?

javascript

CREATE TABLE events (…) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

ANothing; rows must still be deleted individually

BExpiry becomes DROP TABLE events_2026_01 — near-instant, almost no WAL, no vacuum afterwards — instead of a large DELETE that generates dead rows the database must then reclaim; queries filtered by date also skip irrelevant partitions entirely

CRetention is enforced automatically once partitions are declared

DPartitioning removes the need for indexes on the partition key

The application sets app.tenant_id per request. What has to be true for this to be safe?

javascript

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

AThe setting must be applied with SET LOCAL inside the same transaction as the queries it governs — a session-level SET leaks to whichever request next borrows that pooled connection, and under a transaction-mode pooler the connection changes between transactions anyway

BThe policy must be recreated per tenant

CRow-level security cannot be combined with connection pooling at all

DThe setting must be applied by a superuser, which the application must connect as

Answer all 10 questions (7 more), see explanations for every answer, and track your score.