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
- 参考文档skill.json155 B
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
- PostgreSQL Best Practices -- see "Indexing strategy (actual usage)" and Backlog #5 (Advanced indexes)
- Schema Optimization Appendix -- and index decisions
Gotchas
CONCURRENTLYcannot 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 EXISTSprevents 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
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`).
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.
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.
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.
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.
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.