AGENT SKILL

index-creation-concurrently

Index Creation (CONCURRENTLY)

License 许可
MIT
最近更新
2026-11-14
Stars
★ 18
Forks
6

01 前言 Front Matter

name
index-creation-concurrently
metadata
{ "original-index": 10, "short-description": "Index Creation (CONCURRENTLY)" }
description
Index Creation (CONCURRENTLY)

02 资源文件 1

03 SKILL.md 正文

Index Creation (CONCURRENTLY)

What

CREATE INDEX CONCURRENTLY builds an index without holding an exclusive lock on the table. Regular CREATE INDEX blocks all writes (INSERT/UPDATE/DELETE) for the duration of the build. The CONCURRENTLY variant allows writes to continue while the index is being built.

Why

On production tables with active traffic, a regular index build can cause downtime. A 100M-row table might take minutes to index, during which all writes are blocked. CONCURRENTLY eliminates this risk at the cost of a slightly longer build time.

Lock comparison (PG 16 docs: explicit-locking.html)

Mode Lock Acquired Blocks Writes? Blocks Reads?
CREATE INDEX SHARE Yes (INSERT/UPDATE/DELETE blocked) No
CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE No No

How

Basic concurrent index

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_appointmentinfo_hospitalid
    ON public."AppointmentInfo" ("HospitalId");

Covering index (INCLUDE)

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_vwr_animalid_covering
    ON analytics."events" ("entity_id")
    INCLUDE ("AnimalName", "Species", "Breed");

Important: Cannot run inside a transaction

-- This FAILS:
BEGIN;
CREATE INDEX CONCURRENTLY ix_test ON public."MyTable" ("MyCol");
COMMIT;

-- This WORKS (outside any transaction block):
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_test
    ON public."MyTable" ("MyCol");

Index Types We Used

Type Purpose Example
B-tree (default) Equality and range queries WHERE "HospitalId" = 123
Covering (INCLUDE) Avoid table lookups for common queries Index-only scans
Composite Multi-column lookups WHERE "Col1" = x AND "Col2" = y

When to Use

  • Any column used in WHERE, JOIN, or ORDER BY clauses
  • FK columns (PostgreSQL does NOT auto-create indexes on FK columns)
  • High-cardinality columns that filter large result sets

Where We Used It

  • ****: Index on AppointmentInfo.HospitalId (supporting FK)
  • ****: Index on AppointmentInfo.ProviderId (supporting FK)
  • ****: 6 high-value indexes on analytics tables (covering indexes)
  • ****: Baseline indexes on DINO_EC tables

References

Gotchas

  • CONCURRENTLY cannot run inside a DO block or explicit transaction
  • If a concurrent build fails, it leaves an INVALID index -- check with:
    SELECT indexrelid::regclass, indisvalid
    FROM pg_index
    WHERE NOT indisvalid;
    
  • IF NOT EXISTS prevents errors on re-run but does NOT rebuild invalid indexes
  • Concurrent builds take ~2x longer than regular builds (two table scans)
  • Building many indexes concurrently at once can impact performance

*Category: DDL | Origin: , *

04 同仓库更多 Skills

CarlosCaPe
CarlosCaPe/octorato

yeet

Use only when the user explicitly asks to stage, commit, push, and open a GitHub pull request in one flow using the GitHub CLI (`gh`).

★ 18
CarlosCaPe
CarlosCaPe/octorato

wrangler

Cloudflare Workers CLI for deploying, developing, and managing Workers, KV, R2, D1, Vectorize, Hyperdrive, Workers AI, Containers, Queues, Workflows, Pipelines, and Secrets Store. Load before running wrangler commands to ensure correct syntax and best practices. Biases towards retrieval from Cloudflare docs over pre-trained knowledge.

★ 18
CarlosCaPe
CarlosCaPe/octorato

workspace-skill-discovery

Discover and include ALL skills across the workspace — not just global ones. Use at the start of every session or when the user asks about available skills. Ensures project-level skills (under .claude/skills/ or skills/ in any repo) are never missed.

★ 18
CarlosCaPe
CarlosCaPe/octorato

workers-best-practices

Reviews and authors Cloudflare Workers code against production best practices. Load when writing new Workers, reviewing Worker code, configuring wrangler.jsonc, or checking for common Workers anti-patterns (streaming, floating promises, global state, secrets, bindings, observability). Biases towards retrieval from Cloudflare docs over pre-trained knowledge.

★ 18
CarlosCaPe
CarlosCaPe/octorato

winui-app

Create, build and design WinUI 3 desktop apps with C# and the Windows App SDK following Microsoft's official guidance and CommunityToolkit. Covers XAML, navigation, theming, accessibility and deployment.

★ 18
CarlosCaPe
CarlosCaPe/octorato

windows-brain-script-portability

Scripts in ~/.claude/scripts/ that run on a Windows brain: use the python3.cmd shim and not bare python3, and reconfigure stdout to UTF-8 if you print symbols. Use it when writing a brain script or triaging a Windows-only crash.

★ 18