Lessons learned from refactoring multi terabyte databases for the last 10 years
summary
Ten years of refactoring multi‑terabyte databases yields concrete rules and examples: use IDEs (Visual Studio, DataGrip finds dynamic SQL references), keep tests for every external interface, deploy with ordered versioned scripts, and prefer small incremental schema changes. Real cases: tens–hundreds TB systems, a 3 TB DB with 600–800 GB tables and only 400 GB free, rebuilds needing ~600 GB, a one‑time migration that ran three days and finished two hours before reopen. Techniques: partitioning, fill factor ~80–90%, page_log hint removes ghost records, parallel runs and event logs (Kafka) for replay, moving 35% of columns can buy ~50% more cache, and dropping one little‑used index saved ~40% of DB size.
AI-written summary · about
chapters (19)
- 0:00Intro
- 2:26Development Tools
- 3:39SQL Server Features Which Helps Refactoring
- 6:07User Access Documentation
- 6:57Document Interfaces Through Tests
- 8:51Step-By-Step Examples
- 11:37Deploy Scripts Versioning
- 12:41Benefits of Deploy Scripts
- 14:08Sandboxes
- 17:09An Event Log Before Database
- 23:23Internal Fragmentation
- 27:44Data Migration/Synchronization Techniques
- 33:21Parallel Run
- 36:13External API Pros
- 39:30Constraints
- 40:32Split Database First
- 40:46Split Code First
- 47:04Replace One-To-Many With Associative Table
- 48:59Data Exchange
related talks
54mLessons learned from refactoring multi terabyte databases for the last 10 years- 54mLessons learned from refactoring multi terabyte databases for the last 10 years
28mSmall Data: Databases in the real world
51mDealing with Very Large Tables in SQL Server
24mDatabases beyond the tutorials
31mModernizing a megascale database
38mEfficient Reliable Database Migration: A Legacy Innovation Story
01h 12mLegacy Refactoring