Keeping historical data in tables forever – mission (im)possible!
summary
Tests on SQL Server use a real emergency-incident schema partitioned by incident_date_created and load 3–4 million rows in demo and anticipate 1–20M operational rows plus 50M logging rows per year. Partitioning achieves partition elimination when every query and every join includes the date key, but Entity Framework generated wide SELECT queries break that. Tried aligned vs non-aligned indexes, columnstore filtered index* for cold data, partition switch-out to archive tables, and Redgate data generator; conclusion: partitioning works for logs but is risky for operational tables unless queries and schema change.
AI-written summary · about
chapters (7)
- 0:00Start
- 2:04Data Management Strategy is needed
- 5:52Data usage and data volume
- 7:56Obvious solution - Let's Partition!
- 10:50Important rules for partitioning
- 40:21Operational Analytics Considerations Summary
- 42:23Partition Switch OUT
related talks
50mKeeping historical data in tables forever – mission (im)possible!
01h 04mFrom CRUD to Event Sourcing
27mLeveraging table partitioning for query performance and data archiving
24mDatabases beyond the tutorials
50mBeyond the How: Why Table Partitioning Truly Matters
51mDealing with Very Large Tables in SQL Server- 51mDealing with Very Large Tables in SQL Server
30mOvercoming Performance Hurdles in Postgres Partitioned Tables