Hear me out - Don't go offline because of your index deployment strategy
summary
A deployment drops an index then creates a new offline one and the system’s CPU and wait times spike while the table locks; backups every 15 minutes stop truncating the log and live traffic suffers. Use DROP_EXISTING with same name or build side‑by‑side, enable ONLINE = ON to avoid table locks, set RESUMABLE = ON with MAX_DURATION to pause/resume (default 1 day, max 7 days) and test data compression (one client sees ~50% page savings).
AI-written summary · about
chapters (15)
- 0:00Index deployment risks
- 1:24Index deployment disaster case
- 2:51Original deployment script flaws
- 3:35Deployment options
- 5:01DROP EXISTING method
- 7:13Data compression benefits
- 9:23ONLINE deployment trade-offs
- 11:13MAXDOP setting
- 11:53RESUMABLE index creation
- 14:27MAXDURATION with resumable
- 15:09LOW_PRIORITY option
- 16:37Correcting deployment approach
- 17:41Recommended strategy
- 18:01Database config for indexes
- 19:05Recovery benefits
related talks
20mHear me out - Don't go offline because of your index deployment strategy
34mZero Downtime Database Migrations and Deployments
32mLow Latency Serving of Offline Data: Efficient, Safe, and Reliable Data...
40mrails db:migrate:safely
35mDatabase migrations without downtime
43mWhat Timescale's Approach to Dropping Chunks Tells Us About Concurrent DDL
46mEdge Performance with In memory NoSQL
33mDatabase Schema Migrations with Zero Downtime